ODBC"s SQLColumns with column name specified

Hello everybody!

I have a problem with ODBC call to SQLColumns().
The problem is that result set does not contain any data.

How-To-Repeat:
Create table 'test_table' with column 'test_column' in the database
'test', and user 'test' with password 'test'.
Setup DSN 'test_mysql'.

Execute following function(first SQLFetch will return SQL_NO_DATA):

int test_bug()
{
SQLHDBC hdbc; // database connection handle
SQLHENV henv; // environment handle
SQLRETURN sqlResult; // result returned by sql function
SQLSMALLINT NumPrecRadix;
SQLINTEGER cbColumnSize, cbDecimalDigits, cbNumPrecRadix;
SQLHSTMT hstmt;
SQLRETURN ret;
SQLUINTEGER ColumnSize;
SQLSMALLINT DecimalDigits;

// Allocate global environment handle; enable conection pooling on
this handle
if ( SQLSetEnvAttr( NULL, SQL_ATTR_CONNECTION_POOLING,
(SQLPOINTER)SQL_CP_ONE_PER_HENV,
SQL_IS_INTEGER) != SQL_SUCCESS ) {
printf("SQLSetEnvAttr/SQL_ATTR_CONNECTION_POOLING
error\n");
return -1;
}

if (SQLAllocHandle(SQL_HANDLE_ENV,SQL_NULL_HANDLE,&henv) !=
SQL_SUCCESS){
printf("Could not allocate handle for sql environment");
return -1;
}

if(SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION,
(SQLPOINTER) SQL_OV_ODBC3, SQL_IS_INTEGER) !=
SQL_SUCCESS){
printf("Could not set sql version");
return -1;
}

// Allocate a new SQLHDBC. If successful then call SQLConnect using
the
// allocated hdbc and supplied connection information.
if ((sqlResult = SQLAllocHandle(SQL_HANDLE_DBC,henv, (SQLHDBC FAR
*)&hdbc)) != SQL_SUCCESS) {
printf("Could not allocate handle for hdbc");
return -1;
}

sqlResult = SQLConnect(hdbc, (SQLTCHAR*)"test_mysql", SQL_NTS,
(SQLTCHAR*)"test", SQL_NTS,
(SQLTCHAR*)"test", SQL_NTS );

// if failed to connect, free the allocated hdbc before return
if (sqlResult != SQL_SUCCESS && sqlResult != SQL_SUCCESS_WITH_INFO) {
SQLCHAR sqlstate[6];
SQLINTEGER NativeError;
SQLCHAR MessageText[1000];
SQLSMALLINT TextLengthPtr;

SQLGetDiagRec(SQL_HANDLE_DBC,hdbc, 1, sqlstate, &NativeError,
MessageText,
1000, &TextLengthPtr );
printf("Connecting to db failed: sqlstate=%s, NativeError=%d,
MessageText=%s .\n",
sqlstate, NativeError, MessageText);

SQLFreeHandle(SQL_HANDLE_DBC,hdbc);
return -1;
}

if (SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt) != SQL_SUCCESS) {
printf("Could not allocate statement handle, hdbc=%lX\n",
hdbc);
return -1;
}
ret=SQLSetStmtAttr(hstmt, SQL_ATTR_METADATA_ID,
(SQLPOINTER*)SQL_TRUE, SQL_IS_UINTEGER);
if(ret!=SQL_SUCCESS && ret!=SQL_SUCCESS_WITH_INFO){
printf(" Could not set statement attribute.\n");
SQLFreeHandle( SQL_HANDLE_STMT, hstmt);
return -1;
}
ret=SQLColumns(hstmt, NULL, 0, (SQLCHAR*)_T("test"), SQL_NTS,
(SQLCHAR*)"test_table", SQL_NTS,
(SQLCHAR*)"test_column", SQL_NTS);

if (ret != SQL_SUCCESS && ret != SQL_SUCCESS_WITH_INFO) {
printf(" Could not execute SQLColumns().\n");
SQLFreeHandle( SQL_HANDLE_STMT, hstmt);
return -1;
}

/* Bind columns in result set to buffers */
SQLBindCol(hstmt, 7, SQL_C_ULONG, &ColumnSize, 0, &cbColumnSize);
SQLBindCol(hstmt, 9, SQL_C_SSHORT, &DecimalDigits, 0,
&cbDecimalDigits);
SQLBindCol(hstmt, 10, SQL_C_SSHORT, &NumPrecRadix, 0,
&cbNumPrecRadix);

ret = SQLFetch(hstmt);

if (ret != SQL_SUCCESS && ret != SQL_SUCCESS_WITH_INFO) {
printf(" No column %s is found in table %s.\n",
"test_column", "test_table");
SQLFreeHandle( SQL_HANDLE_STMT, hstmt);
return -1;
}
if(SQLFetch(hstmt)!=SQL_NO_DATA){
printf(" more than one data row is retrieved for column %s
in table %s.\n",
"test_column", "test_table");
}

printf(" column %s in table %s.\n", "test_column", "test_table");
printf("ColumnSize=%d, DecimalDigits=%d \n", ColumnSize,
DecimalDigits);

SQLFreeHandle( SQL_HANDLE_STMT, hstmt);
return 0;
}



I debugged this problem and it seems to be caused by the line
sql_parse:1060 , function dispatch_command():
if (!(thd->query=fields=thd->memdup(packet,thd->query_length+1) ))
Here thd->query_length is 0 (if no previous queries were done on this
thread), or some number remained from the previous database query
(not SQLColumns). It seems that replacing this line by
if (!(thd->query=fields=thd->memdup(packet, strlen(packet)+1)))
works fine.
I am not sure if strlen would work in all situations (unicode?).
Also why there is this comment?
case COM_FIELD_LIST: // This isn't actually needed
Does it mean that SQLColumns should be directed to use other server
functions, and this case is not supported?

My box: windows2000 SP3, Pentium4 2.0 ghz
MySQL version: 4.0.9-gamma source for windows
MyODBC version: 3.51.05
Compiler: Visual C++ 6.0

Sergey.



------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread13797@lists.mysql.com
To unsubscribe, e-mail

Re: ODBC"s SQLColumns with column name specified

On Mon, 2003-02-17 at 12:22, Sergey Tulyakov wrote:
> Hello everybody!

Hi !!

>
> I have a problem with ODBC call to SQLColumns().
> The problem is that result set does not contain any data.
>
> How-To-Repeat:
> Create table 'test_table' with column 'test_column' in the database
> 'test', and user 'test' with password 'test'.
> Setup DSN 'test_mysql'.

ok..

>
> Execute following function(first SQLFetch will return SQL_NO_DATA):

The reason is, you are passing wrong input values to SQLColumns.

> ret=SQLColumns(hstmt, NULL, 0, (SQLCHAR*)_T("test"), SQL_NTS,
> (SQLCHAR*)"test_table", SQL_NTS,
> (SQLCHAR*)"test_column", SQL_NTS);

Why are you supplying _T("test") as the schema name ? If you want to
pass catalog name as "test", then pass that with the argument 2 instead
of argument 3 i.e. it should be:

SQLColumns(hstmt,
"test", SQL_NTS, /* catalog name */
SQL_NULL_HANDLE, 0, /* schema name */
"test_table", SQL_NTS, /* table name */
"test_column", SQL_NTS); /* column name */

Thanks
--
Regards, Venu
For technical support contracts, go to https://order.mysql.com
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Venu
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Developer
/_/ /_/\_, /___/\___\_\___/ Palo Alto, CA-94306, USA
<___/ www.mysql.com

Join MySQL Users Conference and Expo:
http://www.mysql.com/events/uc2003/

------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread13804@lists.mysql.com
To unsubscribe, e-mail

Re: ODBC"s SQLColumns with column name specified

Hi,
I tried and it did not work either.
Why I specify schema name "test' is to make it work with oracle.
My understanding is that if database does not support schema concept,
then SQLColumns simply ignores this parameter.
(BTW, I could not run this statement as you wrote, since I am trying
to set SQL_ATTR_METADATA_ID statement attribute to SQL_TRUE,
and Microsoft ODBC driver does not allow schema name parameter to be
NULL in this case. - These are the types of problems you run into
when you want to make your program compatible with different databases.)
Anyway, I am pretty sure that the parameter thd->query_length
is incorrect (it is set to 0 during construction of thd object and is
not changed during SQLColumns call):
if (!(thd->query=fields=thd->memdup(packet,thd->query_length+1) ))
When I run another SQL command before SQLColumns, say
SQLExecDirect(hstmt, (SQLCHAR *)"CREATE TABLE TEST_TABLE( .....)");
this parameter is set to the length of this statement.
So subsequent call to SQLColumns would succeed, since (usually) big
enough buffer will be allocated( this workaround works, but this is just
workaround).
Thank you for replying,
Sergey.

On 18 Feb 2003, Venu wrote:

> On Mon, 2003-02-17 at 12:22, Sergey Tulyakov wrote:
> > Hello everybody!
>
> Hi !!
>
> >
> > I have a problem with ODBC call to SQLColumns().
> > The problem is that result set does not contain any data.
> >
> > How-To-Repeat:
> > Create table 'test_table' with column 'test_column' in the database
> > 'test', and user 'test' with password 'test'.
> > Setup DSN 'test_mysql'.
>
> ok..
>
> >
> > Execute following function(first SQLFetch will return SQL_NO_DATA):
>
> The reason is, you are passing wrong input values to SQLColumns.
>
> > ret=SQLColumns(hstmt, NULL, 0, (SQLCHAR*)_T("test"), SQL_NTS,
> > (SQLCHAR*)"test_table", SQL_NTS,
> > (SQLCHAR*)"test_column", SQL_NTS);
>
> Why are you supplying _T("test") as the schema name ? If you want to
> pass catalog name as "test", then pass that with the argument 2 instead
> of argument 3 i.e. it should be:
>
> SQLColumns(hstmt,
> "test", SQL_NTS, /* catalog name */
> SQL_NULL_HANDLE, 0, /* schema name */
> "test_table", SQL_NTS, /* table name */
> "test_column", SQL_NTS); /* column name */
>
> Thanks
> --
> Regards, Venu
> For technical support contracts, go to https://order.mysql.com
> __ ___ ___ ____ __
> / |/ /_ __/ __/ __ \/ / Mr. Venu
> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Developer
> /_/ /_/\_, /___/\___\_\___/ Palo Alto, CA-94306, USA
> <___/ www.mysql.com
>
> Join MySQL Users Conference and Expo:
> http://www.mysql.com/events/uc2003/
>



------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread13807@lists.mysql.com
To unsubscribe, e-mail

Re: ODBC"s SQLColumns with column name specified

On Tue, 2003-02-18 at 20:51, Sergey Tulyakov wrote:
> Hi,
> I tried and it did not work either.
> Why I specify schema name "test' is to make it work with oracle.
> My understanding is that if database does not support schema concept,
> then SQLColumns simply ignores this parameter.
> (BTW, I could not run this statement as you wrote, since I am trying
> to set SQL_ATTR_METADATA_ID statement attribute to SQL_TRUE,
> and Microsoft ODBC driver does not allow schema name parameter to be
> NULL in this case. - These are the types of problems you run into
> when you want to make your program compatible with different databases.)
> Anyway, I am pretty sure that the parameter thd->query_length
> is incorrect (it is set to 0 during construction of thd object and is
> not changed during SQLColumns call):
> if (!(thd->query=fields=thd->memdup(packet,thd->query_length+1) ))
> When I run another SQL command before SQLColumns, say
> SQLExecDirect(hstmt, (SQLCHAR *)"CREATE TABLE TEST_TABLE( .....)");
> this parameter is set to the length of this statement.
> So subsequent call to SQLColumns would succeed, since (usually) big
> enough buffer will be allocated( this workaround works, but this is just
> workaround).
> Thank you for replying,

Check the following link, where in I posted a simple portable example of
SQLColumns which works fine with MySQL and MSSQL.

http://lists.mysql.com/cgi-ez/ezmlm-cgi?5:msp:6869:mpanfblmj efjdmiibfmg

> Sergey.
>
> On 18 Feb 2003, Venu wrote:
>
> > On Mon, 2003-02-17 at 12:22, Sergey Tulyakov wrote:
> > > Hello everybody!
> >
> > Hi !!
> >
> > >
> > > I have a problem with ODBC call to SQLColumns().
> > > The problem is that result set does not contain any data.
> > >
> > > How-To-Repeat:
> > > Create table 'test_table' with column 'test_column' in the database
> > > 'test', and user 'test' with password 'test'.
> > > Setup DSN 'test_mysql'.
> >
> > ok..
> >
> > >
> > > Execute following function(first SQLFetch will return SQL_NO_DATA):
> >
> > The reason is, you are passing wrong input values to SQLColumns.
> >
> > > ret=SQLColumns(hstmt, NULL, 0, (SQLCHAR*)_T("test"), SQL_NTS,
> > > (SQLCHAR*)"test_table", SQL_NTS,
> > > (SQLCHAR*)"test_column", SQL_NTS);
> >
> > Why are you supplying _T("test") as the schema name ? If you want to
> > pass catalog name as "test", then pass that with the argument 2 instead
> > of argument 3 i.e. it should be:
> >
> > SQLColumns(hstmt,
> > "test", SQL_NTS, /* catalog name */
> > SQL_NULL_HANDLE, 0, /* schema name */
> > "test_table", SQL_NTS, /* table name */
> > "test_column", SQL_NTS); /* column name */
> >
> > Thanks
> > --
> > Regards, Venu
> > For technical support contracts, go to https://order.mysql.com
> > __ ___ ___ ____ __
> > / |/ /_ __/ __/ __ \/ / Mr. Venu
> > / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Developer
> > /_/ /_/\_, /___/\___\_\___/ Palo Alto, CA-94306, USA
> > <___/ www.mysql.com
> >
> > Join MySQL Users Conference and Expo:
> > http://www.mysql.com/events/uc2003/
> >
>
>
>
> ------------------------------------------------------------ ---------
> Before posting, please check:
> http://www.mysql.com/manual.php (the manual)
> http://lists.mysql.com/ (the list archive)
>
> To request this thread, e-mail bugs-thread13807@lists.mysql.com
> To unsubscribe, e-mail
--
Regards, Venu
For technical support contracts, go to https://order.mysql.com
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Venu
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Developer
/_/ /_/\_, /___/\___\_\___/ Palo Alto, CA-94306, USA
<___/ www.mysql.com

Join MySQL Users Conference and Expo:
http://www.mysql.com/events/uc2003/

------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread13808@lists.mysql.com
To unsubscribe, e-mail

Re: ODBC"s SQLColumns with column name specified

Venu,
please do the following experiment.
Run this function 2 times. On the first run comment out
final drop of 'test_column' table:
// rc = SQLExecDirect(hstmt,"DROP TABLE test_column",SQL_NTS);
Everything is fine here, table stays in the database.
On the second run comment out creation table statement:
/* SQLExecDirect(hstmt,(SQLCHAR*)"DROP TABLE test_column",SQL_NTS);

rc = SQLExecDirect(hstmt,(SQLCHAR*)"CREATE TABLE test_column(col0
smallint,\
col1 char(5),\
col2 varchar(20)
not null,\
col3
decimal(10,2),\
col4 tinyint not
null,\
col5 integer
primary key)",SQL_NTS);
*/
This is what we get:

Column col0:
DataType = 52428(-858993460)
ColumnSize = -858993460(-858993460)
DecimalDigits= 52428(-858993460)
NumPrecRadix = 52428(-858993460)
Nullable = YES(-858993460)


To see why it happens run mysqld.exe inside debugger (I used Visual C++).
Put breakpoint on line sql\sql_parse.cpp:2788
thd->query_length = length;
and sql\sql_parse.cpp:1060
if (!(thd->query=fields=thd->memdup(packet,thd->query_length+1) ))
( I have mysql-4.0.9-gamma-win-src.zip).
When you call
rc = SQLExecDirect(hstmt,(SQLCHAR*)"CREATE TABLE test_column(col0 .....")
on the first breakpoint you would get: length=419 (the length of
'create table' statement), so thd->query_length=419 .
You could put a watch on the location also (in my case *(int *)0x04233dec=419).
Now when you call SQLColumns, on the second breakpoint you
would get thd->query_length=419 . If you set a watch on this location
you would see that the value did not change.
You could see that you would allocate a buffer of length 420 for every
SQLColumns call you make, and clolumn names 'col0', ..,'col5' are copied
inside this huge buffer.
If you do not call SQLExecDirect(hstmt,(SQLCHAR*)"CREATE TABLE...)
at the beginning, then thd->query_length=0. So empty buffer is allocated,
column name is not copied anywhere, and column is not found.
Good luck, I appreciate your effort.
Sergey.

On 19 Feb 2003, Venu wrote:

> On Tue, 2003-02-18 at 20:51, Sergey Tulyakov wrote:
> > Hi,
> > I tried and it did not work either.
> > Why I specify schema name "test' is to make it work with oracle.
> > My understanding is that if database does not support schema concept,
> > then SQLColumns simply ignores this parameter.
> > (BTW, I could not run this statement as you wrote, since I am trying
> > to set SQL_ATTR_METADATA_ID statement attribute to SQL_TRUE,
> > and Microsoft ODBC driver does not allow schema name parameter to be
> > NULL in this case. - These are the types of problems you run into
> > when you want to make your program compatible with different databases.)
> > Anyway, I am pretty sure that the parameter thd->query_length
> > is incorrect (it is set to 0 during construction of thd object and is
> > not changed during SQLColumns call):
> > if (!(thd->query=fields=thd->memdup(packet,thd->query_length+1) ))
> > When I run another SQL command before SQLColumns, say
> > SQLExecDirect(hstmt, (SQLCHAR *)"CREATE TABLE TEST_TABLE( .....)");
> > this parameter is set to the length of this statement.
> > So subsequent call to SQLColumns would succeed, since (usually) big
> > enough buffer will be allocated( this workaround works, but this is just
> > workaround).
> > Thank you for replying,
>
> Check the following link, where in I posted a simple portable example of
> SQLColumns which works fine with MySQL and MSSQL.
>
> http://lists.mysql.com/cgi-ez/ezmlm-cgi?5:msp:6869:mpanfblmj efjdmiibfmg
>
> > Sergey.
> >
> > On 18 Feb 2003, Venu wrote:
> >
> > > On Mon, 2003-02-17 at 12:22, Sergey Tulyakov wrote:
> > > > Hello everybody!
> > >
> > > Hi !!
> > >
> > > >
> > > > I have a problem with ODBC call to SQLColumns().
> > > > The problem is that result set does not contain any data.
> > > >
> > > > How-To-Repeat:
> > > > Create table 'test_table' with column 'test_column' in the database
> > > > 'test', and user 'test' with password 'test'.
> > > > Setup DSN 'test_mysql'.
> > >
> > > ok..
> > >
> > > >
> > > > Execute following function(first SQLFetch will return SQL_NO_DATA):
> > >
> > > The reason is, you are passing wrong input values to SQLColumns.
> > >
> > > > ret=SQLColumns(hstmt, NULL, 0, (SQLCHAR*)_T("test"), SQL_NTS,
> > > > (SQLCHAR*)"test_table", SQL_NTS,
> > > > (SQLCHAR*)"test_column", SQL_NTS);
> > >
> > > Why are you supplying _T("test") as the schema name ? If you want to
> > > pass catalog name as "test", then pass that with the argument 2 instead
> > > of argument 3 i.e. it should be:
> > >
> > > SQLColumns(hstmt,
> > > "test", SQL_NTS, /* catalog name */
> > > SQL_NULL_HANDLE, 0, /* schema name */
> > > "test_table", SQL_NTS, /* table name */
> > > "test_column", SQL_NTS); /* column name */
> > >
> > > Thanks
> > > --
> > > Regards, Venu
> > > For technical support contracts, go to https://order.mysql.com
> > > __ ___ ___ ____ __
> > > / |/ /_ __/ __/ __ \/ / Mr. Venu
> > > / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Developer
> > > /_/ /_/\_, /___/\___\_\___/ Palo Alto, CA-94306, USA
> > > <___/ www.mysql.com
> > >
> > > Join MySQL Users Conference and Expo:
> > > http://www.mysql.com/events/uc2003/
> > >
> >
> >
> >
> > ------------------------------------------------------------ ---------
> > Before posting, please check:
> > http://www.mysql.com/manual.php (the manual)
> > http://lists.mysql.com/ (the list archive)
> >
> > To request this thread, e-mail bugs-thread13807@lists.mysql.com
> > To unsubscribe, e-mail
> --
> Regards, Venu
> For technical support contracts, go to https://order.mysql.com
> __ ___ ___ ____ __
> / |/ /_ __/ __/ __ \/ / Mr. Venu
> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Developer
> /_/ /_/\_, /___/\___\_\___/ Palo Alto, CA-94306, USA
> <___/ www.mysql.com
>
> Join MySQL Users Conference and Expo:
> http://www.mysql.com/events/uc2003/
>


------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread13809@lists.mysql.com
To unsubscribe, e-mail

Re: ODBC"s SQLColumns with column name specified

On Wed, 2003-02-19 at 11:56, Sergey Tulyakov wrote:
> Venu,

Hi !!

> please do the following experiment.
> Run this function 2 times. On the first run comment out
> final drop of 'test_column' table:
> // rc = SQLExecDirect(hstmt,"DROP TABLE test_column",SQL_NTS);
> Everything is fine here, table stays in the database.
> On the second run comment out creation table statement:
> /* SQLExecDirect(hstmt,(SQLCHAR*)"DROP TABLE test_column",SQL_NTS);
>
> rc = SQLExecDirect(hstmt,(SQLCHAR*)"CREATE TABLE test_column(col0
> smallint,\
> col1 char(5),\
> col2 varchar(20)
> not null,\
> col3
> decimal(10,2),\
> col4 tinyint not
> null,\
> col5 integer
> primary key)",SQL_NTS);
> */

This is same as what I posted.

> This is what we get:
>
> Column col0:
> DataType = 52428(-858993460)
> ColumnSize = -858993460(-858993460)
> DecimalDigits= 52428(-858993460)
> NumPrecRadix = 52428(-858993460)
> Nullable = YES(-858993460)

The reason for this could be, you are passing/binding wrong arguments in
SQLBindCol to fetch the data and its length or the chances are that the
buffers are corrupted in the application level. Could you please send me
a complete test snippet to reproduce this ?

Thanks
>
>
> To see why it happens run mysqld.exe inside debugger (I used Visual C++).
> Put breakpoint on line sql\sql_parse.cpp:2788
> thd->query_length = length;
> and sql\sql_parse.cpp:1060
> if (!(thd->query=fields=thd->memdup(packet,thd->query_length+1) ))
> ( I have mysql-4.0.9-gamma-win-src.zip).
> When you call
> rc = SQLExecDirect(hstmt,(SQLCHAR*)"CREATE TABLE test_column(col0 .....")
> on the first breakpoint you would get: length=419 (the length of
> 'create table' statement), so thd->query_length=419 .
> You could put a watch on the location also (in my case *(int *)0x04233dec=419).
> Now when you call SQLColumns, on the second breakpoint you
> would get thd->query_length=419 . If you set a watch on this location
> you would see that the value did not change.
> You could see that you would allocate a buffer of length 420 for every
> SQLColumns call you make, and clolumn names 'col0', ..,'col5' are copied
> inside this huge buffer.
> If you do not call SQLExecDirect(hstmt,(SQLCHAR*)"CREATE TABLE...)
> at the beginning, then thd->query_length=0. So empty buffer is allocated,
> column name is not copied anywhere, and column is not found.
> Good luck, I appreciate your effort.
> Sergey.
>
> On 19 Feb 2003, Venu wrote:
>
> > On Tue, 2003-02-18 at 20:51, Sergey Tulyakov wrote:
> > > Hi,
> > > I tried and it did not work either.
> > > Why I specify schema name "test' is to make it work with oracle.
> > > My understanding is that if database does not support schema concept,
> > > then SQLColumns simply ignores this parameter.
> > > (BTW, I could not run this statement as you wrote, since I am trying
> > > to set SQL_ATTR_METADATA_ID statement attribute to SQL_TRUE,
> > > and Microsoft ODBC driver does not allow schema name parameter to be
> > > NULL in this case. - These are the types of problems you run into
> > > when you want to make your program compatible with different databases.)
> > > Anyway, I am pretty sure that the parameter thd->query_length
> > > is incorrect (it is set to 0 during construction of thd object and is
> > > not changed during SQLColumns call):
> > > if (!(thd->query=fields=thd->memdup(packet,thd->query_length+1) ))
> > > When I run another SQL command before SQLColumns, say
> > > SQLExecDirect(hstmt, (SQLCHAR *)"CREATE TABLE TEST_TABLE( .....)");
> > > this parameter is set to the length of this statement.
> > > So subsequent call to SQLColumns would succeed, since (usually) big
> > > enough buffer will be allocated( this workaround works, but this is just
> > > workaround).
> > > Thank you for replying,
> >
> > Check the following link, where in I posted a simple portable example of
> > SQLColumns which works fine with MySQL and MSSQL.
> >
> > http://lists.mysql.com/cgi-ez/ezmlm-cgi?5:msp:6869:mpanfblmj efjdmiibfmg
> >
> > > Sergey.
> > >
> > > On 18 Feb 2003, Venu wrote:
> > >
> > > > On Mon, 2003-02-17 at 12:22, Sergey Tulyakov wrote:
> > > > > Hello everybody!
> > > >
> > > > Hi !!
> > > >
> > > > >
> > > > > I have a problem with ODBC call to SQLColumns().
> > > > > The problem is that result set does not contain any data.
> > > > >
> > > > > How-To-Repeat:
> > > > > Create table 'test_table' with column 'test_column' in the database
> > > > > 'test', and user 'test' with password 'test'.
> > > > > Setup DSN 'test_mysql'.
> > > >
> > > > ok..
> > > >
> > > > >
> > > > > Execute following function(first SQLFetch will return SQL_NO_DATA):
> > > >
> > > > The reason is, you are passing wrong input values to SQLColumns.
> > > >
> > > > > ret=SQLColumns(hstmt, NULL, 0, (SQLCHAR*)_T("test"), SQL_NTS,
> > > > > (SQLCHAR*)"test_table", SQL_NTS,
> > > > > (SQLCHAR*)"test_column", SQL_NTS);
> > > >
> > > > Why are you supplying _T("test") as the schema name ? If you want to
> > > > pass catalog name as "test", then pass that with the argument 2 instead
> > > > of argument 3 i.e. it should be:
> > > >
> > > > SQLColumns(hstmt,
> > > > "test", SQL_NTS, /* catalog name */
> > > > SQL_NULL_HANDLE, 0, /* schema name */
> > > > "test_table", SQL_NTS, /* table name */
> > > > "test_column", SQL_NTS); /* column name */
> > > >
> > > > Thanks
> > > > --
> > > > Regards, Venu
> > > > For technical support contracts, go to https://order.mysql.com
> > > > __ ___ ___ ____ __
> > > > / |/ /_ __/ __/ __ \/ / Mr. Venu
> > > > / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Developer
> > > > /_/ /_/\_, /___/\___\_\___/ Palo Alto, CA-94306, USA
> > > > <___/ www.mysql.com
> > > >
> > > > Join MySQL Users Conference and Expo:
> > > > http://www.mysql.com/events/uc2003/
> > > >
> > >
> > >
> > >
> > > ------------------------------------------------------------ ---------
> > > Before posting, please check:
> > > http://www.mysql.com/manual.php (the manual)
> > > http://lists.mysql.com/ (the list archive)
> > >
> > > To request this thread, e-mail bugs-thread13807@lists.mysql.com
> > > To unsubscribe, e-mail
> > --
> > Regards, Venu
> > For technical support contracts, go to https://order.mysql.com
> > __ ___ ___ ____ __
> > / |/ /_ __/ __/ __ \/ / Mr. Venu
> > / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Developer
> > /_/ /_/\_, /___/\___\_\___/ Palo Alto, CA-94306, USA
> > <___/ www.mysql.com
> >
> > Join MySQL Users Conference and Expo:
> > http://www.mysql.com/events/uc2003/
> >
>
>
> ------------------------------------------------------------ ---------
> Before posting, please check:
> http://www.mysql.com/manual.php (the manual)
> http://lists.mysql.com/ (the list archive)
>
> To request this thread, e-mail bugs-thread13809@lists.mysql.com
> To unsubscribe, e-mail
--
Regards, Venu
For technical support contracts, go to https://order.mysql.com
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Venu
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Developer
/_/ /_/\_, /___/\___\_\___/ Palo Alto, CA-94306, USA
<___/ www.mysql.com

Join MySQL Users Conference and Expo:
http://www.mysql.com/events/uc2003/

------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread13813@lists.mysql.com
To unsubscribe, e-mail

Re: ODBC"s SQLColumns with column name specified

Well,
I did not really expect to spend so much time on this bug report.
I thought I wrote everything clear first time, exactly specifying
the cause of error. What could have happenned if I did not debug it,
and sent simple bug report?
Anyway, here is the full code for reproducing this error.
Notice that I had to disable connection pooling, so that new connection
got its own thread in mysqld server.
Sergey.


#include
#include

#include
#include

#include
#include
#include
using namespace std;

static void t_columns(SQLHDBC hdbc, SQLHSTMT hstmt, int run);

int main()
{
SQLHDBC hdbc; // database connection handle
SQLHENV henv; // environment handle
SQLRETURN sqlResult; // result returned by sql function
SQLHSTMT hstmt;

// Allocate global environment handle; enable conection pooling on
this handle
// if ( SQLSetEnvAttr( NULL, SQL_ATTR_CONNECTION_POOLING,
(SQLPOINTER)SQL_CP_ONE_PER_HENV,
// SQL_IS_INTEGER) != SQL_SUCCESS ) {
// printf("SQLSetEnvAttr/SQL_ATTR_CONNECTION_POOLING
error\n");
// return -1;
// }

if (SQLAllocHandle(SQL_HANDLE_ENV,SQL_NULL_HANDLE,&henv) !=
SQL_SUCCESS){
printf("Could not allocate handle for sql environment");
return -1;
}

if(SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION,
(SQLPOINTER) SQL_OV_ODBC3, SQL_IS_INTEGER) !=
SQL_SUCCESS){
printf("Could not set sql version");
return -1;
}

// Allocate a new SQLHDBC. If successful then call SQLConnect using
the
// allocated hdbc and supplied connection information.
if ((sqlResult = SQLAllocHandle(SQL_HANDLE_DBC,henv, (SQLHDBC FAR
*)&hdbc)) != SQL_SUCCESS) {
printf("Could not allocate handle for hdbc");
return -1;
}

sqlResult = SQLConnect(hdbc, (SQLTCHAR*)"test_mysql", SQL_NTS,
(SQLTCHAR*)"test", SQL_NTS,
(SQLTCHAR*)"test", SQL_NTS );

// if failed to connect, free the allocated hdbc before return
if (sqlResult != SQL_SUCCESS && sqlResult != SQL_SUCCESS_WITH_INFO) {
SQLFreeHandle(SQL_HANDLE_DBC,hdbc);
return -1;
}

if (SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt) != SQL_SUCCESS) {
printf("Could not allocate statement handle, hdbc=%lX\n",
hdbc);
return -1;
}

t_columns(hdbc, hstmt, 0);

SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
SQLDisconnect( hdbc);
SQLFreeHandle(SQL_HANDLE_DBC, hdbc);



// Allocate a new SQLHDBC. If successful then call SQLConnect using
the
// allocated hdbc and supplied connection information.
if ((sqlResult = SQLAllocHandle(SQL_HANDLE_DBC,henv, (SQLHDBC FAR
*)&hdbc)) != SQL_SUCCESS) {
printf("Could not allocate handle for hdbc");
return -1;
}

sqlResult = SQLConnect(hdbc, (SQLTCHAR*)"test_mysql", SQL_NTS,
(SQLTCHAR*)"test", SQL_NTS,
(SQLTCHAR*)"test", SQL_NTS );

if (SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt) != SQL_SUCCESS) {
printf("Could not allocate statement handle, hdbc=%lX\n",
hdbc);
return -1;
}

t_columns(hdbc, hstmt, 1);

}

/* To test SQLColumns misc case */
static void t_columns(SQLHDBC hdbc, SQLHSTMT hstmt, int run)
{
SQLUSMALLINT NumPrecRadix, DataType, Nullable;
SQLINTEGER cbColumnSize, cbDecimalDigits, cbNumPrecRadix,
cbDatabaseName, cbDataType, cbNullable;
SQLRETURN rc;
SQLUINTEGER ColumnSize, i;
SQLUINTEGER ColumnCount= 6;
SQLUSMALLINT DecimalDigits;
SQLCHAR ColumnName[200], DatabaseName[200];
SQLUINTEGER Values[6][5][2]=
{
{ {5,2}, {6,4}, {0,2}, {10,2}, {1,2}},
{ {12,2}, {5,4}, {0,-1}, {10,-1}, {1,2}},
{ {12,2}, {20,4}, {0,-1}, {10,-1}, {0,2}},
{ {3,2}, {10,4}, {2,2}, {10,2}, {1,2}},
{ {65530,2}, {4,4}, {0,2}, {10,2}, {0,2}},
{ {4,2}, {11,4}, {0,2}, {10,2}, {0,2}}
};

// myheader("t_columns");

SQLFreeStmt(hstmt, SQL_CLOSE);
if(run==0){
SQLExecDirect(hstmt,(SQLCHAR*)"DROP TABLE test_column",SQL_NTS);

rc = SQLExecDirect(hstmt,(SQLCHAR*)"CREATE TABLE test_column(col0
smallint,\
col1 char(5),\
col2 varchar(20)
not null,\
col3
decimal(10,2),\
col4 tinyint not
null,\
col5 integer
primary key)",SQL_NTS);
}
// mystmt(hstmt,rc);

// mystmt(hstmt,rc);

rc= SQLSetStmtAttr(hstmt, SQL_ATTR_METADATA_ID,
(SQLPOINTER)SQL_FALSE, SQL_IS_UINTEGER);
// mystmt(hstmt,rc);

rc= SQLGetConnectAttr(hdbc,
SQL_ATTR_CURRENT_CATALOG,(SQLCHAR*)DatabaseName,
200, &cbDatabaseName);/* Current Catalog */
// mycon(hdbc,rc);

for (i=0; i< ColumnCount; i++)
{
sprintf((char*)ColumnName,"col%d",i);

rc= SQLColumns(hstmt,
(SQLCHAR *)DatabaseName,
(SQLUSMALLINT)cbDatabaseName,
SQL_NULL_HANDLE, 0,
(SQLCHAR *)"test_column", SQL_NTS,
(SQLCHAR *)ColumnName, SQL_NTS);
// mystmt(hstmt,rc);

/* 5 -- Data type */
rc= SQLBindCol(hstmt, 5, SQL_C_SSHORT, &DataType, 0, &cbDataType);
// mystmt(hstmt,rc);

/* 7 -- Column Size */
rc= SQLBindCol(hstmt, 7, SQL_C_ULONG, &ColumnSize, 0,
&cbColumnSize);
// mystmt(hstmt,rc);

/* 9 -- Decimal Digits */
rc= SQLBindCol(hstmt, 9, SQL_C_SSHORT, &DecimalDigits, 0,
&cbDecimalDigits);
// mystmt(hstmt,rc);

/* 10 -- Num Prec Radix */
rc= SQLBindCol(hstmt, 10, SQL_C_SSHORT, &NumPrecRadix, 0,
&cbNumPrecRadix);
// mystmt(hstmt,rc);

/* 11 -- Nullable */
rc= SQLBindCol(hstmt, 11, SQL_C_SSHORT, &Nullable, 0, &cbNullable);
// mystmt(hstmt,rc);

rc= SQLFetch(hstmt);
// mystmt(hstmt,rc);

fprintf(stdout,"\n Column %s:", ColumnName);
fprintf(stdout,"\n\t DataType = %d(%d)", DataType, cbDataType);
fprintf(stdout,"\n\t ColumnSize = %d(%d)", ColumnSize,
cbColumnSize);
fprintf(stdout,"\n\t DecimalDigits= %d(%d)", DecimalDigits,
cbDecimalDigits);
fprintf(stdout,"\n\t NumPrecRadix = %d(%d)", NumPrecRadix,
cbNumPrecRadix);
fprintf(stdout,"\n\t Nullable = %s(%d)\n",
Nullable == SQL_NO_NULLS ? "NO": "YES", cbNullable);
/*
myassert(DataType == Values[i][0][0]);
myassert(cbDataType == Values[i][0][1]);

myassert(ColumnSize == Values[i][1][0]);
myassert(cbColumnSize == Values[i][1][1]);

myassert(DecimalDigits == Values[i][2][0]);
myassert(cbDecimalDigits == Values[i][2][1]);

myassert(NumPrecRadix == Values[i][3][0]);
myassert(cbNumPrecRadix == Values[i][3][1]);

myassert(Nullable == Values[i][4][0]);
myassert(cbNullable == Values[i][4][1]);
*/
rc= SQLFetch(hstmt);
// myassert(rc == SQL_NO_DATA);

SQLFreeStmt(hstmt,SQL_UNBIND);
SQLFreeStmt(hstmt,SQL_CLOSE);
}

SQLFreeStmt(hstmt,SQL_UNBIND);
SQLFreeStmt(hstmt,SQL_CLOSE);

if(run==1){
rc = SQLExecDirect(hstmt,(SQLCHAR *)"DROP TABLE test_column",SQL_NTS);
}
//mystmt(hstmt,rc);
SQLFreeStmt(hstmt,SQL_CLOSE);
}




On 20 Feb 2003, Venu wrote:

> On Wed, 2003-02-19 at 11:56, Sergey Tulyakov wrote:
> > Venu,
>
> Hi !!
>
> > please do the following experiment.
> > Run this function 2 times. On the first run comment out
> > final drop of 'test_column' table:
> > // rc = SQLExecDirect(hstmt,"DROP TABLE test_column",SQL_NTS);
> > Everything is fine here, table stays in the database.
> > On the second run comment out creation table statement:
> > /* SQLExecDirect(hstmt,(SQLCHAR*)"DROP TABLE test_column",SQL_NTS);
> >
> > rc = SQLExecDirect(hstmt,(SQLCHAR*)"CREATE TABLE test_column(col0
> > smallint,\
> > col1 char(5),\
> > col2 varchar(20)
> > not null,\
> > col3
> > decimal(10,2),\
> > col4 tinyint not
> > null,\
> > col5 integer
> > primary key)",SQL_NTS);
> > */
>
> This is same as what I posted.
>
> > This is what we get:
> >
> > Column col0:
> > DataType = 52428(-858993460)
> > ColumnSize = -858993460(-858993460)
> > DecimalDigits= 52428(-858993460)
> > NumPrecRadix = 52428(-858993460)
> > Nullable = YES(-858993460)
>
> The reason for this could be, you are passing/binding wrong arguments in
> SQLBindCol to fetch the data and its length or the chances are that the
> buffers are corrupted in the application level. Could you please send me
> a complete test snippet to reproduce this ?
>
> Thanks
> >
> >
> > To see why it happens run mysqld.exe inside debugger (I used Visual C++).
> > Put breakpoint on line sql\sql_parse.cpp:2788
> > thd->query_length = length;
> > and sql\sql_parse.cpp:1060
> > if (!(thd->query=fields=thd->memdup(packet,thd->query_length+1) ))
> > ( I have mysql-4.0.9-gamma-win-src.zip).
> > When you call
> > rc = SQLExecDirect(hstmt,(SQLCHAR*)"CREATE TABLE test_column(col0 .....")
> > on the first breakpoint you would get: length=419 (the length of
> > 'create table' statement), so thd->query_length=419 .
> > You could put a watch on the location also (in my case *(int *)0x04233dec=419).
> > Now when you call SQLColumns, on the second breakpoint you
> > would get thd->query_length=419 . If you set a watch on this location
> > you would see that the value did not change.
> > You could see that you would allocate a buffer of length 420 for every
> > SQLColumns call you make, and clolumn names 'col0', ..,'col5' are copied
> > inside this huge buffer.
> > If you do not call SQLExecDirect(hstmt,(SQLCHAR*)"CREATE TABLE...)
> > at the beginning, then thd->query_length=0. So empty buffer is allocated,
> > column name is not copied anywhere, and column is not found.
> > Good luck, I appreciate your effort.
> > Sergey.
> >
> > On 19 Feb 2003, Venu wrote:
> >
> > > On Tue, 2003-02-18 at 20:51, Sergey Tulyakov wrote:
> > > > Hi,
> > > > I tried and it did not work either.
> > > > Why I specify schema name "test' is to make it work with oracle.
> > > > My understanding is that if database does not support schema concept,
> > > > then SQLColumns simply ignores this parameter.
> > > > (BTW, I could not run this statement as you wrote, since I am trying
> > > > to set SQL_ATTR_METADATA_ID statement attribute to SQL_TRUE,
> > > > and Microsoft ODBC driver does not allow schema name parameter to be
> > > > NULL in this case. - These are the types of problems you run into
> > > > when you want to make your program compatible with different databases.)
> > > > Anyway, I am pretty sure that the parameter thd->query_length
> > > > is incorrect (it is set to 0 during construction of thd object and is
> > > > not changed during SQLColumns call):
> > > > if (!(thd->query=fields=thd->memdup(packet,thd->query_length+1) ))
> > > > When I run another SQL command before SQLColumns, say
> > > > SQLExecDirect(hstmt, (SQLCHAR *)"CREATE TABLE TEST_TABLE( .....)");
> > > > this parameter is set to the length of this statement.
> > > > So subsequent call to SQLColumns would succeed, since (usually) big
> > > > enough buffer will be allocated( this workaround works, but this is just
> > > > workaround).
> > > > Thank you for replying,
> > >
> > > Check the following link, where in I posted a simple portable example of
> > > SQLColumns which works fine with MySQL and MSSQL.
> > >
> > > http://lists.mysql.com/cgi-ez/ezmlm-cgi?5:msp:6869:mpanfblmj efjdmiibfmg
> > >
> > > > Sergey.
> > > >
> > > > On 18 Feb 2003, Venu wrote:
> > > >
> > > > > On Mon, 2003-02-17 at 12:22, Sergey Tulyakov wrote:
> > > > > > Hello everybody!
> > > > >
> > > > > Hi !!
> > > > >
> > > > > >
> > > > > > I have a problem with ODBC call to SQLColumns().
> > > > > > The problem is that result set does not contain any data.
> > > > > >
> > > > > > How-To-Repeat:
> > > > > > Create table 'test_table' with column 'test_column' in the database
> > > > > > 'test', and user 'test' with password 'test'.
> > > > > > Setup DSN 'test_mysql'.
> > > > >
> > > > > ok..
> > > > >
> > > > > >
> > > > > > Execute following function(first SQLFetch will return SQL_NO_DATA):
> > > > >
> > > > > The reason is, you are passing wrong input values to SQLColumns.
> > > > >
> > > > > > ret=SQLColumns(hstmt, NULL, 0, (SQLCHAR*)_T("test"), SQL_NTS,
> > > > > > (SQLCHAR*)"test_table", SQL_NTS,
> > > > > > (SQLCHAR*)"test_column", SQL_NTS);
> > > > >
> > > > > Why are you supplying _T("test") as the schema name ? If you want to
> > > > > pass catalog name as "test", then pass that with the argument 2 instead
> > > > > of argument 3 i.e. it should be:
> > > > >
> > > > > SQLColumns(hstmt,
> > > > > "test", SQL_NTS, /* catalog name */
> > > > > SQL_NULL_HANDLE, 0, /* schema name */
> > > > > "test_table", SQL_NTS, /* table name */
> > > > > "test_column", SQL_NTS); /* column name */
> > > > >
> > > > > Thanks
> > > > > --
> > > > > Regards, Venu
> > > > > For technical support contracts, go to https://order.mysql.com
> > > > > __ ___ ___ ____ __
> > > > > / |/ /_ __/ __/ __ \/ / Mr. Venu
> > > > > / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Developer
> > > > > /_/ /_/\_, /___/\___\_\___/ Palo Alto, CA-94306, USA
> > > > > <___/ www.mysql.com
> > > > >
> > > > > Join MySQL Users Conference and Expo:
> > > > > http://www.mysql.com/events/uc2003/
> > > > >
> > > >
> > > >
> > > >
> > > > ------------------------------------------------------------ ---------
> > > > Before posting, please check:
> > > > http://www.mysql.com/manual.php (the manual)
> > > > http://lists.mysql.com/ (the list archive)
> > > >
> > > > To request this thread, e-mail bugs-thread13807@lists.mysql.com
> > > > To unsubscribe, e-mail
> > > --
> > > Regards, Venu
> > > For technical support contracts, go to https://order.mysql.com
> > > __ ___ ___ ____ __
> > > / |/ /_ __/ __/ __ \/ / Mr. Venu
> > > / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Developer
> > > /_/ /_/\_, /___/\___\_\___/ Palo Alto, CA-94306, USA
> > > <___/ www.mysql.com
> > >
> > > Join MySQL Users Conference and Expo:
> > > http://www.mysql.com/events/uc2003/
> > >
> >
> >
> > ------------------------------------------------------------ ---------
> > Before posting, please check:
> > http://www.mysql.com/manual.php (the manual)
> > http://lists.mysql.com/ (the list archive)
> >
> > To request this thread, e-mail bugs-thread13809@lists.mysql.com
> > To unsubscribe, e-mail
> --
> Regards, Venu
> For technical support contracts, go to https://order.mysql.com
> __ ___ ___ ____ __
> / |/ /_ __/ __/ __ \/ / Mr. Venu
> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Developer
> /_/ /_/\_, /___/\___\_\___/ Palo Alto, CA-94306, USA
> <___/ www.mysql.com
>
> Join MySQL Users Conference and Expo:
> http://www.mysql.com/events/uc2003/
>


------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread13819@lists.mysql.com
To unsubscribe, e-mail