Introduction

The Mosel distribution contains several different interfaces for exchanging data between a model and a database or spreadsheet. As well as an ODBC interface there are software-specific implementations for Oracle and MS Excel, all of which are presented in this document.

ODBC is a protocol for working with databases as external data sources. It can also be used to access data in spreadsheets such as certain versions of MS Excel. However, with spreadsheets some restrictions apply since spreadsheets do not implement the full functionality of databases, especially for writing data into them. As an alternative to ODBC therefore different spreadsheet interfaces (supporting MS Excel formats) are available that remedy some of these drawbacks, they should be used for accessing data in spreadsheets in place of the ODBC interface.

The Mosel module mmodbc provides access to ODBC functionality from within Mosel models. This module requires a specific licence. As always with Mosel modules, when we wish to use this module in a model, we need to indicate its name in a uses statement at the beginning of our model, immediately after the model name:

 uses "mmodbc"

The reader will find the complete documentation of the Mosel module mmodbc in the `Mosel Language Reference Manual', Chapter `mmodbc'.

A separate module, mmoci, defines a software-specific interface to Oracle databases (OCI). This module requires an extra licence. The functionality and manner of use of this module closely resembles that of the ODBC module. The uses statement for working with OCI is:

 uses "mmoci"

The OCI module is documented in the `Mosel Language Reference Manual', Chapter `mmoci'.

The spreadsheet and CSV interfaces are provided by the Mosel module mmsheet that is equally documented in the `Mosel Language Reference Manual', Chapter `mmsheet'. The corresponding uses statement for this module is:

 uses "mmsheet"

The aim of the present document is to explain the different features of the ODBC module (and by analogy, the OCI module) by means of a collection of examples. In the beginning we show how to

• set up ODBC,
• work with the odbc I/O driver in initializations blocks,
• use the full functionality of the module mmodbc in the formulation of SQL queries,
• access MS Excel spreadsheets via the dedicated spreadsheet I/O drivers, and
• set module parameters that may be helpful for debugging.

The main part of this document describes a number of examples that illustrate topics such as

• sparse vs. dense data format,
• reading from / writing to tables holding entries of several data arrays,
• data arrays read from several tables,
• defining SQL queries,
• using the data structures 'list' and 'record',
• working with date and time data types.

All examples described in this document are provided as part of the Xpress distribution (subdirectory examples/mosel/Whitepapers/MoselODBC). Most examples have six versions, namely (1) using an ODBC connection with standard Mosel data initializations to access databases, (2) using SQL statements with the same data sources, (3) using the dedicated Excel interface (driver excel), (4) using the Oracle interface, (5) using the generic Excel interface (drivers xls/xlsx), and (6) using the CSV interface.

The last section contains some hints on how to detect and solve typical problems that may occur when working with ODBC.