If you create a link to an external file then, by default, STEM will decide how to read data from this file on the basis of its file extension. Both
.DTL
and .DTM extensions identify STEM models, whilst the
.XLSX
extension identifies an Excel workbook. STEM reads both types of file directly without using ODBC.
Automatic inference of ODBC driver from file extension
A file with any other extension will be treated as a database if an ODBC driver for that file type is installed on the machine. (You can see which, if any, ODBC drivers are installed on your computer by selecting the ODBC manager in Windows Control Panel.) STEM will automatically construct the appropriate connection string.
For example, if you have installed Microsoft Access, you should find a driver called ‘Microsoft Access Driver (*.mdb)’ in the list of installed drivers, and this will allow you to import data from an Access database, simply by selecting or entering the corresponding filename. So a link defined in STEM as
C:\DATA\COSTS.MDB
will automatically generate the following connection string:
DRIVER={Microsoft Access Driver (*.mdb)};DBQ=
C:\DATA\COSTS.MDB;
which STEM uses to connect to the Access database.
Explicit ODBC links
However, it is not always possible to infer an appropriate ODBC driver from a file extension, because:
- client-server databases, such as Microsoft SQL Server, typically hide the names of the actual database files and communicate via machine names and internal identifiers
- you may wish to import data from an ODBC data source, which is just identified by a name defined in the ODBC manager in Windows Control Panel.
In such cases, you have to select an explicit driver from the list of ODBC Drivers within the Define External Link dialog in the STEM Editor – see 4.13.5 Creating an explicit ODBC link.
This driver identifier mechanism within STEM associates a short identifier with a template for a connection string, with placeholders for parameters supplied for the link, which STEM then uses to construct the full connection string in order to connect to the ODBC source. This means you do not have to enter the whole connection string in the formula bar, and hopefully defers indefinitely the need for you to enter an ODBC connection string yourself.
STEM is supplied with a number of built-in ODBC driver identifiers. These are shown in the ODBC Driver list in the Define External Link dialog, and also in the ODBC Drivers dialog, which you can access directly from the Options menu. These identifiers are described in detail in the following sections.
Client-server databases
Microsoft SQL Server and Oracle are two client-server databases for which we have supplied short driver identifiers,
SQL
and Oracle
respectively. These are fairly similar in structure, and we will describe the
SQL
case in detail.
The SQL identifier is associated with the formatted connection string:
DRIVER={SQL Server};SERVER=%p;DATABASE=%p;UID=sa;PWD=;
which STEM uses to connect to the database server.
However, the %p
elements are first replaced with the parameters supplied for a link. So, for example, a link defined in STEM as
SQL:MyServer:MyDatabase
will generate the following connection string:
DRIVER={SQL Server};SERVER=MyServer;DATABASE=MyDatabase;
UID=sa;PWD=;
Note: The user-id sa
is the default system-administrator id for many databases.
Data Source Names (DSN)
If you have defined any DSNs on your machine, you can connect to them using the short identifier
DSN. This is associated with the formatted connection string:
DSN=%p;
So, for example, a link defined in STEM as DSN:MyDataSource
will generate the following connection string:
DSN=MyDataSource;
which STEM uses to connect to the database identified by the DSN, as defined within the ODBC manager.
The ODBC driver for Microsoft Excel
If you create a link to an Excel workbook (i.e., a file with the extension
.XLSX), by default STEM will read this directly and expect data to be laid out as described in 6.3 Linking input data from Microsoft Excel spreadsheets. However, there is an ODBC driver for Excel, which treats named ranges as database tables. If you need to read data from such an Excel database, then you will need to use the
XLSX
driver identifier in order to stop STEM treating it as a spreadsheet.
The XLSX identifier is associated with the formatted connection string:
DRIVER={Microsoft Excel Driver (*.xlsx)};DBQ=%f;
Here, the %f
element identifies a filename parameter supplied with a link. So, for example, a link defined in STEM as
XLSX:"C:\DATA\COSTS.XLSX"
will generate the following connection string:
DRIVER={Microsoft Excel Driver (*.xlsx)};DBQ=C:\DATA\COSTS.XLSX;
which STEM uses to connect to the Excel file.
Creating additional driver identifiers
Although we have provided a number of built-in ODBC driver identifiers, as well as the automatic inference of driver from file extension, it is possible that you may need to create additional driver identifiers of your own. This might be because:
- you have a client-server database other than Microsoft SQL Server or Oracle
- you want to hard-code a user-id and/or password
- you want to avoid inference by file extension, perhaps because two ODBC drivers handle the same file extension (e.g., the Microsoft drivers for dBase and FoxPro both handle files with extension
.DBF)
- you want to streamline references to a particular database in many models.
This process is described in 4.13.7 Creating a new ODBC driver identifier.
The connection string for other client-server databases should be comparable to that associated with the built-in
SQL
identifier, which is also a good reference for hard-coding a user-id and/or password (if this is desirable – you may prefer to enter these when prompted by the ODBC manager/driver). Similarly, the
XLSX
identifier is a good example of how to bypass inference by file extension.
If you want to reference the same database in many models, you could create an identifier with a connection string which contains no parameter place-holders.
For example, an identifier, DATA, associated with the formatted connection string:
DRIVER={Microsoft Access Driver (*.mdb)};DBQ=
C:\DATA\COSTS.MDB;
would allow you to use a link defined in STEM simply as DATA:
to connect to the Access database. However, it is questionable whether it would really be any easier to work with this than with a link defined as
C:\DATA\COSTS.MDB, and it would also prevent you browsing for the filename or using a relative path.
This last example is somewhat akin to using DSNs, and, in general, you will find that using inference by file extension and the built-in identifiers wherever possible will give you the most portable link structure.