FreeTDS Development Group

["ZIGLIO Frediano" <Frediano.Ziglio AT vodafone.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
> 

I think we should work on curr_resinfo.. This pointer point to current result 
and can be res_info or cur_dyn->res_info. token.c just write to curr_resinfo 
(or it should)... We can use a similar solution for cursors. When we allocate 
a cursor we return a TDSCURSOR* (or whatever) like in tds_submit_prepare we 
return a TDSDYNAMIC*. When we want to get some rows from cursors we just set 
curr_resinfo to our cursor->res_info. Problem is compute. I think is a good 
idea to add a TDSCOMPUTEINFO **comp_info to TDSRESULTINFO structure and 
remove from TDSSOCKET. In this way TDSRESULTINFO can store every possible 
results (parameters, rows and compute) from tds.h 

  typedef struct tds_result_info TDSCOMPUTEINFO;
  typedef TDSRESULTINFO TDSPARAMINFO;

so all results are just same type... tds->res_info should be use only for 
no-dynamic/no-cursors results.
This was just prepared for cursors as declared in an very old mail... 
strangely you add curr_resinfo for this stuff..

freddy77