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]




odbcinspectdb.mos

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

   file odbcinspectdb.mos
   ``````````````````````
   Retrieving information about database table structure.
   
   (c) 2014 Fair Isaac Corporation
       author: S. Heipcke, Feb. 2014, rev. Aug. 2023
*******************************************************!)
model "Analyze DB structure"
  uses "mmodbc", "mmsystem"
  
  parameters
    DB="personnel.sqlite"
   ! DB="DSN=mysql;DB=test"
  end-parameters
  
  forward procedure create_db
  
  declarations
    tables: list of string
    pkeylist: list of string
    pkeyind: list of integer
    fnames: dynamic array(Fields: range) of string
    ftypes: dynamic array(Fields) of integer
    ftypenames: dynamic array(Fields) of string
  end-declarations
    
  setparam("SQLverbose",true)
  
  ! Uncomment the following line to generate the database
  create_db
  
  SQLconnect(DB)
  if not getparam("SQLsuccess"): setioerr("Database connection failed")
  
  ! Retrieve list of database tables
  SQLtables(tables)
  forall(t in tables) do

   ! Retrieve primary keys
    SQLprimarykeys(t, pkeylist)
    writeln(t, " primary key field names: ", pkeylist)
    SQLprimarykeys(t, pkeyind)
    writeln(t, " primary key field indices: ", pkeyind)

   ! Retrieve table structure
    writeln(t, " has ", SQLcolumns(t,fnames,ftypes), " fields")
    res:=SQLcolumns(t,fnames,ftypenames)    
    forall(f in Fields | exists(fnames(f)))
      writeln(f, ": ", fnames(f), " ", ftypes(f), ": ", ftypenames(f))

   ! Delete aux. arrays for next loop iteration
    delcell(fnames)  
    delcell(ftypes)  
    delcell(ftypenames)  
  end-do
  
  SQLdisconnect
  
  
!**** Database creation ****
  procedure create_db
  
    declarations
      tsucc: array ({false,true}) of string
      Tables: list of string
    end-declarations
    tsucc(false):="failed"; tsucc(true):="succeeded"

    SQLconnect(DB)
    if not getparam("SQLsuccess"): setioerr("Database connection failed")
    
    ! Delete the table if it exists already
    SQLtables(Tables)
    if findfirst(Tables, "PersList")>0 then
      SQLexecute("drop table PersList")
    end-if
 
    ! Create a new table
    SQLexecute("create table PersList (Surname varchar(50), FirstName varchar(50), Age integer, Address varchar(200), Country char(3), Status boolean, CreationDate date, CreationTime time, AValue double, primary key (FirstName, Surname))")
    writeln(" - Create PersList (",tsucc(getparam("SQLsuccess")),")")
    
    SQLdisconnect
  end-procedure
  
end-model
Back to examples browserPrevious exampleNext example
© Copyright 2024 Fair Isaac Corporation.