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]

multiout4.mos

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

   file multiout4.mos
   ``````````````````
   Output several data arrays into a single table.
   - Using 'initializations' and OCI statements -
       
   (c) 2012 Fair Isaac Corporation
       author: S. Heipcke, Dec. 2012, rev. Aug. 2023
*******************************************************!)

model "Output multiple data columns (OCI)"
 uses "mmoci"
 options keepassert

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

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

! Read data
 initializations from "multiout.dat"
  COST DUR
 end-initializations


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

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

 OCIexecute("create table CombData (Products varchar(10), Mach integer, Cost double, Duration integer)")
 assert(getparam("OCIsuccess"))
 OCIexecute("delete from CombData")
 OCIexecute("insert into CombData(Products, Mach, Cost, Duration) values (:1,:2,:3,:4)", [COST,DUR])
 assert(getparam("OCIsuccess"))

 OCIlogoff


! **** Using 'initializations to' ****
! (this assumes that the table 'CombData' has been created previously)

 initializations to "mmoci.oci:debug;"+DB
  [COST,DUR] as 'CombData'
 end-initializations


end-model