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]

listinout4.mos

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

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

model "List handling (OCI)"
 uses "mmoci"
 options keepassert

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

 declarations
  LI: list of integer
  A: integer
  LS,LS2: list of string
 end-declarations
 

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

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

 OCIexecute("select * from List1 ", LI)
 A:=OCIreadinteger("select * from A")
 OCIexecute("select * from List2 ", LS)
 
! Display the lists
 writeln("LI: ", LI)
 writeln("A: ", A, ", LS: ", LS)
 
! Reverse the list LI
 reverse(LI)

! Append some text to every entry of LS
 LS2:= sum(l in LS) [l+" "+A]

! Display the modified lists
 writeln("LI: ", LI)
 writeln("LS2: ", LS2)

 OCIexecute("delete from List1Out")     ! Cleaning up previous output
 OCIexecute("delete from List2Out")
 OCIexecute("insert into List1Out values (:1)", LI)
 assert(getparam("OCIsuccess"))
 OCIexecute("insert into List2Out values (:1)", LS2)
 assert(getparam("OCIsuccess"))
 
 OCIlogoff

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

 initializations from "mmoci.oci:debug;"+DB
  LI as "List1"
  A
  LS as "List2"
 end-initializations 

! Display the lists
 writeln("LI: ", LI)
 writeln("A: ", A, ", LS: ", LS)

! Reverse the list LI
 reverse(LI)

! Append some text to every entry of LS
 LS2:= sum(l in LS) [l+" "+A]

! Display the modified lists
 writeln("LI: ", LI)
 writeln("LS2: ", LS2)

 initializations to "mmoci.oci:"+DB
  LI as "List1Out"
  LS2 as "List2Out"
 end-initializations 

end-model


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


! Creation of data tables in an Oracle database:

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

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

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

 OCIexecute("create table List1 (ListValues integer)")
 writeln(" - Create List1 (",tsucc(getparam("OCIsuccess")),")")
 OCIexecute("insert into List1 (ListValues) values (:1)", [ 1,2,3,4,5,6,7,8 ])
 writeln(" - Insert values into List1 (",tsucc(getparam("OCIsuccess")),",",
        getparam("OCIrowcnt"),'/',getparam("OCIrowxfr")," rows)")
 
 OCIexecute("create table A (AValue integer)")
 writeln(" - Create A (",tsucc(getparam("OCIsuccess")),")")
 OCIexecute("insert into A (AValue) values (:1)", [ 2002 ])
 writeln(" - Insert values into A (",tsucc(getparam("OCIsuccess")),",",
        getparam("OCIrowcnt"),'/',getparam("OCIrowxfr")," rows)")
 
 OCIexecute("create table List2 (ListValues char(3))")
 writeln(" - Create List2 (",tsucc(getparam("OCIsuccess")),")")
 OCIexecute("insert into List2 (ListValues) values (:1)", [ Jan,May,Jul,Nov,Dec ])
 writeln(" - Insert values into List2 (",tsucc(getparam("OCIsuccess")),",",
        getparam("OCIrowcnt"),'/',getparam("OCIrowxfr")," rows)")
 
 OCIexecute("create table List1Out (ListValues integer)")
 writeln(" - Create List1Out (",tsucc(getparam("OCIsuccess")),")")
 OCIexecute("create table List2Out (ListValues char(10))")
 writeln(" - Create List2Out (",tsucc(getparam("OCIsuccess")),")")
 
 OCIlogoff