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]




odbcinv.mos

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

   file odbcinv.mos
   ````````````````
   ODBC with selection statements using columns in
   inverted order.
       
   (c) 2008 Fair Isaac Corporation
       author: S. Heipcke, 2006, rev. Aug. 2023
*******************************************************!)

model "ODBC selection of columns"
 uses "mmodbc"
 options keepassert

 declarations
  PRODUCTS: set of string
  MACH: range
  COST,COST2: dynamic array(MACH,PRODUCTS) of real
  DUR,DUR2,TEMP: dynamic array(PRODUCTS,MACH) of integer
 end-declarations

 DB:='multicol.mdb'

! DB:='multicol.sqlite'
! DB:='DSN=sqlite;DATABASE=multicol.sqlite'

 setparam("SQLdebug",true)
 SQLconnect(DB)
 assert(getparam("SQLsuccess"))

! Read data from the table 'ProdData'
 SQLexecute("select Mach,Products,Cost from ProdData", COST)
 SQLexecute("select Products,Mach,Duration from ProdData", DUR)

! Print out what we have read
 writeln(COST); writeln(DUR)

! Write out data to another table (after deleting and re-creating the table)
 SQLexecute("drop table CombData")
 SQLexecute("create table CombData (Products varchar(10), Mach integer, Cost double, Duration integer)")
 assert(getparam("SQLsuccess"))

 SQLexecute("insert into CombData (Mach,Products,Cost) values (?,?,?)", COST)
! Altenatively:
! SQLexecute("insert into CombData (Products,Mach,Cost) values (?2,?1,?3)", COST)
 assert(getparam("SQLsuccess"))

! Fill the 'Duration' field of the output table:
! 1. update the existing entries, 2. add new entries
 SQLupdate("select Products,Mach,Duration from CombData", DUR)
 assert(getparam("SQLsuccess"))
! Equivalent:
! SQLexecute("update CombData set Duration=?3 where Products=?1 and Mach=?2", DUR)
 forall(p in PRODUCTS, m in MACH | exists(DUR(p,m)) and not exists(COST(m,p)))
  TEMP(p,m) := DUR(p,m)
 SQLexecute("insert into CombData (Products,Mach,Duration) values (?,?,?)", TEMP)
 assert(getparam("SQLsuccess"))
 
 SQLdisconnect

 initializations from "mmodbc.odbc:debug;"+DB
  COST2 as "ProdData(Mach,Products,Cost)"
  DUR2 as "ProdData(Products,Mach,Duration)"
 end-initializations

 writeln(COST2); writeln(DUR2)

! Delete and re-create the output table
 SQLconnect(DB)
 assert(getparam("SQLsuccess"))
 SQLexecute("drop table CombData2")
 SQLexecute("create table CombData2 (Products varchar(10), Mach integer, Cost double, Duration integer)")
 assert(getparam("SQLsuccess"))
 SQLdisconnect

 initializations to "mmodbc.odbc:debug;"+DB
  COST2 as "CombData2(Mach,Products,Cost)"
  DUR2 as "CombData2(Products,Mach,Duration)"
 end-initializations
 
end-model
Back to examples browserPrevious exampleNext example
© Copyright 2023 Fair Isaac Corporation.