Some useful parameter settings

Topics covered in this section:

In this section we discuss some configuration options for the database and spreadsheet modules. For a complete list of the module parameters and I/O driver options the reader is refered to the module documentation in the corresponding chapters of the `Mosel Language Reference Manual'.

Parameter settings to aid debugging

While developing an application that involves access to external data sources (and in particular when using ODBC) it is advisable to enable the output of error messages from the driver and possibly other debug information. The module mmodbc defines the following parameters to enable debug output and to retrieve information about the SQL statements that have been executed. To obtain the corresponding mmoci parameter names, replace the prefix SQL by OCI.

SQLverbose

Enable/disable mesasge printing by the ODBC driver.

SQLdebug

Enable/disable debug mode.

SQLrowcnt

Number of lines affected by the last SQL command.

SQLrowxfr

Number of lines transfered by the last SQL command.

SQLsuccess

Indicates whether the last SQL command succeeded.

SQLconnection

Identification number of the active connection to a database.

Parameters are set and retrieved with Mosel statements similar to the following:

 setparam("SQLdebug", true)
 writeln("Number of lines transfered: ", getparam("SQLrowxfr"))

It is recommended to check the execution status of individual SQL commands, and in particular of the connection to the database, via the parameter SQLsuccess and handle error situations suitably in the Mosel program, for example by raising an I/O error if database connection has failed:

 SQLconnect("data.sqlite")
 if not getparam("SQLsuccess"): setioerr("Database connection failed")

With the odbc driver you may also use the `debug' option of the driver instead of the global setting:

 initializations to "mmodbc.odbc:debug;data.xls"
  A as "MyOutTable1"
 end-initializations 

or

 initializations to "mmodbc.odbc:data.xls"
  A as "debug;MyOutTable1"
 end-initializations 

In the first case, the setting applies to the whole initializations block, in the second case only to the specific statement (there may be any number of statements in a single block).

With the oci driver, a connection string including the `debug' option as global setting will look as follows:

 initializations to "mmoci.oci:debug;myusername/mypassword@dbname"
  A as "MyOutTable1"
 end-initializations 

Efficiency considerations

The mmodbc module fixes the maximum size of strings that are accepted to exchange data between Mosel and a database via the control SQLcolsize. This parameter is set by default to a relatively small value in order to avoid unnecessary overhead by reserving unused space—any portion of the strings exceeding the specified size is cut off. If a data set works with larger fields you need to increase the value of this setting, also aligning the setting of SQLbufsize that should always be larger then SQLcolsize (it is expected to be sufficiently large to store at least one entire row of data). To obtain the corresponding mmoci parameter names, replace the prefix SQL by OCI.

SQLcolsize

Maximum length of strings accepted to exchange data.

SQLbufsize

Size in kilobytes of the buffer used for exchanging data between Mosel and the ODBC driver.

The bufsize setting is also available as an option for the spreadsheet I/O drivers, increasing its default value might help to speed up the handling of very large data sets.

If a Mosel model employs a large number of write accesses to a database it is usually preferrably to enable transaction mode, provided that transactions are supported by the database (please refer to the documentation of your database; SQLite that comes with the mmodbc module supports this functionality, but it is not activated by default and needs to be explicitly enabled). This setting needs to be made before the database connection is opened. In transaction mode, database commands are collected up until a call to SQLcommit (commit changes) or SQLrollback (discard changes) is issued—with mmoci replace the prefix to subroutine and parameter names by OCI.

SQLautocommit

If set to false enable transactions, otherwise any changes are sent immeditately to the database.