Product name eGenix mxODBC Zope Database Adapter Description A Database Adapter that allows the creation of a connection pool of ODBC database connections for Zope applications.. Release Date 2004-01-23 Version 1.0.8 Rating (all ratings are on scale of 1-5 with 5 sushi being the best) Usability Software Design Documentation Usefulness Categories Database Adaptor Size 403KBytes Requirements Zope 2.3 and up. Requires a pre-installed ODBC manager. Creator eGenix License Commercial (Click here to read license) URL Click here Packaged as Zip file

Multi threaded and available on demand.

Not all drivers support all its functionality.

eGenix's mxODBC Zope Database Adapter (DA) is a commercial ODBC interface for Zope. It is a Zope Level 3 database adapter that offers multi-threading and works well in an ODBC-enabled platform. MxODBC Zope DA is based on eGenix's mxODBC database driver for Python.

mxODBC Zope DA is the first Zope database adapter released under a proprietary commercial license. The adapter is also available as a 30-day trial download for evaluation purposes. In this article we will review some of mxODBC Zope DA's best-known features.

One of the greatest strengths of this product is its well-written documentation. The DA's distribution includes a detailed 66-page manual. I started by reading this manual. To test the adapter, I used both MS Windows and Linux. My MS Windows box included MySQL 3.23 for Windows, Zope 2.6.1, and the standard MS ODBC Manager. I also used a Red Hat 9 system with MySQL 4.0.3-beta-Max and PostgreSQL 7.4.2 as remote data back-ends. My Linux test bench used iODBC 3.51.2 as the ODBC manager — details about how to install and set up iODBC can be found at the iODBC.org Web site.

To use the MS ODBC Manager you must go to Start->Control Panel->ODBC Data Sources. Figure 1 shows the MS ODBC Manager application. The ODBC Manager has several tabs with information about data sources, drivers, and other options. To set up a new data source you must first install the appropriate driver and then enter some required information.

Figure 1

Figure 2 shows the form used to create a new remote PostgreSQL-based data source. Figure 3 shows a similar window used to create a new MySQL-based data source. As you can see, the configuration window may be different from one driver to another but the information required is almost the same.

Figure 2

Figure 3

Unix-based environments require a more complex setup. For instance, the iODBC Linux manager uses a configuration file which is usually located in the /etc folder for convenience. Listing 1 shows such a file, /etc/odbc.ini, as used by our Linux test bench.

; begin odbc.ini file [ODBC Data Sources] test = MySQL ODBC 3.51 Driver DSN [test] Driver = /usr/lib/libmyodbc3.so Description = MySQL ODBC Driver DSN DSN = test Server = 10.0.32.2 PORT = 3306 SOCKET = /var/lib/mysql/mysql.sock User = test Password = test Database = test ReadOnly = no ServerType = MySQL FetchBufferSize = 99 ServerOptions = ConnectOptions = OPTION = 3 TraceFile = /var/log/mysql_test_trace.log Trace = 0 [test2] Driver = /usr/lib/libodbcpsql.so Description = PostgreSQL ODBC Driver DSN DSN = test2 Server = 10.0.32.2 PORT = 5432 ;SOCKET = User = postgres ;Password = Database = test ReadOnly = no ServerType = postgres FetchBufferSize = 99 ServerOptions = ConnectOptions = ;OPTION = 3 TraceFile = /var/log/postgresql_test_trace.log Trace = 1 [Default] Driver = /usr/lib/libmyodbc3.so Description = MySQL ODBC Driver DSN Server = 10.0.32.2 PORT = 3306 USER = test Password = test SOCKET = /var/lib/mysql/mysql.sock ;end odbc.ini file

Listing 1: /etc/odbc.ini file

(In our review, we will use both MySQL and PostgreSQL data sources — mostly because they are two of the best-known Open Source DBMSs. However, mxODBC Zope DA can be used with any ODBC-enabled DBMS such as Oracle, MS SQL Server, Sybase, and many others — see documentation for details.)

The DA's binaries are available for download at http://www.egenix.com. For MS Windows, I used the zipped binary package. Before installing the binaries it is important to mention that mxODBC Zope DA requires a previously installed Zope instance. I used my old Zope 2.6.1 instance. It is for this reason that I downloaded the corresponding zipped package.

Installing the DA was a simple task. Using an archiving utility (Winzip), I unzipped the compressed package into my Zope home directory. It created two new folders: /lib/python/mx and /lib/python/Products/mxODBCZopeDA.

To be able to use the product, a valid license agreement must be requested from eGenix.com. I got mine by email as a zipped file (Thanks!). After being unzipped, the license agreement includes two files. The first file is a text document with the eGenix.com Commercial License Agreement. The second file is a Python script with information about the licensee and duration of the agreement. Both files must be copied to /lib/python/mx/ODBC/ before Zope is restarted.

Finally, I used two tables for our tests. Both tables were remotely accessed through a LAN from my MS Windows box. The data sources for these two tables were:

DSN=mysql_myweb;UID=test;PWD=test DSN=test2; UID=test;PWD=test

As you can see, both data source strings are based on the information defined at /etc/odbc.ini (see Listing 1).

Using the product could not be easier. Once everything has been installed, the next step is to restart Zope. Figure 4 shows the content of our ZMI Products folder. If your installation succeeds, it should appear like this.

Figure 4

To create a new mxODBC connection pull down the 'Add new product' list box and choose the last item in the list — it should read “eGenix mxODBC Database Connection.” Figure 5 shows how to do the selection from the list.

Figure 5

To add the new connection, it is necessary to fill out the configuration form shown in Figure 6. Sidebar 1 explains each one of these fields.

Figure 6

An interesting property of mxODBX depends on the possibility of setting up more than one physical connection per logical connection — this means that one can set up multiple physical database connections for each Zope database connection object. The advantage of using multiple physical connections becomes clear in environments where a large number of users have access to the same connection object used by a common Zope application. Many database adapters deal with this problem by serializing all database requests that use the same connection object. In contrast, mxODBC uses the underlying Zope threading capabilities to process as many requests as possible on separate physical connections (threads) running concurrently, which translates into a significant burst of performance. However, by definition, there cannot be more physical database connections than Zope threads.

mxODBC Zope DA requires several parameters:

• Connection Id: this is the ZODBC id.
• Connection Title: this is the title used to show the object in the ZMI.
• ODBC Manager/Driver: this is the ODBC manager used by the system platform. mxODBC can use different ODBC managers. However, eGenix recommends that users choose the “Platform Default” option. This option will allow mxODBC Zope DA to automatically select any ODBC manager available on the system.
• Database Connection String: this is the data source string as specified in the ODBC standard. It appears as follows: “DSN=;UID=;PWD=,” where DSN is the data source name, UID the database username, and PWD the database password.
• Database Timezone: this parameter allows the user to associate a time-zone value with database objects of type date/time. However, such a time-zone value has meaning only for data fetched from the database and manipulated by Zope objects. The reason for this is that some databases do not support time-zones. The ODBC standard does not support them either.
• Connection Pool Size: mxODBC can use multiple physical database connections per logical connection. Users may use this parameter to set up however many physical connections are allowed per every logical connection used. Each physical connection is handled by a separate Zope thread — for this reason, eGenix recommends that this number be less than or equal to the number of threads used by the Zope instance running.
• Connection Options: After the connection has been created, this field can be modified to address options available for a particular driver. Some examples of connection options are the following:
  • Ignore database warnings: this allows the user to turn off the ODBC error warning system.
  • Use auto-commit: by default, mxODBC operates in transactional mode, assuring data integrity and consistency. Of course, the option is valid for a database back-end that supports transactions. On systems such as MS Excel or MySQL 3.x one can disable this option.
  • Use connect on demand: connections are created but are not on until they are needed.
  • Fetch TIME columns as strings:
  • Fetch last available result set: in case one uses a stored procedure, mxODBC can fetch multiple result sets. However, by default, mxODBC Zope DA always returns the first available result set. To avoid problems related to this default mode (for instance, returning intermediate results) one can set up the adapter to return the last result set. available
• Open Connection?: if this field is checked, Zope will try to open the connection each time it restarts.

I tested the database adapter by implementing two logical connections, one for each database back-end. Additionally, I defined a pool of two (2) connections for MySQL and three (3) for PostgreSQL. The overall performance of the connections was really good. I created several ZSQL methods to test every connection. Figure 7 shows an example. In this case I used a connection to a PostgreSQL stored procedure that returns a value from a table. Our table is a simple address book with the following attributes.

The stored procedure requires one parameter (lastName) to be passed in order to find the corresponding phone number. Executing the stored procedure was really simple. Figure 7 shows the ZSQL method I used.

Figure 7

I also implemented several queries to retrieve all records and other queries to insert more records. All queries were executed very quickly and efficiently. Even with multiple users accessing different ZSQL methods that used the same connection pool, the database adapter response was very very impressive. Unfortunately, I could not submit the adapter to the kind of load one would expect in a real production environment.

However, using the Unix/Linux 'ps ax' command one can see how many actual (physical) connections are used on the server side. Figure 8 shows the result of executing ps ax on our server.

Figure 8

The green arrows show that the two (2) PostgreSQL physical connections are open. The yellow arrows show the two (2) connections open for MySQL. Note that while we defined a pool size of three (3) connections for PostgreSQL, only two (2) are actually in use, i. e. mxODBC works efficiently.

eGenix mxODBC Zope Database Adapter is a proprietary Zope level 3 database adapter which is easy to use and install. It provides all the functionality of the ODBC standard and is fully compatible with Zope SQL methods. Also, it is completely multi-threaded, allowing the use of multiple physical connections per logical ODBC connection and, at the same time, keeping open only those connections which are really needed. Additionally, the adapter can add more physical connections to the pool on demand, up to the maximum defined pool size.

Unfortunately, not all drivers and DBMSs support all eGenix mxODBC Zope DA features. For drivers without safe multi-threading capabilities, the eGenix database adapter pooling feature is not completely safe.

I would say that for the price, the eGenix mxODBC Zope Database Adapter is an interesting option for those database-based applications that require a low level of latency and multiple access to different DBMSs. Having the possibility of using a single database adapter for all the different DBMS connections might be a good strategy for improving performance and simplifying maintenance. Also, using a well-known standard like ODBC facilitates the solution of many problems associated with integrating applications running on multiple platforms.

Product Review End.