FreeTDS Development Group

["Thompson, Bill D (London)" <bill_d_thompson AT ml.com>]

Hi,

I've been doing some work on cursors recently. 
I have implemented the basic cursor functionality in the tds library, and
implemented the appropriate functions in ct-library.
The protocol issues are pretty much sorted. I've implemented the TDS 5.0
messages which enable the client to access server side cursors on Sybase,
and also the calls to the undocumented SQL server "stored procedures" which
provide the same functionality on that platform.

There's one fairly major shortcoming in what I've done however, and I don't
want to go any further without a discussion.

The implementation of cursors allows the client to maintain more than one
stream of processing on a single connection.
So, using cursors, you would be able to:

* open a cursor to select data from the database
* fetch a row or rows from the cursor.
* use the returned data as the basis of other queries or updates
* fetch more rows from the cursor
....

Previously, this type of processing was only possible if you opened up more
than one connection to the database.

This presents freetds with a bit of problem, and it's to do with the way we
process and store the results metadata.

Currently, we store the results metadata in the tsd_socket structure, which
is associated with a database connection.

This implementation was fine when we could only have one stream of
processing on a connection, i.e.

1. submit sql batch (which might contain multiple statements)
2. While no more results
   2.1 receive and store results metadata
   2.2 receive and process data relating to the result set
   2.3 receive and store "end" data

We knew pretty much that a given connection would only have to handle one
set of results metadata at a time.
Each time we received a new set of results metdata, we could safely discard
the last set and start afresh.

A while back, we stretched the limits of this model to handle "compute"
data, and output parameters from stored procedures. 
If a query contained one or more "compute" clauses, then the server would
return multiple sets of result metadata - one for the main query results,
and one for each compute clause. 
Fortunately for us the result metadata for a compute clause is identified as
such within the TDS protocol. This allowed us to handle and store this
result information separately from the main result set data.
Return paramaters from stored procedures were a similar challenge, but again
we could easily identify these and store them separately.

As a result, we now have the following in the tds_socket structure :

TDSRESULTINFO *curr_resinfo;     /* pointer to one of the below... */
TDSRESULTINFO *res_info;          /* "main" results metadata       */
TDS_INT num_comp_info;             /* number of  compute results  */
TDSCOMPUTEINFO **comp_info;  /* array of compute results metadata */
TDSPARAMINFO *param_info;      /* parameter results metadata  */

With the advent of cursors, two key facts conspire to finally break our
processing, I believe...

1. The results metadata received from an "open cursor" operation is
indistinguishable from the 
    results metadata received from a straightforward query.

2. The results metadata is handled and stored by the "tds" layer in our
code, which is essentially
    "blind" to the context in which those results are received.

I am coming to the conclusion that the storage of the results metadata
should be controlled by the API layers within freetds.

I can envisage a new model where :

tds_process_result_tokens, when it reads a set of results metadata,
allocates the storage for that data, but returns the storage to the calling
function for management.
If an API function, say ct_results or dbresults is calling
tds_process_result_tokens, it could then save the allocated results
structure to it's own control structure - the DBPROCESS structure for
dblibrary or the CS_COMMAND structure for ctlibrary
If an internal function such as tds_set_spid or tds_process_simple_query is
calling tds_process_result_tokens, it could hold the results data
temporarily.

tds_process_row_tokens, when called to retrieve a data row, could be passed
the appropriate results metadata structure to allow it to interpret the
"row" data correctly.

I'm still in the process of thinking this through (as I'm sure you can
tell), but I'd appreciate any feedback or ideas.

Best wishes,


¤º°`°º¤ø,¸¸,ø¤º°`°º¤ø¤º°`°º¤ø,¸¸,ø¤º°`°º¤ø¤º°`°º¤ø,¸¸,ø¤

Bill Thompson
Securities Services Division
Merrill Lynch Europe

For very important information relating to this e-mail please click on this
link:  http://www.ml.com/legal_info.htm