FICO
FICO Xpress Optimization Examples Repository
FICO Optimization Community FICO Xpress Optimization Home
Back to examples browserPrevious exampleNext example

Reading and writing union types

Description
A union is a container capable of holding an object of one of a predefined set of types. Its declaration specifies the set of compatible types or the predefined union type 'any'. The examples display the resulting types when populating union type entities from external data sources. It is also shown how to convert the type of union entities from textual types to 'date' or 'time' types as defined by the Mosel module mmsystem.
  • The model version in unioninout1.mos shows how to work with Mosel's text data file format.
  • Databases can be accessed from Mosel through an ODBC connection using the odbc driver (unioninout.mos) or with SQL statements (unioninout2.mos).
  • For Oracle databases a software-specific connection is provided by the module mmoci (unioninout4.mos).
  • Microsoft Excel spreadsheets can be accessed via the software-specific driver excel (unioninout3.mos). Alternatively, models can use the dedicated I/O drivers xsl/xlsx (unioninout5.mos) from the mmsheet module that support manipulating spreadsheet files on various platforms without having to install Microsoft Excel.
  • Module mmsheet also provides an I/O driver for csv files (unioninout6.mos).
Further explanation of this example: Xpress Whitepaper 'Using ODBC and other database interfaces with Mosel', Section Examples - Working with union types.

uniondata.zip[download all files]

Source Files
By clicking on a file name, a preview is opened at the bottom of this page.
unioninout.mos[download]
unioninout1.mos[download]
unioninout2.mos[download]
unioninout3.mos[download]
unioninout4.mos[download]
unioninout5.mos[download]
unioninout6.mos[download]

Data Files
uniondata.csv[download]
uniondata2.csv[download]
uniondatain.dat[download]
uniondata.xls[download]
uniondata.xlsx[download]
uniondata.sqlite[download]




unioninout.mos

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

   file unioninout.mos
   ```````````````````
   Reading/writing unions from/t databases via ODBC.
   - Using 'initializations from' with odbc IO driver -
   
   (c) 2021 Fair Isaac Corporation
       author: S. Heipcke, Apr. 2021
*******************************************************!)
model "Union handling (ODBC)"
 uses 'mmodbc', 'mmsystem'

 parameters
                              ! Use mysql database `uniondata' (not provided)
!  CNCTIO = 'mmodbc.odbc:debug;DSN=mysql;DB=uniondata'
                              ! Use SQLite database 'uniondata.sqlite' via ODBC
!  CNCTIO = 'mmodbc.odbc:debug;DSN=sqlite;DATABASE=uniondata.sqlite'
                              ! Use SQLite database 'uniondata.sqlite' directly
   CNCTIO = "mmodbc.odbc:debug;uniondata.sqlite"
 end-parameters

 declarations
   L,L2: list of any
   L3: list of text or real
   LS: list of text
 end-declarations

 ! Date and time formats
(! Access and Excel:
 setparam("timefmt", "%y-%0m-%0d %0H:%0M:%0S")
 setparam("datefmt", "%y-%0m-%0d")
 setparam("datetimefmt", "%y-%0m-%0d %0H:%0M:%0S")
!)
(! mysql:
 setparam("timefmt", "%0H:%0M:%0S")
 setparam("datefmt", "%y-%0m-%0d")
 setparam("datetimefmt", "%y-%0m-%0d %0H:%0M:%0S")
!)
(! SQLite:
 setparam("timefmt", "%0H:%0M:%0S")
 setparam("datefmt", "%y-%0m-%0d")
 setparam("datetimefmt", "%y-%0m-%0d %0H:%0M:%0S")
!)

 setparam("SQLverbose",true)

 ! Reading data of different types from a database
 initializations from CNCTIO
   L as "UnionTab"
   L2 as "UnionLst"
   L3 as "UnionTab"
 end-initializations

 write("L orig: ")
 forall(i in L) write (i,": ", i.typeid, "; ")
 writeln
 ! Date and time types are read in textual form
 L(5).date:=date(text(L(5)))
 L(6).time:=time(text(L(6)))
 write("L new:  ")
 forall(i in L) write (i,": ", i.typeid, "; ")
 writeln

 ! Reading into a list defined with a restricted set of types
 write("L3:     ")
 forall(i in L3) write (i,": ", i.typeid, "; ")
 writeln

 ! Textual database types are always read as string
 write("L2:     ")
 forall(i in L2) write (i,": ", i.typeid, "; ")
 writeln


(! Delete existing contents of result tables: 
  SQLconnect(CNCT)
  SQLexecute("delete from UnionOut")
  SQLexecute("delete from Union2Out")
  SQLdisconnect
!)
 LS:=sum(i in L) [text(i)]

 initializations to CNCTIO
  ! Writing data of type 'any' to a database table with various different types
   L as "UnionOut(IVal,RVal,BVal,SVal,DVal,TVal)"
  ! Writing data of type 'any' into textual fields of a database
   L as "Union2Out"
  ! Writing data of a union type to a database
   L3 as "UnionOut(IVal,RVal,BVal,SVal,DVal,TVal)"
  ! Writing text-format data to a database table with various different types
   LS as "UnionOut(IVal,RVal,BVal,SVal,DVal,TVal)"
 end-initializations

 writeln("Output to DB terminated.")

end-model
Back to examples browserPrevious exampleNext example
© Copyright 2024 Fair Isaac Corporation.