FICO Xpress Optimization Examples Repository: Spreadsheets and databases: working with multiple data tables and arrays

FICO Xpress Optimization Examples Repository

Spreadsheets and databases: working with multiple data tables and arrays

Description

A database table (or a spreadsheet range) may contain in its different fields (columns) the data for several Mosel arrays and inversely, a Mosel array may correspond to several data tables (ranges). We have here examples of various different cases:

using the odbc driver,
formulated with SQL statements,
using the excel driver,
using the oci driver,
using the xls driver, and
using the csv driver.

for three sets of examples:

Reading several arrays from a single database table/spreadsheet range (multicol.mos)
Outputting several arrays into a single database table/spreadsheet range (multiout.mos)
Reading an array from several tables/ranges (multitab.mos)

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

multitab.zip

[download all files]

Source Files

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

multicol.mos	[download]
multicol2.mos	[download]
multicol3.mos	[download]
multicol4.mos	[download]
multicol5.mos	[download]
multicol6.mos	[download]
multiout.mos	[download]
multiout2.mos	[download]
multiout3.mos	[download]
multiout4.mos	[download]
multiout5.mos	[download]
multiout6.mos	[download]
multitab.mos	[download]
multitab2.mos	[download]
multitab3.mos	[download]
multitab4.mos	[download]
multitab5.mos	[download]
multitab6.mos	[download]

Data Files

multicol.dat	[download]
multicdd.dat	[download]
multicol.csv	[download]
multicol.mdb	[download]
multicol.xls	[download]
multicol.xlsx	[download]
multicol.sqlite	[download]
multiout.dat	[download]
multicol.csv	[download]
multicol.mdb	[download]
multicol.xls	[download]
multicol.xlsx	[download]
multicol.sqlite	[download]
multitab.csv	[download]
multitab.dat	[download]
multitab.mdb	[download]
multitab.xls	[download]
multitab.xlsx	[download]
multitab.sqlite	[download]

multicol2.mos

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

   file multicol2.mos
   ``````````````````
   Reading several data arrays from a single table.
   - Using SQL commands -
       
   (c) 2008 Fair Isaac Corporation
       author: S. Heipcke, 2002, rev. Aug. 2023
*******************************************************!)

model "Multiple data columns (2)"
 uses "mmetc", "mmodbc"

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

! **** Read data in Mosel format ****
 initializations from "multicol.dat"
  [COST,DUR] as "ProdData"
 end-initializations

 writeln("Mosel format:"); writeln(COST); writeln(DUR)


! **** Read data in diskdata format ****
 diskdata(ETC_IN+ETC_SPARSE, "multicdd.dat", [COST1,DUR1])

! Alternatively: use diskdata IO driver:
(!
 initializations from "mmetc.diskdata:sparse"
  [COST1,DUR1] as "multicdd.dat"
 end-initializations
!)
 writeln("'diskdata' format:"); writeln(COST1); writeln(DUR1)

 
! **** Reading data from databases ****
! This assumes the spreadsheet/database contains a table "ProdData"
! in sparse format (i.e., with indices) with the columns "COST" and "DUR"
 
! Read data from the Excel spreadsheet multicol.xls
!  SQLconnect('DSN=Excel Files;DBQ=C:/xpress/examples/mosel/WhitePapers/MoselData/multicol.xls')
!  SQLconnect('multicol.xls')

! Read data from the Access database multicol.mdb
!  SQLconnect('DSN=MS Access Database;DBQ=C:/xpress/examples/mosel/WhitePapers/MoselData/multicol.mdb')
  SQLconnect('multicol.mdb')

! Read data from the mysql database multicol (not provided)
! SQLconnect('DSN=mysql;DB=multicol')

! Read data from the SQLite database multicol.sqlite
! SQLconnect('DSN=sqlite;DATABASE=multicol.sqlite')
! SQLconnect('multicol.sqlite')

 if not getparam("SQLsuccess"): setioerr("Database connection failed")
 setparam("SQLverbose",true)

 SQLexecute("select * from ProdData ", [COST2,DUR2])
 
 SQLdisconnect

 writeln("ODBC:"); writeln(COST2); writeln(DUR2)

end-model