[Sqlrelay-discussion] SQL Server connections hanging

[Dave B. <ra...@ra...>]

I've been trying to get Zope to talk to SQL Server using FreeTDS, SQL 
Relay, and ZSQLRelayDA. I ran into a problem that apparently others have 
experienced but nobody has been able to help with, so I'm posting this 
message in hopes that it will help others understand the problem and 
perhaps fix the bug.

I'm not sure if the bug is with FreeTDS or SQL Relay, so please let me 
know if I should bring this up with the FreeTDS folks.

The basic problem is that any calls to "commit" or "rollback" after a 
query operation result in an error message, "Attempt to initiate a new SQL 
Server operation with results pending.", which is printed to the console. 
Once this occurs, the connection is corrupted. This is particularly 
unfortunate with Zope because the ZSQLRelayDA adapter calls "commit" or 
"rollback" after every transaction, whether it was a query or otherwise. 
The end result is that after three queries (or however many connections 
SQL Relay is configured for), future queries hang and the whole site goes 
down.

Apparently, as long as your last statement was not a query (ie. anything 
other than SELECT), you can commit or roll back without crashing the 
connection. My temporary fix was to alter the db.py file that comes with 
ZSQLRelayDA to add some (hopefully harmless) dummy statements before the 
commit or rollback:

     def _finish(self, *ignored):
         self.cur.execute('SET NOCOUNT OFF') # hack
         self.con.commit()
         self.con.close()

     def _abort(self, *ignored):
         self.cur.execute('SET NOCOUNT OFF') # hack
         self.con.rollback()
         self.con.close()

These two lines that end with "# hack" have solved the problem for me, at 
least so far. However, now my database adapter is MSSQL-specific, since I 
doubt this statement would work in any other database.

I don't know too much about how the underlying libraries work, but I 
believe this problem has something to do with result sets not being freed 
properly. I can get it to crash with something as simple as:

con = PySQLRDB.connect(...)
cur = con.cursor()
cur.execute('select * from users') # or some other table
con.commit()

Thanks for your time,
Dave

-- 
  .:[ dave benjamin: ramen/[sp00] ]:.
        "Three pounds of VAX!"