RE: Cannot fetch output parameters with SQL_DATA_AT_EXEC input

Thanks for the quick reply.

Can you possibly give an estimate on when the fix will be available?

I'm in desperate need of a soluition. Is there a workaround for the problem?
I need to pass ~30-60K character long ntext parameters and retrieve varchar
and nvarchar ouput parameters.

"Pak-Ming Cheung [MSFT]" wrote:

I can repro the problem on my end. Based on the primitive investigation, it
seems to be a bug in the driver.
We will investigate this issue. Thanks a lot for reporting this issue to us.

Thanks,
MDAC Team, Microsoft.

==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.





"Gabor Tyukasz" wrote:

Hello,

I'm using ODBC in a C++ application to call stored procedures on SQL Server
2005, using SQLPrepare/SQLBindParameter/SQLExecute. When I'm calling an SP
that has variable length input parameters passed with
SQL_DATA_AT_EXEC/SQLParamData/SQLPutData, the output parameters are not
returned.

I have created a stored procedure to demonstrate the problem:

CREATE PROCEDURE sp_GaborTest
@ip1 INT
, @ip2 TEXT
, @op1 INT = NULL OUTPUT
AS
SET NOCOUNT ON
SET @op1 = @ip1
RETURN @ip1

When I execute it from VS2005 Server Explorer, everything works fine:

Running [dbo].[sp_GaborTest] ( @ip1 = 12345678, @ip2 = actually a large text
input, @op1 = <DEFAULT> ).

No rows affected.
(0 row(s) returned)
@op1 = 12345678
@RETURN_VALUE = 12345678
Finished running [dbo].[sp_GaborTest].

I have also created a C++ Win32 command-line app that executes this SP the
same way as the original app (of which the sources are too complex to be
included here):

#define WIN32_LEAN_AND_MEAN
#include <Windows.h>
#include <tchar.h>
#include <stdio.h>

#define SQL_NOUNICODEMAP
#include <sql.h>
#include <sqlext.h>

#define TESTCALL(call) \
res = (call); \
if (!SQL_SUCCEEDED(res) && res != SQL_NEED_DATA) \
{ \
szCall = #call; \
goto testerr; \
}

int _tmain(int argc, _TCHAR* argv[])
{
PCSTR szCall = "<unknown>";
SQLHENV hEnv = NULL;
SQLHDBC hConn = NULL;
SQLHSTMT hStmt = NULL;
SQLCHAR szConnStr[] =
"DRIVER={SQL Server};"
"SERVER=<***Fill Server name***>;"
"DATABASE=<***Fill DB Name***>;"
"Trusted_Connection=yes;"
"AutoTranslate=no;"
"APP=ODBCTest;"
"WSID=ODBCTestMachine";
SQLCHAR szConn[1024];
SQLSMALLINT cbConn = sizeof(szConn);
SQLCHAR szStmt[] = "{?=CALL sp_GaborTest(?,?,?)}";

LONG nRetVal = 0;
SQLLEN cbRetVal = 0;
LONG nIP1 = 12345678;
SQLLEN cbIP1 = 0;
CHAR szIP2[] = "actually a large text input";
SQLLEN cbIP2 = SQL_DATA_AT_EXEC;
LONG nOP1 = 0;
SQLLEN cbOP1 = 0;

SQLRETURN res;

TESTCALL(SQLAllocHandleStd(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &hEnv));
TESTCALL(SQLSetEnvAttr(hEnv, SQL_ATTR_ODBC_VERSION, (VOID*)SQL_OV_ODBC3, 0));

TESTCALL(SQLAllocHandleStd(SQL_HANDLE_DBC, hEnv, &hConn));
TESTCALL(SQLDriverConnect(hConn, NULL, szConnStr, SQL_NTS, szConn, cbConn,
&cbConn, SQL_DRIVER_NOPROMPT));

TESTCALL(SQLAllocHandleStd(SQL_HANDLE_STMT, hConn, &hStmt));
TESTCALL(SQLPrepare(hStmt, szStmt, SQL_NTS));
TESTCALL(SQLBindParameter(hStmt, 1, SQL_PARAM_OUTPUT, SQL_C_LONG,
SQL_INTEGER, 1, 0, &nRetVal, sizeof(nRetVal), &cbRetVal));
TESTCALL(SQLBindParameter(hStmt, 2, SQL_PARAM_INPUT, SQL_C_LONG,
SQL_INTEGER, 1, 0, &nIP1, sizeof(nIP1), &cbIP1));
TESTCALL(SQLBindParameter(hStmt, 3, SQL_PARAM_INPUT, SQL_C_CHAR,
SQL_LONGVARCHAR, MAXLONG, 0, NULL, 0, &cbIP2));
TESTCALL(SQLBindParameter(hStmt, 4, SQL_PARAM_OUTPUT, SQL_C_LONG,
SQL_INTEGER, 1, 0, &nOP1, sizeof(nOP1), &cbOP1));

TESTCALL(SQLExecute(hStmt));
while (res == SQL_NEED_DATA)
{
SQLPOINTER p;
TESTCALL(SQLParamData(hStmt, &p));
if (res == SQL_NEED_DATA)
{
TESTCALL(SQLPutData(hStmt, szIP2, sizeof(szIP2) - 1));
if (SQL_SUCCEEDED(res)) res = SQL_NEED_DATA;
}
}

while (SQL_SUCCEEDED(res))
res = SQLMoreResults(hStmt);

if (res != SQL_NO_DATA && !SQL_SUCCEEDED(res))
{
szCall = "SQLMoreResults(hStmt)";
goto testerr;
}

printf(
"Executed\n"
"@ret (%d): %d\n"
"@ip1 (%d): %d\n"
"@ip2 (%d): %s\n"
"@op1 (%d): %d\n"
,
cbRetVal, nRetVal,
cbIP1, nIP1,
cbIP2, szIP2,
cbOP1, nOP1
);

goto testend;

testerr:
{
SQLCHAR szState[6] = "";
SQLCHAR szDescr[SQL_MAX_MESSAGE_LENGTH] = "";
SQLSMALLINT ccDescr = sizeof(szDescr);
SQLINTEGER nNative = 0;

if (!SQL_SUCCEEDED(SQLGetDiagRec(SQL_HANDLE_STMT, hStmt, 1, szState,
&nNative, szDescr, ccDescr, &ccDescr)) &&
!SQL_SUCCEEDED(SQLGetDiagRec(SQL_HANDLE_DBC, hConn, 1, szState, &nNative,
szDescr, ccDescr, &ccDescr)))
{
SQLGetDiagRec(SQL_HANDLE_ENV, hEnv, 1, szState, &nNative, szDescr,
ccDescr, &ccDescr);
}

printf(
"Failure in call %s.\n"
" Result: %d\n"
" State: %s\n"
" Native: %d\n"
" Error: %s\n"
,
szCall,
res,
szState,
nNative,
szDescr
);
}

testend:
if (hStmt)
{
SQLFreeStmt(hStmt, SQL_CLOSE);
SQLFreeHandle(SQL_HANDLE_STMT, hStmt);
}

if (hConn)
{
SQLDisconnect(hConn);
SQLFreeHandle(SQL_HANDLE_DBC, hConn);
}

if (hEnv)
SQLFreeHandle(SQL_HANDLE_ENV, hEnv);

return 0;
}

This executes without errors for me:

Executed
@ret (4): 12345678
@ip1 (0): 12345678
@ip2 (-2): actually a large text input
@op1 (0): 0

The strange thing is that the return value is filled properly but @op1 is not.

According to the documentation of
SQLBindParameter/SQLExecute/SQLParamData/SQLPutData this should work in
theory. What am I doing wrong? Please help.

Thank you,
Gabor Tyukasz
.

Relevant Pages
Loading