Direct SQL Calls With ODBC and STL

I looked at Dave Merner's article on direct SQL calls with ODBC and some subsequent correspondence regarding non MFC class usage. Some readers were interested in an STL solution and I have made the following changes to the presented code:
  • The CPtrArray has been replaced by a standard STL vector.
  • The Column classes have been coded to orthodox canonical form of class for STL usage.
  • Many of the ODBC calls are deprecated and have been replaced with later ODBC function calls.
  • The demo project contains some example code usage.
  • Worthwhile reader contributions have been applied to the code.

The code and project require a DSN to the MS Northwind sample database in order to run correctly and this short article is offered as a technique for using simple STL container classes and ODBC.

Thanks to all at codeguru for sharing code and ideas.

Example code usage also available in the demo project:

try {
  cout << "Connecting to Sample Northwind database..." << endl;
  CIDSQLDirect sqlDirect;     // Our direct ODBC class

  if (( nRetCode = sqlDirect.Connect("Northwind") ) != SQL_SUCCESS )
     return( nRetCode );

  if (( nRetCode = sqlDirect.ExecuteSQL("SELECT COUNT(*) FROM Employees"))
              != SQL_SUCCESS )
     return( nRetCode );

  if (( nRetCode = sqlDirect.Fetch() ) != SQL_SUCCESS )
     return( nRetCode );  // Fetch the result from this COUNT(*)

  string strFetch = sqlDirect.GetCol( 1 );
  cout << "Employees Table contains (" << strFetch << ") entries" << endl;

  // This query returns all data in the Employees table
  if (( nRetCode = sqlDirect.ExecuteSQL("SELECT * FROM Employees"))
              != SQL_SUCCESS )
     return( nRetCode );

  int nColumns = sqlDirect.GetNumColumns();
  cout << "Query returned (" << nColumns << ") columns" << endl;
  sqlDirect.PrintColumnNames();
  DisplayData( sqlDirect ); // Print the information now contained
  }

catch( ... ) {
  cout << "SQL Exception Caught!" << endl;
}

Downloads

Download demo project - 26 Kb
Download source - 9 Kb


Comments

  • Win32 App Console with this connection class problem

    Posted by Legacy on 04/10/2003 12:00am

    Originally posted by: Alan

    Dear Sir,
    
    

    I am very interested in your class to connect with database.

    I am a newbie in C++. But I do want to use this class for database connection.

    I got a problem, when I create a win32app console application.

    When I include the downloaded files, I got many errors.
    How can I fix it?

    e.g. In my project,
    #include <iostream>
    #include <string>
    #include "IDSQLDirect.h"

    using namespace std;

    void main(void)
    {

    }

    then I compile this file...many errors occurred..
    Pls help!

    Thank you very much.
    cheers,

    Reply
  • Fixed Linker Errors

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

    Originally posted by: Anonymous

    I had to go to project->settings and add in odbc32.lib under the link section after I tried adding the .h and .cpp files to another project I was working on in order to get rid of 14 unresolved external symbol errors generated from the IDSQLDirect.obj.

    Reply
  • OTL is a very good ODBC STL implementation

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

    Originally posted by: francois wirth

    http://www.geocities.com/skuchin/otl/home.htm

    This is a very good STL implementation of not just ODBC but also OCI and CLI. It's well maintained and implements a streaming model of data going in as well as out.

    Take a look at it it's worth it..

    Reply
  • Another alternative is the database template library (DTL)

    Posted by Legacy on 01/08/2002 12:00am

    Originally posted by: Jim Service

    An Introduction to the Database Template Library at http://www.geocities.com/corwinjoy/dtl/dtl_introduction.htm
    
    

    I, too, modified the original SQLDirect sources to use the STL . However, I chose to implement methods to fetch a row, a column or a vector of vectors. For example:

    typedef CString string_ ;
    typedef std::vector<string_> string_vector ;
    typedef std::vector<string_vector> string_matrix ;
    typedef std::map<string_, string_> string_map ;

    class CSQLDirect {
    public:
    // ...
    // Fetch one row either using the given query, SELECT, or using the results of
    // a previous one. ROW is an STL map whose keys are the field names of the
    // returned columns and whose values are the fetched row's values. If the
    // pointer P_ROW_TYPE is non-zero then return the SQL types of each column
    // returned in the fetch.
    int fetch_row(string_map &row, LPCTSTR select = 0,
    string_map *p_row_type = 0) ;
    //
    // Fetch a column resulting from a query and ignore the rest of the columns.
    //
    int fetch_column(int column, string_vector& str_vector);
    // If SELECT is not null then execute the query; otherwise, process an
    // existing query. The field names will be put in the vector FIELD_NAME, the
    // field types in the vector FIELD_TYPE and query results stored row vector by
    // row vector in ROW. An optional LIMIT on the number of rows retrieved may
    // be entered. The return value will be the result of the last ODBC
    // operation.
    int fetch_query(string_vector &field_name, string_vector &field_type,
    string_matrix &row, LPCTSTR select = 0, int limit = 0) ;
    // ..

    For a new database project I would probably go with DTL instead.

    Reply
  • Bug in GetError()

    Posted by Legacy on 01/04/2002 12:00am

    Originally posted by: Ted Ferenc

    SQLGetDiagRec should check for these return codes

    SQL_ERROR and SQL_INVALID_HANDLE

    otherwise if called with a NULL value for m_hStmt it will loop continuously

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

Top White Papers and Webcasts

  • Protecting business operations means shifting the priorities around availability from disaster recovery to business continuity. Enterprises are shifting their focus from recovery from a disaster to preventing the disaster in the first place. With this change in mindset, disaster recovery is no longer the first line of defense; the organizations with a smarter business continuity practice are less impacted when disasters strike. This SmartSelect will provide insight to help guide your enterprise toward better …

  • Java developers know that testing code changes can be a huge pain, and waiting for an application to redeploy after a code fix can take an eternity. Wouldn't it be great if you could see your code changes immediately, fine-tune, debug, explore and deploy code without waiting for ages? In this white paper, find out how that's possible with a Java plugin that drastically changes the way you develop, test and run Java applications. Discover the advantages of this plugin, and the changes you can expect to see …

Most Popular Programming Stories

More for Developers

Latest Developer Headlines

RSS Feeds