将表值参数代码从c ++传递到SQL Server时出现问题 [英] Problem While passing Table Valued Parameter Code from c++ to SQL Server

问题描述

我想要使用ODBC和c ++在MSSQL Server中创建表值参数。这样的SQL代码在SQL Server中运行良好：

 DECLARE @ T1 as TABLE 
（
 PK INT IDENTITY NOT NULL，
 Wert CHAR（20），
名称CHAR（20）
）
 INSERT INTO @ T1（Wert，Name ）VALUES（'123'，'Babio'）
 INSERT INTO @T1（Wert，Name）VALUES（'214'，'Martin'）
 INSERT INTO @T1（Wert，Name）VALUES（' 236'，'Karo'）
 
 select * FROM @ T1其中Wert = 123 

我用c ++编写了这段代码，使用ODBC将此SQL代码发送到SQL Server： / span>

 int main（）
 {
 SQLHENV henv = SQL_NULL_HENV; 
 SQLHDBC hdbc = SQL_NULL_HDBC; 
 SQLHSTMT hstmt = SQL_NULL_HSTMT; 
 SQLRETURN rc; 
 
 
 SQLCHAR语句[] =" DECLARE @ T1作为表（PK INT IDENTITY NOT NULL，Wert CHAR（20），Name CHAR（20））INSERT INTO @ T1（Wert，名称）VALUES（'123'，'Babak'）INSERT INTO @T1（Wert，Name）VALUES（'214'，'Martin'）INSERT INTO @T1（Wert，Name）VALUES（'236'，'Karo'）选择名称FROM @ T1其中Wert = 123" ; 
 
 
 SQLWCHAR dsn [30] = L" mssqltest" ;; //名称DNS 
 SQLWCHAR用户[10] = L" di_test" ;; 
 SQLWCHAR pass [10] = L" di_test" ;; 
 
 SQLCHAR retValFName [256]; 
 SQLCHAR retValLName [256]; 
 SQLINTEGER cbLName，cbFName; 
 
 rc = SQLAllocHandle（SQL_HANDLE_ENV，SQL_NULL_HANDLE，& henv）; 
 rc = SQLSetEnvAttr（henv，SQL_ATTR_ODBC_VERSION，（void *）SQL_OV_ODBC3,0）; 
 rc = SQLAllocHandle（SQL_HANDLE_DBC，henv，& hdbc）; 
 rc = SQLConnectW（hdbc，（SQLWCHAR *）dsn，SQL_NTS，（SQLWCHAR *）user，SQL_NTS，（SQLWCHAR *）pass，SQL_NTS）; 
 rc = SQLAllocHandle（SQL_HANDLE_STMT，hdbc，& hstmt）; 
 rc = SQLPrepareA（hstmt，（SQLCHAR *）Statement，SQL_NTS）; 
 rc = SQLExecute（hstmt）; 
 
 if（rc == SQL_SUCCESS）
 {
 while（true）
 {
 rc = SQLFetch（hstmt）; //在这一行中rc = -1 
 if（rc == SQL_SUCCESS || rc == SQL_SUCCESS_WITH_INFO）
 {
 SQLGetData（hstmt，1，SQL_C_CHAR，retValFName，256，& cbLName ）; 
 std :: cout<< retValFName<<"" "<<的std :: ENDL; 
 system（"暂停"）; 
} 
其他
 {
休息; 
} 
} 
} 
返回0; 
} 

如果我检查错误，我看到这个错误说：

无效的光标状态

如何更改我的代码以解决此问题

< span style ="color：＃222222; font-family：'Helvetica Neue'，Helvetica，Arial，sans-serif; font-size：15px; line-height：19.5px">

解决方案

嗨rahaba，

我测试了你的代码并得到了同样的错误。然后我资助一个有用的线程，可能会给你一些提示。请检查此主题：

http://stackoverflow.com/questions/21417922/sql-run-from-excel-cannot-use-a-temporary-table

我添加SET NOCOUNT ON到您的SQL语句，它可以工作。

 
 SQLCHAR语句[] =" SET NOCOUNT ON; 
 
 DECLARE @ T1作为表（PK INT IDENTITY NOT NULL，Wert CHAR（20），Name CHAR（20））; 
 
 INSERT INTO @T1（Wert，Name）VALUES（'123'，'Babak'）; 
 
 INSERT INTO @T1（Wert，Name）VALUES（'214'，'Martin'）; 
 
 INSERT INTO @T1（Wert，Name）VALUES（'236'，'Karo'）; 
 
选择姓名FROM @ T1其中Wert = 123" ;; 
 

如果您想了解更多有关SET NOCOUNT ON的信息，我建议您因为我不熟悉它，所以请它进入T-SQL论坛。更多信息也可以查看文件：

SET NOCOUNT（Transact- SQL）

希望这会有所帮助。

Shu

I would like to create Table Valued Parameter in MSSQL Server using ODBC and c++. Such a SQL Code works fine in SQL Server:

DECLARE @T1 as TABLE
(
 PK INT IDENTITY NOT NULL,
 Wert CHAR(20),
 Name CHAR(20)
 )
 INSERT INTO @T1(Wert,Name) VALUES ('123','Babio') 
 INSERT INTO @T1(Wert,Name) VALUES ('214','Martin') 
 INSERT INTO @T1(Wert,Name) VALUES ('236','Karo') 

 select * FROM @T1 where  Wert=123 

I have written this block of code in c++ to send this SQL Code to SQL Server using ODBC:

int main()
{
    SQLHENV henv=SQL_NULL_HENV;
    SQLHDBC hdbc=SQL_NULL_HDBC;
    SQLHSTMT hstmt=SQL_NULL_HSTMT;
    SQLRETURN rc;


   SQLCHAR Statement[] = "DECLARE @T1 as TABLE (PK INT IDENTITY NOT NULL, Wert CHAR(20), Name CHAR(20)) INSERT INTO @T1(Wert,Name) VALUES ('123','Babak')  INSERT INTO @T1(Wert,Name) VALUES ('214','Martin') INSERT INTO @T1(Wert,Name) VALUES ('236','Karo')  select Name FROM @T1 Where Wert=123" ;


   SQLWCHAR dsn[30] = L"mssqltest"; //Name DNS
   SQLWCHAR user[10] = L"di_test";
   SQLWCHAR pass[10] = L"di_test";

   SQLCHAR retValFName[256];
   SQLCHAR retValLName[256];
   SQLINTEGER cbLName,cbFName;

   rc = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv);  
   rc= SQLSetEnvAttr(henv,SQL_ATTR_ODBC_VERSION,(void *) SQL_OV_ODBC3,0);
   rc = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc);  
   rc = SQLConnectW(hdbc, (SQLWCHAR *)dsn, SQL_NTS, (SQLWCHAR *) user, SQL_NTS, (SQLWCHAR *) pass, SQL_NTS); 
   rc = SQLAllocHandle(SQL_HANDLE_STMT,hdbc,&hstmt);
   rc = SQLPrepareA(hstmt, (SQLCHAR *)Statement, SQL_NTS);
   rc = SQLExecute(hstmt);

   if(rc==SQL_SUCCESS)
   {
   while(true)
        {
          rc=SQLFetch(hstmt); // In this line rc=-1
          if(rc==SQL_SUCCESS || rc==SQL_SUCCESS_WITH_INFO)
          {
             SQLGetData(hstmt,1,SQL_C_CHAR,retValFName,256, &cbLName);
             std::cout << retValFName <<"   "<< std::endl;
             system("pause");
          }
          else
          {
              break;
          }
        }
   }
   return 0;
}

If I check the error, I see this error which says:

Invalid Cursor state

How could be my code changed to solve this problem

解决方案

Hi rahaba,

I tested your code and got the same error. Then I fund a helpful thread which may give you some hints. PLease check this thread:

http://stackoverflow.com/questions/21417922/sql-run-from-excel-cannot-use-a-temporary-table

I add SET NOCOUNT ON to your SQL statement and it works.

SQLCHAR Statement[] = "SET NOCOUNT ON; 
	DECLARE @T1 as TABLE (PK INT IDENTITY NOT NULL, Wert CHAR(20), Name CHAR(20)); 
	INSERT INTO @T1(Wert,Name) VALUES ('123','Babak');  
	INSERT INTO @T1(Wert,Name) VALUES ('214','Martin'); 
	INSERT INTO @T1(Wert,Name) VALUES ('236','Karo');  
	select Name FROM @T1 Where Wert=123";

If you want to know more about SET NOCOUNT ON, I suggest you ask it to a T-SQL forum since I am not familiar with it. More information also could check the document:

SET NOCOUNT (Transact-SQL)

Hope this helps.

Shu

这篇关于将表值参数代码从c ++传递到SQL Server时出现问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！