ZopeMag's mascot the ZOPE fish


Article Finder
People
Issue 8 - Revision 8  /   September 26, 2004 


 
  ZopeMag Links:
Latest Issue
About the Fish
Issue 10
Issue 09
Issue 08
Issue 07
Issue 06
Issue 05
Issue 04
Issue 03
Issue 02
Issue 01
 
 
Downloads
     
  Letter from the Editor:
   Issue 8

Interviews:
Each issue we interview important people in the Zope world.

  Stéfane Fermigier

Articles:
Throughout the quarter we cover topics of interest to Zope developers, designers, and users.

  Fine Grained Permissions

  Archetypes Part III

  Intro to Silva

  Profiling Zope

  Intro to CPS

Product Review:
Too many Products, too little time? ZopeMag keeps you up-to-date which Zope Products are worthwhile checking out.

  DocFinderTab
  mxODBC DA


Guides:
This quarter we bring you a new SuperGuide. Our miniGuides and SuperGuides give you the background knowledge you need to mastering Zope.

  miniGuide to Zope Hosting
  SuperGuide - Zope for Newbies (Part II)
 
 
Downloads
     
  URLs / Download
Products we talk about in this issues Articles and Reviews

     


mxODBC Zope DA
Commercial Zope Database Adaptor (DA)
- - - - - - - - - - - -

By Samuel Sotillo |  September 25, 2004

print
____
 
 
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
 
____

Pros

Multi threaded and available on demand.

Cons

Not all drivers support all its functionality.



Introduction

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 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 mxODBC

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.

Configuring a new mxODBC connection parameters

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.
Review

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.

Field NameType Size
firstName Text 60
lastName Text 60
addressText 128
phoneText 16
emailText 128

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.

fig. 1
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.

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.


Samuel Sotillo:

Samuel Sotillo is a free-lance writer from Venezuela. His interests are: Zope, transactional systems, Python, PostgreSQL, MySQL, Web services, and Latin-American literature and history.


shim
shim  ZopeMag is committed to bringing you the best in Zope Documentation. shim
shim


Home   Subscribe   FAQ   Contact   Write for us   Privacy Policy   Weekly News   PyZine   opensourcexperts.com  

Reproduction of material from any of ZopeMag's pages without prior written permission is strictly prohibited. Copyright 2003 - 2005 ZopeMag Zope/Plone hosting by Nidelven IT