ODBC connection in my C++ console application

Posted on 2004-04-23
Medium Priority
Last Modified: 2010-07-27

I am interested in creating a web "turn-based" game.  What I want to do is the following:
I want to write a c++ console application that I will set to run at specified times.  This console application will connect to my database, which I have already setup.  It will read certain data out of the database, like the players orders etc.  I am going to write the application so that it creates objects in memory based on certain field data it retrieves and perform actions against those objects, like movement, combat, unit creation etc.

Here is my problem.  I have NO IDEA how to get my console application to connect to my database.  I will more than likely use ODBC to handle the connection, however all the examples of how to do this are very vague and do not work.  I want to avoid using MFC's or 3rd party libraries to handle the connection for me.

Can anyone provide an example of how I might be able to go about doing this.  Or can anyone point me to a website that has a WORKING example.

If someone can give me an example of how to perform the connection and possibly a select statement that would be a great help.

I appreciate any help anyone might be able to provide.  And no this is not for a school project or anything like that, this is for a personal project I am working to flesh out my DirectX game mechanics before I finish my game engine.

Thanks in advance.  Happy programming!

Question by:Eifson
LVL 39

Accepted Solution

itsmeandnobodyelse earned 600 total points
ID: 10897619
You may create an ODBC data source using System Settings - ODBC - System DSN and connect it to the MS Access or other ODBC database.

#include <windows.h>
#include <sqlext.h>

struct PersonRecord
   long  lenLastName;     // will take the length of a VARCHAR string
   char  lastName[30];    // last name 30 chars max
   long  lenName;          // will take the length of a VARCHAR string
   char  name[30];         // name 30 chars max

// free used handles and return given retcode
bool freeHandles(bool ret, SQLHENV hEnv, SQLHDBC hDbc, SQLHSTMT hStmt)                  
   if (hStmt != SQL_NULL_HSTMT)
      SQLFreeStmt(hStmt, SQL_DROP);
   if (hDbc != SQL_NULL_HDBC)
   if (hEnv != SQL_NULL_HENV)
   return ret;

bool fetch()
   // get handle for ODBC environment
   if (SQLAllocEnv(&hEnv) != SQL_SUCCESS)
       return freeHandles(false, hEnv, hDbc, hStmt);
   // get handle for connection
   if (SQLAllocConnect(hEnv, &hDbc); != SQL_SUCCESS)
       return freeHandles(false, hEnv, hDbc, hStmt);
   // connect to datasource
   if (SQLConnect(hDbc, (unsigned char*)"Datasource", SQL_NTS,
                  (unsigned char*)"User", SQL_NTS,
                  (unsigned char*)"Password", SQL_NTS) != SQL_SUCCESS)
       return freeHandles(false, hEnv, hDbc, hStmt);
   // get handle for statement
   if (SQLAllocStmt(hDbc, &hStmt) != SQL_SUCCESS)
      return freeHandles(hEnv, hDbc, hStmt);
   // set cursor type
       return freeHandles(false, hEnv, hDbc, hStmt);
   // set select statement
   char sqlstmt[] = "select lastname, name from person where name like 'A%'";
   // execute select statement
   if (SQLExecDirect(hStmt, (unsigned char*)sqlstmt, strlen(sqlstmt))!= SQL_SUCCESS)
       return freeHandles(false, hEnv, hDbc, hStmt);

   // buffer to fetch one record
   PersonRecord rec;
   // bind columns of table to buffer
   if (SQLBindCol(hStmt, 1, SQL_CHAR,
                  (unsigned char*)rec.lastName, 30, &rec.lenLastName) != SQL_SUCCESS)
       return freeHandles(false, hEnv, hDbc, hStmt);
   if (SQLBindCol(hStmt, 2, SQL_CHAR,
                 (unsigned char*)rec.name, 30, &rec.lenName) != SQL_SUCCESS)
       return freeHandles(false, hEnv, hDbc, hStmt);
   // get the result set record by record
   RETCODE retFetch = 0;
   while ((retFetch = SQLFetch(hStmt)) == SQL_SUCCESS)
       // do something with rec
   return freeHandles(retFetch == SQL_NO_DATA, hEnv, hDbc, hStmt);

You can put the allocation and connection part of the fetch function to separate functions.

You may use SQLExecDirect(..) to perform any other SQL operation like INSERT, UPDATE, DELETE, CREATE TABLE, ....

Hope, that helps



Assisted Solution

agri_amit earned 600 total points
ID: 10897621
here i am giving my sample code,i think u will able to use in ur scenario.
#include <stdio.h>
#include <string.h>
#include <windows.h>
#include <sql.h>
#include <sqlext.h>
#include <sqltypes.h>
#include <odbcss.h>

#define NAME_LEN 50

SQLCHAR      szName[NAME_LEN];
SQLRETURN    retcode;
SQLSMALLINT  colnum=0;
SQLCHAR *    colname;
SQLSMALLINT   buflen;//=sizeof(colname);
SQLSMALLINT *    nlptr;  
SQLSMALLINT *    dtptr;
SQLUINTEGER *    colsptr;
SQLSMALLINT *    nptr;

FILE *fp;

int main() {
   RETCODE retcode;
   UCHAR   szDSN[SQL_MAX_DSN_LENGTH+1] = "conn",
         szUID[MAXNAME] = "sa",
         szAuthStr[MAXNAME] = "sa";

    // Allocate the ODBC Environment and save handle.
   retcode = SQLAllocEnv (&henv);
   // Notify ODBC that this is an ODBC 3.0 application.
    // Allocate an ODBC connection handle and connect.
   retcode = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc1);
   retcode = SQLConnect(hdbc1, szDSN, (SWORD)strlen(szDSN),szUID, (SWORD)strlen(szUID),szAuthStr, (SWORD)strlen(szAuthStr));
   if ( (retcode != SQL_SUCCESS) && (retcode != SQL_SUCCESS_WITH_INFO) )
         // Connect failed, call SQLGetDiagRec for errors.
   else {
        retcode =  SQLAllocHandle(SQL_HANDLE_STMT,hdbc1, &hstmt1);

retcode = SQLExecDirect(hstmt1,"SELECT test FROM cq_employee",SQL_NTS);


printf("%u \n",dtptr);

if (retcode == SQL_SUCCESS) {
   while (TRUE) {
      retcode = SQLFetch(hstmt1);
      if (retcode == SQL_ERROR || retcode == SQL_SUCCESS_WITH_INFO) {
//        printf("Error!");
      if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO){
         SQLGetData(hstmt1, 1, SQL_C_CHAR, szName, NAME_LEN, &cbName);

         /* Print the row of data */
           fprintf( fp,"%s \n",szName);
      } else {


   // Allocate statement handles and do ODBC processing.
   /* Clean up. */
   SQLFreeHandle(SQL_HANDLE_DBC, hdbc1);
   SQLFreeHandle(SQL_HANDLE_ENV, henv);


Assisted Solution

Mafalda earned 600 total points
ID: 10897746
I can't give you the whole code but I can give you the steps and fragments to help you ;o)
odbc_error and ODBCError are error handlers

1)  Create an environment handle and set the type of ODBC supported to V3

   SQLHENV _henv;
   SQLRETURN  retcode;
  //Allocate environment handle
  if (!_henv)
        retcode = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &_henv);
    if (!(retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO))
      throw ODBCError(SQL_HANDLE_ENV, _henv, "SQLAllocHandle");
  // Set the ODBC version environment attribute
  retcode = SQLSetEnvAttr(_henv, SQL_ATTR_ODBC_VERSION, (void*)SQL_OV_ODBC3, 0);
       if (!(retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO))
    throw ODBCError(SQL_HANDLE_ENV, _henv, "SQLSetEnvAttr");

2) An environment handle is needed to construct a databse connection handle

  SQLRETURN retcode;
  SQLHDBC hdbc;
  retcode = SQLAllocHandle(SQL_HANDLE_DBC, _henv, &hdbc);
  if (!(retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO))
      throw ODBCError(SQL_HANDLE_ENV, _henv, "new_connection_handle");

3) Connect  using serer as ODBC server, user as username and password (all here ae STL strings)
  SQLRETURN  retcode;

  // Set login timeout to 5 seconds.
      retcode = SQLSetConnectAttr(hdbc, SQL_ATTR_LOGIN_TIMEOUT, (void *)5, 0);
      if (!(retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO))
    throw ODBCError(SQL_HANDLE_DBC, hdbc, "SQLSetConnectAttr");

      // Connect to data source
      retcode =
               const_cast<SQLCHAR*>(reinterpret_cast<const SQLCHAR*>(server.c_str())), SQL_NTS,
                   const_cast<SQLCHAR*>(reinterpret_cast<const SQLCHAR*>(user.c_str())), SQL_NTS,
                                       const_cast<SQLCHAR*>(reinterpret_cast<const SQLCHAR*>(password.c_str())), SQL_NTS);
      if (!(retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO))
    throw ODBCError(SQL_HANDLE_DBC, hdbc, "SQLConnect");

4) We need a database connection handle to construct a statement handle
  SQLRETURN retcode;
  SQLHSTMT hstmt;
  retcode = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);
  if (!(retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO))
      throw ODBCError(SQL_HANDLE_DBC, hdbc, "make_statement");

5) bind columns

a) double


  if (failed(SQLBindCol(_rep->hstmt, index, SQL_C_DOUBLE , &value, 0, &cb)))
    throw odbc_error("bind(int, double)");

b) char
  if (failed(SQLBindCol(hstmt, index, SQL_C_CHAR , value, len, &cb)))
    throw odbc_error("bind(int, char *, int)");

c) boolean

  if (failed(SQLBindCol(hstmt, index, SQL_C_TINYINT , &value, 0, &cb)))
    throw odbc_error("bind(int, bool)");

d) integer

  if (failed(SQLBindCol(hstmt, index, SQL_C_SLONG , &value, 0, &cb)))
    throw odbc_error("bind(int, int)");

etc ...

6) execute an SQL statement

  if (failed(SQLExecDirect(hstmt, reinterpret_cast<unsigned char *>(const_cast<char *>(sql_command.c_str())) , SQL_NTS)))
    throw odbc_error("execute");

7) retrieve the next row of data.  because a cursor is internaly being use, when we finish close the cursor

  SQLRETURN retcode = SQLFetch(hstmt);
  if (retcode == SQL_NO_DATA) // no more rows. close the cursor.
    return false;


Author Comment

ID: 10906480
Thank you all for your helpful answers, in looking at the code that the three of you have provided, I can tell that this is a bit out of my league.  I was able to get the code samples to compile however with a few errors.  I am going to do some more research into this.  I appreciate all of your help.

Thanks again!


Happy programming!!

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Unlike C#, C++ doesn't have native support for sealing classes (so they cannot be sub-classed). At the cost of a virtual base class pointer it is possible to implement a pseudo sealing mechanism The trick is to virtually inherit from a base class…
Article by: SunnyDark
This article's goal is to present you with an easy to use XML wrapper for C++ and also present some interesting techniques that you might use with MS C++. The reason I built this class is to ease the pain of using XML files with C++, since there is…
The viewer will learn how to pass data into a function in C++. This is one step further in using functions. Instead of only printing text onto the console, the function will be able to perform calculations with argumentents given by the user.
The viewer will learn how to clear a vector as well as how to detect empty vectors in C++.

627 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question