Initializing help system before first use

Reading and writing records and lists
Type: Programming
Rating: 3 (intermediate)
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.
File(s): recordin.mos, recordin1.mos, recordin2.mos, recordin3.mos, recordin4.mos, recordin5.mos, recordin6.mos, recordout.mos, recordout1.mos, recordout2.mos, recordout3.mos, recordout4.mos, recordout5.mos, recordout6.mos, listinout.mos, listinout1.mos, listinout2.mos, listinout3.mos, listinout4.mos, listinout5.mos, listinout6.mos
Data file(s): recorddata.dat, recorddata.csv, recorddata2.csv, recorddata.mdb, recorddata.xls, recorddata2.xls, recordout_templ.xls, recorddata.xlsx, recorddata2.xlsx, recorddata.sqlite, listdata.dat, listdata.csv, listdata.mdb, listdata.xls, listdata.xlsx, listdata.sqlite

recordin.mos
(!******************************************************
   Mosel Example Problems
   ====================== 

   file recordin.mos
   `````````````````
   Reading records from
   spreadsheets or databases via ODBC.
   - Using 'initializations from' with odbc IO driver -
   
   (c) 2008 Fair Isaac Corporation
       author: S. Heipcke, Nov. 2007, rev. Jan. 2014
*******************************************************!)

model "Record input (ODBC)"
 uses "mmodbc"

 parameters
                              ! Use Excel spreadsheet `recorddata.xls'
!  CNCTIO = "recorddata.xls"
                              ! Use Access database `recorddata.mdb'
  CNCTIO = "debug;recorddata.mdb"
                              ! Use mysql database `recorddata' (not provided)
!  CNCTIO = "debug;DSN=mysql;DB=recorddata"
                              ! Use SQLite database `recorddata' via ODBC
!  CNCTIO = 'DSN=sqlite;DATABASE=recorddata.sqlite'
                              ! Use SQLite database `recorddata' directly
!  CNCTIO = 'recorddata.sqlite'
 end-parameters

 public declarations
  PRODUCTS: set of string
  MACH: range
  ProdRec = public record
   Cost: real
   Duration: integer
  end-record 
  PDATA,PDATA2: dynamic array(PRODUCTS,MACH) of ProdRec

  R = 1..9
  AllDataRec = public record
   Product: string
   Mach: integer
   Cost: real
   Duration: integer
  end-record 
  ALLDATA,ALLDATA2: array(R) of AllDataRec
 end-declarations

! **** Reading complete records

 initializations from "mmodbc.odbc:"+CNCTIO
  PDATA as "ProdData"
  ALLDATA as "noindex;ProdData"
 end-initializations

! **** Reading record fields

 initializations from "mmodbc.odbc:"+CNCTIO
  PDATA2(Cost) as "ProdData(IndexP,IndexM,Cost)"
  ALLDATA2(Product,Mach,Duration) as "noindex;ProdData(IndexP,IndexM,Duration)"
 end-initializations

! Now let us see what we have
 writeln('PDATA is: ', PDATA)
 writeln('ALLDATA is: ', ALLDATA)
 writeln('PDATA2 is: ', PDATA2)
 writeln('ALLDATA2 is: ', ALLDATA2)

end-model
recordin1.mos
(!******************************************************
   Mosel Example Problems
   ====================== 

   file recordin1.mos
   ``````````````````
   Reading records from a text file.
   
   (c) 2008 Fair Isaac Corporation
       author: S. Heipcke, Nov. 2007, rev. Jul. 2010
*******************************************************!)

model "Record input (text files)"

 public declarations
  PRODUCTS: set of string
  MACH: range
  ProdRec = public record
   Cost: real
   Duration: integer
  end-record 
  PDATA: dynamic array(PRODUCTS,MACH) of ProdRec

  R = 1..9
  AllDataRec = public record
   Product: string
   Mach: integer
   Cost: real
   Duration: integer
  end-record 
  ALLDATA: array(R) of AllDataRec
 end-declarations

! **** Reading complete records

 initializations from "recorddata.dat"
  PDATA as "ProdData"
  ALLDATA as "AllData"
 end-initializations

! Now let us see what we have
 writeln('PDATA is: ', PDATA)
 writeln('ALLDATA is: ', ALLDATA)

end-model
recordin2.mos
(!******************************************************
   Mosel Example Problems
   ====================== 

   file recordin2.mos
   ``````````````````
   Reading records from
   spreadsheets or databases via ODBC.
   - Using SQL commands -
   
   (c) 2008 Fair Isaac Corporation
       author: S. Heipcke, Nov. 2007, rev. Sep. 2018
*******************************************************!)

model "Record input (SQL)"
 uses "mmodbc"

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

 public declarations
  PRODUCTS: set of string
  MACH: range
  ProdRec = public record
   Cost: real
   Duration: integer
  end-record 
  PDATA: dynamic array(PRODUCTS,MACH) of ProdRec

  R = 1..9
  AllDataRec = public record
   Product: string
   Mach: integer
   Cost: real
   Duration: integer
  end-record 
  ALLDATA: array(R) of AllDataRec
 end-declarations

! **** Reading complete records

 setparam("SQLverbose",true)

 SQLconnect(CNCT)
 SQLexecute("select * from ProdData", PDATA)
 setparam("SQLndxcol", false)           ! Dense data
 SQLexecute("select * from ProdData", ALLDATA)

 SQLdisconnect

! Now let us see what we have
 writeln('PDATA is: ', PDATA)
 writeln('ALLDATA is: ', ALLDATA)

end-model
recordin3.mos
(!******************************************************
   Mosel Example Problems
   ====================== 

   file recordin3.mos
   ``````````````````
   Reading records from
   spreadsheets or databases via ODBC.
   - Using 'initializations from' with the excel driver -
   
   (c) 2008 Fair Isaac Corporation
       author: S. Heipcke, Nov. 2007, rev. Feb. 2019
*******************************************************!)

model "Record input (Excel)"
 uses "mmsheet"

 parameters
  CSTR = 'mmsheet.excel:skiph;recorddata.xls'
  CSTR2= 'mmsheet.excel:skiph;recorddata2.xls'
 end-parameters
 
 public declarations
  PRODUCTS: set of string
  MACH: range
  ProdRec = public record
   Cost: real
   Duration: integer
  end-record 
  PDATA,PDATA2,PDATA3,PDATA4: dynamic array(PRODUCTS,MACH) of ProdRec

  R = 1..9
  AllDataRec = public record
   Product: string
   Mach: integer
   Cost: real
   Duration: integer
  end-record 
  ALLDATA,ALLDATA2,ALLDATA3,ALLDATA4: array(R) of AllDataRec
 end-declarations

! **** Reading from a spreadsheet with named ranges ****

! Reading complete records

 initializations from CSTR
  PDATA as "ProdData"
  ALLDATA as "noindex;ProdData"
 end-initializations

! Reading record fields

 initializations from CSTR
  PDATA2(Cost) as "CostData"
  ALLDATA2(Product,Mach,Duration) as "noindex;DurationData"
 end-initializations

! Now let us see what we have
 writeln('PDATA is: ', PDATA)
 writeln('ALLDATA is: ', ALLDATA)
 writeln('PDATA2 is: ', PDATA2)
 writeln('ALLDATA2 is: ', ALLDATA2)


! **** Reading from a simple spreadsheet without named ranges ****

 initializations from CSTR2
! Reading complete records  
  PDATA3 as "[InputData$A:D]"
  ALLDATA3 as "noindex;[InputData$A:D]"

! Reading selected record fields  
! With option 'skiph' the column headers can be used to select columns
  PDATA4(Cost) as "[InputData$A:D](Product,Mach,Cost)"
 ! PDATA4(Cost) as "[InputData$A:D](#1,#2,#3)"    ! Alternative form of column selection in spreadsheet
  ALLDATA4(Product,Mach,Duration) as "noindex;[InputData$A:D](Product,Mach,Duration)"
 end-initializations

! Now let us see what we have
 writeln('PDATA3 is: ', PDATA3)
 writeln('ALLDATA3 is: ', ALLDATA3)
 writeln('PDATA4 is: ', PDATA4)
 writeln('ALLDATA4 is: ', ALLDATA4)

end-model
recordin4.mos
(!******************************************************
   Mosel Example Problems
   ====================== 

   file recordin4.mos
   ``````````````````
   Reading records from an Oracle database.
   - Using 'initializations' and OCI statements -
   
   (c) 2012 Fair Isaac Corporation
       author: S. Heipcke, Dec. 2012
*******************************************************!)

model "Record input (OCI)"
 uses "mmoci"

 parameters
  DB="myname/mypassword@dbname"     ! Login to Oracle database (not provided)
  CNCT="mmoci.oci:debug;"+DB
 end-parameters

 public declarations
  PRODUCTS: set of string
  MACH: range
  ProdRec = public record
   Cost: real
   Duration: integer
  end-record 
  PDATA,PDATA1,PDATA2: dynamic array(PRODUCTS,MACH) of ProdRec

  R = 1..9
  AllDataRec = public record
   Product: string
   Mach: integer
   Cost: real
   Duration: integer
  end-record 
  ALLDATA,ALLDATA1,ALLDATA2: array(R) of AllDataRec
 end-declarations

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

! Reading complete records
 setparam("OCIdebug",true)
 OCIlogon(DB)
 
 OCIexecute("select * from ProdData", PDATA)
 setparam("ocindxcol",false)      ! Dense data format (no indices)
 OCIexecute("select * from ProdData", ALLDATA)

 OCIlogoff


! **** Using 'initializations from' ****

! Reading complete records
 initializations from CNCT
  PDATA1 as "ProdData"
  ALLDATA1 as "noindex;ProdData"
 end-initializations

! Reading record fields
 initializations from CNCT
  PDATA2(Cost) as "ProdData(IndexP,IndexM,Cost)"
  ALLDATA2(Product,Mach,Duration) as "noindex;ProdData(IndexP,IndexM,Duration)"
 end-initializations


! Now let us see what we have
 writeln('PDATA is: ', PDATA)
 writeln('ALLDATA is: ', ALLDATA)
 writeln('PDATA1 is: ', PDATA1)
 writeln('ALLDATA1 is: ', ALLDATA1)
 writeln('PDATA2 is: ', PDATA2)
 writeln('ALLDATA2 is: ', ALLDATA2)

end-model

**************************************************


! Creation of data tables in an Oracle database:

 declarations
  tsucc: array ({false,true}) of string
  PDATA3: dynamic array(PRODUCTS,MACH) of ProdRec
 end-declarations

 tsucc(false):="failed"; tsucc(true):="succeeded"

 initializations from "recorddata.dat"
  PDATA3 as "ProdData"
 end-initializations

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

 OCIexecute("drop table ProdData")

 OCIexecute("create table ProdData (IndexP varchar(10), IndexM integer, Cost float, Duration integer)")
 writeln(" - Create ProdData (",tsucc(getparam("OCIsuccess")),")")
 OCIexecute("insert into ProdData (IndexP,IndexM,Cost,Duration) values (:1, :2, :3, :4)", PDATA3)
 writeln(" - Insert values into ProdData (",tsucc(getparam("OCIsuccess")),",",
        getparam("OCIrowcnt"),'/',getparam("OCIrowxfr")," rows)")

 OCIlogoff
recordin5.mos
(!******************************************************
   Mosel Example Problems
   ====================== 

   file recordin5.mos
   ``````````````````
   Reading records from spreadsheets.
   - Using 'initializations from' with the xls driver -
   
   (c) 2012 Fair Isaac Corporation
       author: S. Heipcke, Dec. 2012, rev. Feb. 2019
*******************************************************!)

model "Record input (generic spreadsheet)"
 uses "mmsheet"

 parameters
!  CSTR= 'mmsheet.xls:skiph;recorddata.xls'
  CSTR= 'mmsheet.xlsx:skiph;recorddata.xlsx'
!  CSTR2= 'mmsheet.xls:skiph;recorddata2.xls'
  CSTR2= 'mmsheet.xlsx:skiph;recorddata2.xlsx'
 end-parameters
 
 public declarations
  PRODUCTS: set of string
  MACH: range
  ProdRec = public record
   Cost: real
   Duration: integer
  end-record 
  PDATA,PDATA2,PDATA3,PDATA4: dynamic array(PRODUCTS,MACH) of ProdRec

  R = 1..9
  AllDataRec = public record
   Product: string
   Mach: integer
   Cost: real
   Duration: integer
  end-record 
  ALLDATA,ALLDATA2,ALLDATA3,ALLDATA4: array(R) of AllDataRec
 end-declarations

! **** Reading from a spreadsheet with named ranges ****

! Reading complete records

 initializations from CSTR
  PDATA as "ProdData"
  ALLDATA as "noindex;ProdData"
 end-initializations

! Reading record fields

 initializations from CSTR
  PDATA2(Cost) as "CostData"
  ALLDATA2(Product,Mach,Duration) as "noindex;DurationData"
 end-initializations

! Now let us see what we have
 writeln('PDATA is: ', PDATA)
 writeln('ALLDATA is: ', ALLDATA)
 writeln('PDATA2 is: ', PDATA2)
 writeln('ALLDATA2 is: ', ALLDATA2)


! **** Reading from a simple spreadsheet without named ranges ****

 initializations from CSTR2
! Reading complete records  
  PDATA3 as "[InputData$A:D]"
  ALLDATA3 as "noindex;[InputData$A:D]"

! Reading selected record fields 
! With option 'skiph' the column headers can be used to select columns
  PDATA4(Cost) as "[InputData$A:D](Product,Mach,Cost)"
 ! PDATA4(Cost) as "[InputData$A:D](#1,#2,#3)"    ! Alternative form of column selection in spreadsheet
  ALLDATA4(Product,Mach,Duration) as "noindex;[InputData$A:D](Product,Mach,Duration)"
 end-initializations

! Now let us see what we have
 writeln('PDATA3 is: ', PDATA3)
 writeln('ALLDATA3 is: ', ALLDATA3)
 writeln('PDATA4 is: ', PDATA4)
 writeln('ALLDATA4 is: ', ALLDATA4)

end-model
recordin6.mos
(!******************************************************
   Mosel Example Problems
   ====================== 

   file recordin6.mos
   ``````````````````
   Reading records from spreadsheets.
   - Using 'initializations from' with the csv driver -
   
   (c) 2012 Fair Isaac Corporation
       author: S. Heipcke, Dec. 2012, rev. Feb. 2019
*******************************************************!)

model "Record input (CSV)"
 uses "mmsheet"

 parameters
  CSTR= 'mmsheet.csv:recorddata.csv'
  CSTR2= 'mmsheet.csv:skiph;recorddata2.csv'
 end-parameters
 
 public declarations
  PRODUCTS: set of string
  MACH: range
  ProdRec = public record
   Cost: real
   Duration: integer
  end-record 
  PDATA,PDATA2,PDATA3,PDATA4: dynamic array(PRODUCTS,MACH) of ProdRec

  R = 1..9
  AllDataRec = public record
   Product: string
   Mach: integer
   Cost: real
   Duration: integer
  end-record 
  ALLDATA,ALLDATA2,ALLDATA3,ALLDATA4: array(R) of AllDataRec
 end-declarations

! **** Reading from a datafile that contains various data sets in the expected formats ****

! Reading complete records

 initializations from CSTR
  PDATA as "[B4:E12]"
  ALLDATA as "noindex;[B4:E12]"
 end-initializations

! Reading record fields

 initializations from CSTR
  PDATA2(Cost) as "[B4:D12]"
  ALLDATA2(Product,Mach,Duration) as "noindex;[B17:D25]"
 end-initializations

! Now let us see what we have
 writeln('PDATA is: ', PDATA)
 writeln('ALLDATA is: ', ALLDATA)
 writeln('PDATA2 is: ', PDATA2)
 writeln('ALLDATA2 is: ', ALLDATA2)


! **** Reading from a datafile that contains a single table, selecting columns from a specified range ****

 initializations from CSTR2
! Reading complete records  
  PDATA3 as "[A:D]"
  ALLDATA3 as "noindex;[A:D]"

! Reading selected record fields 
! With option 'skiph' the column headers can be used to select columns
  PDATA4(Cost) as "[A:D](Product,Mach,Cost)"
!  PDATA4(Cost) as "[A:D](#1,#2,#3)"    ! Alternative form of column selection in spreadsheet
  ALLDATA4(Product,Mach,Duration) as "noindex;[A:D](Product,Mach,Duration)"
 end-initializations

! Now let us see what we have
 writeln('PDATA3 is: ', PDATA3)
 writeln('ALLDATA3 is: ', ALLDATA3)
 writeln('PDATA4 is: ', PDATA4)
 writeln('ALLDATA4 is: ', ALLDATA4)

end-model
recordout.mos
(!******************************************************
   Mosel Example Problems
   ====================== 

   file recordout.mos
   ``````````````````
   Writing out records to
   spreadsheets or databases via ODBC.
   - Using 'initializations from' with odbc IO driver -
   
   (c) 2008 Fair Isaac Corporation
       author: S. Heipcke, Nov. 2007, rev. Jan. 2014
*******************************************************!)

model "Record output (ODBC)"
 uses "mmodbc"

 parameters
                              ! Use Excel spreadsheet `recorddata.xls'
!  CNCTIO = "recorddata.xls"
                              ! Use Access database `recorddata.mdb'
  CNCTIO = "debug;recorddata.mdb"
                              ! Use mysql database `recorddata' (not provided)
!  CNCTIO = "debug;DSN=mysql;DB=recorddata"
                              ! Use SQLite database `recorddata' via ODBC
!  CNCTIO = 'DSN=sqlite;DATABASE=recorddata.sqlite'
                              ! Use SQLite database `recorddata' directly
!  CNCTIO = 'recorddata.sqlite'
 end-parameters

 declarations
  PRODUCTS: set of string
  MACH: range
  ProdRec = record
   Cost: real
   Duration: integer
  end-record 
  PDATA: dynamic array(PRODUCTS,MACH) of ProdRec

  R = 1..9
  AllDataRec = record
   Product: string
   Mach: integer
   Cost: real
   Duration: integer
  end-record 
  ALLDATA: array(R) of AllDataRec
 end-declarations

! **** Reading data from a text file
 initializations from "recorddata.dat"
  PDATA as "ProdData"
  ALLDATA as "AllData"
 end-initializations

! **** Write out complete records
! (This assumes that the output tables have been created previously.)
! ATTENTION: results from previous runs must be removed previously.

 initializations to "mmodbc.odbc:"+CNCTIO
  PDATA as "ProdDataOutH"
  ALLDATA as "noindex;AllDataOutH"
 end-initializations

! **** Write out record fields

 initializations to "mmodbc.odbc:"+CNCTIO
  PDATA(Cost) as "CostOutH"
  ALLDATA(Product,Mach,Duration) as "noindex;DurationOutH"
 end-initializations

end-model
recordout1.mos
(!******************************************************
   Mosel Example Problems
   ====================== 

   file recordout1.mos
   ```````````````````
   Writing out records to a text file.
   
   (c) 2008 Fair Isaac Corporation
       author: S. Heipcke, Nov. 2007, rev. Jul. 2010
*******************************************************!)

model "Record output (Text file)"

 declarations
  PRODUCTS: set of string
  MACH: range
  ProdRec = record
   Cost: real
   Duration: integer
  end-record 
  PDATA: dynamic array(PRODUCTS,MACH) of ProdRec

  R = 1..9
  AllDataRec = record
   Product: string
   Mach: integer
   Cost: real
   Duration: integer
  end-record 
  ALLDATA: array(R) of AllDataRec
 end-declarations

! **** Reading data from a text file
 initializations from "recorddata.dat"
  PDATA as "ProdData"
  ALLDATA as "AllData"
 end-initializations

! **** Write out complete records

 initializations to "recordout.dat"
  PDATA as "ProdDataOutH"
  ALLDATA as "AllDataOutH"
 end-initializations

! **** Write out record fields

 initializations to "recordout.dat"
  PDATA(Cost) as "CostOutH"
  ALLDATA(Product,Mach,Duration) as "DurationOutH"
 end-initializations

end-model
recordout2.mos
(!******************************************************
   Mosel Example Problems
   ====================== 

   file recordout2.mos
   ```````````````````
   Writing out records to
   spreadsheets or databases via ODBC.
   - Using SQL commands -
   
   (c) 2008 Fair Isaac Corporation
       author: S. Heipcke, Nov. 2007, rev. Sep. 2018
*******************************************************!)

model "Record output (SQL)"
 uses "mmodbc"

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

 public declarations
  PRODUCTS: set of string
  MACH: range
  ProdRec = public record
   Cost: real
   Duration: integer
  end-record 
  PDATA: dynamic array(PRODUCTS,MACH) of ProdRec

  R = 1..9
  AllDataRec = public record
   Product: string
   Mach: integer
   Cost: real
   Duration: integer
  end-record 
  ALLDATA: array(R) of AllDataRec
 end-declarations

! **** Reading data from a text file
 initializations from "recorddata.dat"
  PDATA as "ProdData"
  ALLDATA as "AllData"
 end-initializations

! **** Write out complete records

 setparam("SQLverbose",true)
 setparam("SQLdebug",true)

 SQLconnect(CNCT)
 SQLexecute("delete from ProdDataOutH") ! Cleaning up previous results: works
                                        ! only for databases, cannot be used
                                        ! with spreadsheets (instead, delete
                                        ! previous solutions directly in the
                                        ! spreadsheet file)
 SQLexecute("insert into ProdDataOutH(IndexP,IndexM,Cost,Duration) values (?,?,?,?)",
  PDATA)
 SQLexecute("delete from AllDataOutH")  ! Cleaning up previous results
 setparam("SQLndxcol", false)           ! Dense data
 SQLexecute("insert into AllDataOutH(IndexP,IndexM,Cost,Duration) values (?,?,?,?)",
  ALLDATA)

 SQLdisconnect

end-model
recordout3.mos
(!******************************************************
   Mosel Example Problems
   ====================== 

   file recordout3.mos
   ```````````````````
   Writing out records to
   spreadsheets or databases via ODBC.
   - Using 'initializations to' with the excel driver -
   
   (c) 2008 Fair Isaac Corporation
       author: S. Heipcke, Nov. 2007, rev. Feb. 2019
*******************************************************!)

model "Record output (Excel)"
 uses "mmsheet", "mmsystem"

 parameters
  CSTR = 'mmsheet.excel:recorddata.xls'
  CSTR2 = 'mmsheet.excel:skiph;recordout.xls'
 end-parameters
 
 declarations
  PRODUCTS: set of string
  MACH: range
  ProdRec = record
   Cost: real
   Duration: integer
  end-record 
  PDATA: dynamic array(PRODUCTS,MACH) of ProdRec

  R = 1..9
  AllDataRec = record
   Product: string
   Mach: integer
   Cost: real
   Duration: integer
  end-record 
  ALLDATA: array(R) of AllDataRec
 end-declarations

! **** Reading data from a text file
 initializations from "recorddata.dat"
  PDATA as "ProdData"
  ALLDATA as "AllData"
 end-initializations

! **** Writing out into a spreadsheet with named ranges ****

! Write out complete records
! (This assumes that the output tables have been created previously.)
! ATTENTION: results from previous runs must be removed previously.

! Driver options: noindex - dense data
 initializations to CSTR
  PDATA as "ProdDataOut"
  ALLDATA as "noindex;AllDataOut"
 end-initializations

! Write out selected record fields
 initializations to CSTR
  PDATA(Cost) as "CostOut"
  ALLDATA(Product,Mach,Duration) as "noindex;DurationOut"
 end-initializations


! **** Writing out into a simple spreadsheet without named ranges

! The mmsheet.excel driver cannot create files so we copy a template file
! to be used as output destination (output file from previous runs is replaced)
 fcopy("recordout_templ.xls", "recordout.xls")
 if getsysstat<>0 then writeln("Copying failed"); exit(1); end-if

! Column ranges for output can be specified in various ways
 initializations to CSTR2
 ! Output complete records
  PDATA as "grow;[A:D]"
  ALLDATA as "noindex;grow;[F:I]"

 ! Output selected record fields
  PDATA(Cost) as "grow;[R1C11:R1C13]"
  ALLDATA(Product,Mach,Duration) as "noindex;grow;[R1C15:R1C17]"
 end-initializations

end-model
recordout4.mos
(!******************************************************
   Mosel Example Problems
   ====================== 

   file recordout4.mos
   ```````````````````
   Writing out records to an Oracle database.
   - Using 'initializations to' and OCI statements -
   
   (c) 2012 Fair Isaac Corporation
       author: S. Heipcke, Dec. 2012
*******************************************************!)

model "Record output (OCI)"
 uses "mmoci"

 parameters
  DB="myname/mypassword@dbname"     ! Login to Oracle database (not provided)
  CNCT="mmoci.oci:debug;"+DB
 end-parameters

 declarations
  PRODUCTS: set of string
  MACH: range
  ProdRec = record
   Cost: real
   Duration: integer
  end-record 
  PDATA: dynamic array(PRODUCTS,MACH) of ProdRec

  R = 1..9
  AllDataRec = record
   Product: string
   Mach: integer
   Cost: real
   Duration: integer
  end-record 
  ALLDATA: array(R) of AllDataRec
 end-declarations

! **** Reading data from a text file
 initializations from "recorddata.dat"
  PDATA as "ProdData"
  ALLDATA as "AllData"
 end-initializations


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

 setparam("OCIdebug",true)
 OCIlogon(DB)

 OCIexecute("delete from ProdDataOutH") ! Cleaning up previous results 
 OCIexecute("insert into ProdDataOutH(IndexP,IndexM,Cost,Duration) values (:1, :2, :3, :4)",
  PDATA)
 OCIexecute("delete from AllDataOutH")  ! Cleaning up previous results
 setparam("ocindxcol",false)            ! Dense data format (no indices)
 OCIexecute("insert into AllDataOutH(IndexP,IndexM,Cost,Duration) values (:1, :2, :3, :4)",
  ALLDATA)

 OCIlogoff


! **** Using 'initializations to' ****

! Write out complete records
 initializations to CNCT
  PDATA as "ProdDataOutH"
  ALLDATA as "noindex;AllDataOutH"
 end-initializations

! Write out record fields
 initializations to CNCT
  PDATA(Cost) as "CostOutH"
  ALLDATA(Product,Mach,Duration) as "noindex;DurationOutH"
 end-initializations

end-model

**************************************************


! Creation of data tables in an Oracle database:

 declarations
  tsucc: array ({false,true}) of string
 end-declarations

 tsucc(false):="failed"; tsucc(true):="succeeded"

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

 OCIexecute("drop table ProdDataOutH")
 OCIexecute("create table ProdDataOutH (IndexP varchar(10), IndexM integer, Cost float, Duration integer)")
 writeln(" - Create ProdDataOutH (",tsucc(getparam("OCIsuccess")),")")

 OCIexecute("drop table AllDataOutH")
 OCIexecute("create table AllDataOutH (IndexP varchar(10), IndexM integer, Cost float, Duration integer)")
 writeln(" - Create AllDataOutH (",tsucc(getparam("OCIsuccess")),")")

 OCIexecute("drop table CostOutH")
 OCIexecute("create table CostOutH (IndexP varchar(10), IndexM integer, Cost float)")
 writeln(" - Create CostOutH (",tsucc(getparam("OCIsuccess")),")")

 OCIexecute("drop table DurationOutH")
 OCIexecute("create table DurationOutH (IndexP varchar(10), IndexM integer, Duration integer)")
 writeln(" - Create DurationOutH (",tsucc(getparam("OCIsuccess")),")")

 OCIlogoff
recordout5.mos
(!******************************************************
   Mosel Example Problems
   ====================== 

   file recordout5.mos
   ```````````````````
   Writing out records to spreadsheets.
   - Using 'initializations to' with the xls driver -
   
   (c) 2012 Fair Isaac Corporation
       author: S. Heipcke, Dec. 2012, rev. Feb. 2019
*******************************************************!)

model "Record output (generic spreadsheet)"
 uses "mmsheet", "mmsystem"

 parameters
  CSTR= 'mmsheet.xls:recorddata.xls'
!  CSTR= 'mmsheet.xlsx:recorddata.xlsx'
  FNAME2='recordout.xls'
  CSTR2= 'mmsheet.xls:skiph+;'+FNAME2
!  FNAME2='recordout.xlsx'
!  CSTR2= 'mmsheet.xlsx:skiph+;'+FNAME2
 end-parameters

 declarations
  PRODUCTS: set of string
  MACH: range
  ProdRec = record
   Cost: real
   Duration: integer
  end-record 
  PDATA: dynamic array(PRODUCTS,MACH) of ProdRec

  R = 1..9
  AllDataRec = record
   Product: string
   Mach: integer
   Cost: real
   Duration: integer
  end-record 
  ALLDATA: array(R) of AllDataRec
 end-declarations

! **** Reading data from a text file
 initializations from "recorddata.dat"
  PDATA as "ProdData"
  ALLDATA as "AllData"
 end-initializations

! **** Writing out into a spreadsheet with named ranges ****

! Write out complete records
! (This assumes that the output tables have been created previously.)
! Driver options: noindex - dense data
 initializations to CSTR
  PDATA as "ProdDataOut"
  ALLDATA as "noindex;AllDataOut"
 end-initializations

! Write out selected record fields
 initializations to CSTR
  PDATA(Cost) as "CostOut"
  ALLDATA(Product,Mach,Duration) as "noindex;DurationOut"
 end-initializations


! **** Writing out into a new spreadsheet file (without named ranges) ****

! Remove any copy of the output file created by previous runs
 fdelete(FNAME2)

! Driver option skiph+ enables addition of specified column titles in output
! Column ranges can be specified in various ways;
! if no sheet name is given the first sheet is used
 initializations to CSTR2
 ! Output complete records 
  PDATA as "grow;[OutputData$A:D](Product,Mach,Cost,Duration)"
  ALLDATA as "noindex;grow;[F:I](Product,Mach,Cost,Duration)"

 ! Output selected record fields
  PDATA(Cost) as "grow;[R1C11:R1C13](Product,Mach,Cost)"
  ALLDATA(Product,Mach,Duration) as "noindex;grow;[R1C15:R1C17](Product,Mach,Duration)"
 end-initializations

end-model
recordout6.mos
(!******************************************************
   Mosel Example Problems
   ====================== 

   file recordout6.mos
   ```````````````````
   Writing out records to spreadsheets.
   - Using 'initializations to' with the csv driver -
   
   (c) 2012 Fair Isaac Corporation
       author: S. Heipcke, Dec. 2012, rev. Feb. 2019
*******************************************************!)

model "Record output (CSV)"
 uses "mmsheet", "mmsystem"

 parameters
  CSTR= 'mmsheet.csv:recorddataout.csv'
  FNAME2='recordout.csv'
  CSTR2= 'mmsheet.csv:skiph+;'+FNAME2
 end-parameters

 declarations
  PRODUCTS: set of string
  MACH: range
  ProdRec = record
   Cost: real
   Duration: integer
  end-record 
  PDATA: dynamic array(PRODUCTS,MACH) of ProdRec

  R = 1..9
  AllDataRec = record
   Product: string
   Mach: integer
   Cost: real
   Duration: integer
  end-record 
  ALLDATA: array(R) of AllDataRec
 end-declarations

! **** Reading data from a text file
 initializations from "recorddata.dat"
  PDATA as "ProdData"
  ALLDATA as "AllData"
 end-initializations

! **** Writing out into an existing file ****

! Write out complete records
! (This assumes that the output tables have been created previously.)
! Driver options: noindex - dense data
 initializations to CSTR
  PDATA as "[B4:E12]"
  ALLDATA as "noindex;[G4:J12]"
 end-initializations

! Write out selected record fields
 initializations to CSTR
  PDATA(Cost) as "[B17:D25]"
  ALLDATA(Product,Mach,Duration) as "noindex;[G17:I25]"
 end-initializations


! **** Writing out into a new file ****

! Remove any copy of the output file created by previous runs
 fdelete(FNAME2)

! Driver option skiph+ enables addition of specified column titles in output
! Column ranges can be specified in various ways
 initializations to CSTR2
 ! Output complete records 
  PDATA as "grow;[A:D](Product,Mach,Cost,Duration)"
  ALLDATA as "noindex;grow;[F:I](Product,Mach,Cost,Duration)"

 ! Output selected record fields
  PDATA(Cost) as "grow;[R1C11:R1C13](Product,Mach,Cost)"
  ALLDATA(Product,Mach,Duration) as "noindex;grow;[R1C15:R1C17](Product,Mach,Duration)"
 end-initializations
 
end-model
listinout.mos
(!******************************************************
   Mosel Example Problems
   ====================== 

   file listinout.mos
   ``````````````````
   Reading/writing lists from/to
   spreadsheets or databases via ODBC.
   - Using 'initializations from' with odbc IO driver -
   
   (c) 2008 Fair Isaac Corporation
       author: S. Heipcke, Nov. 2007, rev. Jan. 2014
*******************************************************!)

model "List handling (ODBC)"
 uses "mmodbc"

 parameters
                              ! Use Excel spreadsheet `listdata.xls'
!  CNCTIO = "listdata.xls"
                              ! Use Access database `listdata.mdb'
  CNCTIO = "debug;listdata.mdb"
                              ! Use mysql database `listdata' (not provided)
!  CNCTIO = "debug;DSN=mysql;DB=listdata"
                              ! Use SQLite database 'listdata.sqlite' directly 
!  CNCTIO = "debug;listdata.sqlite"
                              ! Use SQLite database 'listdata.sqlite' via ODBC
!  CNCTIO = "debug;DSN=sqlite;DATABASE=listdata.sqlite"
 end-parameters

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

 initializations from "mmodbc.odbc:"+CNCTIO
  LI as "List1"
  A
  LS as "List2"
 end-initializations 

! 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)

 initializations to "mmodbc.odbc:"+CNCTIO
  LI as "List1Out"
  LS2 as "List2Out"
 end-initializations 

end-model
listinout1.mos
(!******************************************************
   Mosel Example Problems
   ====================== 

   file listinout1.mos
   ```````````````````
   Reading/writing lists from/to text files.
   
   (c) 2008 Fair Isaac Corporation
       author: S. Heipcke, Nov. 2007
*******************************************************!)

model "List handling (Text file)"

 declarations
  R: range
  LI: list of integer
  LS,LS2: array(R) of list of string   ! With text data files we can use
                                       ! compositions of structured types
 end-declarations

 initializations from "listdata.dat"
  LI as "List1"
  LS as "List2"
 end-initializations 

! Display the lists
 writeln("LI: ", LI)
 writeln("LS: ", LS)

! Reverse the list LI
 reverse(LI)

! Append some text to every entry of LS 
 forall(i in R) LS2(i):= sum(l in LS(i)) [l+" year"+i]

! Display the modified lists
 writeln("LI: ", LI)
 writeln("LS2: ", LS2)

 initializations to "listout.dat"
  LI as "List1Out"
  LS2 as "List2Out"
 end-initializations 

end-model
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. Sep. 2018
*******************************************************!)

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

 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
  R: range
  LI: list of integer
  A: integer
  LS,LS2: list of string
 end-declarations

 setparam("SQLverbose",true)

 SQLconnect(CNCT)
 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)
 SQLexecute("insert into List2Out values (?)", LS2)
 SQLdisconnect

end-model
listinout3.mos
(!******************************************************
   Mosel Example Problems
   ====================== 

   file listinout3.mos
   ```````````````````
   Reading/writing lists from/to
   spreadsheets or databases via ODBC.
   - Using 'initializations from' with the excel driver -
   
   (c) 2008 Fair Isaac Corporation
       author: S. Heipcke, Nov. 2007, rev. Dec. 2012
*******************************************************!)

model "List handling (Excel)"
 uses "mmsheet"

 parameters
  CSTR = 'mmsheet.excel:listdata.xls'
 end-parameters
 
 declarations
  R: range
  LI: list of integer
  A: integer
  LS,LS2: list of string
 end-declarations

 initializations from CSTR
  LI as "List1E"
  A as "AE"
  LS as "List2E"
 end-initializations 

! 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)

 initializations to CSTR
  LI as "List1EOut"
  LS2 as "List2EOut"             ! Output into a row
  LS2 as "grow;List2EGOut"       ! Output into a column
 end-initializations

end-model
listinout4.mos
(!******************************************************
   Mosel Example Problems
   ====================== 

   file listinout4.mos
   ```````````````````
   Reading/writing lists from/to
   an Oracle database.
   - Using 'initializations' and OCI statements -
   
   (c) 2012 Fair Isaac Corporation
       author: S. Heipcke, Dec. 2012
*******************************************************!)

model "List handling (OCI)"
 uses "mmoci"

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

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

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

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

 OCIexecute("select * from List1 ", LI)
 A:=OCIreadinteger("select * from A")
 OCIexecute("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)

 OCIexecute("delete from List1Out")     ! Cleaning up previous output   
 OCIexecute("delete from List2Out")
 OCIexecute("insert into List1Out values (:1)", LI)
 OCIexecute("insert into List2Out values (:1)", LS2)
 
 OCIlogoff

! **** Using 'initializations from' ****

 initializations from "mmoci.oci:debug;"+DB
  LI as "List1"
  A
  LS as "List2"
 end-initializations 

! 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)

 initializations to "mmoci.oci:"+DB
  LI as "List1Out"
  LS2 as "List2Out"
 end-initializations 

end-model


**************************************************


! Creation of data tables in an Oracle database:

 declarations
  tsucc: array ({false,true}) of string
 end-declarations

 tsucc(false):="failed"; tsucc(true):="succeeded"

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

 OCIexecute("create table List1 (ListValues integer)")
 writeln(" - Create List1 (",tsucc(getparam("OCIsuccess")),")")
 OCIexecute("insert into List1 (ListValues) values (:1)", [ 1,2,3,4,5,6,7,8 ])
 writeln(" - Insert values into List1 (",tsucc(getparam("OCIsuccess")),",",
        getparam("OCIrowcnt"),'/',getparam("OCIrowxfr")," rows)")
 
 OCIexecute("create table A (AValue integer)")
 writeln(" - Create A (",tsucc(getparam("OCIsuccess")),")")
 OCIexecute("insert into A (AValue) values (:1)", [ 2002 ])
 writeln(" - Insert values into A (",tsucc(getparam("OCIsuccess")),",",
        getparam("OCIrowcnt"),'/',getparam("OCIrowxfr")," rows)")
 
 OCIexecute("create table List2 (ListValues char(3))")
 writeln(" - Create List2 (",tsucc(getparam("OCIsuccess")),")")
 OCIexecute("insert into List2 (ListValues) values (:1)", [ Jan,May,Jul,Nov,Dec ])
 writeln(" - Insert values into List2 (",tsucc(getparam("OCIsuccess")),",",
        getparam("OCIrowcnt"),'/',getparam("OCIrowxfr")," rows)")
 
 OCIexecute("create table List1Out (ListValues integer)")
 writeln(" - Create List1Out (",tsucc(getparam("OCIsuccess")),")")
 OCIexecute("create table List2Out (ListValues char(10))")
 writeln(" - Create List2Out (",tsucc(getparam("OCIsuccess")),")")
 
 OCIlogoff
listinout5.mos
(!******************************************************
   Mosel Example Problems
   ====================== 

   file listinout5.mos
   ```````````````````
   Reading/writing lists from/to spreadsheets.
   - Using 'initializations from' with the xls driver -
   
   (c) 2012 Fair Isaac Corporation
       author: S. Heipcke, Dec. 2012
*******************************************************!)

model "List handling (generic spreadsheet)"
 uses "mmsheet"

 parameters
!  CSTR= 'mmsheet.xls:listdata.xls'
  CSTR= 'mmsheet.xlsx:listdata.xlsx'
 end-parameters
 
 declarations
  R: range
  LI: list of integer
  A: integer
  LS,LS2: list of string
 end-declarations

 initializations from CSTR
  LI as "List1E"
  A as "AE"
  LS as "List2E"
 end-initializations 

! 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)

 initializations to CSTR
  LI as "List1EOut"
  LS2 as "List2EOut"             ! Output into a row
  LS2 as "grow;List2EGOut"       ! Output into a column
 end-initializations

end-model
listinout6.mos
(!******************************************************
   Mosel Example Problems
   ====================== 

   file listinout6.mos
   ```````````````````
   Reading/writing lists from/to spreadsheets.
   - Using 'initializations from' with the csv driver -
   
   (c) 2012 Fair Isaac Corporation
       author: S. Heipcke, Dec. 2012
*******************************************************!)

model "List handling (CSV)"
 uses "mmsheet"

 parameters
  CSTR_IN= 'mmsheet.csv:listdata.csv'
  CSTR_OUT= 'mmsheet.csv:listdataout.csv'
 end-parameters
 
 declarations
  R: range
  LI: list of integer
  A: integer
  LS,LS2: list of string
 end-declarations

 initializations from CSTR_IN
  LI as "[B4:I4]"
  A as "[K4]"
  LS as "[L4:P4]"
 end-initializations 

! 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)

 initializations to CSTR_OUT
  LI as "[B3:I3]"
  LS2 as "[K3:O3]"                 ! Output into a row
  LS2 as "grow;[K6]"               ! Output into a column
 end-initializations

end-model
Parent Topic Mosel language: Examples from the whitepaper 'Using ODBC and other database interfaces with Mosel'

© 2001-2020 Fair Isaac Corporation. All rights reserved. This documentation is the property of Fair Isaac Corporation (“FICO”). Receipt or possession of this documentation does not convey rights to disclose, reproduce, make derivative works, use, or allow others to use it except solely for internal evaluation purposes to determine whether to purchase a license to the software described in this documentation, or as otherwise set forth in a written software license agreement between you and FICO (or a FICO affiliate). Use of this documentation and the software described in it must conform strictly to the foregoing permitted uses, and no other use is permitted.