Solved

ODBC connection in my C++ console application

Posted on 2004-04-23
4
1,072 Views
Last Modified: 2010-07-27
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
Comment
Question by:Eifson
4 Comments
 
LVL 39

Accepted Solution

by:
itsmeandnobodyelse earned 150 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)
      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
 

Assisted Solution

by:agri_amit
agri_amit earned 150 total points
ID: 10897621
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
 
LVL 6

Assisted Solution

by:Mafalda
Mafalda earned 150 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 =
    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
 

Author Comment

by:Eifson
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!

Scott

Happy programming!!
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

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 …
C++ Properties One feature missing from standard C++ that you will find in many other Object Oriented Programming languages is something called a Property (http://www.experts-exchange.com/Programming/Languages/CPP/A_3912-Object-Properties-in-C.ht…
The goal of the video will be to teach the user the concept of local variables and scope. An example of a locally defined variable will be given as well as an explanation of what scope is in C++. The local variable and concept of scope will be relat…
The viewer will be introduced to the member functions push_back and pop_back of the vector class. The video will teach the difference between the two as well as how to use each one along with its functionality.

910 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now