使用ODBC，C ++时绑定表值参数时出错 [英] Error binding table-valued parameter while using ODBC, c++

问题描述

我正在尝试使用ODBC将表值参数作为存储过程中的参数传递.我已经遵循了MSDN的示例，但是在调用 SQLBindParameter 时收到以下错误:

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驱动程序]无效的SQL数据类型

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

这是我的代码.

//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
...

我也在MSDN上找到了它:

I've also found this on MSDN:

表值参数列不能绑定为SQL_SS_TABLE类型.如果在ParameterType设置为SQL_SS_TABLE的情况下调用SQLBindParameter，则返回SQL_ERROR并生成一条SQLSTATE = HY004，无效的SQL数据类型"的诊断记录.使用SQLSetDescField和SQLSetDescRec也会发生这种情况.

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?

非常感谢.

推荐答案

当 SQL数据类型时，对 SQLBindParameter 的调用也可能因无效的SQL数据类型"而失败. ，在这种情况下，SQL_SS_TABLE对于您使用的 ODBC驱动程序是未知的.

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.

您可以通过打开 Drivers 标签下的 ODBC数据源管理员来检查已安装的ODBC驱动程序及其版本:

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

我使用的是默认的"SQL Server"驱动程序，该驱动程序是传递给 SQLDriverConnect 的连接字符串中指定的.

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

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

但是，该驱动程序来自2010年，似乎不支持SQL_SS_TABLE SQL类型. 因此，SQLBindParameter调用会发出无效的类型错误记录.将此驱动程序更改为SQL Server Native Client 11.0可以为我解决此问题，因为该驱动程序来自2016年，并且很可能是最新的.

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

将默认的"SQL Server"驱动程序更新到更高版本或使用更高版本的操作系统很可能也可以解决该问题，或者根本不会导致该问题.

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.

这篇关于使用ODBC，C ++时绑定表值参数时出错的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！