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:39:23



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:}