RE: Issue w/ SQLColumns calling stored procedures

> From: Jonathan Monroe [mailto:monroej@xxxxxxxxxxxxx]
> Sent: September 16, 2003 11:03 PM
> 
> The call to SQLColumns fails because the previous statement 
> is still in a
> pending state.  Looking through the log, the SQLFetch 
> function consumed all
> the data up until the final DONEINPROC.  However, the store 
> procedure also
> returned an (implied) return parameter RETURNSTATUS before the final
> DONEPROC.  Because the return parameter is not consumed, the 
> statement is
> still pending.
... 
> This wouldn't have been an issue if SQLFreeHandle had been 
> called on the
> previous statement before calling SQLColumns, because 
> SQLFreeHandle would
> have issued a cancel command and cleared the buffers.  It 
> also would not be
> an issue if the calling program had called SQLMoreResults, 
> which would have
> consumed the RETURNSTATUS.  However, I don't think either of 
> these cases is
> required in a correct ODBC client implementation, because the 
> ODBC spec says
> nothing about the presence of return parameter following the 
> recordset (at least nowhere I can find).
...
> 3. record in the statement data structure the fact that sp_tables or
> sp_columns has been called, then automatically call 
> SQLMoreReults right
> before SQLFetch is about to return with SQL_NO_DATA_FOUND for 
> the statement.

Jonathan, 

Nice bit of analysis, if I may say so.  :-)

I wonder if a simpler form of #3 wouldn't be better.  AFAIK, in general it's
not required to fetch return status or output parameters.  Isn't it valid in
ODBC to ignore a return status from any stored procedure?  

I suggest that when SQLFetch returns SQL_NO_DATA_FOUND, the driver
immediately inspect the next token, which will often be TDS_RETURNSTATUS.
If it is, scoop it up and prepare to make it available if the caller
requests it.  If the caller neglects to ask for it, overwrite it when the
next result set arrives.  

In any case, I think options #1 and #2 are no better.  Rewriting the queries
seems retrograde; if anything, M$ will make that path harder over time.  And
cancelling the query just to avoid reading the return status (especially
since the status could potentially be useful) is a rather heavy-handed way
to deal with "extra" information.  

>From the TDS point of view, reading the stream smoothly is almost always the
best answer.  Whether the ODBC layer needs a little or a lot of massaging in
this case, I hope our ODBC experts will be able to answer.  

It would be interesting to know what the M$ driver does, if you're inclined
to find out.  

Regards, 

--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.