[DB-SIG] MS SQL Server module in development

Dave Cole djc@object-craft.com.au
03 Jul 2001 15:39:04 +1000


>>>>> "Andy" == Andy Dustman <andy@dustman.net> writes:

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

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

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

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

Ahhh...

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

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

I don't either.  That is why I posted to this mailing list.  After
reading this message I think I have worked out how to solve the
problem.

By default I will fetch the complete result in the Cursor.execute()
then return the result row by row in fetchone(), ...  I will also
offer an alternate cursor which fetches results on demand.  This
cursor will set a busy flag in the Connection object.  If you attempt
to open a new cursor, commit, or rollback while the connection is busy
then the method will raise an exception.

The only remaining question/issues are:

1) Should I raise an exception, or should I just make the on-demand
   cursor fallback to a fetchall cursor and clear the busy state.

2) If I implement all cursors as on-demand then I can automatically
   turn them into fetchall cursors if the cursor(), commit(), or
   rollback() methods are called on the connection.

Any other ideas?

- Dave

-- 
http://www.object-craft.com.au