当前位置：theitroad>C++ SQL 数据库程序

C++ SQL 数据库程序

声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow 原文地址: http://stackoverflow.com/questions/153354/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me): StackOverFlow

提示：将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间：2020-08-27 13:18:16 　来源:igfitidea点击:

C++ SQL Database program

c++sqldatabase

提问by

I have the following code that I wrote but it the SQLBindCol does not seem to work correctly (of course I could have screwed up the whole program too!.) The connection works, it creates the table in the DB, addes the record fine and they all look good in SQL Enterprise Manager. So what I need help with is after the comment "Part 3 & 4: Searchs based on criteria." Perhaps I should have done this assignment completely different or is this an acceptable method?

我编写了以下代码，但 SQLBindCol 似乎无法正常工作（当然我也可以搞砸整个程序！）连接有效，它在数据库中创建了表，添加了记录，并且它们在 SQL 企业管理器中看起来都不错。所以我需要帮助的是在评论“第 3 部分和第 4 部分：基于条件的搜索”之后。也许我应该完全不同地完成这项任务，或者这是一种可以接受的方法吗？

#include <iostream>
#include <cstdio>
#include <string>

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

using namespace std;    // to save us having to type std::

const int MAX_CHAR = 1024;

int main ( )
{
   SQLCHAR   SQLStmt[MAX_CHAR];
   char      strSQL[MAX_CHAR];
   char   chrTemp;

   SQLVARCHAR rtnFirstName[50];
   SQLVARCHAR rtnLastName[50];
   SQLVARCHAR rtnAddress[30];
   SQLVARCHAR rtnCity[30];
   SQLVARCHAR rtnState[3];
   SQLDOUBLE  rtnSalary;
   SQLVARCHAR rtnGender[1];
   SQLINTEGER rtnAge;

   // Get a handle to the database

   SQLHENV EnvironmentHandle;
   RETCODE retcode = SQLAllocHandle( SQL_HANDLE_ENV, SQL_NULL_HANDLE, &EnvironmentHandle );

   // Set the SQL environment flags

   retcode = SQLSetEnvAttr( EnvironmentHandle, SQL_ATTR_ODBC_VERSION, (SQLPOINTER) SQL_OV_ODBC3, SQL_IS_INTEGER );

   // create handle to the SQL database

   SQLHDBC ConnHandle;
   retcode = SQLAllocHandle( SQL_HANDLE_DBC, EnvironmentHandle, &ConnHandle );

   // Open the database using a System DSN

   retcode = SQLDriverConnect(ConnHandle, 
   NULL, 
   (SQLCHAR*)"DSN=PRG411;UID=myUser;PWD=myPass;", 
   SQL_NTS,
   NULL, 
   SQL_NTS, 
   NULL, 
   SQL_DRIVER_NOPROMPT);
   if (!retcode) 
   {
      cout << "SQLConnect() Failed";
   }
   else
   {
      // create a SQL Statement variable

      SQLHSTMT StatementHandle;
      retcode = SQLAllocHandle(SQL_HANDLE_STMT, ConnHandle, &StatementHandle);

      // Part 1: Create the Employee table (Database)

      do
      {
         cout << "Create the new table? ";
         cin >> chrTemp;
      } while (cin.fail());

      if (chrTemp == 'y' || chrTemp == 'Y')
      {
         strcpy((char *) SQLStmt, "CREATE TABLE [dbo].[Employee]([pkEmployeeID] [int] IDENTITY(1,1) NOT NULL,[FirstName] [varchar](50) NOT NULL,[LastName] [varchar](50) NOT NULL,[Address] [varchar](30) NOT NULL,[City] [varchar](30) NOT NULL,[State] [varchar](3) NOT NULL, [Salary] [double] NOT NULL,[Gender] [varchar](1) NOT NULL,  [Age] [int] NOT NULL, CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED ([pkEmployeeID] ASC)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]) ON [PRIMARY]");
         retcode = SQLExecDirect(StatementHandle, SQLStmt, SQL_NTS);
      }

      // Part 2: Hardcode records into the table

      do
      {
         cout << "Add records to the table? ";
         cin >> chrTemp;
      } while (cin.fail());

      if (chrTemp == 'y' || chrTemp == 'Y')
      {
         strcpy((char *) SQLStmt, "INSERT INTO employee([FirstName], [LastName], [Address], [City], [State], [Salary], [Gender],[Age]) VALUES ('Mike','Slentz','123 Torrey Dr.','North Clairmont','CA', 48000.00 ,'M',34)");
         retcode = SQLExecDirect(StatementHandle, SQLStmt, SQL_NTS);

         strcpy((char *) SQLStmt, "INSERT INTO employee([FirstName], [LastName], [Address], [City], [State], [Salary], [Gender],[Age]) VALUES ('Sue','Vander Hayden','46 East West St.','San Diego','CA', 36000.00 ,'F',28)");
         retcode = SQLExecDirect(StatementHandle, SQLStmt, SQL_NTS);

         strcpy((char *) SQLStmt, "INSERT INTO employee([FirstName], [LastName], [Address], [City], [State], [Salary], [Gender],[Age]) VALUES ('Sharon','Stonewall','756 West Olive Garden Way','Plymouth','MA', 56000.00 ,'F',58)");
         retcode = SQLExecDirect(StatementHandle, SQLStmt, SQL_NTS);

         strcpy((char *) SQLStmt, "INSERT INTO employee([FirstName], [LastName], [Address], [City], [State], [Salary], [Gender],[Age]) VALUES ('James','Bartholemew','777 Praying Way','Falls Church','VA', 51000.00 ,'M',45)");
         retcode = SQLExecDirect(StatementHandle, SQLStmt, SQL_NTS);

         strcpy((char *) SQLStmt, "INSERT INTO employee([FirstName], [LastName], [Address], [City], [State], [Salary], [Gender],[Age]) VALUES ('Joe','Smith','111 North 43rd Ave','Peoria','AZ', 44000.00 ,'M', 40)");
         retcode = SQLExecDirect(StatementHandle, SQLStmt, SQL_NTS);

         strcpy((char *) SQLStmt, "INSERT INTO employee([FirstName], [LastName], [Address], [City], [State], [Salary], [Gender],[Age]) VALUES ('Michael','Smith','20344 North Swan Park','Phoenix','AZ', 24000.00 ,'M', 40)");
         retcode = SQLExecDirect(StatementHandle, SQLStmt, SQL_NTS);

         strcpy((char *) SQLStmt, "INSERT INTO employee([FirstName], [LastName], [Address], [City], [State], [Salary], [Gender],[Age]) VALUES ('Jennifer','Jones','123 West North Ave','Flagstaff','AZ', 40000.00 ,'F', 40)");
         retcode = SQLExecDirect(StatementHandle, SQLStmt, SQL_NTS);

         strcpy((char *) SQLStmt, "INSERT INTO employee([FirstName], [LastName], [Address], [City], [State], [Salary], [Gender],[Age]) VALUES ('Cora','York','33rd Park Way Drive','Mayville','MI', 30000.00 ,'F', 61)");
         retcode = SQLExecDirect(StatementHandle, SQLStmt, SQL_NTS);

         strcpy((char *) SQLStmt, "INSERT INTO employee([FirstName], [LastName], [Address], [City], [State], [Salary], [Gender],[Age]) VALUES ('Tom','Jefferson','234 Friendship Way','Battle Creek','MI', 41000.00 ,'M', 31)");
         retcode = SQLExecDirect(StatementHandle, SQLStmt, SQL_NTS);
      }

      // Part 3 & 4: Searchs based on criteria

      do
      {
         cout << "1. Display all records in the database" << endl;
         cout << "2. Display all records with age greater than 40" << endl;
         cout << "3. Display all records with salary over K" << endl;
         cout << "4. Exit" << endl << endl;

         do
         {
            cout << "Please enter a selection: ";
            cin >> chrTemp;
         } while (cin.fail());

         if (chrTemp == '1')
         {
            strcpy((char *) SQLStmt, "SELECT [FirstName], [LastName], [Address], [City], [State], [Salary], [Gender],[Age] FROM EMPLOYEE");
         }
         else if (chrTemp == '2')
         {
            strcpy((char *) SQLStmt, "SELECT [FirstName], [LastName], [Address], [City], [State], [Salary], [Gender],[Age] FROM EMPLOYEE WHERE [AGE] > 40");
         }
         else if (chrTemp == '3')
         {
            strcpy((char *) SQLStmt, "SELECT [FirstName], [LastName], [Address], [City], [State], [Salary], [Gender],[Age] FROM EMPLOYEE WHERE [Salary] > 30000");
         }

         if (chrTemp == '1'  || chrTemp == '2' || chrTemp == '3')
         {
            retcode = SQLExecDirect(StatementHandle, SQLStmt, SQL_NTS);

            SQLBindCol(StatementHandle, 1, SQL_C_CHAR, &rtnFirstName, sizeof(rtnFirstName), NULL );
            SQLBindCol(StatementHandle, 2, SQL_C_CHAR, &rtnLastName, sizeof(rtnLastName), NULL );
            SQLBindCol(StatementHandle, 3, SQL_C_CHAR, &rtnAddress, sizeof(rtnAddress), NULL );
            SQLBindCol(StatementHandle, 4, SQL_C_CHAR, &rtnCity, sizeof(rtnCity), NULL );
            SQLBindCol(StatementHandle, 5, SQL_C_CHAR, &rtnState, sizeof(rtnState), NULL );
            SQLBindCol(StatementHandle, 6, SQL_C_DOUBLE, &rtnSalary, sizeof(rtnSalary), NULL );
            SQLBindCol(StatementHandle, 7, SQL_C_CHAR, &rtnGender, sizeof(rtnGender), NULL );
            SQLBindCol(StatementHandle, 8, SQL_C_NUMERIC, &rtnAge, sizeof(rtnAge), NULL );

            for(;;) 
            {
               retcode = SQLFetch(StatementHandle);
               if (retcode == SQL_NO_DATA_FOUND) break;

               cout << rtnFirstName << " " << rtnLastName << " " << rtnAddress << " " << rtnCity << " " << rtnState << " " << rtnSalary << " " << rtnGender << "" << rtnAge << endl;
            }
         }
      } while (chrTemp != '4');

      SQLFreeStmt(StatementHandle, SQL_CLOSE );
      SQLFreeConnect(ConnHandle);
      SQLFreeEnv(EnvironmentHandle);

      printf( "Done.\n" );
   }

   return 0;
}

回答by

OK, here is the code now working...

好的，这是现在工作的代码......

using namespace std;    // to save us having to type std::

const int MAX_CHAR = 1024;

int main ( )
{
     SQLSMALLINT     RecNumber;
     SQLCHAR *       SQLState;
     SQLINTEGER *    NativeErrorPtr;
     SQLCHAR *       MessageText;
     SQLSMALLINT     BufferLength;
     SQLSMALLINT *   TextLengthPtr;

    SQLCHAR   SQLStmt[MAX_CHAR];
    char      strSQL[MAX_CHAR];
    char      chrTemp;

    SQLVARCHAR rtnFirstName[50];
    SQLVARCHAR rtnLastName[50];
    SQLVARCHAR rtnAddress[30];
    SQLVARCHAR rtnCity[30];
    SQLVARCHAR rtnState[3];
    SQLDOUBLE  rtnSalary;
    SQLVARCHAR rtnGender[2];
    SQLINTEGER rtnAge;

    // Get a handle to the database

    SQLHENV EnvironmentHandle;
    RETCODE retcode = SQLAllocHandle( SQL_HANDLE_ENV, SQL_NULL_HANDLE, &EnvironmentHandle );

    // Set the SQL environment flags

    retcode = SQLSetEnvAttr( EnvironmentHandle, SQL_ATTR_ODBC_VERSION, (SQLPOINTER) SQL_OV_ODBC3, SQL_IS_INTEGER );

    // create handle to the SQL database

    SQLHDBC ConnHandle;
    retcode = SQLAllocHandle( SQL_HANDLE_DBC, EnvironmentHandle, &ConnHandle );

    // Open the database using a System DSN

    retcode = SQLDriverConnect(ConnHandle, 
        NULL, 
        (SQLCHAR*)"DSN=PRG411;UID=myUser;PWD=myPass;", 
        SQL_NTS,
        NULL, 
        SQL_NTS, 
        NULL, 
        SQL_DRIVER_NOPROMPT);
    if (!retcode) 
    {
        cout << "SQLConnect() Failed";
    }
    else
    {
        // create a SQL Statement variable

        SQLHSTMT StatementHandle;
        retcode = SQLAllocHandle(SQL_HANDLE_STMT, ConnHandle, &StatementHandle);

        // Part 1: Create the Employee table (Database)

        do
        {
            cout << "Create the new table? ";
            cin >> chrTemp;
        } while (cin.fail());

        if (chrTemp == 'y' || chrTemp == 'Y')
        {
            strcpy((char *) SQLStmt, "CREATE TABLE [dbo].[Employee]([pkEmployeeID] [int] IDENTITY(1,1) NOT NULL,[FirstName] [varchar](50) NOT NULL,[LastName] [varchar](50) NOT NULL,[Address] [varchar](30) NOT NULL,[City] [varchar](30) NOT NULL,[State] [varchar](3) NOT NULL, [Salary] [decimal] NOT NULL,[Gender] [varchar](1) NOT NULL,  [Age] [int] NOT NULL, CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED ([pkEmployeeID] ASC)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]) ON [PRIMARY]");
            retcode = SQLExecDirect(StatementHandle, SQLStmt, SQL_NTS);
        }

        // Part 2: Hardcode records into the table

        do
        {
            cout << "Add records to the table? ";
            cin >> chrTemp;
        } while (cin.fail());

        if (chrTemp == 'y' || chrTemp == 'Y')
        {
            strcpy((char *) SQLStmt, "INSERT INTO employee([FirstName], [LastName], [Address], [City], [State], [Salary], [Gender],[Age]) VALUES ('Mike','Slentz','123 Torrey Dr.','North Clairmont','CA', 48000.00 ,'M',34)");
            retcode = SQLExecDirect(StatementHandle, SQLStmt, SQL_NTS);

            strcpy((char *) SQLStmt, "INSERT INTO employee([FirstName], [LastName], [Address], [City], [State], [Salary], [Gender],[Age]) VALUES ('Sue','Vander Hayden','46 East West St.','San Diego','CA', 36000.00 ,'F',28)");
            retcode = SQLExecDirect(StatementHandle, SQLStmt, SQL_NTS);

            strcpy((char *) SQLStmt, "INSERT INTO employee([FirstName], [LastName], [Address], [City], [State], [Salary], [Gender],[Age]) VALUES ('Sharon','Stonewall','756 West Olive Garden Way','Plymouth','MA', 56000.00 ,'F',58)");
            retcode = SQLExecDirect(StatementHandle, SQLStmt, SQL_NTS);

            strcpy((char *) SQLStmt, "INSERT INTO employee([FirstName], [LastName], [Address], [City], [State], [Salary], [Gender],[Age]) VALUES ('James','Bartholemew','777 Praying Way','Falls Church','VA', 51000.00 ,'M',45)");
            retcode = SQLExecDirect(StatementHandle, SQLStmt, SQL_NTS);

            strcpy((char *) SQLStmt, "INSERT INTO employee([FirstName], [LastName], [Address], [City], [State], [Salary], [Gender],[Age]) VALUES ('Joe','Smith','111 North 43rd Ave','Peoria','AZ', 44000.00 ,'M', 40)");
            retcode = SQLExecDirect(StatementHandle, SQLStmt, SQL_NTS);

            strcpy((char *) SQLStmt, "INSERT INTO employee([FirstName], [LastName], [Address], [City], [State], [Salary], [Gender],[Age]) VALUES ('Michael','Smith','20344 North Swan Park','Phoenix','AZ', 24000.00 ,'M', 40)");
            retcode = SQLExecDirect(StatementHandle, SQLStmt, SQL_NTS);

            strcpy((char *) SQLStmt, "INSERT INTO employee([FirstName], [LastName], [Address], [City], [State], [Salary], [Gender],[Age]) VALUES ('Jennifer','Jones','123 West North Ave','Flagstaff','AZ', 40000.00 ,'F', 40)");
            retcode = SQLExecDirect(StatementHandle, SQLStmt, SQL_NTS);

            strcpy((char *) SQLStmt, "INSERT INTO employee([FirstName], [LastName], [Address], [City], [State], [Salary], [Gender],[Age]) VALUES ('Cora','York','33rd Park Way Drive','Mayville','MI', 30000.00 ,'F', 61)");
            retcode = SQLExecDirect(StatementHandle, SQLStmt, SQL_NTS);

            strcpy((char *) SQLStmt, "INSERT INTO employee([FirstName], [LastName], [Address], [City], [State], [Salary], [Gender],[Age]) VALUES ('Tom','Jefferson','234 Friendship Way','Battle Creek','MI', 41000.00 ,'M', 31)");
            retcode = SQLExecDirect(StatementHandle, SQLStmt, SQL_NTS);
        }

        // Part 3 & 4: Searchs based on criteria

        do
        {
            cout << "1. Display all records in the database" << endl;
            cout << "2. Display all records with age 40 or over" << endl;
            cout << "3. Display all records with salary K or over" << endl;
            cout << "4. Exit" << endl << endl;

            do
            {
                cout << "Please enter a selection: ";
                cin >> chrTemp;
            } while (cin.fail());

            if (chrTemp == '1')
            {
                strcpy((char *) SQLStmt, "SELECT [FirstName], [LastName], [Address], [City], [State], [Salary], [Gender],[Age] FROM EMPLOYEE");
            }
            else if (chrTemp == '2')
            {
                strcpy((char *) SQLStmt, "SELECT [FirstName], [LastName], [Address], [City], [State], [Salary], [Gender],[Age] FROM EMPLOYEE WHERE [AGE] >= 40");
            }
            else if (chrTemp == '3')
            {
                strcpy((char *) SQLStmt, "SELECT [FirstName], [LastName], [Address], [City], [State], [Salary], [Gender],[Age] FROM EMPLOYEE WHERE [Salary] >= 30000");
            }

            if (chrTemp == '1'  || chrTemp == '2' || chrTemp == '3')
            {
                retcode = SQLExecDirect(StatementHandle, SQLStmt, SQL_NTS);

                //SQLGetDiagRec(SQL_HANDLE_STMT, StatementHandle, RecNumber, SQLState, NativeErrorPtr, (SQLCHAR*) MessageText, (SQLINTEGER) BufferLength, (SQLSMALLINT*) &TextLengthPtr);

                SQLBindCol(StatementHandle, 1, SQL_C_CHAR, &rtnFirstName, sizeof(rtnFirstName), NULL );
                SQLBindCol(StatementHandle, 2, SQL_C_CHAR, &rtnLastName, sizeof(rtnLastName), NULL );
                SQLBindCol(StatementHandle, 3, SQL_C_CHAR, &rtnAddress, sizeof(rtnAddress), NULL );
                SQLBindCol(StatementHandle, 4, SQL_C_CHAR, &rtnCity, sizeof(rtnCity), NULL );
                SQLBindCol(StatementHandle, 5, SQL_C_CHAR, &rtnState, sizeof(rtnState), NULL );
                SQLBindCol(StatementHandle, 6, SQL_C_DOUBLE, &rtnSalary, sizeof(rtnSalary), NULL );
                SQLBindCol(StatementHandle, 7, SQL_C_CHAR, &rtnGender, sizeof(rtnGender), NULL );
                SQLBindCol(StatementHandle, 8, SQL_C_LONG, &rtnAge, sizeof(rtnAge), NULL );

                for(;;) 
                {
                    retcode = SQLFetch(StatementHandle);
                    if (retcode == SQL_NO_DATA_FOUND) break;

                    cout << rtnFirstName << " " << rtnLastName << " " << rtnAddress << " " << rtnCity << " " << rtnState << " " << rtnSalary << " " << rtnGender << " " << rtnAge << endl;
                }

                SQLFreeStmt(StatementHandle, SQL_CLOSE);

            }
        } while (chrTemp != '4');

        SQLFreeStmt(StatementHandle, SQL_CLOSE );
        SQLFreeHandle(SQL_HANDLE_STMT, StatementHandle);

        SQLDisconnect(ConnHandle);

        SQLFreeHandle(SQL_HANDLE_DBC, ConnHandle);
        SQLFreeHandle(SQL_HANDLE_ENV, EnvironmentHandle);

        printf( "Done.\n" );
    }

    return 0;
}

回答by antik

You can get enough diagnostic out of SQL that you may be able to isolate and resolve the issue.

您可以从 SQL 中获得足够的诊断信息，以便您能够隔离和解决问题。

You can get the statement handle to tell you what has gone wrong with it by calling when SQLExecDirect returns something other than SQL_SUCCESSor SQL_SUCCESS_WITH_INFO

您可以通过在 SQLExecDirect 返回的不是SQL_SUCCESS或SQL_SUCCESS_WITH_INFO

SQLGetDiagRec( SQL_HANDLE_STMT, StatementHandle, req, state, &error, (SQLCHAR*) buffer, (SQLINTEGER) MAX_CHAR, (SQLSMALLINT*) &output_length );

SQLGetDiagRec( SQL_HANDLE_STMT, StatementHandle, req, state, &error, (SQLCHAR*) buffer, (SQLINTEGER) MAX_CHAR, (SQLSMALLINT*) &output_length );

You'll have to allocate the variables you see here of course... I suggest you put a throw away line after the SQLGetDiagReccall and assign a breakpoint to it. When it breaks there, you can look at state's value: that will align with the "Diagnostics" section here: http://msdn.microsoft.com/en-us/library/ms713611(VS.85).aspx

当然，您必须分配您在此处看到的变量...我建议您在SQLGetDiagRec调用后放置一个丢弃行并为其分配一个断点。当它在那里中断时，您可以查看state的值：这将与此处的“诊断”部分一致：http: //msdn.microsoft.com/en-us/library/ms713611(VS.85).aspx

回答by antik

You said you were getting errors with:

您说您遇到以下错误：

string sqlString = "Select * From Customers Where Customers.Employee = '" +id+ "'";

string sqlString = "Select * From Customers Where Customers.Employee = '" +id+ "'";

It should be obvious, sorry, lol. The id is integer, sure. But when you evaluate the string, it comes up like so:

这应该是显而易见的，对不起，哈哈。id 是整数，当然。但是当你对字符串求值时，结果是这样的：

string sqlString = "Select * From Customers Where Customers.Employee = '100'";

string sqlString = "Select * From Customers Where Customers.Employee = '100'";

Notice what's wrong? You have single quotes around it. So no matter what data type you are using, the single quotes makes the SQL treat it as a string. So just take them out like so:

注意有什么问题吗？你在它周围有单引号。因此，无论您使用什么数据类型，单引号都会使 SQL 将其视为字符串。所以就像这样把它们拿出来：

string sqlString = "Select * From Customers Where Customers.Employee = " + id + ""; . . . . . . . Or, string sqlString = "Select * From Customers Where Customers.Employee = " + id;

string sqlString = "Select * From Customers Where Customers.Employee = " + id + ""; . . . . . . . 或者，字符串 sqlString = "Select * From Customers Where Customers.Employee = " + id;

My question is this... Can you explain how looping through records in C++ works? For example, the user inputs a user name to strUName, and you wanna see if that user name is in the database table Users. The SQL is easy enough (select * from Users where [UName] = '" + strUName + "'; But How do you actually execute it in C++ and figure it out?

我的问题是……你能解释一下在 C++ 中循环记录是如何工作的吗？例如，用户向 strUName 输入一个用户名，您想查看该用户名是否在数据库表 Users 中。SQL 很简单（select * from Users where [UName] = '" + strUName + "'; 但是你如何在 C++ 中实际执行它并弄清楚呢？

I see the SQLStmt, I see it being executed using Direct. I then see some SQLBindCol junk and then an infinite loop until break evals. But I don't quite get what's happening (This is easy for any other language for me, but I'm new to C++.

我看到 SQLStmt，我看到它正在使用 Direct 执行。然后我看到一些 SQLBindCol 垃圾，然后是一个无限循环，直到中断 evals。但我不太明白发生了什么（这对我来说任何其他语言都很容易，但我是 C++ 的新手。

回答by Satheesh

Here is the corrected code.

这是更正后的代码。

    #include <iostream>
    #include <cstdio>
    #include <string>

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

    using namespace std;    // to save us having to type std::

    const int MAX_CHAR = 1024;

    int main()
    {
        SQLSMALLINT     RecNumber;
        SQLCHAR *       SQLState;
        SQLINTEGER *    NativeErrorPtr;
        SQLCHAR *       MessageText;
        SQLSMALLINT     BufferLength;
        SQLSMALLINT *   TextLengthPtr;

        SQLCHAR   SQLStmt[MAX_CHAR];
        char      strSQL[MAX_CHAR];
        char      chrTemp;

        SQLVARCHAR rtnFirstName[50];
        SQLVARCHAR rtnLastName[50];
        SQLVARCHAR rtnAddress[30];
        SQLVARCHAR rtnCity[30];
        SQLVARCHAR rtnState[3];
        SQLDOUBLE  rtnSalary;
        SQLVARCHAR rtnGender[2];
        SQLINTEGER rtnAge;

        // Get a handle to the database

        SQLHENV EnvironmentHandle;
        RETCODE retcode = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &EnvironmentHandle);

        // Set the SQL environment flags
        HWND desktopHandle = GetDesktopWindow();
        retcode = SQLSetEnvAttr(EnvironmentHandle, SQL_ATTR_ODBC_VERSION, (SQLPOINTER)SQL_OV_ODBC3, SQL_IS_INTEGER);

        // create handle to the SQL database

        SQLHDBC ConnHandle;
        retcode = SQLAllocHandle(SQL_HANDLE_DBC, EnvironmentHandle, &ConnHandle);
        SQLSetConnectAttr(ConnHandle, SQL_LOGIN_TIMEOUT, (SQLPOINTER)5, 0);
        // Open the database using a System DSN

        retcode = SQLDriverConnect(ConnHandle,
            desktopHandle,
            (SQLCHAR*)"DSN=PRG411;UID=myUser;PWD=myPass;",
            SQL_NTS,
            NULL,
            SQL_NTS,
            NULL,
            SQL_DRIVER_NOPROMPT);
        if (retcode != SQL_SUCCESS || retcode != SQL_SUCCESS_WITH_INFO)
        {
            cout << "SQLConnect() Failed";
        }   
        else
        {
            // create a SQL Statement variable

            SQLHSTMT StatementHandle;
            retcode = SQLAllocHandle(SQL_HANDLE_STMT, ConnHandle, &StatementHandle);

            // Part 1: Create the Employee table (Database)

            do
            {
                cout << "Create the new table? ";
                cin >> chrTemp;
            } while (cin.fail());

            if (chrTemp == 'y' || chrTemp == 'Y')
            {
                strcpy_s((char *)SQLStmt,1024,  "CREATE TABLE [dbo].[Employee]([pkEmployeeID] [int] IDENTITY(1,1) NOT NULL,[FirstName] [varchar](50) NOT NULL,[LastName] [varchar](50) NOT NULL,[Address] [varchar](30) NOT NULL,[City] [varchar](30) NOT NULL,[State] [varchar](3) NOT NULL, [Salary] [decimal] NOT NULL,[Gender] [varchar](1) NOT NULL,  [Age] [int] NOT NULL, CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED ([pkEmployeeID] ASC)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]) ON [PRIMARY]");
                retcode = SQLExecDirect(StatementHandle, SQLStmt, SQL_NTS);
            }

            // Part 2: Hardcode records into the table

            do
            {
                cout << "Add records to the table? ";
                cin >> chrTemp;
            } while (cin.fail());

            if (chrTemp == 'y' || chrTemp == 'Y')
            {
                strcpy_s((char *)SQLStmt,1024,  "INSERT INTO employee([FirstName], [LastName], [Address], [City], [State], [Salary], [Gender],[Age]) VALUES ('Mike','Slentz','123 Torrey Dr.','North Clairmont','CA', 48000.00 ,'M',34)");
                retcode = SQLExecDirect(StatementHandle, SQLStmt, SQL_NTS);

                strcpy_s((char *)SQLStmt,1024,  "INSERT INTO employee([FirstName], [LastName], [Address], [City], [State], [Salary], [Gender],[Age]) VALUES ('Sue','Vander Hayden','46 East West St.','San Diego','CA', 36000.00 ,'F',28)");
                retcode = SQLExecDirect(StatementHandle, SQLStmt, SQL_NTS);

                strcpy_s((char *)SQLStmt,1024,  "INSERT INTO employee([FirstName], [LastName], [Address], [City], [State], [Salary], [Gender],[Age]) VALUES ('Sharon','Stonewall','756 West Olive Garden Way','Plymouth','MA', 56000.00 ,'F',58)");
                retcode = SQLExecDirect(StatementHandle, SQLStmt, SQL_NTS);

                strcpy_s((char *)SQLStmt,1024,  "INSERT INTO employee([FirstName], [LastName], [Address], [City], [State], [Salary], [Gender],[Age]) VALUES ('James','Bartholemew','777 Praying Way','Falls Church','VA', 51000.00 ,'M',45)");
                retcode = SQLExecDirect(StatementHandle, SQLStmt, SQL_NTS);

                strcpy_s((char *)SQLStmt,1024,  "INSERT INTO employee([FirstName], [LastName], [Address], [City], [State], [Salary], [Gender],[Age]) VALUES ('Joe','Smith','111 North 43rd Ave','Peoria','AZ', 44000.00 ,'M', 40)");
                retcode = SQLExecDirect(StatementHandle, SQLStmt, SQL_NTS);

                strcpy_s((char *)SQLStmt,1024,  "INSERT INTO employee([FirstName], [LastName], [Address], [City], [State], [Salary], [Gender],[Age]) VALUES ('Michael','Smith','20344 North Swan Park','Phoenix','AZ', 24000.00 ,'M', 40)");
                retcode = SQLExecDirect(StatementHandle, SQLStmt, SQL_NTS);

                strcpy_s((char *)SQLStmt,1024,  "INSERT INTO employee([FirstName], [LastName], [Address], [City], [State], [Salary], [Gender],[Age]) VALUES ('Jennifer','Jones','123 West North Ave','Flagstaff','AZ', 40000.00 ,'F', 40)");
                retcode = SQLExecDirect(StatementHandle, SQLStmt, SQL_NTS);

                strcpy_s((char *)SQLStmt,1024,  "INSERT INTO employee([FirstName], [LastName], [Address], [City], [State], [Salary], [Gender],[Age]) VALUES ('Cora','York','33rd Park Way Drive','Mayville','MI', 30000.00 ,'F', 61)");
                retcode = SQLExecDirect(StatementHandle, SQLStmt, SQL_NTS);

                strcpy_s((char *)SQLStmt,1024,  "INSERT INTO employee([FirstName], [LastName], [Address], [City], [State], [Salary], [Gender],[Age]) VALUES ('Tom','Jefferson','234 Friendship Way','Battle Creek','MI', 41000.00 ,'M', 31)");
                retcode = SQLExecDirect(StatementHandle, SQLStmt, SQL_NTS);
            }

            // Part 3 & 4: Searchs based on criteria

            do
            {
                cout << "1. Display all records in the database" << endl;
                cout << "2. Display all records with age 40 or over" << endl;
                cout << "3. Display all records with salary K or over" << endl;
                cout << "4. Exit" << endl << endl;

                do
                {
                    cout << "Please enter a selection: ";
                    cin >> chrTemp;
                } while (cin.fail());

                if (chrTemp == '1')
                {
                    strcpy_s((char *)SQLStmt,1024,  "SELECT [FirstName], [LastName], [Address], [City], [State], [Salary], [Gender],[Age] FROM EMPLOYEE");
                }
                else if (chrTemp == '2')
                {
                    strcpy_s((char *)SQLStmt,1024,  "SELECT [FirstName], [LastName], [Address], [City], [State], [Salary], [Gender],[Age] FROM EMPLOYEE WHERE [AGE] >= 40");
                }
                else if (chrTemp == '3')
                {
                    strcpy_s((char *)SQLStmt,1024,  "SELECT [FirstName], [LastName], [Address], [City], [State], [Salary], [Gender],[Age] FROM EMPLOYEE WHERE [Salary] >= 30000");
                }

                if (chrTemp == '1' || chrTemp == '2' || chrTemp == '3')
                {
                    retcode = SQLExecDirect(StatementHandle, SQLStmt, SQL_NTS);

                    //SQLGetDiagRec(SQL_HANDLE_STMT, StatementHandle, RecNumber, SQLState, NativeErrorPtr, (SQLCHAR*) MessageText, (SQLINTEGER) BufferLength, (SQLSMALLINT*) &TextLengthPtr);

                    SQLBindCol(StatementHandle, 1, SQL_C_CHAR, &rtnFirstName, sizeof(rtnFirstName), NULL);
                    SQLBindCol(StatementHandle, 2, SQL_C_CHAR, &rtnLastName, sizeof(rtnLastName), NULL);
                    SQLBindCol(StatementHandle, 3, SQL_C_CHAR, &rtnAddress, sizeof(rtnAddress), NULL);
                    SQLBindCol(StatementHandle, 4, SQL_C_CHAR, &rtnCity, sizeof(rtnCity), NULL);
                    SQLBindCol(StatementHandle, 5, SQL_C_CHAR, &rtnState, sizeof(rtnState), NULL);
                    SQLBindCol(StatementHandle, 6, SQL_C_DOUBLE, &rtnSalary, sizeof(rtnSalary), NULL);
                    SQLBindCol(StatementHandle, 7, SQL_C_CHAR, &rtnGender, sizeof(rtnGender), NULL);
                    SQLBindCol(StatementHandle, 8, SQL_C_LONG, &rtnAge, sizeof(rtnAge), NULL);

                    for (;;)
                    {
                        retcode = SQLFetch(StatementHandle);
                        if (retcode == SQL_NO_DATA_FOUND) break;

                        cout << rtnFirstName << " " << rtnLastName << " " << rtnAddress << " " << rtnCity << " " << rtnState << " " << rtnSalary << " " << rtnGender << " " << rtnAge << endl;
                    }

                    SQLFreeStmt(StatementHandle, SQL_CLOSE);

                }
            } while (chrTemp != '4');

            SQLFreeStmt(StatementHandle, SQL_CLOSE);
            SQLFreeHandle(SQL_HANDLE_STMT, StatementHandle);

            SQLDisconnect(ConnHandle);

            SQLFreeHandle(SQL_HANDLE_DBC, ConnHandle);
            SQLFreeHandle(SQL_HANDLE_ENV, EnvironmentHandle);

            printf("Done.\n");
        }

        return 0;
    }

相关推荐

C++ 在 CMake 中使用预编译的头文件

C++ 在 CMake 中使用预编译的头文件

C++ CMake中的平台检测

C++ CMake中的平台检测

C++ 循环缓冲区的线程安全实现

C++ 循环缓冲区的线程安全实现

在 C++ 中存储货币值的最佳方法

在 C++ 中存储货币值的最佳方法

相关推荐
最近更新
标签