FICO Xpress Optimization Examples Repository: Reading and writing records and lists

FICO Xpress Optimization Examples Repository

FICO Optimization Community

FICO Xpress Optimization Home

Reading and writing records and lists

Description

This set of examples shows how to work with advanced data structures when accessing data in spreadsheets and databases:

reading in records (recordin*.mos)
writing out records (recordout*.mos)
reading and writing lists (listinout*.mos)

The basic model version uses an ODBC connection to spreadsheets or databases through the odbc driver, model version (2) uses ODBC through SQL statements, and model version (3) uses the software-specific driver excel to access Excel spreadsheets. Model version (1) shows how to work with Mosel's text data file format.

Further explanation of this example: Xpress Whitepaper 'Using ODBC and other database interfaces with Mosel', Section Examples - Working with lists - Working with records.

listrecord.zip

[download all files]

Source Files

By clicking on a file name, a preview is opened at the bottom of this page.

recordin.mos	[download]
recordin1.mos	[download]
recordin2.mos	[download]
recordin3.mos	[download]
recordin4.mos	[download]
recordin5.mos	[download]
recordin6.mos	[download]
recordout.mos	[download]
recordout1.mos	[download]
recordout2.mos	[download]
recordout3.mos	[download]
recordout4.mos	[download]
recordout5.mos	[download]
recordout6.mos	[download]
listinout.mos	[download]
listinout1.mos	[download]
listinout2.mos	[download]
listinout3.mos	[download]
listinout4.mos	[download]
listinout5.mos	[download]
listinout6.mos	[download]

Data Files

recorddata.dat	[download]
recorddata.csv	[download]
recorddata2.csv	[download]
recorddata.mdb	[download]
recorddata.xls	[download]
recorddata2.xls	[download]
recordout_templ.xls	[download]
recorddata.xlsx	[download]
recorddata2.xlsx	[download]
recorddata.sqlite	[download]
listdata.dat	[download]
listdata.csv	[download]
listdata.mdb	[download]
listdata.xls	[download]
listdata.xlsx	[download]
listdata.sqlite	[download]

recordin4.mos

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

   file recordin4.mos
   ``````````````````
   Reading records from an Oracle database.
   - Using 'initializations' and OCI statements -
   
   (c) 2012 Fair Isaac Corporation
       author: S. Heipcke, Dec. 2012, rev. Aug. 2023
*******************************************************!)

model "Record input (OCI)"
 uses "mmoci"

 parameters
  DB="myname/mypassword@dbname"     ! Login to Oracle database (not provided)
  CNCT="mmoci.oci:debug;"+DB
 end-parameters

 public declarations
  PRODUCTS: set of string
  MACH: range
  ProdRec = public record
   Cost: real
   Duration: integer
  end-record 
  PDATA,PDATA1,PDATA2: dynamic array(PRODUCTS,MACH) of ProdRec

  R = 1..9
  AllDataRec = public record
   Product: string
   Mach: integer
   Cost: real
   Duration: integer
  end-record 
  ALLDATA,ALLDATA1,ALLDATA2: array(R) of AllDataRec
 end-declarations

 
! **** Using SQL statements ****

! Reading complete records
 setparam("OCIdebug",true)
 OCIlogon(DB)
 if not getparam("OCIsuccess"): setioerr("Database connection failed")
 
 OCIexecute("select * from ProdData", PDATA)
 setparam("ocindxcol",false)      ! Dense data format (no indices)
 OCIexecute("select * from ProdData", ALLDATA)

 OCIlogoff


! **** Using 'initializations from' ****

! Reading complete records
 initializations from CNCT
  PDATA1 as "ProdData"
  ALLDATA1 as "noindex;ProdData"
 end-initializations

! Reading record fields
 initializations from CNCT
  PDATA2(Cost) as "ProdData(IndexP,IndexM,Cost)"
  ALLDATA2(Product,Mach,Duration) as "noindex;ProdData(IndexP,IndexM,Duration)"
 end-initializations


! Now let us see what we have
 writeln('PDATA is: ', PDATA)
 writeln('ALLDATA is: ', ALLDATA)
 writeln('PDATA1 is: ', PDATA1)
 writeln('ALLDATA1 is: ', ALLDATA1)
 writeln('PDATA2 is: ', PDATA2)
 writeln('ALLDATA2 is: ', ALLDATA2)

end-model

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


! Creation of data tables in an Oracle database:

 declarations
  tsucc: array ({false,true}) of string
  PDATA3: dynamic array(PRODUCTS,MACH) of ProdRec
 end-declarations

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

 initializations from "recorddata.dat"
  PDATA3 as "ProdData"
 end-initializations

 setparam("OCIdebug",true)
 OCIlogon(DB)
 writeln("Connection number: ",getparam("OCIconnection"))

 OCIexecute("drop table ProdData")

 OCIexecute("create table ProdData (IndexP varchar(10), IndexM integer, Cost float, Duration integer)")
 writeln(" - Create ProdData (",tsucc(getparam("OCIsuccess")),")")
 OCIexecute("insert into ProdData (IndexP,IndexM,Cost,Duration) values (:1, :2, :3, :4)", PDATA3)
 writeln(" - Insert values into ProdData (",tsucc(getparam("OCIsuccess")),",",
        getparam("OCIrowcnt"),'/',getparam("OCIrowxfr")," rows)")

 OCIlogoff