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. 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). 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/MoselData). 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.
© 2001-2024 Fair Isaac Corporation. All rights reserved. This documentation is the property of Fair Isaac Corporation (“FICO”). Receipt or possession of this documentation does not convey rights to disclose, reproduce, make derivative works, use, or allow others to use it except solely for internal evaluation purposes to determine whether to purchase a license to the software described in this documentation, or as otherwise set forth in a written software license agreement between you and FICO (or a FICO affiliate). Use of this documentation and the software described in it must conform strictly to the foregoing permitted uses, and no other use is permitted.