FICO
FICO Xpress Optimization Examples Repository
FICO Optimization Community FICO Xpress Optimization Home
Back to examples browserPrevious exampleNext example

Formulation of SQL (selection) statements

Description
If some advanced SQL functionality is required when accessing a data source through an ODBC or OCI connection it will in general be necessary to formulate the corresponding SQL statements directly in the Mosel model instead of using the corresponding IO driver in initializations blocks. We show here two examples:
  1. Selection statements using columns in inverted order (ODBC: odbcinv.mos, Oracle: ociinv.mos)
  2. SQL with conditional selection statements and functions (ODBC: odbcselfunc.mos, Oracle: ociselfunc.mos)
  3. Retrieving information about database table structure: list of database tables, list of field names, primary keys (odbcinspectdb.mos)
  4. Implementing 'stop on error' behaviour for SQL statements (odbcchkstatus.mos)
Further explanation of this example:

odbcsel.zip[download all files]

Source Files
By clicking on a file name, a preview is opened at the bottom of this page.
odbcinv.mos[download]
odbcselfunc.mos[download]
odbcchkstatus.mos[download]
ociinv.mos[download]
ociselfunc.mos[download]
odbcinspectdb.mos[download]

Data Files
multicol.mdb[download]
multicol.sqlite[download]
ocidata.dat[download]
odbcsel.mdb[download]
odbcsel.xls[download]
odbcsel.sqlite[download]




odbcchkstatus.mos

(!******************************************************
   Mosel Example Problems
   ======================

   file odbcchkstatus.mos
   ``````````````````````
   Implementing 'stop on error' behaviour for SQL statements.
   
   (c) 2020 Fair Isaac Corporation
       author: S. Heipcke, Feb. 2020, rev. Aug. 2023
*******************************************************!)
model "Check SQL status"
  uses "mmodbc", "mmsystem"
  
  parameters
    DB="mytest.sqlite"
  end-parameters
  
  forward procedure checksqlstatus
  forward procedure createdb

  declarations
    A: array(string) of real
  end-declarations
  A::(['a','ab','b'])[1.5,2,2.5]

  setparam("SQLverbose",true)
  setparam("SQLdebug",true)

  SQLconnect(DB)
  if not getparam("SQLsuccess"): setioerr("Database connection failed")

  createdb

  SQLexecute("insert into TestTable (AnIndx,AValue) values (?,?)", A)
  checksqlstatus

  ! This is a duplicate entry, so causes an error
  initializations to "mmodbc.odbc:debug;"+DB
    A as "TestTable(AnIndx,AValue)"
  end-initializations
!)
  ! This is a duplicate entry, so causes an error
  SQLexecute("insert into TestTable (AnIndx,AValue) values (?,?)", A)
  checksqlstatus

  SQLdisconnect

  !**** Check status and stop on error ****
  procedure checksqlstatus  
    declarations
      sstat: boolean
    end-declarations
    sstat:= getparam("SQLsuccess")
    writeln_("SQL status: ", if(sstat, "succeeded", "failed"), ". ",
             getparam("SQLrowcnt"), " rows affected")
    if not sstat then
      setioerr("SQL error")   ! Stop on error if parameter 'ioctrl' is false
    end-if
  end-procedure 

!**** Database creation ****
  procedure createdb
  
    declarations
      tsucc: array ({false,true}) of string
      Tables: list of string
    end-declarations
    tsucc(false):="failed"; tsucc(true):="succeeded"
   
    ! Delete the table if it exists already
    SQLtables(Tables)
    if findfirst(Tables, "TestTable")>0 then
      SQLexecute("drop table TestTable")
    end-if
 
    ! Create a new table
    SQLexecute("create table TestTable (AnIndx varchar(50), ACharField char(3), Status boolean, CreationDate date, CreationTime time, AValue double, primary key (AnIndx))")
    writeln(" - Create TestTable (",tsucc(getparam("SQLsuccess")),")")
  end-procedure  
end-model
Back to examples browserPrevious exampleNext example
© Copyright 2023 Fair Isaac Corporation.