Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

ODBC Connections to Access/Oracle via C++

Posted on 2004-04-05
5
Medium Priority
?
751 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
[X]
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
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: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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

Introduction This article is the first in a series of articles about the C/C++ Visual Studio Express debugger.  It provides a quick start guide in using the debugger. Part 2 focuses on additional topics in breakpoints.  Lastly, Part 3 focuses on th…
IntroductionThis article is the second in a three part article series on the Visual Studio 2008 Debugger.  It provides tips in setting and using breakpoints. If not familiar with this debugger, you can find a basic introduction in the EE article loc…
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 learn how to clear a vector as well as how to detect empty vectors in C++.

705 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