Programming Forums
User Name Password Register
 

RSS Feed
FORUM INDEX | TODAY'S POSTS | UNANSWERED THREADS | ADVANCED SEARCH

Reply
 
Thread Tools Display Modes
Old Sep 29th, 2009, 9:38 AM   #1
Ancient Dragon
Achieved Level 70

 
Ancient Dragon's Avatar
 
Join Date: Jun 2005
Location: near St Louis, MO. (USA)
Posts: 4,345
Rep Power: 10 Ancient Dragon will become famous soon enoughAncient Dragon will become famous soon enough
Odbc

This is a simple program that illustrates how to use ODBC to run a database query and get the resultset in c++ programs. It uses the Northwind database (attached) that is supplied with MS-Access, and could also be downloaded from Microsoft. This was written for MS-Windows os. *nix coders could want to replace windows.h with some other header files that declared widgets (see line 138 of sqltypes.h).

Note: This program does NOT show the results of the query. So if you compile and run it you won't see anything on the console screen. I did that to keep this program as simple as possible.

#include "stdafx.h"
#include <windows.h>
#include <sql.h>
#include<sqltypes.h>
#include<sqlext.h>
#include <string>
#include <vector>
#include <iostream>
using namespace std;
// You can delete this line if you 
// are not using Microsoft VC++ 2008/2010 compiler.
#pragma warning(disable: 4996)
////////////////////////////////////////


// Define The ODBC_Class Class
class ODBC_Class
{
    struct ColDescription
    {
        SQLSMALLINT colNumber;
        SQLCHAR colName[80];
        SQLSMALLINT nameLen;
        SQLSMALLINT dataType;
        SQLULEN colSize;
        SQLSMALLINT decimalDigits;
        SQLSMALLINT nullable;
    };
// Attributes
public:
    SQLHANDLE EnvHandle;
    SQLHANDLE ConHandle;
    SQLHANDLE StmtHandle;
    SQLRETURN rc;
    vector<ColDescription> cols;
    vector< vector<string> > colData;
// Operations
public:
    ODBC_Class(); // Constructor
    ~ODBC_Class(); // Destructor
    SQLRETURN GetResultset();
    void DescribeColumns();
private:
    _inline SQLRETURN Describe(ColDescription& c);
    SQLRETURN GetColData(int colnum, string& str);
};

// Define The Class Constructor
ODBC_Class::ODBC_Class()
{
    // Initialize The Return Code Variable
    rc = SQL_SUCCESS;
    // Allocate An Environment Handle
    rc = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &EnvHandle);
    // Set The ODBC Application Version To 3.x
    if (rc == SQL_SUCCESS)
        rc = SQLSetEnvAttr(EnvHandle, SQL_ATTR_ODBC_VERSION,
            (SQLPOINTER) SQL_OV_ODBC3, SQL_IS_UINTEGER);
    // Allocate A Connection Handle
    if (rc == SQL_SUCCESS)
        rc = SQLAllocHandle(SQL_HANDLE_DBC, EnvHandle, &ConHandle);
}

// Define The Class Destructor
ODBC_Class::~ODBC_Class()
{
    // Free The Connection Handle
    if (ConHandle != NULL)
        SQLFreeHandle(SQL_HANDLE_DBC, ConHandle);
    // Free The Environment Handle
    if (EnvHandle != NULL)
        SQLFreeHandle(SQL_HANDLE_ENV, EnvHandle);
}

// Get the data for one column and return the info in the form
// of a std::string.  The ODBC driver will make all necessary
// data conversions from whatever type the data is in the database
// to SQL_CHAR.  You could make this function more comples by 
// getting the return type as it appears in the database then constructing
// a VARIANT object to hold the data.
SQLRETURN ODBC_Class::GetColData(int colnum, string& str)
{
    SQLCHAR buf[255] = {0};
    if( (rc = SQLGetData(StmtHandle, colnum, SQL_CHAR, buf, sizeof(buf), NULL)) == SQL_SUCCESS)
        str = reinterpret_cast<char*>(buf);
    return rc;
}

//
// Define The ShowResults() Member Function
SQLRETURN ODBC_Class::GetResultset()
{
    // Get all column description
    DescribeColumns();
    // erase anything that's in the colData vector
    colData.clear();
    // fetch a row from the resultset
    while( SQLFetch(StmtHandle) == SQL_SUCCESS)
    {
        // vector of strings to hold the column data
        vector<string> col;
        string data;
        // column counter
        int i = 1;
        // get the data for each column and add it to 
        // the col vector
        while( GetColData(i, data) == SQL_SUCCESS)
        {
            col.push_back(data);
            ++i; // increment the column number
        }
        // add column data to the colData vector
        colData.push_back(col);
    }
    return SQL_SUCCESS;
}

// Get the description for one column in the resultset.
// This was made a seprate function to simplify the coding
SQLRETURN  ODBC_Class::Describe(ColDescription& c)
{
    return SQLDescribeCol(StmtHandle,c.colNumber,
        c.colName, sizeof(c.colName), &c.nameLen,
        &c.dataType, &c.colSize, &c.decimalDigits, &c.nullable);
}

// Get the description for all the columns in the resultset.
void ODBC_Class::DescribeColumns()
{
    ColDescription c;
    c.colNumber = 1;
    cols.clear();
    while( Describe(c) == SQL_SUCCESS)
    {
        cols.push_back(c);
        ++c.colNumber;
    }
        
}


/*-----------------------------------------------------------------*/
/* The Main Function */
/*-----------------------------------------------------------------*/
int main()
{
    // Declare The Local Memory Variables
    SQLRETURN rc = SQL_SUCCESS;
    SQLCHAR DBName[] = "Northwind";
    SQLCHAR SQLStmt[255] = {0};
    // Create An Instance Of The ODBC_Class Class
    ODBC_Class Example;
    // Connect To The Northwind Sample Database
    if (Example.ConHandle != NULL)
    {
        rc = SQLConnect(Example.ConHandle, DBName, SQL_NTS,
            (SQLCHAR *) "", SQL_NTS, (SQLCHAR *) "", SQL_NTS);
        // Allocate An SQL Statement Handle
        rc = SQLAllocHandle(SQL_HANDLE_STMT, Example.ConHandle,
                &Example.StmtHandle);
        if (rc == SQL_SUCCESS)
        {
            // Define A SELECT SQL Statement
            strcpy((char *) SQLStmt, "SELECT * FROM categories");
            // Prepare And Execute The SQL Statement
            rc = SQLExecDirect(Example.StmtHandle, SQLStmt, SQL_NTS);
            // Display The Results Of The SQL Query
            if (rc == SQL_SUCCESS)
            {
                Example.GetResultset();
                // At this point you would want to do something
                // with the resultset, such as display it.
            }
        }
        // Free The SQL Statement Handle
        if (Example.StmtHandle != NULL)
            SQLFreeHandle(SQL_HANDLE_STMT, Example.StmtHandle);
        // Disconnect From The Northwind Sample Database
        rc = SQLDisconnect(Example.ConHandle);
    }
    // Return To The Operating System
    return(rc);
}
__________________
PFO's FAQ is here
Forum Rules

There is no cow level (Diablo III)
If you never push yourself you will never improve (Diablo III)

Last edited by Ancient Dragon; Apr 2nd, 2012 at 9:17 PM.
Ancient Dragon is offline   Reply With Quote
Reply

Bookmarks

« Previous Thread in Forum | Next Thread in Forum »

Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
OLE DB or ODBC King C# 3 Sep 23rd, 2007 2:03 PM
C# VS 2005 - SQL Query Parameters to an ODBC DataSource jcrcarmo C# 3 Apr 10th, 2006 3:58 PM
How to set result pointer in ODBC connection? some1 C++ 0 Apr 17th, 2005 3:53 PM
Whats ODBC? some1 C++ 8 Apr 6th, 2005 5:58 PM
Using ODBC to connect to a remote database in a C program bigi C++ 1 Mar 8th, 2005 3:19 PM




DaniWeb IT Discussion Community
All times are GMT -5. The time now is 2:10 AM.

Powered by vBulletin® Version 3.7.0, Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
Copyright ©2007 DaniWeb® LLC