[Sqlrelay-discussion] FreeTDS "new operation while results pending" or something like that...

[David M. <dav...@fi...>]

Hello SQL Relay'ers,

For a while now, people have been running into a problem using FreeTDS
where they get an error like:

"Attempt to initiate a new SQL Server operation with results pending."

I finally discovered the cause of the problem last night.

Most databases support a connection/cursor paradigm where multiple
queries can be run and multiple result sets can be accessed
simultaneously through a single connection, using separate cursors for
each result set.

Sybase and MSSQLServer's native API's support this paradigm too, but
they also support running a query without a cursor.

FreeTDS currently only supports running a query without a cursor.  You
can apparently have multiple "statements" active at the same time, but
you cannot execute one statement while another statement's result set is
still open.  I had previously confused FreeTDS statements with cursors,
but now I understand.  And it has interesting consequences.

For instance, you really can't run nested queries using FreeTDS using a
single connection.  Ie, you can't "select * from mytable" and then step
through the result set, inserting rows into another table.  Or, it would
surprise me if you could.

At any rate, what's happening here is:

When it's time to run a query, the sqlr-connection-freetds deamon grabs
a cursor, closes the currently open result set of the previous query (if
there was one) for that cursor and runs the query.

The sqlr-connection-freetds deamon also keeps track of whether an
insert/update/delete was run and runs a commit when the client exits if
it was by allocating a new cursor, running the commit and closing the
cursor.

Whenever a new cursor is allocated, that cursor runs a "use" query to
select the proper database to use.

So, if an SQL Relay client runs an insert, then runs a select, then
closes the connection, the sqlr-connection-freetds daemon will attempt
to run a commit.  It will allocate a new cursor and try to run the "use"
query, but alas, the select's result set is still open, and since the
select isn't attached to a FreeTDS cursor, just to a FreeTDS statement,
it's really attached to a FreeTDS connection, and so is the "use" query,
thus the error:  we're attempting to initiate a new SQL Server operation
(the "use" query) with the results of the previous select still pending.



Understanding the problem doesn't exactly tell me how to solve it
though.  Fundamentally, FreeTDS needs to support cursors, or I need to
totally rewrite SQL Relay's freetds driver to do everything through the
connection and simulate multiple cursors by caching result sets or
something.  FreeTDS does have the ct_cursor function and all of the
associated macros, but they don't appear to work.  Or at least didn't
last time I tried, code that worked using Sybase's native ctlib doesn't
work against FreeTDS.  Programs like sqsh that can use FreeTDS only
allow you to run 1 query at a time. They don't even use cursors, so I
can't really look at any example code from there.

Anyway, I just wanted to keep folks informed.  I'm going to look into
the issue further.  Now that I know the problem, I'll find a solution.

In the mean time, there are some workarounds that might be helpful:

* If you do an insert/updtate/delete, doing a manual commit before the
client exits.

* Otherwise, do insert/update/delete's in separate transactions or
sessions from select's.

* If you have to do nested queries, such as doing a select, then
iterating over the result set and doing inserts, try writing a stored
procedure to do it.

Hope this helps, 

Dave
dav...@fi...