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]




unioninout4.mos

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

   file unioninout4.mos
   ````````````````````
   Reading/writing unions from/to an Oracle database.
   - Using 'initializations' and OCI statements -
   
   (c) 2021 Fair Isaac Corporation
       author: S. Heipcke, Apr. 2021, rev. Aug. 2023
*******************************************************!)
model "Union handling (OCI)"
 uses "mmoci", "mmsystem"

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

 procedure gendb
   declarations
     tsucc: array ({false,true}) of string
   end-declarations
   tsucc(false):="failed"; tsucc(true):="succeeded"

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

  ! Oracle date and time formats:
   setparam("timefmt", "%0d-%N-%0Y %0h.%0M.%0S.%0s %P")
   setparam("datefmt", "%0d-%N-%0Y")
   setparam("datetimefmt", "%0d-%N-%0Y %0h.%0M.%0S.%0s %P")

   OCIlogon(DB)
   if not getparam("OCIsuccess"): setioerr("Database connection failed")
(!
   OCIexecute("drop table UnionTab")
   OCIexecute("drop table UnionLst")
   OCIexecute("drop table UnionOut")
   OCIexecute("drop table Union2Out")
!)
   OCIexecute("create table UnionTab (IVal integer, RVal float, BVal number(1,0), SVal varchar(20), DVal date, TVal timestamp(3))")
   writeln(" - Create UnionTab (",tsucc(getparam("OCIsuccess")),")")
   OCIexecute("insert into UnionTab (IVal,RVal,SVal,BVal,TVal,DVal) values(:1,:2,:3,:4,:5,:6)", [ 5, 1.75, "some text", true, '1-Jan-1 11:25:30am', '20-Mar-2021' ])
   writeln(" - Insert values into UnionTab (",tsucc(getparam("OCIsuccess")),",",
        getparam("OCIrowcnt"),'/',getparam("OCIrowxfr")," rows)")
 
   OCIexecute("create table UnionLst (UValues varchar(20))")
   writeln(" - Create UnionLst (",tsucc(getparam("OCIsuccess")),")")
   OCIexecute("insert into UnionLst (UValues) values (:1)", [string(5), string(1.75), string(true), "some text", '20-Mar-2021', '1-Jan-0 11:25:30am'])
   writeln(" - Insert values into UnionLst (",tsucc(getparam("OCIsuccess")),",",
        getparam("OCIrowcnt"),'/',getparam("OCIrowxfr")," rows)")

   OCIexecute("create table UnionOut (IVal integer, RVal float, BVal number(1,0), SVal varchar(20), DVal date, TVal timestamp(3))")
   writeln(" - CreateUnionOut (",tsucc(getparam("OCIsuccess")),")")
   OCIexecute("create table Union2Out (UnionValues varchar(25))")
   writeln(" - Create Union2Out (",tsucc(getparam("OCIsuccess")),")")

   OCIlogoff
 end-procedure

 gendb

 declarations
   L,L2: list of any
   L3: list of text or real
   LS: list of text
 end-declarations
 
 setparam("OCIverbose",true)
 setparam("OCIdebug",true)

 OCIlogon(DB)
 if not getparam("OCIsuccess"): setioerr("Database connection failed")

 ! Reading data of different types from a database
 OCIexecute("select * from UnionTab", L)
 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).datetime:=datetime(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
 OCIexecute("select * from UnionTab", L3)
 write("L3:     ")
 forall(i in L3) write (i,": ", i.typeid, "; ")
 writeln

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

 ! Writing data of type 'any' to a database
 OCIexecute("delete from UnionOut")
 OCIexecute("insert into UnionOut (IVal,RVal,BVal,SVal,DVal,TVal) values(:1,:2,:3,:4,:5,:6)", L)
 writeln(" - writing L into UnionOut succeeded (",getparam("OCIsuccess"),")")

 ! Writing data of type 'any' into textual fields of a database
 OCIexecute("delete from Union2Out")
 OCIexecute("insert into Union2Out (UnionValues) values(:1)", L)
 writeln(" - writing L into Union2Out succeeded (",getparam("OCIsuccess"),")")

 ! Writing data of a union type to a database
 OCIexecute("insert into UnionOut (IVal,RVal,BVal,SVal,DVal,TVal) values(:1,:2,:3,:4,:5,:6)", L3)
 writeln(" - writing L3 into UnionOut succeeded (",getparam("OCIsuccess"),")")

 ! Writing text-format data to a database table with various different types
 LS:=sum(i in L) [text(i)]
 OCIexecute("insert into UnionOut (IVal,RVal,BVal,SVal,DVal,TVal) values(:1,:2,:3,:4,:5,:6)", LS)
 writeln(" - writing LS into UnionOut succeeded (",getparam("OCIsuccess"),")")

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