Skip to Content.
Sympa Menu

freetds - [freetds] SQLExecDirect returns SQL_NO_DATA

freetds AT lists.ibiblio.org

Subject: FreeTDS Development Group

List archive

Chronological Thread  
  • From: "John Wythe (work)" <jwythe AT silksystems.com>
  • To: freetds AT lists.ibiblio.org
  • Subject: [freetds] SQLExecDirect returns SQL_NO_DATA
  • Date: Sat, 11 Mar 2006 09:51:33 -0800

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@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
***************************************








Archive powered by MHonArc 2.6.24.

Top of Page