Environment: [eg VC6, Win9x/ME Win NT/2000/XP Unix?]

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

I'll bet you're already thinking that this should look like an STL container with an iterator. Your right, I'm probably going to write something that looks like that soon. mySQL has a beautifully 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'" ); // lt 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;
   }

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

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

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

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.

Also, 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.

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

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. The machines are all late model with at least Pentium III's and database acquire times are for medium reports are only a few seconds. Inserts, updates and deletes are almost instant.

Downloads

History