[egenix-users] SQL Server: Experimental Solution

nwingfield at che-llp.com nwingfield at che-llp.com
Wed Oct 22 18:48:21 CEST 2003


We have been experiencing many problems with mxODBC and SQL Server, similar
to those expressed by others list members (Sébastien Walgraffe, Peter
Sabaini).  The most common error is this:
'HY000', 0, '[Microsoft][ODBC SQL Server Driver]Connection is busy with
results for another hstmt', 5911
By eliminating Z SQL methods, we seem to have remedied this error, though
only time will tell.  We have replaced Z SQL methods with the External
Method that I am including below.  Another problem that we experienced was
that when the connection pool was set greater than one, pages executing
multiple SQL statements could lock up.  This is because each SQL method was
potentially executed on a different connection.  Rather than limit
ourselves to a connection pool of one, we incorporated some logic into the
aforementioned method to maintain one connection per request.  If the
locking isn't a problem for you, you can strip the extra logic out of this
method.

Let me know if this helps.

def executeSQL(sstmt,odbcObject,REQUEST,max_rows=None):
   """
   DESCRIPTION:
   This method will execute an SQL statement using the mxODBC Connector
provided.
   For the duration of the request, it ensures that only one connection is
used.
   This is done in order to prevent weird hard locks on SQL Server.
   PARAMETERS:
   sstmt:      well-formed SQL to be executed (don't forget the semicolon!)
   odbcObject: the mxODBC data connector to be used
   REQUEST:    context.REQUEST
   max_rows:   maximum rows to retrieve
   """
   REQUEST_KEY = 'DB_CONNECTION' + '_'.join(odbcObject.getPhysicalPath())
   sstmt = sstmt.strip()
   if not hasattr(REQUEST,REQUEST_KEY):
      REQUEST[REQUEST_KEY] = odbcObject.get_connection()
   conn = REQUEST[REQUEST_KEY]
   results = conn.query(sstmt,max_rows=max_rows)
   rs = Results(results)
   return rs




More information about the egenix-users mailing list