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]

listinout2.mos

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

   file listinout2.mos
   ```````````````````
   Reading/writing lists from/to
   spreadsheets or databases via ODBC.
   - Using SQL commands -
   
   (c) 2008 Fair Isaac Corporation
       author: S. Heipcke, Nov. 2007, rev. Aug. 2023
*******************************************************!)

model "List handling (SQL)"
 uses "mmodbc"
 options keepassert

 parameters
                              ! Use Excel spreadsheet `listdata.xls'
!  CNCT = 'DSN=Excel Files;DBQ=' + getparam("workdir") + '/listdata.xls'
!  CNCT = 'listdata.xls'
                              ! Use Access database `listdata.mdb'
!  CNCT = 'DSN=MS Access Database;DBQ=C:/xpress/examples/mosel/WhitePapers/MoselData/listdata.mdb'
  CNCT = 'listdata.mdb'
                              ! Use mysql database `listdata' (not provided)
!  CNCT = 'DSN=mysql;DB=listdata'
                              ! Use SQLite database 'listdata.sqlite' via ODBC
!  CNCT = 'DSN=sqlite;DATABASE=listdata.sqlite'
                              ! Use SQLite database 'listdata.sqlite' directly
!  CNCT = "listdata.sqlite"
 end-parameters

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

 setparam("SQLverbose",true)

 SQLconnect(CNCT)
 assert(getparam("SQLsuccess"))
 SQLexecute("select * from List1", LI)
 A:= SQLreadinteger("select * from A")
 SQLexecute("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)

 SQLexecute("delete from List1Out")     ! Cleaning up previous results: works
 SQLexecute("delete from List2Out")     ! only for databases, cannot be used
                                        ! with spreadsheets (instead, delete
                                        ! previous solutions directly in the
                                        ! spreadsheet file)
 SQLexecute("insert into List1Out values (?)", LI)
 assert(getparam("SQLsuccess"))
 SQLexecute("insert into List2Out values (?)", LS2)
 assert(getparam("SQLsuccess"))
 SQLdisconnect

end-model