Data source access from Mosel models

Topics covered in this section:

Access to data files with formats other than the initializations text file format can be achieved with the help of modules or packages that define additional functions specific to a format or data source type. Alternatively, other formats can be used directly in the initializations block, and hence minimizing the changes required in a model to switch between different data formats.

The drivers odbc, excel and diskdata described in this section are used in the initializations block of the Mosel language to provide access to new data file formats and data sources. Some of the drivers described in Section Exchange of information with embedded models (e.g. raw and mem) also provide such a functionality. However, they are only of use in models embedded into an application and run through the Mosel libraries, not in stand-alone Mosel models as is the case with the odbc, excel and diskdata drivers.

For a more in depth discussion of the database and spreadsheet drivers and examples of their use, the reader is referred to the Xpress whitepaper Using ODBC and other database interfaces with Mosel. Besides the ODBC and spreadsheet access functionality described in the following sections, this whitepaper also explains how to use the direct Oracle interface defined by the module mmoci (including the I/O driver mmoci.oci that behaves similar to mmodbc.odbc and SQL statements corresponding to the functionality described in Section Alternative implementation using SQL statements) in the place of an ODBC connection.

Database access via ODBC

The odbc I/O driver defined by the module mmodbc automatically generates the SQL queries that are required to read in data from an external data source (database) or insert data into this data source. The following modifications need to be made to model Burglar2 to switch to file access via ODBC:

model "Burglar2 (ODBC)"
 uses "mmxprs"

 parameters
  CNCTIO = ''
 end-parameters
 ...
 initializations from CNCTIO
  [VALUE,WEIGHT] as "BurgData"
 end-initializations
 ...
! Insert solutions into database: results from previous runs must be
! removed previously; otherwise the new results will either be appended
! to the existing ones or, if `ITEM' has been defined as key field
! in a database, the insertion may fail.
 initializations to CNCTIO
  SOLTAKE as "SolTake"
 end-initializations
end-model

The connection string CNCTIO indicates the data source, for example:

• CNCTIO = "mmodbc.odbc:burglar.mdb" for the MS Access database burglar.mdb
• CNCTIO = "mmodbc.odbc:DSN=mysql;DB=burglar" for the mysql database burglar

To run this example, the ODBC driver for the corresponding data source must be present. The connection string may vary depending on the installation of the ODBC driver. For further detail on setting up an ODBC connection and working with ODBC in Mosel see the Xpress Whitepaper `Using ODBC and other database interfaces with Mosel'.

In the case of database connections, the data are read from a table called BurgData that contains (at least) a field for the indices (e.g., labeled Item), and the fields Val and Wght. The results are written into the table SolTake, containing a field for the indices (e.g., Item) and a field to receive the solution values (e.g., called IfTake). In a spreadsheet, BurgData and SolTake are the names of named ranges. The columns of the named ranges must bear headers (to be included in the selected range area), these headers are not used by the (generated) SQL commands and may therefore differ from the names used in the model. The range BurgData must have three columns, for instance bearing the headers Item, Val, and Wght, and the range SolTake must have a column to receive the indices (e.g., labeled Item) and a second one to receive the solution values (e.g., IfTake). If a database table has additional fields, or its fields do not appear in the order expected by Mosel, we can append the database field names to the table name, for example:

 initializations from CNCTIO
  [VALUE,WEIGHT] as "BurgData(Item,Val,Wght)"
 end-initializations

The ODBC driver may take several options, see the section `mmodbc' in the `Mosel Language Reference Manual' for further detail.

Alternative implementation using SQL statements

The model Burglar2 may be implemented as follows using standard mmodbc functionality.

model "Burglar2 (SQL)"
 uses "mmxprs", "mmodbc"

 parameters
  CNCT = ''
 end-parameters
 ...
! Reading data from file
 SQLconnect(CNCT)
 if not getparam("SQLsuccess"): setioerr("Database connection failed")
 SQLexecute("select * from BurgData", [VALUE,WEIGHT])
 SQLdisconnect
 ...
! Solution output
 SQLconnect(CNCT)
 SQLexecute("delete from SolTake")    ! 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 SolTake values (?,?)" , SOLTAKE)
 SQLdisconnect
end-model

As before, the connection string CNCT indicates the data source:

• CNCT = "burglar.mdb" (the full form "DSN=MS Access Database;DBQ=burglar.mdb" is determined automatically) for the MS Access database burglar.mdb
• CNCT = "DSN=mysql;DB=burglar" for the mysql database burglar

As shown in the code snippet above, the execution status of SQL commands, and in particular of the connection to the database, needs to be checked via the parameter SQLsuccess and handled suitably by the Mosel program.

All of these functions may be used in conjunction with the ODBC I/O driver. For instance, when working with a database it may be helpful to make the lines

 SQLconnect(CNCT)
 SQLexecute("delete from SolTake")

preceed the insertion of the solution into the database to clean up previous results.

Spreadsheet drivers

The model mmsheet provides a set of spreadsheet I/O drivers that implement all basic data access tasks similarly to the functionality provided by ODBC (but none of the advanced SQL statements).

• mmsheet.excel is a software-specific I/O driver that accesses directly Excel spreadsheets. If the spreadsheet file is kept opened while running the Mosel model the output is written to the spreadsheet without saving it. This driver cannot be used remotely.
• mmsheet.xls and mmsheet.xlsx are I/O drivers for accessing spreadsheets in the Excel formats XLS and XLSX/XLSM respectively. They do not depend on Excel and can be used for reading and writing to spreadsheets on non-Windows platforms (Linux, macOS). They take the same options as mmsheet.excel and their use is analogous to this driver, with the exception that they save data immediately into the spreadsheet file, that is, an output file should not be open in another application while writing to it with these drivers.
• mmsheet.csv provides access to spreadsheet files in CSV format (text files). It is available for all platforms running Mosel and the file name is not restricted to physical files, it may be an extended file name. As with xls/xlsx output data is saved directly into the file.

With all mmsheet spreadsheet drivers output always starts at the same place (that is, previous output gets overwritten).

model "Burglar2 (Excel)"
 uses "mmxprs"

 parameters
  CNCTEXC = 'mmsheet.excel:burglar.xls'
 end-parameters
 ...

! Spreadsheet range includes header line -> option 'skiph' to skip header
 initializations from CNCTEXC
  [VALUE,WEIGHT] as "skiph;BurgData"
 end-initializations
 ...

! Insert solutions into spreadsheet: results from previous runs
! are overwritten by new output
! Only first line of output range is specified -> option 'grow'
 initializations to CNCTEXC
  SOLTAKE as "skiph;grow;SolTake"
 end-initializations
end-model

The input and output ranges in the spreadsheet used with the excel driver contain just the data, no column headers. To work with the ODBC format of ranges the option skiph needs to be used as shown in the example. Another option employed when outputting data is grow, this indicates that the output range is specified by a single row and may grow on demand.

Instead of naming the ranges in the spreadsheet it is equally possible to work directly with the cell references for the input and output ranges (including the worksheet name, which is `burglar' in our case):

 initializations from CNCTEXC
  [VALUE,WEIGHT] as "[burglar$B4:D11]"
 end-initializations
 ...

! Insert solutions into spreadsheet: results from previous runs
! are overwritten by new output
! Only first line of output range is specified -> option 'grow'
 initializations to CNCTEXC
  SOLTAKE as "grow;[burglar$F4:G4]"
 end-initializations

Or alternatively, using the row-column notation:

 initializations from CNCTEXC
  [VALUE,WEIGHT] as "[burglar$R4C2:R11C4]"
 end-initializations
 ...

 initializations to CNCTEXC
  SOLTAKE as "grow;[burglar$R4C6:R4C7]"
 end-initializations

Other formatting options for the specification of spreadsheet ranges include the selection of columns via their relative position within the cell range:

 initializations from CNCTEXC
  [VALUE,WEIGHT] as "[burglar$A4:F11](#2,#3,#4)"
 end-initializations

and the possibility to omit row numbers, thereby indicating that we want to read the contents of the entire column, typically used in combination with option skiph to skip the first row (assuming it contains the header text):

 initializations from CNCTEXC
  [VALUE,WEIGHT] as "skiph;[burglar$F:G]"
 end-initializations

Switching to the more generic driver mmsheet.xls in the examples above simply means changing the driver name in the filename string CNCTEXC. For CSV format, besides changing the driver name, we need to save the spreadsheet with CSV format. CSV files do not contain worksheets or range names, so ranges always need to be defined via cell references when using mmsheet.csv.

For further detail on the spreadsheet drivers the reader is refered to the section `mmsheet' of the `Mosel Language Reference Manual' and the examples discussed in the Xpress Whitepaper `Using ODBC and other database interfaces with Mosel'.

Alternative text data file formats

In the previous section we have introduced the I/O driver mmsheet.csv that reads and writes text files in CSV format. With only minimal changes to the previous model we now switch to a different text data format, namely the diskdata file format that is accessed via the diskdata I/O driver.

model Burglar2dd
 ...
 initializations from "mmetc.diskdata:sparse;noq"
  [VALUE,WEIGHT] as "burglardd.dat"
 end-initializations
 ...
 initializations to "mmetc.diskdata:append,sparse"
  SOLTAKE as "burglarout.txt"
 end-initializations
end-model

This will result in exactly the same behaviour as when using the procedure diskdata of the module mmetc instead of the initializations blocks.

model Burglar2ddb
 uses "mmetc", "mmxprs"
 ...
! Reading data from file
 diskdata(ETC_SPARSE+ETC_NOQ, "burglardd.dat", [VALUE,WEIGHT])
 ...
! Solution output
 diskdata(ETC_OUT+ETC_APPEND+ETC_SPARSE, "burglarout.txt", SOLTAKE)
end-model

The data file burglardd.dat used in both cases has the following contents.

! Data file for `burglar2dd.mos'
camera,    15,  2
necklace, 100, 20
vase,      90, 20
picture,   60, 30
tv,        40, 40
video,     15, 30
chest,     10, 60
brick,      1, 10

The result file burglarout.txt is now formatted as shown here.

"camera",1
"necklace",1
"vase",1
"picture",1
"tv",0
"video",1
"chest",0
"brick",0

The diskdata I/O driver and the procedure diskdata are documented in the section `mmetc' in the `Mosel Language Reference Manual'.

XML and JSON format files

The data for our example problem could also be provided in the form of the following XML file burglar.xml.

<!-- Data file for `burglar2x.mos' -->
<BurgData>
  <Item name="camera">   <Value> 15</Value> <Weight> 2</Weight> </Item>
  <Item name="necklace"> <Value>100</Value> <Weight>20</Weight> </Item>
  <Item name="vase">     <Value> 90</Value> <Weight>20</Weight> </Item>
  <Item name="picture">  <Value> 60</Value> <Weight>30</Weight> </Item>
  <Item name="tv">       <Value> 40</Value> <Weight>40</Weight> </Item>
  <Item name="video">    <Value> 15</Value> <Weight>30</Weight> </Item>
  <Item name="chest">    <Value> 10</Value> <Weight>60</Weight> </Item>
  <Item name="brick">    <Value>  1</Value> <Weight>10</Weight> </Item>
</BurgData> 

Given that XML files do not use any fixed format that would make it possible to define a suitable I/O driver, we cannot read or write XML files through initializations blocks. XML files need to be parsed or queried for their contents using the routines of the mmxml module. The data file burglar.xml is read into our Mosel data structures using the following code:

 declarations
  ITEMS: set of string           ! Index set for items
  VALUE: array(ITEMS) of real    ! Value of items
  WEIGHT: array(ITEMS) of real   ! Weight of items

  BurgData, ResData: xmldoc      ! XML document
  Root, Node: integer            ! XML nodes
  NodeList: list of integer
 end-declarations

! Reading data from an XML file
 load(BurgData, "burglar.xml")

! Retrieve all 'Item' nodes
 getnodes(BurgData, "BurgData/Item", NodeList)

! Retrieve 'Value' and 'Weight' information
 forall(i in NodeList) do
   VALUE(getstrattr(BurgData,i,"name")):=
     getrealvalue(BurgData, getnode(BurgData, i, "Value") )
   WEIGHT(getstrattr(BurgData,i,"name")):=
     getrealvalue(BurgData, getnode(BurgData, i, "Weight") )
 end-do   

For generating solution output in XML format we need to build up an XML document following standard XML design rules (all elements are forming a tree under a single root element).

! Create solution representation in XML format
 Root:=addnode(ResData, 0, XML_ELT, "SolTake")    ! Create root node "SolTake"
 setattr(ResData, Root, "objective", getobjval)   ! ... with attr. "objective"

 forall(i in ITEMS) do
   Node:=addnode(ResData, Root, XML_ELT, "Item") ! Add a node to "SolTake"
   setattr(ResData, Node, "name", i)             ! ... with attribute "name"
   setvalue(ResData, Node, SOLTAKE(i))           ! ... and solution value
 end-do

 save(ResData, "burglarout.xml")    ! Save solution to XML format file 

This Mosel code will result in the following output (the appearance can be influenced via different controls and settings of mmxml, see the corresponding chapter of the `Mosel Language Reference Manual'):

<?xml version="1.0" encoding="iso-8859-1"?>
<SolTake objective="280">
  <Item name="camera">1</Item>
  <Item name="necklace">1</Item>
  <Item name="vase">1</Item>
  <Item name="picture">1</Item>
  <Item name="tv">0</Item>
  <Item name="video">1</Item>
  <Item name="chest">0</Item>
  <Item name="brick">0</Item>
</SolTake>   

The module mmxml also provides routines for reading and writing text files in JSON format, transforming them internally to an XML-based representation where JSON 'values' are encoded as jsv elements. The value of the attribute 'jst' indicates the object type ( 'num'—number, 'boo'—boolean, 'str'—string, 'nul'—null, 'obj'—object, or 'arr'—array). Elements representing object members have the name of the member (instead of jsv).
Data from the following JSON file

{
  "Item": [
    {"Name":"camera",   "Value": 15, "Weight": 2},
    {"Name":"necklace", "Value":100, "Weight":20},
    {"Name":"vase",     "Value": 90, "Weight":20},
    {"Name":"picture",  "Value": 60, "Weight":30},
    {"Name":"tv",       "Value": 40, "Weight":40},
    {"Name":"video",    "Value": 15, "Weight":30},
    {"Name":"chest",    "Value": 10, "Weight":60},
    {"Name":"brick",    "Value":  1, "Weight":10} ]
}

can be read with this Mosel code (all declarations remain the same as in the previous model version reading XML format data):

! Reading data from a JSON file
 jsonload(BurgData, "burglar.json")

! Retrieve all 'Item' nodes
 getnodes(BurgData, "jsv/Item/jsv", NodeList)

! Retrieve 'Value' and 'Weight' information
 forall(i in NodeList) do
   VALUE(getstrvalue(BurgData, getnode(BurgData,i,"Name"))):=
     getrealvalue(BurgData, getnode(BurgData, i, "Value") )
   WEIGHT(getstrvalue(BurgData, getnode(BurgData,i,"Name"))):=
     getrealvalue(BurgData, getnode(BurgData, i, "Weight") )
 end-do
 

After loading the contents of a JSON file, it may be helpful to display the resulting XML representation on screen since we parse this representation to retrieve the input data:

 save(BurgData, "")        ! Display the XML representation on screen

The results output in JSON format is constructed using XML routines:

! Create solution representation in JSON format
 Root:=addnode(ResData, 0, XML_ELT, "jsv")            ! Create root node "jsv"
 Node:=addnode(ResData, Root, "Objective", getobjval) ! Add a node to "jsv"
 Node:=addnode(ResData, Root, XML_ELT, "Items")       ! Add a node to "jsv"
 forall(i in ITEMS)
   n:=addnode(ResData, Node, i, SOLTAKE(i))           ! Add a node to "Items"

 jsonsave(ResData, "burglarout.json")    ! Save solution to JSON format file

This results in the following output in file burglarout.json:

{
  "Objective":280,
  "Items":{
    "camera":1,
    "necklace":1,
    "vase":1,
    "picture":1,
    "tv":0,
    "video":1,
    "chest":0,
    "brick":0
  }
}

Free format files

If none of the provided text file access methods fits a particular data file format, you can always use read/readln in combination with fopen and fclose. For example, a data file of the form

! Data file for `burglar2ff.mos'
camera [v=15, w=2]
necklace [v=100, w=20]
...

can be read by the following Mosel code (model file burglar2ff.mos)

 fopen("burglarff.dat", F_INPUT) ! Open file for reading
 while (not iseof) do            ! Read up to end-of-file
   fskipline("!")                ! Skip lines starting with '!'
   readln(j, " [v=", VALUE(j), ", w=", WEIGHT(j), "]")
   if getparam("nbread") < 6 then
     writeln("Error reading data for index '", j, "'")
   end-if
 end-do
 fclose(F_INPUT)                 ! Close the input file 

And similarly, for redirecting output to a file instead of displaying it on the default output (screen) we simply surround it by fopen and fclose, this time selecting the output stream in fopen:

 fopen("burglarsol.txt", F_OUTPUT)
 writeln("Solution:\n Objective: ", getobjval)
 forall(i in ITEMS)  writeln(" take(", i, "): ", getsol(take(i)))
 fclose(F_OUTPUT)

Multiline text or string

It is also possible to include data in the form of text blocks directly in a model. A multiline text (or string) must be surrounded by backquotes and an optional marker text. For example, the text data file read by the initial version of our model can be included and read thus (model version burglar2t.mos):

 public declarations
  default_data: text            ! Data input values
 end-declarations

 default_data:=
`--------------- Input data ----------------
           ! Item      Value  Weight
BurgData: [(camera)    [ 15   2]
           (necklace)  [100  20]
           (vase)      [ 90  20]
           (picture)   [ 60  30]
           (tv)        [ 40  40]
           (video)     [ 15  30]
           (chest)     [ 10  60]
           (brick)     [  1  10]]
--------------- Input data ----------------`

 initializations from "text:default_data"
  [VALUE,WEIGHT] as "BurgData"
 end-initializations

And similarly, for redirecting output in Mosel's text format to a text block instead of writing it to a physical file we can use the following Mosel code:

 public declarations
  burglarout: text              ! Solution output
 end-declarations

 initializations to "text:burglarout"
  SOLTAKE
 end-initializations

! Display contents of output file
 writeln("Solution file:\n", burglarout)