C++ SQL Database program (Yes it is for school!)

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

Answer 1

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

Answer 2

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;

}

Answer 3

I have a question though.

What if i want to give a parameter in my sql query like this:

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

Employee value is integer in database, my id is also integer but i get a compilation error: "Invalid pointer addition" Thats ok, i understand this.

Everytime i convert id to string, there s no error but i get no results since Employee is integer in database. Parameter id is correctly passing through my method , so there s no error there.

string sqlString = "Select * From Customers Where Customers.Employee = 128"; That works.

My database is MsAccess, any ideas ? What should i do to have results since my Employee value is integer? Is there something wrong in my query?

Answer 4

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