[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