Re: [Sqlrelay-discussion] SQL Server connections hanging

[Firstworks/4access <dav...@fi...>]

I have been looking into this lately, but I haven't figured out what's
going on yet.

I have a hunch that it has to do with the fact that Sybase and MS SQL
Server can return multiple result sets for a single query.  For example,
if you run a query which has output bind variables and a result set,
then the output bind variables are returned as one result set and the
rows and columns as another.

Maybe for queries where nocount isn't set on, some output bind variables
are getting implicitly generated and the main result set is getting
processed but the output bind variable result set isn't, and the attempt
to cancel it is failing too.

It seems like there was a time when this bug didn't occur.  Perhaps an
older version of FreeTDS didn't properly return both result sets and now
that it does, it exposes a bug in SQL Relay.

I'm still not sure, but fixing this is high on my list of priorities.

David Muse
dav...@fi...

On Mon, 2005-10-10 at 13:01 -0500, Dave Benjamin wrote:
> 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
>