Issue with table parameter types and varchar column in C++ ODBC
- From: Barnaby <Barnaby@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Mon, 20 Apr 2009 00:03:01 -0700
Hi,
I am experimenting with 'table' user data types in SQL Server 2008 sp1 and I
am getting a consistent error from the connection when I pass 'character
arrays' across the interface (ints/floats appear to work fine) to a stored
procedure with a table parameter type as an argument. The error is:
"Execute Failed: [Microsoft][SQL Server Native Client 10.0]Unspecified error
occurred on SQL Server. Connection may have been terminated by the server."
It occurs on the 'second' SP call to the interface using the same connection
handle. The first loop SP execution does succeed and character arrays
specified are inserted into the SQL server table from the table parameter
type.
An abbreviated C++ code sample is below. I have been careful to remove all
state on each loop apart from the connection, but the error still occurs,
perhaps indicating the connection handle is not removing internal state
correctly. Also the C++ character string array handling is tricky, perhaps
there is a cleaner implementation available.
Any help appreciated,
Barnaby
SQLHDBC dbc = NULL;
SQLRETURN retcode = NULL;
retcode = SQLDriverConnect(dbc, NULL, (SQLCHAR*)"Driver={SQL Server Native
Client 10.0};Server=(local);Trusted_Connection=Yes;Database=testdb;",
SQL_NTS,NULL, 0, NULL, SQL_DRIVER_COMPLETE);
if ( !SQL_SUCCEEDED(retcode) ) // Fail
{
return;
}
// Loop 10 times, inserting 10 rows each time
for (int nLoops = 0; nLoops < 10; i++)
{
SQLHSTMT hstmt = NULL;
// Allocate statement handle
SQLRETURN retcode = SQLAllocHandle(SQL_HANDLE_STMT, dbc, &hstmt);
if ( !SQL_SUCCEEDED(retcode) ) // Fail
{
break;
}
#define SQL_VARCHAR_SIZE 8
SQLULEN nInsertRows = 10;
SQLCHAR *szCharBuffer = new SQLCHAR[SQL_VARCHAR_SIZE*nInsertRows];
SQLLEN *pLenBuffer = new SQLLEN[nInsertRows];
::ZeroMemory(szCharBuffer, nInsertRows*SQL_VARCHAR_SIZE);
SQLLEN cbTVP = 0;
retcode = SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_DEFAULT,
SQL_SS_TABLE, nInsertRows, 0, (SQLPOINTER)1, 0, &cbTVP);
// Bind columns for the table-valued parameter
// First set focus on param 1
retcode = SQLSetStmtAttr(hstmt, SQL_SOPT_SS_PARAM_FOCUS, (SQLPOINTER) 1,
SQL_IS_INTEGER);
// VarChar Table Parameter
retcode = SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_CHAR ,
SQL_VARCHAR, SQL_VARCHAR_SIZE-1, 0, szCharBuffer, SQL_VARCHAR_SIZE,
pLenBuffer);
// Reset param focus.
retcode = SQLSetStmtAttr(hstmt, SQL_SOPT_SS_PARAM_FOCUS, (SQLPOINTER) 0,
SQL_IS_INTEGER);
// Number of rows available for input.
::ZeroMemory(szCharBuffer, nInsertRows*SQL_VARCHAR_SIZE); // Clear
cbTVP = 0;
for (UINT nPos = 0; nPos < nInsertRows; nPos++)
{
sprintf((char*)(szCharBuffer+(cbTVP*SQL_VARCHAR_SIZE)), "Test"); // Set
each 8 character array to 'Test'
pLenBuffer[cbTVP] = SQL_NTS;
cbTVP++;
}
// Call the procedure.
retcode = SQLExecDirect(hstmt, (SQLCHAR *) "{call INSERT_TEST(?)}",SQL_NTS);
if ( !SQL_SUCCEEDED(retcode) ) // Fail
{
ReportError(hstmt);
}
// Cleanup
delete szCharBuffer;
delete pLenBuffer;
SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
}
.
- Prev by Date: Re: Semicolon in file name or file path
- Next by Date: Cannot Create System Dsn Odbc Data Souce as Administrator
- Previous by thread: RE: Using ODBC to export to xlsx format
- Next by thread: Cannot Create System Dsn Odbc Data Souce as Administrator
- Index(es):
Relevant Pages
|
