Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


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


Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This article is a continuation of the C/C++ Visual Studio Express debugger series. Part 1 provided a quick start guide in using the debugger. Part 2 focused on additional topics in breakpoints. As your assignments become a little more …
Basic understanding on "OO- Object Orientation" is needed for designing a logical solution to solve a problem. Basic OOAD is a prerequisite for a coder to ensure that they follow the basic design of OO. This would help developers to understand the b…
The goal of the video will be to teach the user the difference and consequence of passing data by value vs passing data by reference in C++. An example of passing data by value as well as an example of passing data by reference will be be given. Bot…
The viewer will be introduced to the technique of using vectors in C++. The video will cover how to define a vector, store values in the vector and retrieve data from the values stored in the vector.
Suggested Courses

578 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