• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1115
  • Last Modified:

ODBC connection in my C++ console application

Hello,

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!

Scott
0
Eifson
Asked:
Eifson
3 Solutions
 
itsmeandnobodyelseCommented:
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)
      SQLFreeConnect(hDbc);
   if (hEnv != SQL_NULL_HENV)
      SQLFreeEnv(hEnv);
   return ret;
}

bool fetch()
{
   SQLHENV  hEnv  = SQL_NULL_HENV;
   SQLHDBC  hDbc  = SQL_NULL_HDBC;
   SQLHSTMT hStmt = SQL_NULL_HSTMT;
   // 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
   if (SQLSetStmtOption(hStmt, SQL_CURSOR_TYPE, SQL_CURSOR_STATIC) != SQL_SUCCESS)
       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

Alex

0
 
agri_amitCommented:
hi,
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];
SQLINTEGER   cbName;
SQLRETURN    retcode;
SQLHENV      henv = SQL_NULL_HENV;
SQLHDBC      hdbc1 = SQL_NULL_HDBC;
SQLHSTMT      hstmt1 = SQL_NULL_HSTMT;
SQLSMALLINT  colnum=0;
SQLCHAR *    colname;
SQLSMALLINT   buflen;//=sizeof(colname);
SQLSMALLINT *    nlptr;  
SQLSMALLINT *    dtptr;
SQLUINTEGER *    colsptr;
SQLSMALLINT *    dec;
SQLSMALLINT *    nptr;


FILE *fp;

int main() {
   RETCODE retcode;
   UCHAR   szDSN[SQL_MAX_DSN_LENGTH+1] = "conn",
         szUID[MAXNAME] = "sa",
         szAuthStr[MAXNAME] = "sa";
   fp=fopen("sqlquery.txt","w+");

    // 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);

retcode=SQLDescribeCol(hstmt1,1,colname,25,&nptr,&dtptr,colsptr,dec,nptr);

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 {
         break;
      }
   }
}

   }

   // Allocate statement handles and do ODBC processing.
   /* Clean up. */
   SQLDisconnect(hdbc1);
   SQLFreeHandle(SQL_HANDLE_DBC, hdbc1);
   SQLFreeHandle(SQL_HANDLE_ENV, henv);
   fclose(fp);
   return(0);
}

regards,
amit
0
 
MafaldaCommented:
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 =
    SQLConnect(hdbc,
               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

  SQLINTEGER cb;

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

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

c) boolean

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

d) integer

  SQLINTEGER cb;
  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.
  {
    SQLCloseCursor(hstmt);
    return false;
  }

ENJOY !
0
 
EifsonAuthor Commented:
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!

Scott

Happy programming!!
0
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

Featured Post

Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now