Software setup

Topics covered in this section:

Setting up ODBC

The ODBC technology is available for many database/platform combinations. It relies on a driver manager that is used as an interface between applications (like mmodbc) and a data source that is accessed through a dedicated driver. The Mosel module mmodbc provides an interface to ODBC, it does not contain any drivers or driver managers. These must therefore be installed and set up on the operating system before this module can be used.

Under Windows, usually the driver manager is part of the system and most data sources (e.g., MS Access, MS Excel, SQLserver) are provided with their ODBC driver. The ODBC drivers are set up as User Data Source in the ODBC Data Source Administrator. To check which drivers are set up under Windows (2000 or more recent) select Start » Settings » Control Panel » Administrative Tools » Data Sources (ODBC). For example, for MS Access there should be a data source named MS Access Database (this name is refered to in ODBC as DSN, the data source name). If the drivers are not set up, you may add the DSN for Access by clicking Add and selecting Microsoft Access Driver (*.mdb, *.accdb).

On the other supported operating systems it may be necessary to install a driver manager as well as the required driver(s). The module mmodbc supports two driver managers: iODBC (http://www.iodbc.org) and unixODBC (http://www.unixodbc.org). The module initialization succeeds only if one of these two driver managers is installed and can be accessed (in general this requires updating some environment variables). In addition, you must make sure that the ODBC driver for the data source you wish to use is installed. For the database MySQL, for instance, you can download the required ODBC driver, MyODBC, from http://dev.mysql.com/downloads/connector/odbc.

ODBC connection strings in Mosel

When connecting to a database from a Mosel model the filename is given in the form of a connection string that consists of the DSN and the name of the external data source (abbreviated as DBQ (Windows) or DB), such as 'DSN=mysql;DB=data' or 'DSN=MS Access Database;DBQ=C:/xpress/examples/data.mdb'. Please note that some databases do not accept blank spaces in the connection string.

Under Windows it is not necessary to state explicitely the DSN in the connection string since Mosel will automatically locate the appropriate driver (if it is installed). We may therefore work with connection strings shortened to the name of the external data source, such as 'data.mdb'.

For a more general introduction to the concept of database connection strings the reader is refered to http://www.connectionstrings.com.

The Excel interface

The spreadsheet interfaces defined by the module mmsheet do not require any extra setup or additional software as this is the case with ODBC. The dedicated Excel driver excel can only be used if MS Excel is installed and licensed. The drivers xls, xlsx, and csv are independent of Excel and can be used, including, on non-Windows platforms.

Excel spreadsheets are accessed by simply stating their file name, preceded by the driver prefix as shown below (see Section Spreadsheets: Excel and CSV).

Although certain spreadsheets support some ODBC functionality, we recommend to use one of the dedicated spreadsheet interfaces in the place of an ODBC connection. The access to the spreadsheet is more direct and hence more efficient and these interfaces remove some restrictions and possible sources of problems specific to the use of ODBC technology with Excel spreadsheets.

The Oracle interface

The Oracle interface defined by the module mmoci accesses Oracle databases via the Oracle Call Interface (OCI). Oracle's Instant Client package must be installed on the machine that runs the Mosel model. The Oracle Instant Client package is available for download from https://www.oracle.com/database/technologies/instant-client/downloads.html

The logon information for an Oracle database comprises the user name and password along with the database name, formated as a string such as 'myusername/mypassword@dbname'.

It is possible to access Oracle databases via an ODBC connection (that is, using module mmodbc instead of mmoci). In this case, an appropriate ODBC driver must be installed.

The SQLite interface

SQLite databases can be accessed via the ODBC interface. SQLite is included in the mmodbc module on all platforms that are supported by Xpress. The ODBC driver mmsqlite to access SQLite equally forms part of the distribution, no ODBC setup and no additional installations are required when using this driver. The full connection string to employ in this case has the form 'DRIVER=mmsqlite;READONLY=false;DB=mydatabase.sqlite' (notice that instead of referring to a DSN definition we directly use the built-in ODBC driver). However, it is sufficient to simply state the database filename (and path) if it has one of the extensions sqlite, sqlite3, db, or db3 — Mosel will automatically generate the complete connection string.

Alternatively, if you wish to use a different version of SQLite than the one provided with Mosel, you need to follow the ODBC installation procedure: after downloading an external ODBC driver for SQLite, a DSN must be setup as explained above in Section Setting up ODBC. Assuming that we have called the DSN 'sqlite', this results in a connection string of the form 'DSN=sqlite;DATABASE=mydatabase.sqlite'.

For visualizing and editing SQLite databases, you may choose to install additional software, such as the SQLite Manager plugin for Firefox browsers that can be downloaded from https://addons.mozilla.org or the DB Browser for SQLite at http://sqlitebrowser.org