C ++ SQL Database Program

Question

I have the following code that I wrote, but SQLBindCol is not working correctly (of course, I could hang the whole program too!). The connection works, it creates a table in the database, adds excellent records, and they all look good in SQL Enterprise Manager. Therefore, I need help after the comment "Part 3 and 4: Search by criteria." Perhaps I should have made 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; } 

Answer 1

OK, here is the code that now works ...

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

Answer 2

You can get enough diagnostics from SQL so that you can isolate and solve the problem.

You can get an instruction handle to tell you what happened to 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 will need to highlight the variables that you see here, of course ... I suggest that you put a SQLGetDiagRec line after calling SQLGetDiagRec and assign a breakpoint to it. When it breaks there, you can look at the state value: it will be aligned with the Diagnostics section here: http://msdn.microsoft.com/en-us/library/ms713611(VS.85).aspx

Answer 3

You said you were getting errors:

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

That should be obvious, sorry lol. Ident is a whole, of course. But when you evaluate a string, it appears like this:

string sqlString = "Select * From clients, where Customers.Employee = '100'";

Notice what happened? You have single quotes around it. Therefore, no matter what data type you are using, single quotes force SQL to treat it as a string. So just take them out like this:

string sqlString = "Select * From customers, where Customers.Employee =" + id + ""; ,,,, Or, string sqlString = "Select * From clients, where Customers.Employee =" + id;

---

My question is ... Can you explain how the write cycle in C ++ works? For example, a user enters a username in strUName, and you want to find out if that username is in the "Users" database table. SQL is simple enough (select * from Users, where [UName] = '"+ strUName +"'; But how do you actually run it in C ++ and understand?

I see SQLStmt, I see that it runs using Direct. Then I see some SQLBindCol garbage, and then an infinite loop until the evaluation comes. But I don’t quite understand what is happening (it’s easy for any other language for me, but I am new to C ++.

Answer 4

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