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:


Source Files

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





ociinv.mos

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

   file ociinv.mos
   ```````````````
   Accessing an Oracle database with selection 
   statements using columns in inverted order.
       
   (c) 2008 Fair Isaac Corporation
       author: S. Heipcke, 2007, rev. Jul. 2010
*******************************************************!)

model "OCI selection of columns"
 uses "mmoci"

 parameters
  DB="myname/mypassword@dbname"     ! Login to Oracle database (not provided)
 end-parameters

 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

 setparam("OCIdebug",true)
 OCIlogon(DB)

! Read data from the table 'ProdData'
 OCIexecute("select Mach,Products,Cost from ProdData", COST)
 OCIexecute("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)
 OCIexecute("drop table CombData")
 OCIexecute("create table CombData (Products varchar(10), Mach integer, Cost float, Duration integer)")

 OCIexecute("insert into CombData (Mach,Products,Cost) values (:1,:2,:3)", COST)
! Altenatively:
! OCIexecute("insert into CombData (Products,Mach,Cost) values (:2,:1,:3)", COST)

! Fill the 'Duration' field of the output table:
! 1. update the existing entries, 2. add new entries
 OCIexecute("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)
 OCIexecute("insert into CombData (Products,Mach,Duration) values (:1,:2,:3)", TEMP)
 
 OCIlogoff

 initializations from "mmoci.oci: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
 OCIlogon(DB)
 OCIexecute("drop table CombData2")
 OCIexecute("create table CombData2 (Products varchar(10), Mach integer, Cost float, Duration integer)")
 OCIlogoff

 initializations to "mmoci.oci:debug;"+DB
  COST2 as "CombData2(Mach,Products,Cost)"
  DUR2 as "CombData2(Products,Mach,Duration)"
 end-initializations
 
end-model


**************************************************


! Creation of the data table in an Oracle database:


 declarations
  tsucc: array ({false,true}) of string
 end-declarations

 tsucc(false):="failed"; tsucc(true):="succeeded"

 OCIexecute("create table ProdData (Mach integer, Products varchar(10), Cost float, Duration integer)")
 writeln(" - Create ProdData (",tsucc(getparam("OCIsuccess")),")")

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

 initializations from "ocidata.dat"
  COST DUR
 end-initializations

 OCIexecute("insert into ProdData (Mach, Products, Cost, Duration) values (:2, :1, :3, :4)", [COST,DUR])
 writeln(" - Insert values in ProdData (",tsucc(getparam("OCIsuccess")),",",
        getparam("OCIrowcnt"),'/',getparam("OCIrowxfr")," rows)")

Back to examples browserPrevious exampleNext example