[freetds] SQLExecDirect returns SQL_NO_DATA

John Wythe (work) jwythe at silksystems.com
Sat Mar 11 12:51:33 EST 2006


>> I have found a problem with SQLExecDirect.  If I execute a query that is 
>> not intended to return data, I get an SQL_NO_DATA result.  This is not 
>> what the same function returns on Windows, it returns SQL_SUCCESS.   If I 
>> use ISQL to execute the query, which uses SQLPrepare, and SQLExecute, I 
>> get the proper result.
>>
>
> This is not a problem... ODBC 3 specification document that
> SQLExecDirect should return SQL_NO_DATA if no data returned... mssql
> driver seems to not complain to these specifications... I'll look more
> deeper...
>
> Can you post a query that cause this behavior?

This is the first one I had a problem with, but other will do it as well.

set quoted_identifier on

others

Create database .....
Drop database ......

>
>
>> I have looked at the source, and found the problem.  Maybe you can look 
>> at the before and after code snippets and tell me if I made the right 
>> change, or direct me how to make the correct change.   Eventually the fix 
>> should probably be incorperated into the main code.   Basically, from 
>> debugging the program, the done flag doesn't get set, and the program 
>> tries to get another token, but there aren't any more, so it thinks it 
>> was trying to do a SQLFetch for more data which in that case should 
>> return SQL_NO_DATA, since, in this case in_row is zero, this is not the 
>> case. My question would be whether or not TDS_DONEPROC_RESULT should also 
>> do this?
>>
>> Snippets from src/odbc/odbc.c    function SQLExecDirect
>>
>> Before:
>>         case TDS_PARAM_RESULT:
>>             odbc_set_return_params(stmt);
>>             break;
>>
>>         case TDS_DONE_RESULT:
>>         case TDS_DONEPROC_RESULT:
>>             if (done_flags & TDS_DONE_COUNT) {
>>                 got_rows = 1;
>> After:
>>
>>         case TDS_PARAM_RESULT:
>>             odbc_set_return_params(stmt);
>>             break;
>>
>>         case TDS_DONE_RESULT:
>>             if (!in_row && !(done_flags & TDS_DONE_COUNT) && !(done_flags 
>> & TDS_DONE_ERROR))
>>         /* prevent queries with no result set from returning SQL_NO_DATA 
>> jww */
>>                 stmt->row_count = 0;
>>             else {
>>                 if (done_flags & TDS_DONE_COUNT) {
>>                     got_rows = 1;
>>                     stmt->row_count = tds->rows_affected;
>>                 }
>>                 if (done_flags & TDS_DONE_ERROR)
>>                     stmt->errs.lastrc = SQL_ERROR;
>>             }
>>             done = 1;
>>             break;
>
> Quite complicated... where does this code came?

Sorry, this code is in src/odbc/odbc.c   function   SQLExecute around line 
2455.

>
>>         case TDS_DONEPROC_RESULT:
>>             if (done_flags & TDS_DONE_COUNT) {
>>                 got_rows = 1;
>>
>> Thanks
>>
>> John
>
> freddy77
>
>
>
>
> ------------------------------
>
> Message: 5
> Date: Sat, 11 Mar 2006 12:56:35 +0100
> From: Frediano Ziglio <freddyz77 at tin.it>
> Subject: Re: [freetds] quoted identifier bug - namely numeric database
> To: FreeTDS Development Group <freetds at lists.ibiblio.org>
> Message-ID: <1142078195.3264.11.camel at freddy>
> Content-Type: text/plain
>
> Il giorno sab, 11/03/2006 alle 01.50 -0800, John Wythe (work) ha
> scritto:
>> The is a discrepancy in the way freetds handles quoted/quoting 
>> identifiers as compared to MS SQL's ODBC windows driver.
>>
>> In particular I am having a problem when I set the DATABASE field,  of 
>> the connection string passed to SQLDriverConnect, to 210001.  I tried 
>> setting it to [210001], but that didn't work either.  On Windows with MS 
>> SQL's ODBC driver both work just fine.
>>
>
> Specification do not speak about already quoted identifiers. You should
> use "{}" style quoting for SQLDriverConnect (as correctly documented)
>
>> There are two bugs:
>> a) tdsfree needs to quote identifiers that begin with a digit.
>
> Fixed many time ago in 0.64 (which should had been released on
> January...).
>
>> b) tdsfree should not quote an identifier that is already quoted with []
>>
>> Here is the fix I came up with.  src/tds/query.c
>>
>> BEFORE:
>> /**
>>  * Quote an id
>>  * \param tds    state information for the socket and the TDS protocol
>>  * \param buffer buffer to store quoted id. If NULL do not write anything
>>  *        (useful to compute quote length)
>>  * \param id     id to quote
>>  * \param idlen  id length
>>  * \result written chars (not including needed terminator)
>>  */
>> int
>> tds_quote_id(TDSSOCKET * tds, char *buffer, const char *id, int idlen)
>> {
>>     int i;
>>
>>     if (idlen < 0)
>>         idlen = strlen(id);
>>
>>     /* need quote ?? */
>>     for (i = 0; i < idlen; ++i)
>>         switch(id[i]) {
>>         case '\"':
>>         case '\'':
>>         case ' ':
>>         case '(':
>>         case ')':
>>         case '[':
>>         case ']':
>>         case '{':
>>         case '}':
>>             return tds_quote(tds, buffer, TDS_IS_MSSQL(tds) ? ']' : '\"', 
>> id, idlen);
>>         }
>>
>>     if (buffer) {
>>         memcpy(buffer, id, idlen);
>>         buffer[idlen] = '\0';
>>     }
>>     return idlen;
>> }
>>
>> AFTER:
>> /**
>>  * Quote an id
>>  * \param tds    state information for the socket and the TDS protocol
>>  * \param buffer buffer to store quoted id. If NULL do not write anything
>>  *        (useful to compute quote length)
>>  * \param id     id to quote
>>  * \param idlen  id length
>>  * \result written chars (not including needed terminator)
>>  */
>> int
>> tds_quote_id(TDSSOCKET * tds, char *buffer, const char *id, int idlen)
>> {
>>     int i;
>>     int need_quote = 0;
>>     int quoted = 0;
>>
>>     if (idlen < 0)
>>         idlen = strlen(id);
>>
>> /* identifiers that begin with a digit need quoting 
>> jwythe at silksystems.com */
>> /* don't quote id's that are already quoted */
>> /* let's assume quoted_identifier is off for MS SQL */
>> /* not sure what this change will do to sybase, so let's only do for MS 
>> SQL */
>>     /* need quote ?? */
>>     if (TDS_IS_MSSQL(tds))
>>         {
>>         if (isdigit(*id))
>>             need_quote = 1;
>>         else
>>             if (*id == '[' && id[idlen-1] == ']')
>>                 quoted = 1;
>>         }
>>
>>     if (!quoted)
>>         {
>>         for (i = 0; i < idlen && !need_quote; ++i)
>>             switch(id[i]) {
>>             case '\"':
>>             case '\'':
>>             case ' ':
>>             case '(':
>>             case ')':
>>             case '[':
>>             case ']':
>>             case '{':
>>             case '}':
>>                 need_quote = 1;
>>             }
>>
>>         if (need_quote)
>>             return tds_quote(
>>                 tds,
>>                 buffer,
>>                 TDS_IS_MSSQL(tds) ? ']' : '\"',
>>                 id,
>>                 idlen);
>>         }
>>
>>     if (buffer) {
>>         memcpy(buffer, id, idlen);
>>         buffer[idlen] = '\0';
>>     }
>>     return idlen;
>> }
>>
>
> Current code
>
> /**
> * Quote an id
> * \param tds    state information for the socket and the TDS protocol
> * \param buffer buffer to store quoted id. If NULL do not write
> anything
> *        (useful to compute quote length)
> * \param id     id to quote
> * \param idlen  id length
> * \result written chars (not including needed terminator)
> */
> int
> tds_quote_id(TDSSOCKET * tds, char *buffer, const char *id, int idlen)
> {
>        int i;
>
>        CHECK_TDS_EXTRA(tds);
>
>        if (idlen < 0)
>                idlen = strlen(id);
>
>        /* need quote ?? */
>        for (i = 0; i < idlen; ++i) {
>                char c = id[i];
>
>                if (c >= 'a' && c <= 'z')
>                        continue;
>                if (c >= 'A' && c <= 'Z')
>                        continue;
>                if (i > 0 && c >= '0' && c <= '9')
>                        continue;
>                if (c == '_')
>                        continue;
>                return tds_quote(tds, buffer, TDS_IS_MSSQL(tds) ? ']' :
> '\"', id, idlen);
>        }
>
>        if (buffer) {
>                memcpy(buffer, id, idlen);
>                buffer[idlen] = '\0';
>        }
>        return idlen;
> }
>
> bye
>  freddy77
>
>
>
>
> ------------------------------
>
> _______________________________________________
> FreeTDS mailing list
> FreeTDS at lists.ibiblio.org
> http://lists.ibiblio.org/mailman/listinfo/freetds
>
>
> End of FreeTDS Digest, Vol 38, Issue 10
> ***************************************
>
> 




More information about the FreeTDS mailing list