Solved

ODBC Connections to Access/Oracle via C++

Posted on 2004-04-05
5
701 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 50 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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

In days of old, returning something by value from a function in C++ was necessarily avoided because it would, invariably, involve one or even two copies of the object being created and potentially costly calls to a copy-constructor and destructor. A…
What is C++ STL?: STL stands for Standard Template Library and is a part of standard C++ libraries. It contains many useful data structures (containers) and algorithms, which can spare you a lot of the time. Today we will look at the STL Vector. …
The goal of the tutorial is to teach the user how to use functions in C++. The video will cover how to define functions, how to call functions and how to create functions prototypes. Microsoft Visual C++ 2010 Express will be used as a text editor an…
The viewer will learn how to user default arguments when defining functions. This method of defining functions will be contrasted with the non-default-argument of defining functions.

759 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

19 Experts available now in Live!

Get 1:1 Help Now