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: November 20, 2014 @ 2:00 p.m. ET / 11:00 a.m. PT Are you wanting to target two or more platforms such as iOS, Android, and/or Windows? You are not alone. 90% of enterprises today are targeting two or more platforms. Attend this eSeminar to discover how mobile app developers can rely on one IDE to create applications across platforms and approaches (web, native, and/or hybrid), saving time, money, and effort and introducing apps to market faster. You'll learn the trade-offs for gaining long …

  • Live Event Date: October 29, 2014 @ 11:00 a.m. ET / 8:00 a.m. PT Are you interested in building a cognitive application using the power of IBM Watson? Need a platform that provides speed and ease for rapidly deploying this application? Join Chris Madison, Watson Solution Architect, as he walks through the process of building a Watson powered application on IBM Bluemix. Chris will talk about the new Watson Services just released on IBM bluemix, but more importantly he will do a step by step cognitive …

Most Popular Programming Stories

More for Developers

Latest Developer Headlines

RSS Feeds