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)";
if (conn.InicializaSql((text *) usuario, (text *) pwd,
(text *) bd, (text *)"COMMIT",1))
{
error();
return 1;
}
if (conn.LanzaSql(sql,1))
{
error();
return 1;
}
if (conn.nfilas != 0)
{
for (int i=0;i<conn.nfilas;i++)
{
conn.siguiente();
....
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.
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...