c++ - 以错误的顺序运行包含大量 INSERT 语句和一个 SELECT 语句的查询

Question

我想在一个表中插入很多行。我将我的值作为数组发送到 C++ 中使用 ODBC 的参数化插入查询，如下所示:

INSERT INTO information1(Wert1,Wert2) VALUES(?,?)  select wert1  from information1 WHERE Wert2=9"

如您所见，我想在 INSERT 之后运行一个 select 语句。

这段代码可以在表中插入行，但是select语句的结果是错误的(结果应该是d)。我希望在数据库中运行这样的东西:

INSERT INTO information1(Wert1,Wert2) VALUES(a,4)
INSERT INTO information1(Wert1,Wert2) VALUES(b,3)
INSERT INTO information1(Wert1,Wert2) VALUES(c,17)
INSERT INTO information1(Wert1,Wert2) VALUES(d,9)
INSERT INTO information1(Wert1,Wert2) VALUES(e,5)
INSERT INTO information1(Wert1,Wert2) VALUES(f,45)
INSERT INTO information1(Wert1,Wert2) VALUES(g,6)
INSERT INTO information1(Wert1,Wert2) VALUES(h,4)
INSERT INTO information1(Wert1,Wert2) VALUES(i,2)
SELECT Wert1 FROM information1 WHERE Wert2=9

但实际发生的是:

   INSERT INTO information1(Wert1,Wert2) VALUES(a,4)
    SELECT Wert1 FROM information1 WHERE Wert2=9
    INSERT INTO information1(Wert1,Wert2) VALUES(b,3)
    INSERT INTO information1(Wert1,Wert2) VALUES(c,17)
    INSERT INTO information1(Wert1,Wert2) VALUES(d,9)
    INSERT INTO information1(Wert1,Wert2) VALUES(e,5)
    INSERT INTO information1(Wert1,Wert2) VALUES(f,45)
    INSERT INTO information1(Wert1,Wert2) VALUES(g,6)
    INSERT INTO information1(Wert1,Wert2) VALUES(h,4)
    INSERT INTO information1(Wert1,Wert2) VALUES(i,2)

因此它什么都不返回(对我来说是错误的结果)。有人能解释一下为什么在数据库中运行的语句顺序错误吗？以及我应该如何更改我的代码以获得正确的顺序？

在这里你可以找到我的全部代码:

#include <windows.h> 
#include <stdlib.h> 
#include <stdio.h> 
#include<tchar.h>

#include <sql.h> 
#include <sqlext.h> 
#include<sqltypes.h>


//#include<odbcss.h>
//#include<odbcinst.h>

#include<exception>
#include<iostream>
#include<stdio.h>

#include <conio.h>


#include "Debug\sqlncli.h";

void HandleDiagnosticRecord (SQLHANDLE      hHandle,    
                             SQLSMALLINT    hType,  
                             RETCODE        RetCode)
{
    SQLSMALLINT iRec = 0;
    SQLINTEGER  iError;
    WCHAR       wszMessage[1000];
    WCHAR       wszState[SQL_SQLSTATE_SIZE+1];


    if (RetCode == SQL_INVALID_HANDLE)
    {
        fwprintf(stderr, L"Invalid handle!\n");
        return;
    }

    while (SQLGetDiagRec(hType,
                         hHandle,
                         ++iRec,
                         wszState,
                         &iError,
                         wszMessage,
                         (SQLSMALLINT)(sizeof(wszMessage) / sizeof(WCHAR)),
                         (SQLSMALLINT *)NULL) == SQL_SUCCESS)
    {
        // Hide data truncated..
        if (wcsncmp(wszState, L"01004", 5))
        {
            fwprintf(stderr, L"[%5.5s] %s (%d)\n", wszState, wszMessage, iError);
        }
    }


}

#define TRYODBC(h, ht, x)   {   RETCODE rc = x;\
                             if (rc != SQL_SUCCESS) \
                                { \
                                    HandleDiagnosticRecord (h, ht, rc); \
                                } \
                                if (rc == SQL_ERROR) \
                                { \
                                    fwprintf(stderr, L"Error in " L#x L"\n"); \
                                    Sleep(30000); \
                                }  \
                            }


void extract_error(
      char *fn,
      SQLHANDLE handle,
      SQLSMALLINT type)
  {
    SQLINTEGER i = 0;
    SQLINTEGER native;
    SQLWCHAR state[ 7 ];
    SQLWCHAR text[256];
    SQLSMALLINT len;
    SQLRETURN ret;
    fprintf(stderr,
            "\n"
            "The driver reported the following diagnostics whilst running "
            "%s\n\n",
            fn);

    do
    {
      ret = SQLGetDiagRec(type, handle, ++i, state, &native, text,
      sizeof(text), &len );
      if (SQL_SUCCEEDED(ret))
      printf("%s:%ld:%ld:%s\n", state, i, native, text);
    }
    while( ret == SQL_SUCCESS );
  }


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

        SQLTCHAR Statement[] = _T("INSERT INTO information1(Wert1,Wert2) VALUES(?,?)  select count(*) as a from information1 ") ;
        SQLTCHAR sqlStatementOUT[255];
        SQLINTEGER lenStatementOUT=0;
        memset(sqlStatementOUT,' ',255);



   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;
   SQLLEN cName=0;

   SQLCHAR *TVPTableName=(SQLCHAR *) "TableTVP";

    #define PARAM_ARRAY_SIZE 9
    #define STRING_SIZE 20

   SQLCHAR     Arrayname1[PARAM_ARRAY_SIZE][STRING_SIZE] = {"4","3","17","9","5","45","6","4","2"};
SQLCHAR     Arrayname2[PARAM_ARRAY_SIZE][STRING_SIZE] = {"a","b","c","d","e","f","g","h","i"};

    SQLLEN cbTVPTableNAme=10;
    SQLLEN cbArraycode[10],cbArrayname[10];
    SQLLEN  lTVPRowsUsed;


   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); // from this line rc=-1

   rc = SQLAllocHandle(SQL_HANDLE_STMT,hdbc,&hstmt);


   rc = SQLSetStmtAttr (hstmt, SQL_ATTR_PARAM_BIND_TYPE, SQL_PARAM_BIND_BY_COLUMN, 0);
   SQLUSMALLINT Param_status_array[PARAM_ARRAY_SIZE];
   SQLULEN ParamsProcessed;
   rc=SQLSetStmtAttr(hstmt,SQL_ATTR_PARAMSET_SIZE,(SQLPOINTER*) PARAM_ARRAY_SIZE, 0);
   rc=SQLSetStmtAttr(hstmt,SQL_ATTR_PARAM_STATUS_PTR,Param_status_array,0);
   rc=SQLSetStmtAttr(hstmt,SQL_ATTR_PARAMS_PROCESSED_PTR,&ParamsProcessed,0);

   SQLUSMALLINT newsize=0;


   rc=SQLBindParameter(hstmt,1,SQL_PARAM_INPUT, SQL_C_CHAR, SQL_VARCHAR, STRING_SIZE, 0, Arrayname1, STRING_SIZE,0);
   rc=SQLBindParameter(hstmt,2,SQL_PARAM_INPUT, SQL_C_CHAR, SQL_VARCHAR, STRING_SIZE, 0, Arrayname2, STRING_SIZE,0);

   rc = SQLPrepare(hstmt, (SQLTCHAR *)Statement, SQL_NTS);
   rc=SQLGetStmtAttr(hstmt,SQL_ATTR_PARAMSET_SIZE,&newsize,4,NULL);
   newsize=0;

   rc = SQLExecute(hstmt);
   rc=SQLGetStmtAttr(hstmt,SQL_ATTR_PARAMSET_SIZE,&newsize,4,NULL);


   if(rc==SQL_SUCCESS)
   {
       SQLSMALLINT sNumResults;
       SQLNumResultCols(hstmt,&sNumResults);
       SQLBindCol(hstmt,
                    1,
                    SQL_C_TCHAR,
                    (SQLPOINTER) retValFName,
                    100,
                   0);
   while(true)
        {
          rc=SQLFetch(hstmt);

          if(rc==SQL_SUCCESS || rc==SQL_SUCCESS_WITH_INFO)
          {

             SQLGetData(hstmt,1,SQL_C_CHAR,retValFName,256, &cbLName);
             std::cout << retValFName <<"   "<< std::endl;

          }
          else
          {
              break;
          }
        }
   }
   system("pause");
   return 0;
}

Answer 1

INSERT INTO information1(Wert1,Wert2) VALUES(?,?) 执行单个记录插入。不是批量插入。
所以当你执行

INSERT INTO information1(Wert1,Wert2) VALUES(?,?)  select count(*) as a from information1  

它执行单个插入，然后执行选择。
将两个sql命令分开。对数组进行insert，然后单独进行select。