ODBC

To access spreadsheets or databases through initializations blocks using an ODBC connection we need to prefix the name of the data source (ODBC connection string as described in Section ODBC connection strings in Mosel) by the name of the ODBC I/O driver followed by a colon, that is, "mmodbc.odbc:".

Data input using odbc

As a first example we shall now see how to read data from an MS Access database into a Mosel array. Let us suppose that in a database called data.mdb you have created the following table MyDataTable with 3 fields holding the following data values:

We may then use the following model duo.mos to read in the array A4 from the database and print it out.

model "Duo input (1)"
 uses "mmodbc"

 declarations
  A4: dynamic array(range,range) of real
 end-declarations

! Use an initializations block with the odbc driver to read data
 initializations from "mmodbc.odbc:data.mdb"
  A4 as 'MyDataTable'
 end-initializations

! Print out the data we have read
 writeln('A4 is: ', A4)

end-model 

If we want to read the data from another database, say the SQLite database data.sqlite, the only change we need to make is to change the filename string to "mmodbc.odbc:data.sqlite". For a MySQL database data we would have to use the long form of the connection string: "mmodbc.odbc:DSN=mysql;DB=data". Any database we use with the model printed above needs to contain a table called `MyDataTable' with three fields, the first two (for the indices) of type integer, and the third of type double.

Data output using odbc

Outputting data from a Mosel model through the odbc I/O driver again only requires few changes to the model. Consider the following example (file duo_out.mos)—notice that the index ranges are -1, 0, 1 and 5, 6, 7 and not the standard 1, 2, 3:

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

 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 odbc driver for writing data
 initializations to "mmodbc.odbc:data.mdb"
  A as "MyOutTable1"
 end-initializations

end-model 

When we wish to write data to a database we need to prepare a suitable data table to receive the data: our table needs to be named `MyOutTable1' with fields that correspond to the data array we want to write. In our case, the first two (index) fields must be of type integer and the third field of type double.

In terms of database functionality, when writing out data with initializations to, Mosel performs an ``insert'', no replacement or update. If the data table contains already some data, for instance from previous model runs, the new output will be appended to the existing data. In the case of a database table, the insertion will fail if a key field has been defined and you are trying to write the same data entries a second time. The deletion of any existing data in the table(s) used for output must be done manually directly in the database or spreadsheet, or with the corresponding SQL commands in the Mosel model (the latter option only applies to databases). With this objective, the odbc I/O driver may be used in combination with other mmodbc functionality, for instance to execute specific SQL queries (see Section ODBC).