[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