Issue with table parameter types and varchar column in C++ ODBC
Barnaby
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);
}
Pak-Ming Cheung - MSFT
with the following (my C++ code should be the same as yours. But i attach as
follow for completeness). So, please try again:
First, create a table with:
CREATE TABLE [dbo].[MyTVP_Table](
[data] [varchar](50) NOT NULL
) ON [PRIMARY]
GO
Second, create a SProc that takes a TVP parameter:
CREATE PROCEDURE [dbo].[usp_blockInsertion]
@myTvpInput MyCharTableType READONLY
AS
SET NOCOUNT ON
INSERT INTO MyTVP_Table (data)
SELECT *
FROM @myTvpInput;
GO
Third, create a table type:
CREATE TYPE [dbo].[MyCharTableType] AS TABLE(
[LocationName] [varchar](50) NULL
)
GO
Lastly, compile and run the following code in C++:
#include <windows.h>
#include "sql.h" // use new header files
#include "sqlext.h"
#include <sqltypes.h> // define many data types, like DATE_STRUCT
#include <sqlncli.h>
#include <stdlib.h>
#include <stdio.h>
#include <time.h>
#include <tchar.h>
#include <strsafe.h>
//-----------------------------------------------------------------------------------------------
// Macro definitions
#define SafeDelete(x) { if(x) {free(x); x = NULL;} }
SQLTCHAR CONN_STR[1024] = TEXT("Driver={SQL Server Native Client
10.0};Server=xxxxxx;UID=xxxxxx;PWD=xxxxxx;Database=xxxxxx");
#define NUMELEM(x) ( sizeof(x) / sizeof(*x) )
#define SQL_VARCHAR_SIZE 8
// simple helper functions
int MySQLSuccess(SQLRETURN rc)
{
return (rc==SQL_SUCCESS || rc==SQL_SUCCESS_WITH_INFO);
}
// Handle the status checking and display the error message by
displayErrorDetail
// return true if the status is SQL_SUCCESS or SQL_SUCCESS_WITH_INFO
BOOL MyStatusChecking(SQLRETURN rc, SQLSMALLINT HandleType, SQLHANDLE
Handle, const char* errorMsg );
//--------------------------------------------------------------------------------------------
// function declaration
// Main test routine
void test();
// Print more detail about the error
void displayErrorDetail( SQLSMALLINT HandleType, SQLHANDLE Handle );
//--------------------------------------------------------------------------------------------
// Functions Definition
void test()
{
SQLHENV henv;
SQLHDBC hdbc;
SQLHSTMT hstmt;
SQLHDESC hdescIPD;
SQLRETURN retCode;
SQLTCHAR outBuffer[90];
SQLULEN nInsertRows = 10;
//----------------------------------------------------------------------------------------------
// Step 1: setup
// allocate environment
if ( !MySQLSuccess(retCode =
SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE,
&henv)) )
{
printf( "Cannot allocate environment handle, code = %d\n", retCode );
}
// set environment attributes
retCode = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION,
(void*)SQL_OV_ODBC3, -1);
MyStatusChecking( retCode, SQL_HANDLE_ENV, henv,
"Cannot set environment attribute" );
// allocate connection
retCode = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc);
MyStatusChecking( retCode, SQL_HANDLE_ENV, henv,
"Cannot allocate connection handle" );
// Set login timeout
retCode = SQLSetConnectAttr(hdbc, SQL_LOGIN_TIMEOUT, (SQLPOINTER)10, 0);
MyStatusChecking( retCode, SQL_HANDLE_DBC, hdbc, "Cannot set timeout" );
// connect to database
retCode = SQLDriverConnect(hdbc, NULL, CONN_STR, SQL_NTS,
outBuffer,
NUMELEM(outBuffer), NULL,
SQL_DRIVER_NOPROMPT);
if ( !MySQLSuccess( retCode ) )
{
// display error message
printf( "Cannot connect to database, code = %d\n", retCode );
displayErrorDetail( SQL_HANDLE_DBC, hdbc );
exit(-1);
}
// allocate statement
retCode = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);
MyStatusChecking( retCode, SQL_HANDLE_DBC, hdbc,
"Cannot allocate statement handle" );
SQLCHAR* szCharBuffer = new SQLCHAR[SQL_VARCHAR_SIZE*nInsertRows];
SQLLEN *pLenBuffer = new SQLLEN[nInsertRows];
::ZeroMemory(szCharBuffer, nInsertRows*SQL_VARCHAR_SIZE);
SQLLEN cbTVP = 0;
// Number of rows available for input.
for (cbTVP = 0; cbTVP < nInsertRows; cbTVP++)
{
// Set each 8 character array to 'Test'
sprintf( (char*)(szCharBuffer+(cbTVP*SQL_VARCHAR_SIZE)), "Test");
pLenBuffer[cbTVP] = SQL_NTS;
}
retCode = SQLBindParameter( hstmt,
1,
SQL_PARAM_INPUT,
SQL_C_DEFAULT,
SQL_SS_TABLE,
nInsertRows,
0,
(SQLPOINTER)1,
0,
&cbTVP);
MyStatusChecking( retCode, SQL_HANDLE_STMT, hstmt,
"Cannot call SQLBindParameter (level
0)" );
// 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);
MyStatusChecking( retCode, SQL_HANDLE_STMT, hstmt,
"Cannot change focus to level 1" );
// 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);
MyStatusChecking( retCode, SQL_HANDLE_STMT, hstmt,
"Cannot call SQLBindParameter (level
1)" );
// Reset param focus.
retCode = SQLSetStmtAttr(hstmt, SQL_SOPT_SS_PARAM_FOCUS,
(SQLPOINTER) 0,
SQL_IS_INTEGER);
MyStatusChecking( retCode, SQL_HANDLE_STMT, hstmt,
"Cannot change focus to level 0" );
// execute the query with table-valued parameter
retCode = SQLExecDirect(hstmt,
(SQLTCHAR*)TEXT("{call
usp_blockInsertion(?)}"), SQL_NTS);
MyStatusChecking( retCode, SQL_HANDLE_STMT, hstmt, "Cannot execute
query" );
// this should return
retCode = SQLMoreResults(hstmt);
if ( retCode != SQL_NO_DATA )
{
printf( "ret = %d, Cannot call SQLMoreResults\n", retCode );
}
// clean up
SQLFreeHandle( SQL_HANDLE_STMT, hstmt );
delete [] szCharBuffer;
delete [] pLenBuffer;
retCode = SQLDisconnect( hdbc );
MyStatusChecking( retCode, SQL_HANDLE_DBC, hdbc, "Cannot disconnect" );
SQLFreeHandle( SQL_HANDLE_DBC, hdbc );
SQLFreeHandle( SQL_HANDLE_ENV, henv );
printf( "Finished\n" );
}
void displayErrorDetail( SQLSMALLINT HandleType, SQLHANDLE Handle )
{
enum { StateLength = 5 };
int MessageLength = 512;
SQLRETURN retCode;
SQLTCHAR Sqlstate[StateLength+1];
SQLINTEGER NativeError;
SQLTCHAR* MessageText = (SQLTCHAR*)malloc( (MessageLength+1) *
sizeof(SQLTCHAR) );
SQLSMALLINT TextLength;
int errNum = 1;
Sqlstate[0] = L'\0';
MessageText[0] = L'\0';
NativeError = 0;
while( 1 )
{
retCode = SQLGetDiagRec( HandleType, Handle, errNum,
Sqlstate, &NativeError, MessageText,
MessageLength+1, &TextLength );
if ( retCode == SQL_SUCCESS )
{
_tprintf( TEXT("Error Num = %d (SQL State = %s):\n"), errNum,
Sqlstate );
_tprintf( TEXT("Native error = %ld\n"), NativeError );
_tprintf( TEXT("Message Text = %s\n\n"), MessageText );
errNum++;
}
else if ( retCode == SQL_NO_DATA )
break;
else if ( retCode == SQL_SUCCESS_WITH_INFO )
{
// reallocating the buffer and repeat retrieval the same error
message
MessageLength = TextLength;
MessageText = (SQLTCHAR*)realloc( MessageText, (MessageLength+1)
* sizeof(SQLTCHAR) );
}
else
{
printf( "Cannot retrieve error message, code = %d\n\n", retCode
); // other errors
break;
}
}
fflush(stdout);
free(MessageText);
}
BOOL MyStatusChecking(SQLRETURN rc, SQLSMALLINT HandleType, SQLHANDLE
Handle, const char* errorMsg )
{
BOOL retValue = MySQLSuccess(rc);
if ( retValue == FALSE )
printf( "ERROR: %s, code = %d\n", errorMsg, rc );
// even if it is success, it may still have warning
displayErrorDetail( HandleType, Handle );
return retValue;
}
//----------------------------------------------------------------------------------------------------------------
// main function
int main()
{
// start testing
test();
// Return success code; example executed successfully
return 0;
}
Thanks,
Ming.
WDAC Team, Microsoft.
P.S. We recommend customers to use the forum to ask questions in the future,
where you can obtain a faster response (Forum is at:
http://social.msdn.microsoft.com/forums/en-US/sqldataaccess/threads/)
Barnaby
I have register the issue on the link below, as it was confirmed a bug by a
contact as Microsoft.
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=443864
The workaround was to explicitly set the table param 'type name', before
calling the code in the loop. Fixed the problem in my case nicely.
Barnaby.