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

• Download source files - 2.85 Kb

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.