zxJDBC

Database Connectivity in JPython

 
Home
 
News
 
FAQ
 
Download
 
Documentation
 
License
 
    

Documentation

Getting started

First, make sure both zxJDBC.jar and a valid JDBC driver are in your classpath. Then start JPython and import the zxJDBC connection factory. Supply the database, username, password and JDBC driver classname to the connect method. If your driver requires special arguments, pass them into the connect method as standard Python keyword arguments. You will be returned a connection object.

JPython 1.1+07 on java1.2.2 (JIT: symcjit)
Copyright (C) 1997-1999 Corporation for National Research Initiatives
>>> from com.ziclix.python.sql import zxJDBC
>>> zxJDBC.autocommit = 0
>>> d, u, p, v = "database", "user", "password", "com.informix.jdbc.IfxDriver"
>>> db = zxJDBC.connect(d, u, p, v)
 optionally
>>> db = zxJDBC.connect(d, u, p, v, CHARSET='iso_1')
>>>

To access the database, create a cursor from the connection. To execute a query simply provide the sql expression and call execute. The cursor now has a description attribute detailing the column information. To navigate the result set, call one of the fetch methods and a list of tuples will be returned.

>>> c = db.cursor()
>>> c.execute("select count(*) c from player")
>>> c.description
[('c', 3, 17, None, 15, 0, 1)]
>>> for a in c.fetchall():
...  print a
...
(13569,)
>>>
When finished, close the connections.
>>> c.close()
>>> db.close()
>>>

Standard extensions to the Python DB API

connection.dbname
Same as DatabaseMetaData.getDatabaseProductName

connection.dbversion

Same as DatabaseMetaData.getDatabaseProductVersion

cursor.tables(qualifier,owner,table,type)

Same as DatabaseMetaData.getTables

cursor.columns(qualifier,owner,table,column)

Same as DatabaseMetaData.getColumns

cursor.foreignkeys(primary_qualifier,primary_owner,pimary_table, foreign_qualifier,foreign_owner,foreign_table)

Same as DatabaseMetaData.getCrossReference

cursor.primarykeys(qualifier,owner,table)

Same as DatabaseMetaData.getPrimaryKeys

cursor.procedures(qualifier,owner,procedure)

Same as DatabaseMetaData.getProcedures

cursor.procedurecolumns(qualifier,owner,procedure,column)

Same as DatabaseMetaData.getProcedureColumns

cursor.statistics(qualifier,owner,table,unique,accuracy)

Same as DatabaseMetaData.getIndexInfo

Datatype mapping callbacks through DataHandler

The DataHandler interface has three methods for handling type mappings. They are called at two different times, one when fetching and the other when binding objects for use in a prepared statement. I have chosen this architecture for type binding because I noticed a number of discrepancies in how different JDBC drivers handled database types, in particular the additional types available in later JDBC versions.

public PyObject getPyObject(ResultSet set, int col, int type);

This method is called upon fetching data from the database. Given the JDBC type, return the appropriate PyObject subclass from the the Java object at column col in the ResultSet set.

public Object getJDBCObject(PyObject object, int type);

This method is called when a PreparedStatement is created through use of the execute method. When the parameters are being bound to the statement, the DataHandler gets a callback to map the type. This is only called if type bindings are present.

public Object getJDBCObject(PyObject object);

This method is called when no type bindings are present during the execution of a PreparedStatement.

It is simple to use these callbacks to achieve the desired result for your database driver. In the majority of cases nothing needs to be done to get the correct datatype mapping. However, in the cases where drivers differ from the spec or handle values differently, the DataHandler callbacks should provide the solution.

Example DataHandler for Informix booleans

One such case where a driver needs a special mapping is Informix booleans. The are represented as the characters 't' and 'f' in the database and have their own type boolean. You can see from the example below, without the special DataHandler, the boolean type mapping fails.

JPython 1.1+07 on java1.2.2 (JIT: symcjit)
Copyright (C) 1997-1999 Corporation for National Research Initiatives
>>> from com.ziclix.python.sql import zxJDBC
>>> zxJDBC.autocommit = 0
>>> d, u, p, v = "database", "user", "password", "com.informix.jdbc.IfxDriver"
>>> db = zxJDBC.connect(d, u, p, v)
>>> c = db.cursor()
>>> c.execute("create table g (a boolean)")
>>> c.execute("insert into g values (?)", [1])
Traceback (innermost last):
File "<console>", line 1, in ?
Error: No cast from integer to boolean. [SQLCode: -9634]
>>> from com.ziclix.python.sql.informix import InformixDataHandler
>>> c.datahandler = InformixDataHandler(c.datahandler)
>>> c.execute("insert into g values (?)", [1], {0:zxJDBC.OTHER})
>>>

As you can see, the default handler fails to convert the Python 1 into an Informix boolean because the IfxDriver treats booleans as JDBC type OTHER. The InformixDataHandler is intimately aware of the IfxDriver mappings and understands how to interpret Python values as booleans when the JDBC type is OTHER.

This functionality is also useful in handling the more advanced JDBC 2.0 types CLOB, BLOB and Array.

You can also implement the DataHandler from within JPython as in this simple example:

>>> class PyHandler(DataHandler):
>>>  def __init__(self, handler):
>>>   self.handler = handler
>>>  def getPyObject(self, set, col, datatype):
>>>   return self.handler.getPyObject(set, col, datatype)
>>>  def getJDBCObject(self, object, datatype):
>>>   print "handling prepared statement"
>>>   return self.handler.getJDBCObject(object, datatype)
>>>
>>> c.datahandler = PyHandler(c.datahandler)
>>> c.execute("insert into g values (?)", [1])
handling prepared statement
>>>

Javadoc

You can also look at the javadoc available here.

Feel free to email me with questions or suggestions at bzimmer@ziclix.com


$Id: documentation.html,v 1.5 2000/07/06 00:32:15 bzimmer Exp $