Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

ODBC Connections to Access/Oracle via C++

Posted on 2004-04-05
5
Medium Priority
?
758 Views
Last Modified: 2012-05-04
Experts, I have spent countless hours on google looking for a way to connect to a database using c++.  As many people out there, I hate the MSDN provided my microsoft.  It is not very helpful.  Google didn't return much luck either.  Any sites/books to point me in the right direction?

I will be querying a database where the requests are sent by a remote server, and returning the results to it.  Not sure how, but I'm sure that the experts here will help me find a clever way to do so.

Thanks!!
0
Comment
Question by:JK2429
5 Comments
 
LVL 17

Expert Comment

by:rstaveley
ID: 10756711
0
 
LVL 39

Accepted Solution

by:
itsmeandnobodyelse earned 200 total points
ID: 10757024
If you are using MFC simply create a new class using Class Wizard derived from CRecordset. There are a few sample programs if using VC++.

If not using MFC try this:

You may create a ODBC data source using System Settings - ODBC - System DSN and connect it to the MS Access or ORACLEL 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.

Hope, that helps

Alex
0
 
LVL 4

Expert Comment

by:YuriPutivsky
ID: 10761713
Take a look at Terimber C++ Db library
http://www.terimber.com/text/download/dbaccess.html
0
 
LVL 4

Author Comment

by:JK2429
ID: 10767138
I will be accessing Access database  (Pitiful, I know!!).  Alex, do you have the source code I can view, It seems that I have will be doing something similar to what you have provided me with.
0
 
LVL 39

Expert Comment

by:itsmeandnobodyelse
ID: 10768479
It's an huge C++ class library rather than a simple-to-copy application sample. It doesn't use MFC but my own standard libraries and is far too much code to post it here in EE. In fact it is ten times bigger than MFC recordsets as it works as a factory to make C++ classes persistent and get C++ instances from a database query.

I would suggest you adopting the sample code from above. You can make queries on all existing tables of an ACCESS or ORACLE datasource with that. We easily could add code to update, insert and delete records as it is always the same simple sequence:

    - build the SQL statement
    - allocate that statement
    - use SQLExecDirect(..) to perform the statement

In fact the select statement from above is the most complicated of all as it requires fetching a result set after the statement is executed.

Regards, Alex

0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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.

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

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…
This article will show you some of the more useful Standard Template Library (STL) algorithms through the use of working examples.  You will learn about how these algorithms fit into the STL architecture, how they work with STL containers, and why t…
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 additional member functions of the vector class. Specifically, the capacity and swap member functions will be introduced.

886 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