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

  • Live Event Date: December 11, 2014 @ 1:00 p.m. ET / 10:00 a.m. PT Market pressures to move more quickly and develop innovative applications are forcing organizations to rethink how they develop and release applications. The combination of public clouds and physical back-end infrastructures are a means to get applications out faster. However, these hybrid solutions complicate DevOps adoption, with application delivery pipelines that span across complex hybrid cloud and non-cloud environments. Check out this …

  • Relying on outside companies to manage your network and server environments for your business and applications to meet the needs and demands of your users can be stressful. This is especially true as many Managed Hosting organizations fail to meet their service level agreements. Read this Forrester total economic impact report and learn what makes INetU different and how they exceed their customers' managed hosting expectations.

Most Popular Programming Stories

More for Developers

RSS Feeds