Ultra Simple DB Class Interface for ODBC

Introduction—Opening the Database

The ODBC API has a lot of power and flexibility, but sometimes you just want to get the data without all the effort. Hence, these simple class wrappers. In addition, from insulating you from having to bind buffers and or figure out data type widths and conversions, they have a fairly simple interface. They operate on the premise of dealing with everything as ASCII character data. Oversimplification? You be the judge. Let's get into what some sample code and see how easy it is to get to your data.

First we'll open the database...

   CSQLDatabase db;
   if ( ! db.Open( "MPHSample" ) ) 
   {
      cout << "couldn't open data source name" << endl;
      return 0;
   }

Extracting Data

Next, we'll extract some data from a known table. I kept it to those with last names starting with A so it would be under a screen full.

I'll bet you're already thinking that this should look like an STL container with an iterator. You're right; I'm probably going to write something that looks like that soon. MySQL has a beautiful design that I would probably emulate.

Still, this makes extraction pretty easy. The only thing to watch out for is the ordering; the variables must follow the order of the original SQL statement and be extracted sequentially from the beginning.

   CSQLRecordset rs( db );
   rs << "select name, address1, city, state,"
      " zip from provider";
   rs.SetWhere( "name < 'B'" );    // It's 1 screen full
   rs.SQLExecDirect();
   while ( rs.SQLFetch() )
   {
      int n = 1;    // order is crucial;
                    // hence, the counter
      string sname     = rs.SQLGetData( n++ );
      string sAddress1 = rs.SQLGetData( n++ );
      string sCity     = rs.SQLGetData( n++ );
      string sState    = rs.SQLGetData( n++ );
      string sZip      = rs.SQLGetData( n++ );

      cout  << left
            << setw(22) << sname
            << sAddress1 << ", "
            << sCity << ", "
            << sState << "  "
            << sZip
            << endl;
   }

Adding a Record

Next, we'll add a record. Note that the overloaded functions, SetColumn() and SetWhere(), will convert strings, doubles, ints, and dates to the correct format for the SQL command.

   try
   {
      CSQLInsert s( "provider" );
      s.SetColumn( "provider", "Smith, Josh" );
      // note that the column name's case is ignored by the
      // ODBC translator; however, the data goes in exactly
      // as you typed it.
      s.SetColumn( "aDDRess1", "123 Anystreet" ); 
      s.SetColumn( "City", "Spokane" );
      s.SetColumn( "state", "WA" );
      s.SetColumn( "zip", "99204" );
      s.SetColumn( "DOB", true, 12, 13, 1955 );
      s.SetColumn( "Referrals", 0 );
      // note: if you're using a class like COleDateTime, there's
      // a macro in the headers that can be used like this--
      // s.SetColumn( "DOB", SQLBASE_OLEDATETIME(myVar) );
      // otherwise, there's no reference to MFC in the code.
      db.ExecuteSQL( s.GetStatement() );
   }
   catch ( CSQLException* e )
   {
      // in case the inserted record exists
      cout << endl << "Error on insert" << endl;
      cout << e->m_strError << endl;
   }

Performing a Record Update

   try
   {
      CSQLUpdate s( "provider" );
      // where clause necessary for update
      s.SetWhere( "provider", "Smith, Josh" );
      s.SetColumn( "address1", "2903 West Pacific Ave." );
      s.SetColumn( "Referrals=Referrals+1" );
      db.ExecuteSQL( s.GetStatement() );
   }
   catch ( CSQLException* e )
   {
      cout << endl << "Error on update" << endl;
      cout << e->m_strError << endl;
   }

Performing a Deletion

   try
   {
      CSQLDelete s( "provider" );
      // where clause necessary for delete
      s.SetWhere( "provider", "Castanza, George" );
      db.ExecuteSQL( s.GetStatement() );
   }
   catch ( CSQLException* e )
   {
      cout << endl << "Error on delete" << endl;
      cout << e->m_strError << endl;
   }

Running the Sample Program

To run the sample program:

1. Create a DSN in the ODBC administrator that points to the sample access database, MPHSample.mdb, provided with the project. Call it MPHSample.
2. Make sure that ImplicitCommitSync is set to 'Yes' in the advanced options for the DSN.
3. Compile, link, and run the program.

It's pretty easy to get an application up and running with these classes and the overloads keep you out of trouble.

Other Things

I've tested these on Personal Oracle and they work fine. I would imagine that SQL Server would give the same results.

Important: If you decide to close the database connection and reopen it, be sure to close all recordset objects first. Otherwise, you'll get an unknown exception from the ODBC middleware that's pretty hard to track down. You'll see that this is handled in the new demo project. Try removing the catch block (...) and you'll see it even runs differently in debug vs. release mode. Possibly the CSQLDatabase class could use a reference counter and declared as a friend class in CRecordset? Anyway, know this can be an issue and watch out for it.

A comment about the extraction of data. You no doubt noticed that it has to be in sequential order. This is an ODBC feature. I've thought that it would be a good enhancement to use a map file and load the data into it. Then you could extract it using syntax like rs.flds["Name"], rs["Name"] or rs[2] like Jet does it. I'll leave that for you to implement.

I've been using these classes at several sites with 5-6 terminals each pounding on Access databases and I'm getting excellent performance from them.

Permissions

You can freely distribute and use this code in your projects as long as my name is in the revision log. This includes commercial products and projects. Also, feel free to modify them if you like.

Addendum

I'd like to thank everyone for their comments, and especially for the bug fixes on the code. I've included your names in the revision logs.

Downloads

When I update any of my class libraries, I make the code available at this location. Also, if there were any missing source code includes, they can be found here.