Page 1 of 1

ODBC C++ Example This program uses Northwind Microsoft Acess database. Rate Topic: -----

#1 Ancient Dragon  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 81
  • View blog
  • Posts: 679
  • Joined: 19-July 09

Post icon  Posted 24 September 2009 - 09:42 PM

This is a complete, working ODBC C++ program which uses the Northwind database supplied with Microsoft Access program. With only minor changes to this program you could change it to use any ODBC-compliant database. The mods you would need to make are only in the main() function, the database name and the table name.

This program makes no attempt to display any of the data retrieved in the resultset. You would have to add functionality yourself to do that. The intent of this tutorial is to show how to retrieve the data in a database/table-independent form. Its a very simple c++ class which would require lots of improvements/enhancements in order to put it into a production environment. If that is your intent then you would probably want to get one of several existing c++ ODBC classes, most are not free.

// You can delete this line if you 
// are not using Microsoft compiler.
#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 0;
}



Is This A Good Question/Topic? 0
  • +

Replies To: ODBC C++ Example

#2 Draconian Paladin  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 3
  • Joined: 28-November 09

Posted 16 October 2010 - 01:47 AM

Hello!

Sorry for necro'ing post that is over a year ago. I just found out this post yesterday, and proceed to try it out in my program. I separated this program into header and cpp, but it should essentially the same thing. I managed to get it to run but it doesn't really do anything. There is however, a few things that is not same with the program here.

1. I couldn't include "stdafx.h", so I comment it out. I managed to find a file with this name in my computer, but it is empty, as in it have a line or two comments, but nothing else.
2. I don't have "Northwind" in my computer. I downloaded Access sample from Microsoft, which is "Northwind 2007", but renaming it doesn't really work.
3. I am actually trying to connect to MS SQL Server 2008.
4. When I compile, VB Studio 2008 complain about unable to convert to SQLWCHAR *, when the line is doing casting into (SQLCHAR *). So, I assume that things changed and it is now SQLWCHAR * instead of SQLCHAR * and change them accordingly.
5. The program does run, so I test value of rc at different point. In line 157 in main, the condition does evaluate to true and the program get into the if block without problem. But when I print out the value of rc after the connect and AllocHandle statements, I realised that both of them print out -1, which I assume is a failure.
6. I tried changing username to the format of "MyMachineName\SQLEXPRESS" but that doesn't work as well.

So, now I come to implore the wisdom of programmers here. What had I done wrong? What do I need to change to connect to MS SQL Server? Thank you very much for your time. :)
Was This Post Helpful? 0
  • +
  • -

#3 snoopy11  Icon User is offline

  • Engineering ● Software
  • member icon

Reputation: 710
  • View blog
  • Posts: 2,033
  • Joined: 20-March 10

Posted 21 October 2010 - 05:23 AM

The Author is not using a unicode build that
is why you are getting the error on SQLWCHAR
To change it go to Project -> Properties
->Configuration Properties -> General
and click on Character Set and change it to
Use Multi-Byte Character Set.

The Tutorial uses 'Northwind'
so I think your pretty much locked into using that database.

Why couldnt you find stdafx.h ? MSVC creates it automatically for you
but you dont really need it so that doesnt matter much.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1