Announcing: A C Interface to ORACLE (ALPHA)

From: Zane Dodson <dodson_at_aquila.plk.af.mil>
Date: 10 Sep 1994 20:32:08 -0600
Message-ID: <34tq78$1j6q_at_indus.unm.edu>

[ I am posting this announcement to comp.lang.perl due to the wide   use of Kevin Stock's Oraperl. ]
Announcing: A C Interface to ORACLE (ALPHA)

---

To ORACLE7 Users,

I am seeking ALPHA testers for a simple, but powerful, C interface to ORACLE (which I call ciORA). Below is a brief description of ciORA followed by copyright, licensing and alpha testing information.

ciORA

---

(From the man page of ciORA....)
ciORA is a set of C interface routines to ORACLE that are modeled after the standard IO portion of the C library. ciORA presents a familiar interface to an experienced C programmer by avoiding the awkward embedding of SQL statements using precompilers and the tedium of using low-level OCI calls. ciORA eliminates the need for precompilers by supplying an interface library providing equivalent functions. It also provides a higher level of abstraction to the functions in the ORACLE Call Interface (OCI). ciORA manages (and hides) the tedious details necessary when writing programs using OCI by replacing the cumbersome ORACLE constructs such as logon data areas, cursor data areas, and external datatypes, the ORACLE array interface, bind variables, select-list-items, and the like with constructs familiar to a C programmer using the standard IO portion of the C library. ciORA also provides a consistent interface to ORACLE errors similar to the convention used in C's errno.

The basic models underlying ciORA are database descriptors and SQL descriptors. The two descriptors are user-level handles, similar to file pointers in the standard C library. They need not be examined by the user, but merely presented as necessary to other ciORA functions. Similar to the C file interface, ciORA functions exist to open, close, read, write, and perform other operations with the descriptors.

A database descriptor is a handle to an ORACLE database connection. A connection to an ORACLE database is established by opening a database descriptor with ora_dbo(), supplying a database name, logon name, password. Multiple open database descriptors are possible using successive calls to ora_dbo() and allow concurrent access to several database connections in the same program. Each database descriptor is independent of the others just as two concurrent sessions of SQLPlus are independent. An open database descriptor is a prerequisite for every other ciORA function.

An SQL descriptor is a handle to an ORACLE cursor, an object necessary for ORACLE to process SQL statements during a connect session. (However, knowledge of the correspondence of an SQL descriptor with an ORACLE cursor is unnecessary except to aid in understanding the underlying implementation.) An open SQL descriptor is necessary to process any SQL statement that is a query or contains substitution variables. Opening an SQL descriptor is done with ora_sqlo(). This function has a number of arguments which are described in detail in ora_sqlo(3). Among them are the database descriptor to which this SQL descriptor will apply, the SQL statement (complete with substitution variables), and information describing the format of input and output to and from the descriptor.

Following ora_sqlo(), the SQL descriptor is prepared for IO with ora_sqlr() and ora_sqlw(). (These functions may not apply if the SQL statement associated with the SQL descriptor does not support both input and output.) Input to the descriptor is accomplished through ora_sqlw() and supplies values for the substitution variables in the SQL statement. Output is retrieved with ora_sqlr() and returns results from the query.

IO to/from SQL descriptors occurs in two modes: internal buffering mode, and array mode. Both modes of operation are described in detail in ora_sqlo(3). Briefly, internal buffering mode buffers input and/or output from the SQL descriptor internally, copying data to or from the arguments of ora_sqlr() and ora_sqlw(). (ora_sqlri() and ora_sqlwi() are also used to read and write to SQL descriptors; they allow the use of indicator variables during reading and writing.) This allows the convenience of row-by-row processing while supporting the ORACLE array interface to make distributed transactions much more efficient. Array mode prevents the copying to/from internal buffers and transfers data directly to/from ORACLE to/from the given C arrays. This mode also uses the ORACLE array interface, but has the advantage of reducing the overhead of the data movement associated with internal buffering.

The general structure for a C program using ciORA is

     Open a database descriptor.
        Open an SQL descriptor.
           Perform I/O on the SQL descriptor.
        Close SQL descriptor.
     Close database descriptor.

An example appears below:

/* Sample ciORA program.

   Illustrates the use of all ciORA functions (except ora_sqlri() and    ora_sqlwi()) in a complete program.
   The SQL descriptor accepts input only. */

#include <stdio.h>
#include <ora_if.h>

int main() {

   ORA_DB *ora_db;
   ORA_SQL *ora_sql;

/* Open a database descriptor to the database given by ORACLE_SID.

      Logon as scott/tiger. */
   ora_db = ora_dbo( NULL, "scott", "tiger" );

/* Issue the SQL statement to create a database table. */
   ora_do( ora_db, "CREATE TABLE holidays "

           "( month VARCHAR2(3), day NUMBER, holiday VARCHAR2(30) ) " );

/* Turn autocommit on. */

   ora_acom( ora_db, 1 );

/* Open an SQL descriptor to insert data into the database. Cache

       5 rows in an internal buffer. */
   ora_sql = ora_sqlo( ora_db, "INSERT INTO holidays VALUES "

                       "(:1,:2,:3)", 5, "%5s%d%35s", 0, "" );

/* Write 3 rows to the SQL descriptor. */

   ora_sqlw( ora_sql, "Jan", 1, "New Year's Day" );
   ora_sqlw( ora_sql, "Jan", 17, "Martin Luther King, Jr. Day" );
   ora_sqlw( ora_sql, "Feb", 21, "Lincoln Day" );

/* Post, also commit as autocommit is on. */
   ora_sqlp( ora_sql );

/* Turn autocommit off. */

   ora_acom( ora_db, 0 );

/* Write 6 additional records to the SQL descriptor. */

   ora_sqlw( ora_sql, "May", 30, "Memorial Day" );
   ora_sqlw( ora_sql, "Jul", 4, "Independence Day" );
   ora_sqlw( ora_sql, "Sep", 5, "Labor Day" );
   ora_sqlw( ora_sql, "Oct", 10, "Columbus Day" );
   ora_sqlw( ora_sql, "Nov", 11, "Veteran's Day" );
   ora_sqlw( ora_sql, "Nov", 24, "Thanksgiving Day" );



   /* Commit the current transaction on the database connection, first


       posting the SQL descriptor, ora_sql, to flush the internal buffer. */

   ora_com( ora_db, 1, ora_sql );

/* Write another row to the SQL descriptor. */
   ora_sqlw( ora_sql, "Dec", 25, "Christmas Day" );

/* Post the SQL descriptor. */

   ora_sqlp( ora_sql );

/* Roll back all uncommitted changes. In this case, only the last

       insert will be removed. */
   ora_rollb( ora_db );

/* Rewrite the last record to the SQL descriptor as the previous write

       has been rolled back. */
   ora_sqlw( ora_sql, "Dec", 25, "Christmas Day" );

/* Post and close the SQL descriptor. */
   ora_sqlc( ora_sql );

/* Close the database descriptor, logging off the database. */
   ora_dbc( ora_db );
}

Author

---

dodson_at_aquila.plk.af.mil (Zane Dodson) MZA Associates Corporation

Acknowledgments

---

ciORA is not derived from any other software. However, I did borrow ideas from Kevin Stock's Oraperl. (Oraperl is an excellent Perl interface to ORACLE.)

Licensing and Copyright Information

---

ciORA will be distributed under the terms of the GNU General Public License (GPL) and is Copyright (C) 1994 Zane Dodson and MZA Associates Corporation. For more information regarding the terms and conditions for copying, distribution, and modification of this software, retrieve the file prep.ai.mit.edu:/pub/gnu/GPL. I will also include a copy of the GPL with the ciORA distribution.

Alpha Testing

---

ciORA was developed using

   ORACLE7 version 7.0.15.4.0
   SGI IRIS Indigo
   IRIX Aquila 5.2 02282014 IP7 mips

I am also interested in ORACLE users using different machines and operating systems, eg. SUN, HP, DEC, IBM, etc. as I do not have access to other ORACLE RDBMS servers. I am unfamiliar with ORACLE under DOS/Windows, I'll attempt to help, but have no experience using OCI under these systems. Consequently, DOS/Windows ALPHA testers will be largely on their own, but I'll help as much as possible.

For UNIX ALPHA testers, knowledge of C, Makefiles, linking under UNIX, and use of UNIX libraries are necessary, as well as knowledge of ORACLE and PRO*C. Knowledge of OCI is helpful, but someone familiar only with PRO*C should have no problem using ciORA. In fact, I hope PRO*C users find it much easier to use than ORACLE's precompilers.

I did not become involved with ORACLE until version 7, so I am unfamiliar with earlier versions. However, ciORA uses the ORACLE Array Interface which *I believe* did not exist prior to ORACLE 7. (If someone has further knowledge regarding early versions, I would appreciate a definitive answer.)

Please note that an ANSI C compiler is needed. (If someone still has a K&R C compiler and is interested in ALPHA testing the software, I will attempt to help in producing a version of the source usable under K&R.)

I will be available for bug reports, questions, comments, concerns, etc. via email and will respond as soon as possible, probably on a daily basis. If enough ORACLE users are interested in ALPHA testing ciORA, I will set up a mailing list for discussing ciORA in a group setting.

If you are interested in ALHPA testing ciORA, please send me email indicating the following information:

    ORACLE version
    Machine Type
    OS type (via uname -a)

I am considering the development of a C++ class library to provide basically the same functionality, but in the object oriented style of C++ with more extensive data manipulation capabilities. If you would be interested in this piece of software, please state so in your email message.

ciORA will be placed on our anonymous ftp server in a hidden directory until out of ALPHA. I expect to make an announcement to all ALPHA testers on September 16, 1994 to indicate the location and other information regarding ciORA.

Questions and comments are welcome.

Regards,

dodson_at_aquila.plk.af.mil (Zane Dodson) MZA Associates Corporation
10616 Royal Troon NE
Albuquerque, NM 87111
(505) 269-0590 Received on Sun Sep 11 1994 - 04:32:08 CEST