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 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
 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 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

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:

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 transferred 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.
SQLgetparam
Get the value of an SQL parameter.
SQLparam
Generate an SQL parameter.
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

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),
noindex
to indicate that only data (no indices) are transferred between the data source and Mosel. By default, the first columns of each table is 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.xls"
 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 


If you have any comments or suggestions about these pages, please send mail to support@fico.com.

© Copyright 2001-2013 Fair Isaac Corporation. All rights reserved.