FreeTDS Development Group

[Dylan Kucera <djkucera AT sympatico.ca>]

Freddy,

Attached is a dump of the problem occurring with the unmodified
FreeTDS.  I'm working with 0.83.dev.20110125 as a base.

The plot thickened a little bit when we went to upgrade Oracle
Heterogeneous Services in order to try to remedy some other issues.
The newer version required the driver to also be able to
SQLDescribeCol before SQLExecute.  So I started using a method of
Executing the statement early in SQLNumResultCols and SQLDescribeCol
and keeping a record of that fact so I wouldn't try to execute twice.
This worked until it also needed to do SQLNumResultCols on statements
with Bind Variables before binding all the variables.  So I had to
keep my original "counting commas" approach as well.

I'm including a diff file as well of the things I have done to odbc.c
in order to allow (in some use-cases) the early use of
SQLNumResultCols and SQLDescribeCol.  Maybe you can think of a more
elegant way to do it?  I'm sure there must be a better way than a lazy
global variable approach, but I'm not familiar enough with the
underlying structures to make the change properly.  And it's most
certainly not MARS friendly! :-O  Regardless, for now, this appears to
be a decent stop-gap for our use-case as I've not found a way in which
Oracle Heterogeneous Services can trip up FreeTDS with this patch.

I will be travelling for the next couple of weeks, so please forgive
me if I'm latent in responses for the next while - I am still very
interested in finding a solution that the community would be willing
to adopt into the official code.

Thank you for your response and consideration!
DK.

On Tuesday, March 29, 2011 11:08:47 AM, Frediano Ziglio wrote:

> Ehmm... should work... could you post a dump please??

> There is also a test that check this.

> freddy77

> 2011/3/12 Dylan Kucera <djkucera AT sympatico.ca>:
>> The following post is unrelated to the issue I just posted with respect to
>> the "handle 0" problem with TDS 8.0 that I just posted.
>>
>> I was hoping to get some feedback on a work-around that I created for the
>> limitation where FreeTDS cannot determine the answer for SQLNumResultCols
>> after only calling SQLPrepare.  As designed, from what I understand,
>> SQLExecute must be called first.
>>
>> I'm working with the Oracle Heterogenous Services database link capability
>> so I have no ability to change the approach it takes, and in some
>> scenarios it asks for SQLNumResultCols after only doing a SQLPrepare.
>>
>> From what I've observed, I believe I can count on the structure of the
>> statement where this occurs to be
>>
>> SELECT "c1", "c2", ..., "cn" FROM "t1" WHERE ...
>>
>> So, assuming there is no reason to believe the database link has chosen
>> invalid columns (which it won't because it's already confirmed the column
>> list with sp_columns!  It just seems to have obsessive compulsive
>> disorder!) then counting the commas before the FROM and adding one does
>> the trick.
>>
>> I know it's not the most elegant thing in the world but after doing this I
>> can use my Oracle Heterogeneous Services database links just fine.  For
>> those who generally work on the FreeTDS code, would you consider putting
>> something like this into the main code line for those of us wishing to use
>> FreeTDS in this use case?  Would it really be worse than returning 0?
>>
>> Perhaps at the least this helps someone else out, though I'd be open to
>> any cautionary tales if someone has already been down this path.
>>
>> in odbc.c, 2 changes:
>>
>> 1) Near line 1320:  redefine IRD_UPDATE as per another post I found where
>> someone had a similar problem:
>>
>> #define IRD_UPDATE(desc, errs, exit) do { } while(0)
>>
>> #if 0
>> #define IRD_UPDATE(desc, errs, exit) \
>>        do { \
>>                if (desc->type == DESC_IRD && 
>> ((TDS_STMT*)desc->parent)->need_reprepare && \
>>                    odbc_update_ird((TDS_STMT*)desc->parent, errs) != 
>> SQL_SUCCESS) \
>>                        exit; \
>>        } while(0)
>> #endif
>>
>> 2) Near line 4425: redefine SQLNumResultCols as follows:
>>
>> SQLRETURN ODBC_API
>> SQLNumResultCols(SQLHSTMT hstmt, SQLSMALLINT FAR * pccol)
>> {
>> char *s;
>> char *p;
>> long c;
>>
>>        INIT_HSTMT;
>>
>>
>>        tdsdump_log(TDS_DBG_FUNC, "SQLNumResultCols(%p, %p)\n",
>>                        hstmt, pccol);
>>
>>        /*
>>         * 3/15/2001 bsb - DBD::ODBC calls SQLNumResultCols on non-result
>>         * generating queries such as 'drop table'
>>         */
>> #if 0
>>        if (stmt->row_status == NOT_IN_ROW) {
>>                odbc_errs_add(&stmt->errs, "24000", NULL);
>>                ODBC_RETURN(stmt, SQL_ERROR);
>>        }
>> #endif
>>        IRD_UPDATE(stmt->ird, &stmt->errs, ODBC_RETURN(stmt, SQL_ERROR));
>>        *pccol = stmt->ird->header.sql_desc_count;
>>
>>
>>        if (stmt->ird->header.sql_desc_count == 0) {
>> tdsdump_log(TDS_DBG_FUNC, "SQLNumResultCols(%p, %p) - Desparate measures - 
>> parse from %s\n",
>> hstmt, pccol, stmt->prepared_query);
>>                s = malloc (strlen(stmt->prepared_query) + 64);
>>                strcpy(s, stmt->prepared_query);
>>                strupr(s);
>>                p = strstr(s, " FROM ");
>>                if (p != 0) {
>>                        *p = '\0';
>>                        c = 1;
>>                        p = s;
>>                        while ((p = strchr(p+1, ',')) != 0) {
>>                                ++c;
>>                        }
>>                        *pccol = c;
>>                }
>>                free(s);
>>        }
>>
>>
>> tdsdump_log(TDS_DBG_FUNC, "SQLNumResultCols(%p, %p) = %d\n",
>> hstmt, pccol, *pccol);
>>
>>
>>        ODBC_RETURN_(stmt);
>> }
>>
>>
>> _______________________________________________
>> FreeTDS mailing list
>> FreeTDS AT lists.ibiblio.org
>> http://lists.ibiblio.org/mailman/listinfo/freetds
>>

Attachment: FreeTDS_ODBC_Dump_SQLNumResultCols.rar
Description: Binary data

Attachment: odbc.c.diff
Description: Binary data