Error binding table-valued parameter while using ODBC, c++

Question

I am attempting to pass a table-valued parameter as a parameter in a stored procedure using ODBC. I have followed examples from MSDN, but receive the following error when I call SQLBindParameter:

HY004 [Microsoft][ODBC SQL Server Driver]Invalid SQL data type

Here is my code.

//Allocate stament handle
SQLAllocHandle(SQL_HANDLE_STMT, hDbc, &hStmt);

//Prep command
SQLPrepare(hStmt, (SQLCHAR*)"{call myStoredProc(?)}", SQL_NTS)

//Variables
const int arraySize = 2;
const int varcharSize = 30;
SQLCHAR *myUserDefTableName = (SQLCHAR *) "myUserDefTableName";
SQLLEN     myUserDefTableInd = 0;



//bind table item

int result = SQLBindParameter(hStmt, 
  1,// ParameterNumber
  SQL_PARAM_INPUT,// InputOutputType
  SQL_C_DEFAULT,// ValueType 
  SQL_SS_TABLE,// Parametertype
  (SQLINTEGER)arraySize,// ColumnSize - for a TVP this the row array size
  0,// DecimalDigits - for a TVP this is the number of columns in the TVP 
  (SQLPOINTER)myUserDefTableName,// ParameterValuePtr - for a TVP this is       the type name of the TVP
  SQL_NTS,// BufferLength - for a TVP this is the length of the type name or SQL_NTS
  &myUserDefTableInd);// StrLen_or_IndPtr - for a TVP this is the number of rows available

//bind columns for the table-valued parameter 
//and execute command
...

I've also found this on MSDN:

A table-valued parameter column cannot be bound as type SQL_SS_TABLE. If SQLBindParameter is called with ParameterType set to SQL_SS_TABLE, SQL_ERROR is returned and a diagnostic record is generated with SQLSTATE=HY004, "Invalid SQL data type". This can also occur with SQLSetDescField and SQLSetDescRec.

But I am trying to bind the table item, not the table columns. This almost seems to directly contradict what is stated in their code examples. I am unsure as to why this error occurs. Any ideas?

Many thanks.

score 1 · Answer 1 · answered Aug 27 '15 at 02:39

Are you sure that you have ODBC set to version 3.x? Try this right after the call to get the statement handle:

 SQLHENV  hEnv  = SQL_NULL_HENV;
 SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &hEnv); 

 SQLSetEnvAttr(hEnv, SQL_ATTR_ODBC_VERSION,(SQLPOINTER)SQL_OV_ODBC3, SQL_IS_INTEGER);

score 1 · Answer 2 · answered Nov 04 '17 at 17:03

The call to SQLBindParameter can also fail with "Invalid SQL data type" when the SQL Data Type, SQL_SS_TABLE in this case, is unknown to the ODBC Driver you're using.

You can check the installed ODBC drivers and their versions by opening the ODBC Data Source Administrator under the Drivers tab:

I was using the default "SQL Server" driver, as specified in the connection string passed to SQLDriverConnect.

SQLDriverConnect(hdbc, NULL, (SQLCHAR*)"DRIVER={SQL Server}...

However, this driver is from 2010 and does not appear to support the SQL_SS_TABLE SQL Type. Hence the SQLBindParameter call issues the invalid type error record. Changing this driver to SQL Server Native Client 11.0 resolved the issue for me, as that one is from 2016 and most likely more up-to-date.

SQLDriverConnect(hdbc, NULL, (SQLCHAR*)"DRIVER={SQL Server Native Client 11.0}...

Updating the default "SQL Server" driver to a later version or using a later operating system will most likely solve the issue as well or not cause it in the first place.

Error binding table-valued parameter while using ODBC, c++

2 Answers2

Linked