mmodbc

Topics covered in this chapter:

The Mosel ODBC interface provides a set of procedures and functions that may be used to access databases for which an ODBC driver is available. This module also includes the SQLite database engine that can be directly run without the need for any additional software.
To use the ODBC interface, the following line must be included in the header of a Mosel model file:

 uses 'mmodbc'

This manual describes the Mosel ODBC interface and shows how to use some standard SQL commands, but it is not meant to serve as a manual for SQL. The reader is referred to the documentation of the software he is using for more detailed information on these topics.

Prerequisite

The ODBC technology relies on a driver manager that is used as an interface between applications (like mmodbc) and a data source itself accessed through a dedicated driver. As a consequence, this module requires that both, a driver manager and the necessary drivers (one for each data source to be used), are installed and set up on the operating system.

Under Windows, usually the driver manager is part of the system and most data sources are provided with their ODBC driver (for instance Excel, Access or SQLServer).

On the other supported operating systems it may be necessary to install a driver manager (as well as the necessary drivers). The module mmodbc supports two driver managers: iODBC (http://www.iodbc.org) and unixODBC (http://www.unixodbc.org). Upon startup the module tries to load the dynamic library "libiodbc.so" then, if this fails, tries "libodbc.so". If none of these libraries can be found only the SQLite integrated driver will be available, please make sure that one of the driver managers is installed and that the corresponding libraries can be accessed (in general this requires updating some environment variable).

Example

Assume that the data source ``mydata'' defines a database that contains a table ``pricelist'' of the following form:

articlenum	color	price
1001	blue	10.49
1002	red	10.49
1003	black	5.99
1004	blue	3.99
...

The following small example shows how to connect to a database from an Mosel model file, read in data, and disconnect from the data source.

model 'ODBCexample'
 uses 'mmodbc'

 declarations
  prices: array (range) of real
 end-declarations

 setparam("SQLverbose", true)   ! Enable ODBC message printing in case of error
 SQLconnect("DSN=mydata")       ! Connect to the database defined by `mydata'

 writeln("Connection number: ", getparam("SQLconnection"))

 SQLexecute("select articlenum,price from pricelist", prices)
                                ! Get the entries of field `price' (indexed by
                                ! field `articlenum') in table `pricelist'

 SQLdisconnect                  ! Disconnect from the database
end-model

Here the SQLverbose control parameter is set to true to enable ODBC message printing in case of error. Following the connection, the procedure SQLexecute is called to retrieve entries from the field price (indexed by field articlenum) in the table pricelist. Finally, the connection is closed.

For further examples of working with databases and spreadsheets, the reader is referred to the Xpress whitepaper Using ODBC and other database interfaces with Mosel.

Data transfer between Mosel and the database

Data transfer beetween Mosel and the database is achieved by calls to the procedure SQLexecute. The value of the control parameter SQLndxcol and the type and structure of the second argument of the procedure decide how the data are transferred between the two systems.

From the database to Mosel

Information is moved from the database to Mosel when performing a SELECT command for instance. Assuming mt has been declared as follows:

mt: array(1..10,1..3) of integer

the execution of the call:

SQLexecute("SELECT c1,c2,c3 from T", mt)

behaves differently depending on the value of SQLndxcol. If this control parameter is true, the columns c1 and c2 are used as indices and c3 is the value to be assigned. For each row (i,j,k) of the result set, the following assignment is performed by mmodbc:

mt(i,j):=k

With a table T containing:

c1 c2 c3 c4
 1  2  5 7
 4  3  6 8

We obtain the initialization:

m2(1,2)=5, m(4,3)=6

If the control parameter SQLndxcol is false, all columns are treated as data. In this case, for each row (i,j,k) the following assignments are performed:

mt(r,1):=i; mt(r,2):=j; mt(r,3):=k

where r is the row number in the result set.

Here, the resulting initialization is:

mt(1,1)=1, mt(1,2)=2, mt(1,3)=5
mt(2,1)=4, mt(2,2)=3, mt(2,3)=6

If the SQL statement selects 4 columns (instead of 3) as in:

SQLexecute("SELECT c1,c2,c3,c4 from T", mt)

and the control parameter SQLndxcol is false, the first column is used as the first array index while the remaining columns are treated as data. As a consequence, for each row (i,j,k,l) the following assignments are performed:

mt(i,1):=j; mt(i,2):=k; mt(i,3):=l

The resulting initialization is therefore:

mt(1,1)=2, mt(1,2)=5, mt(1,3)=7
mt(4,1)=3, mt(4,2)=6, mt(4,3)=8

The second argument of SQLexecute may also be a list of arrays. When using this version, the value of SQLndxcol is ignored and the first column(s) of the result set are always considered as indices and the following ones as values for the corresponding arrays. For instance, assuming we have the following declarations:

m1, m2: array(1..10) of integer

With the statement:

SQLexecute("SELECT c1,c2,c3 from T", [m1,m2])

for each row (i,j,k) of the result set, the following assignments are performed:

m1(i):=j; m2(i):=k

So, if we use the table T of our previous example, we get the initialization:

m1(1)=2, m1(4)=5
m2(1)=3, m2(4)=6

From Mosel to the database

Information is transferred from Mosel to the database when performing an INSERT command for instance. In this case, the way to use the Mosel arrays has to be specified by using parameters in the SQL command. These parameters are identified by the symbol `?' in the expression. For instance in the following expression 3 parameters are used:

INSERT INTO T (c1,c2,c3) VALUES (?,?,?)

The command is then executed repeatedly as many times as the provided data allows to build new tuples of parameters. The initialization of parameters is similar to what is done for a SELECT statement.

Assuming mt has been declared as follows:

mt: array(1..2,1..3) of integer

and initialized with this assignment:

mt::[1,2,3,
     4,5,6]

the execution of the call:

SQLexecute("INSERT INTO T (c1,c2,c3) VALUES (?,?,?)",mt)

behaves differently depending on the value of SQLndxcol. If this control parameter is true, for each execution of the command, the following assignments are performed by mmodbc (?1,?2,?3 denote respectively the first second and third parameter):

'?1':= i, '?2':= j, '?3':= mt(i,j)  

The execution is repeated for all possible values of i and j (in our example 6 times). The resulting table T is therefore:

c1 c2 c3
 1  1  1
 1  2  2
 1  3  3
 2  1  4
 2  2  5
 2  3  6

If the control parameter SQLndxcol is false, only the values of the Mosel array are used to initialize the parameters. So, for each execution of the command, we have:

'?1':=mt(i,1), '?2':=mt(i,2), '?3':=mt(i,3)

The execution is repeated for all possible values of i (in our example 2 times). The resulting table T is therefore:

c1 c2 c3
 1  2  3
 4  5  6

However if the SQL query defines 4 parameters (instead of 3) as in:

SQLexecute("INSERT INTO T (c1,c2,c3,c4) VALUES (?,?,?,?)",mt)

and the control parameter SQLndxcol is false, the first parameter is used as the first array index while the remaining parameters are populated with data. As a consequence, for each execution of the command, the following assignments are performed by mmodbc:

'?1':= i, '?2':= mt(i,1), '?3':= mt(i,2), '?4':=mf(i,3)

The execution is repeated for all possible values of i (in our example 2 times). The resulting table T is therefore:

c1 c2 c3 c4
 1  1  2  3
 2  4  5  6

When SQLexecute is used with a list of arrays, the behavior is again similar to what has been described earlier for the SELECT command: the first parameter(s) are assigned index values and the final ones the actual array values. For instance, assuming we have the following declarations:

m1,m2: array(1..3) of integer

And the arrays have been initialized as follows:

m1::[1,2,3]
m2::[4,5,6]

Then the following call:

SQLexecute("INSERT INTO T (c1,c2,c3) VALUES (?,?,?)",[m1,m2])

executes 3 times the INSERT command. For each execution, the following parameter assignments are performed:

'?1':=i, '?2':=m1(i), '?3':=m2(i)

The resulting table T is therefore:

c1 c2 c3
 1  1  4
 2  2  5
 3  3  6

ODBC and MS Excel

Microsoft Excel is a spreadsheet application. Since ODBC was primarily designed for databases special rules have to be followed to read and write Excel data using ODBC:

• A table of data is referred to as either a named range (e.g. MyRange), a worksheet name (e.g. [Sheet1$]) or an explicit range (e.g. [Sheet1$B2:C12]).
• By default, the first row of a range is used for naming the columns (to be used in SQL statements). The option FIRSTROWHASNAMES=0 disables this feature and columns are implicitly named F1, F2... However, even with this option, the first row is ignored and cannot contain data.
• The data type of columns is deduced by the Excel driver by scanning the first 8 rows. The number of rows analyzed can be changed using the option MAXSCANROWS=n (n between 1 and 8).

It is important to be aware that when writing to database tables specified by a named range in Excel, they will increase in size if new data is added using an INSERT statement. To overwrite existing data in the worksheet, the SQL statement UPDATE can be used in most cases (although this command is not fully supported). Now suppose that we wish to write further data over the top of data that has already been written to a range using an INSERT statement. Within Excel it is not sufficient to delete the previous data by selecting it and hitting the Delete key. If this is done, further data will be added after a blank rectangle where the deleted data used to reside. Instead, it is important to use Edit/Delete/Shift cells up within Excel, which will eliminate all traces of the previous data, and the enlarged range.

Microsoft Excel tables can be created and opened by only one user at a time. However, the "Read Only" option available in the Excel driver options allows multiple users to read from the same .xls files.

When first experimenting with acquiring or writing data via ODBC it is tempting to use short names for column headings. This can lead to horrible-to-diagnose errors if you inadvertently use an SQL keyword. We strongly recommend that you use names like ``myParameters'', or ``myParams'', or ``myTime'', which will not clash with SQL reserved keywords.

I/O drivers

In order to simplify access to ODBC enabled data sources, this module provides a driver designed to be used in initializations blocks for both reading and writing data.

Driver odbc

odbc:[debug;][noindex;][colsize=#;][bufsize=#;]DSN

The driver can only be used in `initializations' blocks. The Data Source Name to use has to be given in the opening part of the block. Before the DSN, the following options may be stated:

debug

to execute the block in debug mode (to display what SQL queries are produced). This option is ignored if the model is not compiled with debug information,

noindex

to indicate that only data (no indices) are transferred between the data source and Mosel. By default, the first columns of each table are interpreted as index values for the array to be transferred. This behaviour is changed by this option,

colsize=c

to set the size of a text column (default 64 characters),

bufsize=c

to set the size of the data buffer in kilobytes (default 4).

In the block, each label entry is understood as a table name optionally followed by a list of column names in brackets (e.g. "my_table(col1,col2)"). All columns are used if no list of names is specified. Note that, before the table name, one can add option noindex to indicate that for this particular entry indices are not used.

Example:

initializations from "mmodbc.odbc:auction.db3"
 NWeeks as "PARAMS(Weeks)"      ! Initialize `NWeeks' with column `Weeks'
                                ! of table `PARAMS'
 BPROF as "noindex;BPROFILE"    ! Initialize `BPROF' with table `BPROFILE'
                                ! all columns being data (no indices)
end-initializations