Click here to Skip to main content
Click here to Skip to main content

Oracle connection and cursor class for Visual C++ in a VB Style

, 19 Dec 2002
Rate this:
Please Sign up or sign in to vote.
A class to connect Oracle using OCI8 libs, that simplifies the connection and the access to data using SQL strings.

Introduction

First of all 2 things: I'm Spanish and parts of the code is in Spanish but only a little, and sorry for my English.

This is a Visual C++ class that uses the Oracle OCI library to connect to an Oracle database, keeping a cursor and a connection until the class object is destroyed.

It uses the OCI library, and you will need these files:

#include <oratypes.h>
#include <ocidfn.h>
#include <ociapr.h>
#include <ocidem.h>

Also you need the respective .dlls installed in your system.

When you start a new project to include this class, you have to open the Project Settings and add oci.lib in the Object/library modules (in the Link tab). You need oci.lib and other .lib from the OCI libs in the default lib directory.

Note: The OCI lib is included with any Oracle Client or is available from their web site.

The code is simple and is based on Oracle samples with oci lib.

The defines section defines the language options of SQL. Also the max number of fields to return in the SQL (MAX_SELECT_LIST_SIZE), and the max length of the data returned (MAX_ITEM_BUFFER_SIZE).

The class members are Lda and Cda structures to manage the data and connect with Oracle. It could be converted to private and only keep public the most useful ones:

  • ncolumnas - stores the number of columns of the SQL (SELECT )
  • nfilas - stores the number of rows returned by the SELECT
  • resultado - an array that stores the returned data of the SELECT
  • resultadostr - an array that stores the returned data in a string format
  • txterror - string that stores the error if there is one.

The description of the member functions is the next:

CursorOrc() - is the constructor and initializes a few member variables

  • Conecta() - makes a connection with Oracle using a user, password and a database name (defined in TNSNAMES.ORA)
  • Abre() - Opens a cursor, member of the class (each instance of the class will have its own cursor)
  • parsea() - checks the SQL sentence looking for errors
  • reserva() & describe() - these call describes define function to reserve memory and make data definitions.
  • ejecuta() - executes the SQL to return the data. Also takes the number of rows returned.

The parameter it takes must be true if is a SELECT sentence, or false if is a UPDATE, INSERT, ... or if it is a SELECT only to know the number of rows returned (not the data, i.e. a SELECT COUNT )

We don't need to call all these functions, just need two:

  • InicializaSql() - connects to Oracle, parses the SQL, initialize vars and executes the SQL. Must be used first time of connection or if we need to change the user.
  • LanzaSql() - if we have call InicializaSql previously, the rest of SQL sentences will be executed calling this function. The boolean parameter is passed to ejecuta() function.

If we have executed a SELECT, to access the data, we can use siguiente() to go to the next data row returned (used also to access the first time). After calling it, we have the data in resultadostr matrix of the row. The order is the one in the SELECT sentence.

Not yet developed the prev() or first() functions typical in cursors in VB objects.

A sample code using this class could be something like this:

...
CursorOrc conn;
...
const text *sql = (text *) "SELECT FIELD1, ..., 
    TO_CHAR(FIELDDATE,'DD/MM/YYYY'), .. FROM 
    TABLE WHERE FIELD1 NOT IN (SELECT FIELD3 FROM TABLE2)";

//cheks the connection
if (conn.InicializaSql((text *) usuario, (text *) pwd, 
                         (text *) bd, (text *)"COMMIT",1)) 
 {
   error();
   return 1;
 }

//launchs the sql
if (conn.LanzaSql(sql,1)) 
   {
      error();
      return 1;
   }

//if some result
if (conn.nfilas != 0)
{
   for (int i=0;i<conn.nfilas;i++)
   {
     conn.siguiente();
     ....
     //outs the first field of each row
     outs(conn.resultadostr[0]);
   }
}

If anyone needs help or any question about this class, it will be welcome, as any suggestion or modification.

Email: jperez@valnera.com.

Site: www.valnera.com.

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here

Share

About the Author

jperez
Web Developer
Spain Spain
Telecommunications Engineer from Cantabria, Spain.
 
Actually developing domino, java, and MFC projects envolving Oracle access and event processing in finances.
 
14 years coding in C, Basic, Visual Basic, Java, php, html, domino...

Comments and Discussions

 
GeneralVC++ With Access Pinmemberyogikaushik8-Mar-07 20:36 
QuestionORACLE/ODBC. PinmemberM_Nuaimi25-Apr-06 14:03 
GeneralDemo Application Pinmemberchilghoza9-Dec-04 0:47 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web03 | 2.8.141220.1 | Last Updated 20 Dec 2002
Article Copyright 2002 by jperez
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid