Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

ODBC connection in my C++ console application

Posted on 2004-04-23
4
1,082 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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

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 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 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.

839 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