FreeTDS Development Group

["Lowden, James K" <LowdenJK AT bernstein.com>]

> From: Thompson, Bill D (London) [mailto:ThompBil AT exchange.uk.ml.com]
> Sent: January 24, 2003 7:39 AM
> 
> can anyone (Brian?, Scott?) enlighten me on cursor support 
> within Sybase/SQL Server/TDS ?
> I am investigating a Sybase ct-library application which I am 
> contemplating porting to MS SQL
> This application makes heavy use of ct_cursor() API calls, 
> and this function is not implemented in FreeTDS.
>
> I might have a go at adding the code, but I'm unsure if there is an
> equivalent concept in SQL server, and if there is, whether 
> there is a TDS
> protocol in TDS 7.0 to support this functionality.
> From the looks of the MS SQL server online help there is very 
> little mention of cursors

Bill, 

Look up "sp_cursor" in Books Online; you'll find a whole section on Cursors
in "Accessing and Changing Data".  Confirming what Scott said, it says:

"API Server Cursor Implementation
...
sp_cursor is used to request positioned updates. 
...
These system stored procedures will show up in SQL Server Profiler traces of
ADO, OLE DB,  ODBC, and DB-Library applications that are using API server
cursors. They are intended only for the internal use of the SQL Server
Provider for OLE DB, the SQL Server ODBC driver, and the DB-Library DLL. The
full functionality of these procedures is available to the applications
through the use of the cursor functionality of the database APIs. Specifying
the procedures directly in an application is not supported."

[But that's fine, right?  Anything FreeTDS does that *is* supported probably
belongs to another project.]

The special thing about cursors is that the processing of their results can
be interrupted by other queries.  The docs go on to explain why sp_cursor
and friends exist:  

        "When SQL Server executes a statement for a connection, no other
statements can be executed on the connection until all the results from the
first statement have been processed or canceled. This rule still holds when
using API server cursors, but to the application it looks like SQL Server
has started supporting multiple active statements on a connection. This is
because the full result set is stored in the server cursor and the only
statements being transmitted to SQL Server are the executions of the
sp_cursor system stored procedures. SQL Server executes the stored
procedure, and as soon as the client retrieves the result set it can execute
any other statement. The OLE DB provider and ODBC driver always retrieve all
the results from an sp_cursor stored procedure before they return control to
the application. This lets applications interleave fetches against multiple
active server cursors."

Microsoft defines 3 kinds of cursors:

1.  Transact-SQL cursors.  Sybase calls these "Langugage cursors".  
2.  API server cursors.  "Client-Library cursors" to Sybase.  
3.  Client cursors.  Provided by Sybase in db-lib.  

#1 doesn't concern us, and #3 doesn't exist in ct-lib.  #2 offers an API to
control cursors created on the server (to simplify somewhat).  Sybase and
Microsoft took wildly divergent paths to acheive highly similar results:
Sybase tokenized it in TDS 5.0; Microsoft opted for a collection of stored
procedures.  

Whether or not you can implement the full ct-lib cursor functionality
against a Microsoft server will turn on the capabilities of those stored
procedures.  Prepare for some serious reverse engineering, though, because
they (sp_cursoropen, sp_cursorfetch, sp_cursorclose, sp_cursoroption,
sp_cursor, sp_cursorprepare, sp_cursorexecute, sp_cursorunprepare) aren't in
master..sysobjects.  When they are generated -- or if in fact they are ever
manifested in the system catalog -- I don't know.  

The good news is that Microsoft's server-side cursors are much richer than
Sybase's, so implementing ct-lib cursors looks theoretically feasible.
Sybase cursors are always forward-only, for instance.  Isolation levels will
be tricky, because for Sybase isolation is a property of the session whereas
for Microsoft it's a property of the cursor.  

For Sybase servers, we can bolt ct-lib right on top of TDS 5.0.  

Not that you're concerned about it right now, but while we're on the
subject, db-lib cursor support is going to be, um, interesting.  Whereas
Sybase's implementation is strictly client-side, Microsoft's is not.  They
say:

"dbcursoropen
Opens a cursor. It is an explicit server cursor if you are connected to
Microsoft® SQL Server(tm) version 6.0 or later and dbcursorfetchex is used
for the first fetch. It is a transparent server cursor if you are connected
to SQL Server 6.0 or later and dbcursorfetch is used for the first fetch. It
is a client cursor if you are connected to SQL Server 4.2, or if the
DBCLIENTCURSORS option is set, and only dbcursorfetch can be used to fetch
rows."

Oy veh.  

+++

Bill, you've observed before that libtds includes a lot of
common-but-not-TDS-related stuff, and suggested we should have another
library for that purpose.  It seems to me cursor support is of that ilk:
cursors are offered by each API, some client- and some server-based.  I was
going to suggest a new directory in the tree called, say, "common" or
"support", but I think that would make things harder to find (for the
programmer).  Instead, I think we should have a new directory just for
cursors (called, yea, "cursor") to implement the underlying logic, keeping
the client library support as thin as possible.  I imagine three modules to
start: client.c, server_microsoft.c, and server_sybase.c.  

One other suggestion, a page out of Extreme Programming:  Write the unit
tests first.  If you wrote the ct-lib cursor unit test(s), someone might be
willing to express them in ODBC terms.  An ODBC cursor unit test could be
linked to Microsoft's libraries and run in a Win32 framework, with tcpdump
on hand to observe the server interaction.  My bet is that you're going to
need that trace real bad.  Especially when your application passes
CS_CURSOR_DECLARE to ct_dynamic().  

Hope you're not sorry you asked.  :-)

--jkl


The information contained in this transmission may contain privileged and
confidential information and is intended only for the use of the person(s)
named above.  If you are not the intended recipient, or an employee or agent
responsible for delivering this message to the intended recipient, any
review, dissemination, distribution or duplication of this communication is
strictly prohibited. If you are not the intended recipient, please contact
the sender immediately by reply e-mail and destroy all copies of the
original message. Please note that we do not accept account orders and/or
instructions by e-mail, and therefore will not be responsible for carrying
out such orders and/or instructions.