Data output to text files, spreadsheets and databases, and to memory

This example shows different ways of writing out data from a Mosel model, namely to

1. differently formatted text files (example trio_out.mos described in the 'Mosel User Guide', Section 10.2 File output): using 'initializations to', free-format output with 'write'/'writeln', or 'diskdata' format,
2. spreadsheets or databases (examples duo_out.mos, duosheet_out.mos, duooci_out.mos, and duoexc_out.mos described in the Xpress Whitepaper 'Using ODBC and other database interfaces with Mosel'),
3. the application (C/Java) executing the Mosel model (examples dataoutputc.mos and dataoutputj.mos

Using initializations to data is written to spreadsheets or databases through the ODBC connector in a similar way as text files are accessed; all necessary SQL commands are generated automatically. With the extended filename to "mmodbc.odbc:data.mdb" the data will be written to the MS Access database data.mdb and the string "mmodbc.odbc:DSN=mysql;DB=data" could be used to access a mysql database named data.

Instead of using an initializations block, we may write out data from a Mosel model directly with the corresponding SQL statements (this makes it possible, for example, to clear the data table before writing to it - this functionality is not available with spreadsheets)

When working with Excel spreadsheets it is recommended to use one of the dedicated spreadsheet drivers from the mmsheet module: with the excel driver, the spreadsheet may remain open while writing to it from a Mosel model, in this case the data written out to the spreadsheet does not get saved, making it possible to re-run the application with different data sets without causing any lasting modifications to your spreadsheet. All other spreadsheet drivers require the spreadsheet file to be closed. The xls/xlsx drivers do not require an installation of Excel and are available for certain non-Windows platforms.

The module mmoci (requires a separate license) defines a software-specific interface to Oracle databases that is used in a similar way as the ODBC connection provided through mmmodbc. With initializations to an extended filename will take a form like "mmoci.oci:debug;myname/mypassword@dbname" and it is equally possible to use PL/SQL statements directly.

Using initializations to it is also possible to send data from a Mosel model in memory to the calling application (C/C++, C# or Java). This is done using a combination of the I/O drivers mem (data held in memory) and raw driver (data in binary format) in C, drivers dotnet and dotnetraw in C# programs and drivers java and jraw in Java programs.

Other possibilities of outputting data from a Mosel model include

• Use of the Mosel Libraries: information about the model objects is retrieved after the execution of the model is terminated. See the 'Mosel User Guide', Part III Working with the Mosel Libraries and the example dataafterexec.c.
• Redirection of the output and error streams: to a file or to the calling application with the help of a callback function (I/O driver cb).
• Static modules: data exchange between the calling application and a model via custom functions, using the Mosel Native Interface. See the example datainout.c.
• Custom formats/custom data sources: the Mosel Native Interface allows the user to implement his own I/O drivers (to be used with initializations blocks and all other Mosel functionality involving file access, such as matrix export or model compilation). Possible uses of such user-written drivers include encoding/decoding of data on the fly or compression/decompression of data. See the Xpress Whitepaper 'Generalized file handling in Mosel' for the complete code and documentation of an example implementing a compression driver with the zip library.
  Furthermore, the Mosel I/O driver pipe may be used to channel the output of a Mosel model to an external program. See the section on I/O drivers in the 'Mosel Libraries Reference Manual' for more information.

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

   file duo_out.mos 
   ```````````````` 
   Two ways of writing data to spreadsheets or 
   databases via ODBC.
 
   (c) 2008 Fair Isaac Corporation
       author: S. Heipcke, 2006, rev. Jan. 2014
*******************************************************!)

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

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

 declarations
  A: array(-1..1,5..7) of real
 end-declarations

 A :: [ 2,  4,  6,
       12, 14, 16,
       22, 24, 26]

! First method: use an initializations block with the odbc driver
! ATTENTION: results from previous runs must be removed previously;
! otherwise the new results will either be appended to the existing ones
! or, if one of the fields has been defined as a key field in a database,
! the insertion will fail.

 initializations to "mmodbc.odbc:"+CNCTIO
  A as "MyOutTable1"
 end-initializations

! Second method: use SQL statements 
 SQLconnect(CNCT)
 SQLexecute("delete from MyOutTable2") ! 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 MyOutTable2 (Index1,Index2,AValue) values (?,?,?)", A)
 SQLdisconnect

end-model

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

   file duosheet_out.mos 
   ````````````````````` 
   Writing data to spreadsheets using the 
   generic spreadsheet drivers.
 
   (c) 2012 Fair Isaac Corporation
       author: S. Heipcke, Dec. 2012
*******************************************************!)

model "Duo output (generic spreadsheet)"
 uses "mmsheet"

 parameters
  CNCTIO1 = "data.xls"           ! Use Excel spreadsheet `data.xls'
  CNCTIO2 = "data.xlsx"          ! Use Excel spreadsheet `data.xlsx'
  CNCTIO3 = "data.csv"           ! Use spreadsheet `data.csv'
 end-parameters

 declarations
  A: array(-1..1,5..7) of real
 end-declarations

 A :: [ 2,  4,  6,
       12, 14, 16,
       22, 24, 26]

! Use the xlsx driver:
! Data writing always starts at the same point, that is, results from 
! previous runs will be overwritten
! Options: skiph - skip the header line (first line of specified range)
!          grow  - start line of output range is selected, grow on demand
 initializations to "mmsheet.xls:"+CNCTIO1
  A as 'skiph;grow;MyOutTable1'
 end-initializations

(!
! Alternatively:
 initializations to "mmsheet.xls:"+CNCTIO1
  A as 'grow;MyOutTable3'
 end-initializations 
! Or: 
 initializations to "mmsheet.xls:"+CNCTIO1
  A as '[Sheet1$F3:H11]'
 end-initializations
!)


! Use the xlsx driver:
 initializations to "mmsheet.xlsx:"+CNCTIO2
  A as 'skiph;grow;MyOutTable1'
 end-initializations


! Use the csv driver:
 initializations to "mmsheet.csv:"+CNCTIO3
  A as 'grow;[F3:H3]'
 end-initializations


! **** Creating a new file ****

! Use the xls driver:
 initializations to "mmsheet.xls:anewfile.xls"
  A as 'skiph+;grow;[1$F:H](Index1,Index2,Value_of_A)'
 end-initializations


! Use the xlsx driver:
 initializations to "mmsheet.xlsx:anewfile.xlsx"
  A as 'skiph+;grow;[1$F:H](Index1,Index2,Value_of_A)'
 end-initializations

! Use the csv driver:
 initializations to "mmsheet.csv:anewfile.csv"
  A as 'skiph+;grow;[F:H](Index1,Index2,Value_of_A)'
 end-initializations


end-model

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

   file duoexc_out.mos 
   ``````````````````` 
   Writing data to Excel spreadsheets using the 
   excel IO driver.
 
   (c) 2008 Fair Isaac Corporation
       author: S. Heipcke, 2007, rev. Dec. 2012
*******************************************************!)

model "Duo output (Excel)"
 uses "mmsheet"

 parameters
  CNCT = 'data.xls'
 end-parameters

 declarations
  A: array(-1..1,5..7) of real
 end-declarations

 A :: [ 2,  4,  6,
       12, 14, 16,
       22, 24, 26]

! Use an initializations block with the excel driver:
! data writing always starts at the same point, that is, results from 
! previous runs will be overwritten
! Options: skiph - skip the header line (first line of specified range)
!          grow  - start line of output range is selected, grow on demand
 initializations to "mmsheet.excel:" + CNCT
  A as 'skiph;grow;MyOutTable1'
 end-initializations
(!
! Alternatively:
 initializations to "mmsheet.excel:" + CNCT
  A as 'grow;MyOutTable3'
 end-initializations 
! Or: 
 initializations to "mmsheet.excel:" + CNCT
  A as '[Sheet1$F3:H11]'
 end-initializations
!)
end-model

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

   file duooci_out.mos 
   ``````````````````` 
   Two ways of writing data to Oracle databases.
 
   (c) 2008 Fair Isaac Corporation
       author: S. Heipcke, Jan. 2007
*******************************************************!)

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

 parameters
  CNCT = 'myname/mypassword@dbname'
 end-parameters

 declarations
  A: array(-1..1,5..7) of real
 end-declarations

 A :: [ 2,  4,  6,
       12, 14, 16,
       22, 24, 26]

! First method: use an initializations block with the oci driver
! ATTENTION: results from previous runs must be removed previously;
! otherwise the new results will either be appended to the existing ones
! or, if one of the fields has been defined as a key field in the database,
! the insertion will fail.

 initializations to "mmoci.oci:debug;"+CNCT
  A as "MyOutTable1"
 end-initializations

! Second method: use SQL statements 
 OCIlogon(CNCT)
 OCIexecute("delete from MyOutTable2") ! Cleaning up previous results: works
                                       ! only for databases, cannot be used
                                       ! with spreadsheets (instead, delete
                                       ! previous solutions directly in the
                                       ! spreadsheet file)
 OCIexecute("insert into MyOutTable2 (Index1,Index2,AValue) values (:1,:2,:3)", A)
! Alternatively:
! OCIexecute("update MyOutTable2 set AValue=:3 where Index1=:1 and Index2=:2", A)
 OCIlogoff

end-model



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


! Creation of the output tables in an Oracle database:

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

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

 OCIexecute("create table MyOutTable1 (Index1 integer, Index2 integer, Value float)")
 writeln(" - Create MyOutTable1 (",tsucc(getparam("OCIsuccess")),")")
 OCIexecute("create table MyOutTable2 (Index1 integer, Index2 integer, AValue float)")
 writeln(" - Create MyOutTable2 (",tsucc(getparam("OCIsuccess")),")")