[DB-SIG] MS SQL Server module in development

[Andy Dustman]

On 2 Jul 2001, Dave Cole wrote:

> MS SQL Server uses a DB library which looks a lot like the old Sybase
> DB library.

That makes sense, since supposedly MS SQL Server was derived from Sybase.

> The DB library only allows a single result set to be in flight over a
> server connection.  To make it possible to open multiple cursors
> simultaneously I have done the "obvious" thing of opening a new
> connection for each cursor.
>
> This introduces the problem: how can I implement Connection.commit()
> and Connection.rollback()?
>
> I really have two options:
>
> 1) Do not allow multiple simultaneous cursors on a connection.
>
> 2) Implement Cursor.commit() and Cursor.rollback()
>
> Can anyone else think of any other options?

MySQL has similar implementation details. There are two ways to get the
result set (in the C API):

* mysql_store_result(): retrieves all rows of the result set from the
server and stores them on the client side.

* mysql_use_result(): keeps the result set on the server.

Rows are returned to the client application via mysql_fetch_row(). If
mysql_store_result() was used, the row is already in memory. OTOH, if
mysql_use_result() was used, the row has to be retrieved from the server.
Note that if mysql_store_result() is used, another query can be issued
right away, but this is illegal if mysql_use_result was used: All rows
must be retrieved before new queries can be executed.

In MySQLdb, the default Cursor class uses mysql_store_result(), but in
addition, it immediately fetches all rows, storing them in python-space
inside the cursor, and freeing them in the client library
(mysql_free_result()). If you have a very large result set, this can be a
problem, so there is also a SSCursor that employs mysql_use_result(), and
fetches rows one-by-one. You cannot have more than one active SSCursors at
the same time, and must fetch all the rows and close the cursor before
creating another one. At present, this must be enforced by user
discipline.

I don't think cursor.commit() is a good option.

-- 
Andy Dustman         PGP: 0xC72F3F1D
    @       .net     http://dustman.net/andy
I'll give spammers one bite of the apple, but they'll
have to guess which bite has the razor blade in it.