mxODBC Configuration Guide

Home	People	mxODBC	Emulation
		Python

Note

This document will shortly be revised to exclude details of the mxODBC 1.x series.

Abstract

mxODBC is a package for the Python programming language, providing connectivity to relational database management systems (RDBMSs) using the ODBC standard. This document aims to explain how to configure mxODBC on UNIX systems.

Note that in this document I will use the term "database system" to mean RDBMS unless otherwise stated. This seems to be a common application of the term.

Author: Paul Boddie (paul@boddie.org.uk)

Starting

With mxODBC version 2.0.0, the method of installation has changed from version 1.1.1. Here, instructions are provided for both releases.

mxODBC Version 2.0.0

First, read the instructions for the database systems you intend to use mxODBC with. Only after making the appropriate adjustments to the configuration files should you attempt to install mxODBC as this is a genuine installation process which will typically put files in special places within your Python installation.

Once you have made the appropriate adjustments, follow the instructions in the mxODBC documentation. In brief, you will need to install the egenix-mx-base-2.0.0 package in order to make the mxDateTime package available to mxODBC. Then, you will need to install the egenix-mx-commercial-2.0.0 package in order to make the mxODBC package available in your Python installation. For both of these packages, installation is done by entering the root directory of each package and issuing the following command:

python setup.py install

You may need to be the root user for this command to work successfully.

mxODBC Version 1.1.1

Follow the instructions in the mxODBC documentation concerning the installation of the software (along with the mxDateTime package). In this document, when referring to this version of mxODBC, we shall refer to the location of these packages as /home/mx, so that /home/mx/ODBC and /home/mx/DateTime are the locations of the installed mxODBC and mxDateTime packages respectively.

Suitability

First, it is important to be aware of the range of database modules available for Python. ODBC configuration can be very time-consuming, and if you can get a "native" module (meaning a module which uses the database system's own API) working using a reasonably convenient compilation, linking and installation procedure then you will probably have saved a considerable amount of time; this is likely to be the case if you have some Python experience but little ODBC experience.

Database Suitability

There are, in my experience, two types of database system concerning connectivity issues:

Those supplied with ODBC drivers as standard.
Those supplied with a number of libraries for the purpose of connectivity using the database system's own API.

If a database system is supplied with ODBC drivers then it may be worth trying mxODBC even if a "native" module is available. This is because mxODBC provides one of the nicest/closest implementations of the DB-API specification (version 2) that I have seen.

If a database system only provides its own libraries then be prepared to spend a lot of time finding the right drivers, configuring them, and learning how they operate, should you decide to choose ODBC as the connection mechanism. In comparision, the "native" modules are usually straightforward enough to install, although they may require header files not supplied with the database system and this could prevent you from building those modules unless you find such header files in other locations. In such situations you may end up being forced to choose ODBC. However, even if all the necessary resources are available, mxODBC may implement the DB-API specification better than the available "native" module and, in such cases, ODBC is going to be practically unavoidable - not all of the modules for Sybase have, in the past, tended to support parameters in queries/actions, but this feature is very useful for serious work.

An example of missing header files involves Sybase Adaptive Server Enterprise (ASE) version 11.5 for Solaris 2.6. This product lacks the sqlda.h file which is needed by ctsybasemodule. However, it can be found in certain downloadable packages on Sybase's Web site.

Database Systems with ODBC Drivers

The following database systems are supplied with ODBC drivers:

Sybase Adaptive Server Anywhere 6.0.3

Sybase Adaptive Server Anywhere (ASA) version 6.0.3 for Linux can be used with mxODBC either with or without the iODBC driver manager. The following instructions describe the process of using the supplied drivers directly.

mxODBC Version 2.0.0

Note the location of the ASA installation. In these instructions we shall refer to this location as /home/sybase.
Ensure that the library /home/sybase/lib/dbodbc6.so is present. If not, you may need to reinstall ASA.
Make a symbolic link to the dbodbc6.so library within the /home/sybase/lib directory, calling it libodbc.so or any appropriate library name which will be recognised by your compiler/linker when the flag -lodbc is used. (This is recommended in the ASA installation instructions.)

Enter the root directory of the egenix-mx-commercial-2.0.0 package and edit the mxCOMMERCIAL.py file, making sure that an extension definition is set up. The following patch replaces the default extension definitions with a suitable definition for ASA:

76,77c76,77
<         'mx.ODBC.iODBC',
<         'mx.ODBC.unixODBC',
---
>         #'mx.ODBC.iODBC',
>         #'mx.ODBC.unixODBC',
83a84
>         'mx.ODBC.SybaseASA',
113,122c114,123
<         Extension('mx.ODBC.iODBC.mxODBC',
<                   ['mx/ODBC/iODBC/mxODBC.c',
<                    'mx/ODBC/iODBC/mxSQLCodes.c'
<                    ],
<                   include_dirs=['mx/ODBC/iODBC',
<                                 '/usr/local/include'],
<                   define_macros=[('iODBC', None)],
<                   library_dirs=['/usr/local/lib'],
<                   libraries=['iodbc']
<                   ),
---
>         #Extension('mx.ODBC.iODBC.mxODBC',
>         #          ['mx/ODBC/iODBC/mxODBC.c',
>         #           'mx/ODBC/iODBC/mxSQLCodes.c'
>         #           ],
>         #          include_dirs=['mx/ODBC/iODBC',
>         #                        '/usr/local/include'],
>         #          define_macros=[('iODBC', None)],
>         #          library_dirs=['/usr/local/lib'],
>         #          libraries=['iodbc']
>         #          ),
124,133c125,134
<         Extension('mx.ODBC.unixODBC.mxODBC',
<                   ['mx/ODBC/unixODBC/mxODBC.c',
<                    'mx/ODBC/unixODBC/mxSQLCodes.c'
<                    ],
<                   include_dirs=['mx/ODBC/unixODBC',
<                                 '/usr/include'],
<                   define_macros=[('unixODBC', None)],
<                   library_dirs=['/usr/lib'],
<                   libraries=['odbc']
<                   ),
---
>         #Extension('mx.ODBC.unixODBC.mxODBC',
>         #          ['mx/ODBC/unixODBC/mxODBC.c',
>         #           'mx/ODBC/unixODBC/mxSQLCodes.c'
>         #           ],
>         #          include_dirs=['mx/ODBC/unixODBC',
>         #                        '/usr/include'],
>         #          define_macros=[('unixODBC', None)],
>         #          library_dirs=['/usr/lib'],
>         #          libraries=['odbc']
>         #          ),
151a153,163
>         Extension('mx.ODBC.SybaseASA.mxODBC',
>                   ['mx/ODBC/SybaseASA/mxODBC.c',
>                    'mx/ODBC/SybaseASA/mxSQLCodes.c'
>                    ],
>                   include_dirs=['mx/ODBC/SybaseASA',
>                                 '/home/sybase/include'],
>                   define_macros=[('SybaseAnywhere', None)],
>                   library_dirs=['/home/sybase/lib'],
>                   libraries=['odbc']
>                   ),
>         
179,180c191,195
<         'mx/ODBC/iODBC/COPYRIGHT',
<         'mx/ODBC/iODBC/LICENSE',
---
>         #'mx/ODBC/iODBC/COPYRIGHT',
>         #'mx/ODBC/iODBC/LICENSE',
> 
>         #'mx/ODBC/unixODBC/COPYRIGHT',
>         #'mx/ODBC/unixODBC/LICENSE',
182,183c197,198
<         'mx/ODBC/unixODBC/COPYRIGHT',
<         'mx/ODBC/unixODBC/LICENSE',
---
>         'mx/ODBC/SybaseASA/COPYRIGHT',
>         'mx/ODBC/SybaseASA/LICENSE',

Note that the package is called SybaseASA, but the definition required in the compilation process is called SybaseAnywhere. To apply this patch, save it as mxCOMMERICAL.py.diff and issue the following command:

patch < mxCOMMERCIAL.py.diff

Answer the question of the file to patch with the filename mxCOMMERCIAL.py.

Install mxODBC as instructed above in the Starting section.
Start the ASA server process using the dbsrv6 or dbeng6 program as you usually would.
Make sure that the user who will be running Python and mxODBC has the ODBC driver directory set in their environment. For example, on Linux, the LD_LIBRARY_PATH variable must include the directory /home/sybase/lib.
Start Python in interactive mode and try and import the ODBC.SybaseASA module:
```
import mx.ODBC.SybaseASA
```
Try and connect to the asademo database using the default user details:
```
c = mx.ODBC.SybaseASA.Connect("asademo", "dba", "sql")
```

mxODBC Version 1.1.1

Note the location of the ASA installation. In these instructions we shall refer to this location as /home/sybase.
Ensure that the library /home/sybase/lib/dbodbc6.so is present. If not, you may need to reinstall ASA.
Move into the Sybase subdirectory of the installed mxODBC package: /home/mx/ODBC/Sybase

If you have version 1.1.1 of mxODBC, you will need to edit the Setup file, defining the following things:

        -DHAVE_SQLDriverConnect \
        -DASA \
        -DODBC_UNIX \
        -I/home/sybase/include \
        /home/sybase/lib/dbodbc6.so

If you have version 1.1.1 of mxODBC, you will need to edit the mxODBC.h file, adding a special ASA section, as the following diff output shows:

206a207,215
> #ifdef ASA
> /* Adaptive Server Anywhere driver */
> # include "odbc.h"
> # define MXODBC_INTERFACENAME "Adaptive Server Anywhere ODBC"
> # ifndef HAVE_SQLDriverConnect
> #  define HAVE_SQLDriverConnect
> # endif
> #else
> 
291a301
> #endif /* ASA */

Build the Sybase subpackage as instructed on the mxODBC page.
Start the ASA server process using the dbsrv6 or dbeng6 program as you usually would.
Make sure that the user who will be running Python and mxODBC has the ODBC driver directory set in their environment. For example, on Linux, the LD_LIBRARY_PATH variable must include the directory /home/sybase/lib.
Start Python in interactive mode and try and import the ODBC.Sybase module:
```
import ODBC.Sybase
```
Try and connect to the asademo database using the default user details:
```
c = ODBC.Sybase.Connect("asademo", "dba", "sql")
```

Troubleshooting

If the following error is given, you may need to run Python and mxODBC as the same user who runs the ASA server process dbsrv6 or dbeng6.

Traceback (innermost last):
  File "", line 1, in ?
mxODBC.OperationalError: ('IM003', 0, '[iODBC][Driver Manager]Specified driver
could not be loaded', 4265)

Database Systems without ODBC

The following database systems are provided without ODBC drivers:

Sybase Adaptive Server Enterprise 11.5

Sybase Adaptive Server Enterprise (ASE) version 11.5 for Solaris 2.6 is provided with some libraries which enable client applications to connect to and use the database system. However, these libraries do not directly support ODBC connectivity.

One source of ODBC drivers for ASE is OpenLink Software. They have many products, but the "Data Access Driver Suite (Multi Tier Edition) Version 3.2" product can be persuaded to work. The following instructions describe the process.

mxODBC Version 1.1.1

Note the location of the ASE installation. In these instructions we shall refer to this location as /home/sybase.
Visit the OpenLink Software site and search for "Data Access Driver Suite (Multi Tier Edition) Version 3.2" with the following details:
- Client Operating System: Solaris 2.5 (Sparc)
- Data Access Mechanism: ODBC
- Database: Sybase
Choose an appropriate option, such as:
- Solaris v2.5 (Sparc)
- Sybase 11.x; compatible with 10.x
- smads11c.taz
Download the components:
- Server Component(s) - Request Broker:
  - Request Broker (taz): ftp://www.openlinksw.co.uk/open32/smbrzzzz.taz
  - Unix Installation Shell Script: ftp://www.openlinksw.co.uk/open32/install.sh
- Server Component(s) - Database Agent(s):
  - Sybase 11.x; compatible with 10.x (taz): ftp://www.openlinksw.co.uk/open32/smads11c.taz
- Server Component(s) - JDBC, ODBC, Proxy Agents:
  - ODBC Agent (taz): ftp://www.openlinksw.co.uk/open32/smaozzzz.taz
Follow the instructions for installation that OpenLink provide. To summarise, you can place these component packages in a common directory, along with the install.sh script, and then execute that script specifying a suitable location for the installed components. In these instructions we shall refer to this location as /home/openlink.
In /home/openlink there will be two files: openlink.csh and openlink.sh. These define environment variables which make the usage of the software more convenient. Add the appropriate definitions to your shell's startup file.

Make sure that the /home/openlink/bin/odbc.ini is set up correctly for the database system that you will be using. For example:

[ODBC Data Sources]
Badger = Test of the OpenLink Generic ODBC Driver

[Badger]
Driver          = /home/openlink/lib/oplodbc.so.1
Description     = Sample OpenLink DSN
Host            = localhost
ServerType      = Sybase 11
FetchBufferSize = 99
UserName        = 
Password        = 
Database        = 
ServerOptions   = 
ConnectOptions  = 
Options         = 
ReadOnly        = no
Trace           = 0
TraceFile       = /tmp/iodbc.trace

[Default]
Driver = /home/openlink/lib/oplodbc.so.1

Make sure that the /home/openlink/bin/oplrqb.ini file contains the correct location of the ASE installation, as follows:
```
[Environment SYBASE11]
SYBASE  = /home/sybase
DSQUERY = Vole
```
Start the iODBC request broker process, possibly using the /home/openlink/bin/oplcfg program.
Start the ASE server process as you usually would.
Now use the /home/openlink/samples/ODBC/odbctest program, using the following connection string (which uses the data source Badger as defined in the /home/openlink/bin/odbc.ini file):
```
DSN=Badger;UID=username;PWD=password
```
Now follow the instructions in the iODBC section to begin the configuration of mxODBC. Return here once that has been done.
Unfortunately, mxODBC version 1.1.1 will not work correctly with the OpenLink components due to some inconsistencies with the ODBC specification in one or the other. This means that some definitions should be added to the Setup file in the iODBC subdirectory of the mxODBC package: /home/mx/ODBC/iODBC

Edit the Setup file, exposing the following definitions:

        -DiODBC \
        -DUSE_PYTHONTYPE_BINDING \
        -DPB \
        -I/home/iODBC/include/ \
        /home/iODBC/lib/libiodbc.so

In mxODBC version 1.1.1, the PB definition must be used in the mxODBC.c file to prevent some code being executed when the execute method of a cursor object is invoked. The following diff output summarises the change:
```
3053a3054
> #ifndef PB
3055a3057
> #endif
```
Build the iODBC subpackage as instructed below.
Make sure that the user who will be running Python and mxODBC has the iODBC library directory set in their environment. For example, on Solaris and Linux, the LD_LIBRARY_PATH variable must include the directory /home/iODBC/lib. In addition, the driver directory must also be stated in the LD_LIBRARY_PATH variable, so in the above example, this would be /home/openlink/lib.
Start Python in interactive mode and try and import the ODBC.iODBC module:
```
import ODBC.iODBC
```
Try and connect to the Badger database using the appropriate user details:
```
c = ODBC.iODBC.Connect("Badger", "username", "password")
```

Solid Embedded Engine 3.5

Solid Embedded Engine version 3.5 for Solaris 2.6, when downloaded for evaluation, is provided with some ODBC libraries and some demonstration programs which connect to a database using the ODBC API. However, I could not get mxODBC to work with these libraries, receiving errors when the SQLNumParams function was invoked.

However, OpenLink Software is, as with Sybase ASE, to the rescue with their "Data Access Driver Suite (Multi Tier Edition) Version 3.2" product. Follow the instructions as you would for Sybase ASE, substituting "Solid" for any "Sybase" references, and Sybase-related filenames with the equivalent Solid-related filenames. It does not seem to be necessary to tell the OpenLink request broker to use any particular port in order to access a database, at least if that database is being "exported" on the default TCP/IP port 1313. Presumably, the /home/openlink/bin/oplrqb.ini file would need to be modified and the SOLID environment changed to recognise different addresses and ports.

iODBC

Unless you can link mxODBC directly with an ODBC driver, which is the case for some database systems you will need to install the iODBC driver manager. I found that the iODBC Developers Open Source Release V2.50.3 was suitable for Solaris 2.6, but for Linux the "iODBC Driver Manager Runtime Package" and "iODBC Developers Kit" seem to work as well.

Even if you installed the OpenLink components, to build the ODBC.iODBC module you may still need to find the header files for iODBC, since they may not be provided with those components. Therefore, download the appropriate packages noted above and follow these instructions:

mxODBC Version 1.1.1

Decide on a location for the installed iODBC driver manager library package. If you download and install the Linux RPMs, you will probably find that by default they are placed in /usr. In these instructions, however, we shall refer to this location as /home/iODBC.
Move into the iODBC subdirectory of the installed mxODBC package: /home/mx/ODBC/iODBC
If you have version 1.1.1 of mxODBC, you will need to edit the Setup file, defining the following things:
```
        -DiODBC \
        -I/home/iODBC/include/ \
        /home/iODBC/lib/libiodbc.so
```
There is a danger that some kind of version mismatch between the libiodbc.so library referenced at build time and that referenced at run time might affect the operation of mxODBC, if you installed the OpenLink "Data Access Driver Suite (Multi Tier Edition) Version 3.2". You could copy the libraries found in /home/iODBC/lib into /home/openlink/lib, making sure that the symbolic links in that directory are adjusted accordingly.
Build the iODBC subpackage as instructed on the mxODBC page.
If you do not already have a description file for the available data sources, such as /home/openlink/bin/odbc.ini, then set up such a file in the home directory of the user who will be running Python and mxODBC, calling it .odbc.ini. The following contents are suitable for using Sybase ASA 6.0.3 on Linux with iODBC (rather than by directly linking to the ODBC driver provided):
```
[asademo]
Server        = asademo
Driver        = dbodbc6.so
```
Make sure that the user who will be running Python and mxODBC has the iODBC library directory set in their environment. For example, on Solaris and Linux, the LD_LIBRARY_PATH variable must include the directory /home/iODBC/lib. In addition, the driver directory must also be stated in the LD_LIBRARY_PATH variable, so in the above example, this would be /home/sybase/lib.