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

  • Savvy enterprises are discovering that the cloud holds the power to transform IT processes and support business objectives. IT departments can use the cloud to redefine the continuum of development and operations—a process that is becoming known as DevOps. Download the Executive Brief DevOps: Why IT Operations Managers Should Care About the Cloud—prepared by Frost & Sullivan and sponsored by IBM—to learn how IBM SmartCloud Application services provide a robust platform that streamlines …

  • Mobile is introducing sweeping changes throughout your workplace. As a senior stakeholder driving mobile initiatives in your organization, you may be lost in a sea of technologies and claims from vendors promising rapid delivery of applications to your employees, customers, and partners. To help explain some of the topics you will need to be aware of, and to separate the must-haves from the nice-to-haves, this reference guide can help you with applying a mobile strategy in the context of application …

Most Popular Programming Stories

More for Developers

Latest Developer Headlines

RSS Feeds