C++ SQL Database program

Question

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?

#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 $30K" << 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;
}

if(!retcode) should be if(retcode != SQL_SUCCESS && retcode != SQL_SUCCESS_WITH_INFO) — Peter Olsson
– Peter Olsson, Commented Jan 20, 2009 at 15:40

AHF · Accepted Answer · 2013-11-16 13:36:50Z

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 $30K 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;
}

antik · Accepted Answer · 2008-10-01 17:26:34Z

2

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

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

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 SQLGetDiagRec call 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

edited Oct 1, 2008 at 17:26

answered Sep 30, 2008 at 14:48

antik

5,3601 gold badge36 silver badges47 bronze badges

2 Comments

PapaDaniel Over a year ago

The SQLExecDirect has to be before the SQLBindCol according to all the doc including the example here: msdn.microsoft.com/en-us/library/ms711010(VS.85).aspx Thanks!

antik Over a year ago

Interesting: I missed that. That means SQLBindCol can be called either before after SQLExecute: I bind before calling exec without problems on the very project I'm working on now. Good catch though!

Suamere · Accepted Answer · 2009-08-30 03:34:21Z

You said you were getting errors with:

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:

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:

string sqlString = "Select * From Customers Where Customers.Employee = " + id + ""; . . . . . . . Or, string 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?

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

Satheesh · Accepted Answer · 2015-01-20 12:05:27Z

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 $30K 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;
    }

Collectives™ on Stack Overflow

C++ SQL Database program

4 Answers 4

Comments

2 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related