FreeTDS Development Group

[Jonathan Monroe <monroej AT breaktech.com>]

I am attempting to integrate a commercial program and FreeTDS using ODBC. I
think I have come across an issue related to the fact that the ODBC
SQLTables and SQLColumns functions are implemented by calling two built-in
SQL Server procs, "sp_tables" and "sp_columns".

I don't have access to the source code for the program, but I have deduced
that after creating the environment and connecting, it allocates a statement
and calls SQLTables.  It then calls SQLFetch successively until it returns
SQL_NO_DATA_FOUND.  It then allocates a new statement (without freeing the
previous statement) and calls SQLColumns.  This is where the trouble starts.

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

To be more correct, I think the implementations of these two functions
should be changed so that they either only return a recordset or ignore the
presence of a trailing return parameter.  The three options would be:

1. implement the functions as a non-stored procedure SQL query, i.e. copying
the guts of sp_tables into a big SQL statement and then calling it

2. automatically call tds_send_cancel() if a previous statement is still
pending, then proceed with the requested operation

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.

I'm willing to do the work on this one, but I'd like to get some feedback
first on the right way to go.  I already implemented option #3 because it
was quickets and dirtiest, but I think #1 is probably the better choice.

I haven't yet set up an environment to perform the same calls on the MS
SQLServer driver to see if it has the same problem (I doubt if it does) or
if it issues a cancel command, but I may do so if I get a little free time.

Opinions?

BTW, I'm calling SQL Server 2000 / TDS 8.0.

Thanks,

Jonathan Monroe