Problem with OUTPUT parameters when string is passed using SQLPutData. (Is this a BUG)

Problem with OUTPUT parameters when string is passed using SQLPutData. (Is this a BUG)

Post by Gautam Bhaka » Fri, 13 Sep 2002 06:33:37



Hi,

I was trying to retrive some values from stored proceedure on MSSQL, but ran
into problem. Basically if I pass the string using SQLPutData, then the
output are hosed. Can anyone help me in what is going wrong. I have attached
the repro code.

To show the bug compile the code with SHOW_BUG defined. Here are the output
that I see.
F:\test\foo>cl -GX hoo.cpp -link odbc32.lib
Microsoft (R) 32-bit C/C++ Optimizing Compiler Version 13.10.2136 for 80x86
Copyright (C) Microsoft Corporation 1984-2002. All rights reserved.

hoo.cpp
Microsoft (R) Incremental Linker Version 7.10.2136
Copyright (C) Microsoft Corporation.  All rights reserved.

/out:hoo.exe
odbc32.lib
hoo.obj

F:\test\foo>hoo
Before result sets cleared: RetCode = 0, OutParm = 1.
After result sets drained: RetCode = 90, OutParm = 100.

F:\test\foo>cl -GX -DSHOW_BUG hoo.cpp -link odbc32.lib
Microsoft (R) 32-bit C/C++ Optimizing Compiler Version 13.10.2136 for 80x86
Copyright (C) Microsoft Corporation 1984-2002. All rights reserved.

hoo.cpp
Microsoft (R) Incremental Linker Version 7.10.2136
Copyright (C) Microsoft Corporation.  All rights reserved.

/out:hoo.exe
odbc32.lib
hoo.obj

F:\test\foo>hoo
Sending Data through Putdata
Before result sets cleared: RetCode = 0, OutParm = 1.
After result sets drained: RetCode = 90, OutParm = 1.            <--------
the OutParm should be 100 not 1

Gautam

#include <stdio.h>
#include <string.h>
#include <windows.h>
#include <sql.h>
#include <sqlext.h>
#include <odbcss.h>
#include <string>
#include <crtdbg.h>

using namespace std;
#define MAXBUFLEN   255

#if 0

text="This is good"
AS



GO
#endif
SQLHENV      henv = SQL_NULL_HENV;
SQLHDBC      hdbc1 = SQL_NULL_HDBC;
SQLHSTMT      hstmt1 = SQL_NULL_HSTMT;

int main() {
   RETCODE retcode;
   // SQLBindParameter variables.
   SWORD   sParm1=0, sParm2=1, sParm3=100;
   SDWORD   cbParm1=SQL_NTS,
cbParm2=SQL_NTS,cbParm3=SQL_NTS,cbParm4=SQL_LEN_DATA_AT_EXEC(0);
   string
ConnectionString="UID=sa;PWD=;SERVER=localhost;DATABASE=tempdb;DRIVER=SQL
Server;";
#ifdef SHOW_BUG
   string sParm4;
#endif
   char OutConnectionString[1024];
   int Len=0;

    // Allocate the ODBC environment and save handle.
   retcode = SQLAllocHandle (SQL_HANDLE_ENV, NULL, &henv);
   // Notify ODBC that this is an ODBC 3.0 app.
   retcode = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION,
                     (SQLPOINTER) SQL_OV_ODBC3, SQL_IS_INTEGER);
   // Allocate ODBC connection handle and connect.
   retcode = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc1);
   retcode = SQLDriverConnect(hdbc1,
                              NULL,
                              (SQLCHAR *)ConnectionString.c_str(),
                              ConnectionString.size(),
                              (SQLCHAR *)OutConnectionString,
                              sizeof(OutConnectionString),
                              (SQLSMALLINT *)&Len,
                              SQL_DRIVER_COMPLETE);

   // Allocate statement handle.
   retcode = SQLAllocHandle(SQL_HANDLE_STMT, hdbc1, &hstmt1);
   // Bind the return code to variable sParm1.
   retcode = SQLBindParameter(hstmt1,1,SQL_PARAM_OUTPUT,SQL_C_SSHORT,
         SQL_INTEGER,0,0,&sParm1,0,&cbParm1);
   // Bind the output parameter to variable sParm2.
   retcode = SQLBindParameter(hstmt1,2,SQL_PARAM_OUTPUT,SQL_C_SSHORT,
                        SQL_INTEGER,0,0,&sParm2,0,&cbParm2);
   // Bind the input parameter to variable sParm3.
   retcode = SQLBindParameter(hstmt1,3,SQL_PARAM_INPUT,SQL_C_SSHORT,
                        SQL_INTEGER,0,0,&sParm3,0,&cbParm3);

#ifdef SHOW_BUG
   // Bind the input parameter to variable sParm4.
   retcode = SQLBindParameter(hstmt1,4,SQL_PARAM_INPUT,SQL_C_CHAR,
                        SQL_CHAR,200,0,(SQLPOINTER)4,0,&cbParm4);
#endif

   // Execute the command.
   retcode = SQLExecDirect(hstmt1, (SQLCHAR *)"{? = call TestParm3(?,?"
#ifdef SHOW_BUG
                           ",?)}"
#else
                           ")}"
#endif
                           , SQL_NTS);

   if (retcode==SQL_NEED_DATA)
     {
       int Token;
       do
         {
           retcode = SQLParamData(hstmt1,(SQLPOINTER *)&Token);
           if (retcode==SQL_NEED_DATA)
             {
               _ASSERTE(Token==4);
               SQLPutData(hstmt1,(void *)sParm4.c_str(),sParm4.size());
               printf("Sending Data through Putdata\n");
             }
         }
       while(retcode==SQL_NEED_DATA);
     }
   // Show parameters are not filled.
   printf("Before result sets cleared: RetCode = %d, OutParm = %d.\n",
         sParm1, sParm2);

   // Clear any result sets generated.
   while ( ( retcode = SQLMoreResults(hstmt1) ) != SQL_NO_DATA );

   // Show parameters are now filled.
   printf("After result sets drained: RetCode = %d, OutParm = %d.\n",
         sParm1, sParm2);

   /* Clean up. */
   SQLFreeHandle(SQL_HANDLE_STMT, hstmt1);
   SQLDisconnect(hdbc1);
   SQLFreeHandle(SQL_HANDLE_DBC, hdbc1);
   SQLFreeHandle(SQL_HANDLE_ENV, henv);
   return(0);

Quote:}

 
 
 

Problem with OUTPUT parameters when string is passed using SQLPutData. (Is this a BUG)

Post by Umachandar Jayachandra » Fri, 13 Sep 2002 16:17:50


    I fixed couple of potential problems in the C++ code & tried your
sample. Looks like there is a bug but I haven't coded with ODBC to tell if
this is an usage issue. If you can dig on this further & confirm, I will be
happy to report this bug to MS. The problem I see is that when you use the
text input, somehow the return value parameter information is lost & the
driver essentially sends only a EXEC call. So the return value is not even
coming from the server.
    I tried couple of things with the code but I cannot confirm if there is
a problem with ODBC or the way the functions are being called. I am
attaching the modified code:

#include <stdio.h>

#include <string.h>

#include <windows.h>

#include <sql.h>

#include <sqlext.h>

#include <odbcss.h>

#include <string>

#include <crtdbg.h>

using namespace std;

#define MAXBUFLEN 255

#if 0


text='This is good'

AS




GO

#endif

SQLHENV henv = SQL_NULL_HENV;

SQLHDBC hdbc1 = SQL_NULL_HDBC;

SQLHSTMT hstmt1 = SQL_NULL_HSTMT;

int main() {

RETCODE retcode;

// SQLBindParameter variables.

SWORD sParm1=0, sParm2=1, sParm3=100;

SDWORD
cbParm1=SQL_NTS,cbParm2=SQL_NTS,cbParm3=SQL_NTS,cbParm4=SQL_LEN_DATA_AT_EXEC
(0);

string ConnectionString="Integrated
Security=SSPI;SERVER=localhost;DATABASE=tempdb;DRIVER=SQL Server;";

#ifdef SHOW_BUG

string sParm4="This is good";

#endif

char OutConnectionString[1024];

int Len=0;

// Allocate the ODBC environment and save handle.

retcode = SQLAllocHandle (SQL_HANDLE_ENV, NULL, &henv);

// Notify ODBC that this is an ODBC 3.0 app.

retcode = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION,

(SQLPOINTER) SQL_OV_ODBC3, SQL_IS_INTEGER);

// Allocate ODBC connection handle and connect.

retcode = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc1);

retcode = SQLDriverConnect(hdbc1,

NULL,

(SQLCHAR *)ConnectionString.c_str(),

ConnectionString.size(),

(SQLCHAR *)OutConnectionString,

sizeof(OutConnectionString),

(SQLSMALLINT *)&Len,

SQL_DRIVER_COMPLETE);

// Allocate statement handle.

retcode = SQLAllocHandle(SQL_HANDLE_STMT, hdbc1, &hstmt1);

// Bind the return code to variable sParm1.

retcode = SQLBindParameter(hstmt1,1,SQL_PARAM_OUTPUT,SQL_C_SSHORT,

SQL_INTEGER,0,0,&sParm1,0,&cbParm1);

// Bind the output parameter to variable sParm2.

retcode = SQLBindParameter(hstmt1,2,SQL_PARAM_INPUT_OUTPUT,SQL_C_SSHORT,

SQL_INTEGER,0,0,&sParm2,0,&cbParm2);

// Bind the input parameter to variable sParm3.

retcode = SQLBindParameter(hstmt1,3,SQL_PARAM_INPUT,SQL_C_SSHORT,

SQL_INTEGER,0,0,&sParm3,0,&cbParm3);

#ifdef SHOW_BUG

// Bind the input parameter to variable sParm4.

retcode = SQLBindParameter(hstmt1,4,SQL_PARAM_INPUT,SQL_C_CHAR,

SQL_LONGVARCHAR,sParm4.size(),0,(SQLPOINTER)4,0,&cbParm4);

#endif

// Execute the command.

retcode = SQLExecDirect(hstmt1, (SQLCHAR *)"{? = call TestParm3(?,?"

#ifdef SHOW_BUG

",?)}"

#else

")}"

#endif

, SQL_NTS);

if (retcode==SQL_NEED_DATA)

{

int Token;

do

{

retcode = SQLParamData(hstmt1,(SQLPOINTER *)&Token);

if (retcode==SQL_NEED_DATA)

{

_ASSERTE(Token==4);

#ifdef SHOW_BUG

SQLPutData(hstmt1,(void *)sParm4.c_str(),sParm4.size());

#endif

printf("Sending Data through Putdata\n");

Quote:}
}

while(retcode==SQL_NEED_DATA);

Quote:}

// Show parameters are not filled.

printf("Before result sets cleared: RetCode = %d, OutParm = %d.\n",

sParm1, sParm2);

// Clear any result sets generated.

while ( ( retcode = SQLMoreResults(hstmt1) ) != SQL_NO_DATA );

// Show parameters are now filled.

printf("After result sets drained: RetCode = %d, OutParm = %d.\n",

sParm1, sParm2);

/* Clean up. */

SQLFreeHandle(SQL_HANDLE_STMT, hstmt1);

SQLDisconnect(hdbc1);

SQLFreeHandle(SQL_HANDLE_DBC, hdbc1);

SQLFreeHandle(SQL_HANDLE_ENV, henv);

return(0);

Quote:}

--
Umachandar Jayachandran
SQL Resources at http://www.umachandar.com/resources.htm
( Please reply only to newsgroup. )

 
 
 

Problem with OUTPUT parameters when string is passed using SQLPutData. (Is this a BUG)

Post by Gautam Bhaka » Fri, 13 Sep 2002 17:08:54


Your sample repros on my machine.

Moreover if I am using isql.exe to see if the stored proceedures are working
fine, they do work fine in isql, this means that there is nothing wrong with
the procedures. But somewhere in middle the values for output parameter is
lost. If you pass the string straight from SQLBindParameter (not relying on
SQLPutData) then the sample will work just fine. It's only when I pass the
string using PutData that I see this bug.

Gautam


>     I fixed couple of potential problems in the C++ code & tried your
> sample. Looks like there is a bug but I haven't coded with ODBC to tell if
> this is an usage issue. If you can dig on this further & confirm, I will
be
> happy to report this bug to MS. The problem I see is that when you use the
> text input, somehow the return value parameter information is lost & the
> driver essentially sends only a EXEC call. So the return value is not even
> coming from the server.
>     I tried couple of things with the code but I cannot confirm if there
is
> a problem with ODBC or the way the functions are being called. I am
> attaching the modified code:

> #include <stdio.h>

> #include <string.h>

> #include <windows.h>

> #include <sql.h>

> #include <sqlext.h>

> #include <odbcss.h>

> #include <string>

> #include <crtdbg.h>

> using namespace std;

> #define MAXBUFLEN 255

> #if 0


> text='This is good'

> AS




> GO

> #endif

> SQLHENV henv = SQL_NULL_HENV;

> SQLHDBC hdbc1 = SQL_NULL_HDBC;

> SQLHSTMT hstmt1 = SQL_NULL_HSTMT;

> int main() {

> RETCODE retcode;

> // SQLBindParameter variables.

> SWORD sParm1=0, sParm2=1, sParm3=100;

> SDWORD

cbParm1=SQL_NTS,cbParm2=SQL_NTS,cbParm3=SQL_NTS,cbParm4=SQL_LEN_DATA_AT_EXEC

- Show quoted text -

Quote:> (0);

> string ConnectionString="Integrated
> Security=SSPI;SERVER=localhost;DATABASE=tempdb;DRIVER=SQL Server;";

> #ifdef SHOW_BUG

> string sParm4="This is good";

> #endif

> char OutConnectionString[1024];

> int Len=0;

> // Allocate the ODBC environment and save handle.

> retcode = SQLAllocHandle (SQL_HANDLE_ENV, NULL, &henv);

> // Notify ODBC that this is an ODBC 3.0 app.

> retcode = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION,

> (SQLPOINTER) SQL_OV_ODBC3, SQL_IS_INTEGER);

> // Allocate ODBC connection handle and connect.

> retcode = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc1);

> retcode = SQLDriverConnect(hdbc1,

> NULL,

> (SQLCHAR *)ConnectionString.c_str(),

> ConnectionString.size(),

> (SQLCHAR *)OutConnectionString,

> sizeof(OutConnectionString),

> (SQLSMALLINT *)&Len,

> SQL_DRIVER_COMPLETE);

> // Allocate statement handle.

> retcode = SQLAllocHandle(SQL_HANDLE_STMT, hdbc1, &hstmt1);

> // Bind the return code to variable sParm1.

> retcode = SQLBindParameter(hstmt1,1,SQL_PARAM_OUTPUT,SQL_C_SSHORT,

> SQL_INTEGER,0,0,&sParm1,0,&cbParm1);

> // Bind the output parameter to variable sParm2.

> retcode = SQLBindParameter(hstmt1,2,SQL_PARAM_INPUT_OUTPUT,SQL_C_SSHORT,

> SQL_INTEGER,0,0,&sParm2,0,&cbParm2);

> // Bind the input parameter to variable sParm3.

> retcode = SQLBindParameter(hstmt1,3,SQL_PARAM_INPUT,SQL_C_SSHORT,

> SQL_INTEGER,0,0,&sParm3,0,&cbParm3);

> #ifdef SHOW_BUG

> // Bind the input parameter to variable sParm4.

> retcode = SQLBindParameter(hstmt1,4,SQL_PARAM_INPUT,SQL_C_CHAR,

> SQL_LONGVARCHAR,sParm4.size(),0,(SQLPOINTER)4,0,&cbParm4);

> #endif

> // Execute the command.

> retcode = SQLExecDirect(hstmt1, (SQLCHAR *)"{? = call TestParm3(?,?"

> #ifdef SHOW_BUG

> ",?)}"

> #else

> ")}"

> #endif

> , SQL_NTS);

> if (retcode==SQL_NEED_DATA)

> {

> int Token;

> do

> {

> retcode = SQLParamData(hstmt1,(SQLPOINTER *)&Token);

> if (retcode==SQL_NEED_DATA)

> {

> _ASSERTE(Token==4);

> #ifdef SHOW_BUG

> SQLPutData(hstmt1,(void *)sParm4.c_str(),sParm4.size());

> #endif

> printf("Sending Data through Putdata\n");

> }

> }

> while(retcode==SQL_NEED_DATA);

> }

> // Show parameters are not filled.

> printf("Before result sets cleared: RetCode = %d, OutParm = %d.\n",

> sParm1, sParm2);

> // Clear any result sets generated.

> while ( ( retcode = SQLMoreResults(hstmt1) ) != SQL_NO_DATA );

> // Show parameters are now filled.

> printf("After result sets drained: RetCode = %d, OutParm = %d.\n",

> sParm1, sParm2);

> /* Clean up. */

> SQLFreeHandle(SQL_HANDLE_STMT, hstmt1);

> SQLDisconnect(hdbc1);

> SQLFreeHandle(SQL_HANDLE_DBC, hdbc1);

> SQLFreeHandle(SQL_HANDLE_ENV, henv);

> return(0);

> }

> --
> Umachandar Jayachandran
> SQL Resources at http://www.umachandar.com/resources.htm
> ( Please reply only to newsgroup. )

 
 
 

Problem with OUTPUT parameters when string is passed using SQLPutData. (Is this a BUG)

Post by Umachandar Jayachandra » Sat, 14 Sep 2002 15:13:59


    Looks like a bug to me. I will pass this repro on to MS & see what they
have to say. I would check the ODBC SDK samples or MSDN for more information
on doing these type of things. Good luck.

--
Umachandar Jayachandran
SQL Resources at http://www.umachandar.com/resources.htm
( Please reply only to newsgroup. )