[Previous chapter] [Next chapter]

mmodbc


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. 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" ("libiodbc.sl" under HP-UX) then, if this fails, tries "libodbc.so" ("libodbc.sl" under HP-UX). The initialization succeeds only if one of these libraries can be found and publishes the required symbols. In case of initialization failure, 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.

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 transfered 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
 1  2  5
 4  3  6

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

The second argument of SQLexecute may also be an array 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 transfered 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

When SQLexecute is used with an array 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:

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.

Control parameters

The following parameters are defined by mmodbc:

SQLbufsize
Data buffer size.
SQLcolsize
Maximum string length.
SQLconnection
Identification number of the active ODBC connection.
SQLdebug
Enable/disable debug mode.
SQLdm
Driver manager currently used.
SQLextn
Enable/Disable extended syntax.
SQLndxcol
Indicate whether to use first columns as indices.
SQLrowcnt
Number of lines affected by the last SQL command.
SQLrowxfr
Number of lines transfered during the last SQL command.
SQLsuccess
Indicate whether the last SQL command succeeded.
SQLverbose
Enable/disable message printing by the ODBC driver.

All parameters can be accessed with the Mosel function getparam, and those that are not marked read-only in the list below may be set using the procedure setparam.

Example:

setparam("SQLverbose", true)    ! Enable message printing by the ODBC driver
csize:=getparam("SQLcolsize")   ! Get the maximum string length
setparam("SQLconnection", 3)    ! Select the connection number 3

Procedures and functions

This section lists in alphabetical order the functions and procedures that are provided by the mmodbc module.

SQLconnect
Connect to a database.
SQLdisconnect
Terminate the active database connection.
SQLexecute
Execute an SQL command.
SQLreadinteger
Read an integer value from a database.
SQLreadreal
Read a real value from a database.
SQLreadstring
Read a string from a database.
SQLupdate
Update the selected data with the provided array(s).

I/O drivers

This module provides two drivers designed to be used in initializations blocks for both reading and writing data. The odbc IO driver simplifies access to ODBC enabled data sources and the excel IO driver is a direct interface to the MS Excel spreadsheet. The excel driver is available on the Windows platform only.

Driver odbc

odbc:[debug;][noindex;]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, two options may be stated:

debug
to execute the block in debug mode (to display what SQL queries are produced), and
noindex
to indicate that only data (no indices) are transfered between the data source and Mosel. By default, the first columns of each table is interpreted as index values for the array to be transfered. This behaviour is changed by this option.

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.xls"
 NWeeks as "PARAMS(Weeks)"      ! Initialize `NWeeks' with colmun `Weeks' 
                                ! of table `PARAMS'
 BPROF as "noindex;BPROFILE"    ! Initialize `BPROF' with table `BPROFILE' 
                                ! all columns being data (no indices)
end-initializations

Driver excel

excel:[noindex;][grow;][skiph;][newxl;]filename

The driver can only be used in `initializations' blocks. The file name to use (an Excel spreadsheet with the .xls extension) has to be given in the opening part of the block. Before this name, four options may be stated:

noindex
indicates that only data (no indices) are transfered between the spreadsheet and Mosel. By default, the first columns of each table is interpreted as index values for the array to be transfered. This behaviour is changed by this option
grow
when writing data, the driver uses the provided range ignoring the end of the data if there is not enough space. When this option is specified, the driver extends the range by adding lines if necessary
skiph
with this option, the driver skips the first line (or header) of the provided range. If the range contains only one line, the following line is selected
newx
the driver does not open the file if it can find a running instance of Excel having the required file open: it works directly with the application and modifications made to the workbook are not saved when the file is closed in Mosel. If this option is specified a new instance of Excel is started in all cases and the workbook is saved before quitting the application when the file is closed in Mosel.

In the block, each label entry is understood as a range in the workbook: named ranges are represented by their name (e.g. "MyRange") and explicit ranges are noted using square brackets (e.g. "[sheet1$a1:c2]"). For explicit ranges, the sheet is identified by its name or number and separated from the cell selection with the $ sign. The first sheet of the workbook is selected if no indication is given. Similarly, the first cell of the selected sheet is assumed if no selection is provided ("[]" is the same as "[1$a1]"). Note that, before the range selection, one can add options as for the file opening. For instance, "skiph;grow;" is suitable to use a named range formatted for an ODBC connection.

Example:

initializations from "mmodbc.excel:skiph;auction.xls"
 NWeeks as "[b1:d12]"           ! Initialize `NWeeks' with data in b2:d12
 BPROF as "noindex;BPROFILE"    ! Initialize `BPROF' with named range `BPROFILE' 
                                ! all columns being data (no indices)
end-initializations

[Next] [Index]
If you have any comments or suggestions about these pages, please send mail to docs@dashoptimization.com.