Issue 8 - Revision 8 / September 26, 2004
mxODBC Zope DA
Commercial Zope Database Adaptor (DA)
- - - - - - - - - - - -
By Samuel Sotillo | September 25, 2004
Multi threaded and available on demand.Cons
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.
Prerequisites and Installation Tips
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 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.
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.
(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:
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
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:
As you can see, both data source strings are based on the information defined at
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.
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.
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.
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.Configuring a new mxODBC connection parameters
mxODBC Zope DA requires several parameters:
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.
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.
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.Summary
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.
|ZopeMag is committed to bringing you the best in Zope Documentation.|
|Reproduction of material from any of ZopeMag's pages without prior written permission is strictly prohibited. Copyright 2003 - 2005 ZopeMag|