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.
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).
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:
- A table of data is refered 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.
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).
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.
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:
- to execute the block in debug mode (to display what SQL queries are produced),
- 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,
- to set the size of a text column (default 64 characters),
- 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 email@example.com.
© Copyright 2001-2013 Fair Isaac Corporation. All rights reserved.