Ultra Simple DB Class Interface for ODBC

Environment: VC6, Win9x/ME Win NT/2000/XP

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

Download demo project - 159 Kb
Download source - 22 Kb

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.



Comments

  • Transaction Commit(); Roolback() is not working

    Posted by Legacy on 02/20/2004 12:00am

    Originally posted by: Azkar

    Hi,
    This sample really doing very good job in my development case. But, I am struggling with transaction. I want to rollback the changes using RollBack() method, But, I couldn't.
    I appreciate your quick response in this regards.
    Thanks in advance

    Reply
  • errors

    Posted by Legacy on 09/12/2003 12:00am

    Originally posted by: mena

    Hi
    when i try to compile this app i get this errors
    SQLDatabase.obj : error LNK2001: unresolved external symbol _SQLGetInfo@20
    SQLDatabase.obj : error LNK2001: unresolved external symbol _SQLFreeHandle@8
    SQLDatabase.obj : error LNK2001: unresolved external symbol _SQLDisconnect@4
    SQLDatabase.obj : error LNK2001: unresolved external symbol _SQLConnect@28
    SQLDatabase.obj : error LNK2001: unresolved external symbol _SQLSetConnectAttr@16
    SQLDatabase.obj : error LNK2001: unresolved external symbol _SQLSetEnvAttr@16
    SQLDatabase.obj : error LNK2001: unresolved external symbol _SQLAllocHandle@12
    SQLDatabase.obj : error LNK2001: unresolved external symbol _SQLEndTran@12
    SQLDatabase.obj : error LNK2001: unresolved external symbol _SQLExecDirect@12
    SQLRecordset.obj : error LNK2001: unresolved external symbol _SQLExecDirect@12
    SQLDatabase.obj : error LNK2001: unresolved external symbol _SQLGetDiagRec@32
    SQLRecordset.obj : error LNK2001: unresolved external symbol _SQLGetDiagRec@32
    SQLRecordset.obj : error LNK2001: unresolved external symbol _SQLAllocStmt@8
    SQLRecordset.obj : error LNK2001: unresolved external symbol _SQLFetch@4
    SQLRecordset.obj : error LNK2001: unresolved external symbol _SQLFreeStmt@8
    SQLRecordset.obj : error LNK2001: unresolved external symbol _SQLGetData@24
    SQLRecordset.obj : error LNK2001: unresolved external symbol _SQLColAttributes@28
    SQLRecordset.obj : error LNK2001: unresolved external symbol _SQLDescribeCol@36
    Debug/SQLdb01.exe : fatal error LNK1120: 16 unresolved externals
    Error executing link.exe.
    Creating browse info file...

    SQLdb01.exe - 19 error(s), 0 warning(s)

    Can you help me solving this problem ?

    Reply
  • connecting to access db using odbc

    Posted by Legacy on 06/13/2003 12:00am

    Originally posted by: Joseph

    I have been trying to connect the Access Data Base using ODBC in VC 6.0. My application is an Dialog based and when i started i had no thought of any data base.Now i need to connect to one. I have tried all means with no success.The program runs but when the dialog box is invoked it posts a message saying either it is not compatable or not able to find an DSN name. The DSN name is already given.How can do this connectivity.

    Reply
  • Need latest version

    Posted by Legacy on 05/23/2003 12:00am

    Originally posted by: hewitt

    your url is not working..........

    Reply
  • Where is Latest version?

    Posted by Legacy on 01/30/2003 12:00am

    Originally posted by: Archana Rele

    Where is the latest version available. The link provided in the answer to Lastest Version comment is not working.

    Reply
  • does the sample work independent?

    Posted by Legacy on 01/02/2003 12:00am

    Originally posted by: erdem

    truly, i couldnt understand the logic of the relation btw the sql and the sample

    Reply
  • added files to mfc project, get error

    Posted by Legacy on 09/17/2002 12:00am

    Originally posted by: Steve

    sqlbase.cpp(96) : fatal error C1010: unexpected end of file while looking for precompiled header directive
    SQLBaseWhere.cpp

    I get one of these for all 8 files.

    Reply
  • 1 more error?

    Posted by Legacy on 09/02/2002 12:00am

    Originally posted by: Kirya

    I think that there is an error in your code. It occurs in a situation when we try to close odbc connection and open it again - database and environmaent handles will become invalid. So i've moved handles allocation code into SQLConnect method and everything works fine now (or at least seems to work fine).

    Reply
  • more complex "where" statement?

    Posted by Legacy on 07/22/2002 12:00am

    Originally posted by: Adam

    Thanks so much for posting your solution.  I am writing a very similar DB application for a course I am taking. I am searching a table which is a list phone calls.  Each phone call has a date associated with it.  How would I construct a "SetWhere" statement to return all the calls in a given date range?  For example, to return a list of all calls that occurred in month '06'...?
    
    --Thank you!

    Reply
  • changes to ur code

    Posted by Legacy on 05/30/2002 12:00am

    Originally posted by: from germany

    hello,
    
    

    thanks a lot for your wrapper classes: exactly what i was looking for!
    i tried anyway to compile it with different compilers, and only VC++ has accepted your code "as-is": for the other i had to change some details... but i think that was compiler problems.

    anyway i tried as far only with ms access.
    i ve got a question: why should the option "ImplicitCommitSync" turned on? i tried to let it turned off, and added following code in order to use the datbase over transactions commit/rollback:

    void CSQLDatabase::SetAutoCommit( bool tf ) // makes all transactions COMMIT immediately
    {
    _autoCommit = tf;

    if ( _autoCommit == true )
    return;

    if ( ! SupportsTransactions() )
    {
    _autoCommit = true;
    return; // added
    }

    // added: desactivate the autocommit-mode in order to start a new transaction after each commit/rollback
    _rc = ::SQLSetConnectAttr( _hdbc, SQL_ATTR_AUTOCOMMIT, SQL_AUTOCOMMIT_OFF, 0);
    if ( !( _rc == SQL_SUCCESS || _rc == SQL_SUCCESS_WITH_INFO ) )
    ThrowError(SQL_HANDLE_DBC);
    }

    for now, call SetAutoCommit(false) only work before any recordset has been opened, but it might be odbc-driver dependant. Otherwise i get an error "setting this option is not possible at this time".

    If you want to apply these modifications to your new uploaded code, feel free!

    bye, philippe

    Reply
  • Loading, Please Wait ...

Leave a Comment
  • Your email address will not be published. All fields are required.

Top White Papers and Webcasts

  • Live Event Date: August 14, 2014 @ 2:00 p.m. ET / 11:00 a.m. PT Data protection has long been considered "overhead" by many organizations in the past, many chalking it up to an insurance policy or an extended warranty you may never use. The realities of today make data protection a must-have, as we live in a data driven society. The digital assets we create, share, and collaborate with others on must be managed and protected for many purposes. Check out this upcoming eSeminar and join eVault Chief Technology …

  • Is your sales and operations planning helping or hurting your bottom line? Here are 5 useful tips from the experts at Quintiq to guide you to a better S&OP strategy.

Most Popular Programming Stories

More for Developers

Latest Developer Headlines

RSS Feeds