How to access Oracle 8.1 database on AIX 5.1 from C program

/*--------------------------------------------------------------------
 * simple example Pro*C program to select records from a database
 *
 *                                
 *--------------------------------------------------------------------
 */

#include <stdio.h>
#include <sqlca.h>

void sqlerror();

EXEC SQL BEGIN DECLARE SECTION;
char *connstr = "scott/tiger";
char db_ename[30];
int  db_deptno;
EXEC SQL END DECLARE SECTION;

void main() {
      EXEC SQL WHENEVER SQLERROR DO sqlerror();
      EXEC SQL WHENEVER SQLWARNING CONTINUE;
      EXEC SQL CONNECT :connstr;

      EXEC SQL WHENEVER NOTFOUND GOTO notfound;
      EXEC SQL SELECT ENAME, DEPTNO
             INTO   db_ename, db_deptno
               FROM   EMP
             WHERE  EMPNO = 7369;

found:
      printf("%s is in department %i\n", db_ename, db_deptno);
      return;

notfound:
      printf("Employee record not found in database.\n");
      return;
}

void sqlerror() {
      printf("Stop Error:\t%25i\n", sqlca.sqlcode);
      return;
}

Should one use OCI or the Oracle Precompilers?

OCI is superior to Pro*C in the following ways:
Performance is much better with OCI
Reduced code size
Direct access to built-in functions (No intermediate files or substitutions).
Piecewise Operation on LONG fields (All LONG field problems are solved)
In Pro*C one cannot dynamically allocate memory to be used as bind variables
You cannot control the Pro*C precompiler to provide better and more compilable C-code.
etc...


Common problems with OCI:

OCI code is difficult to write and to maintain
Very few people can write, let alone maintain OCI code
etc...
An OCI application program must do the following:
Connect to one or more databases: call the OCILogon (olog, olon or orlon) routines
Open the cursors needed by the program: use oexec, oexn, ofen or oftech calls.
Process the SQL statements that are needed to perform the application's tasks.
Close the cursors using the oclose routine.
Disconnect from the databases: use ologoff to close an open connection to oracle.

Look at this very basic sample Oracle OCI8 program:
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <oci.h>

#pragma comment(lib, "d:\\orant\\oci80\\lib\\msvc\\ora803.lib")

static OCIEnv           *p_env;
static OCIError         *p_err;
static OCISvcCtx        *p_svc;
static OCIStmt          *p_sql;
static OCIDefine        *p_dfn    = (OCIDefine *) 0;
static OCIBind          *p_bnd    = (OCIBind *) 0;

void main()
{
   int             p_bvi;
   char            p_sli[20];
   int             rc;
   char            errbuf[100];
   int             errcode;

   rc = OCIInitialize((ub4) OCI_DEFAULT, (dvoid *)0,  /* Initialize OCI */
           (dvoid * (*)(dvoid *, size_t)) 0,
           (dvoid * (*)(dvoid *, dvoid *, size_t))0,
           (void (*)(dvoid *, dvoid *)) 0 );

   /* Initialize evironment */
   rc = OCIEnvInit( (OCIEnv **) &p_env, OCI_DEFAULT, (size_t) 0, (dvoid **) 0 );

   /* Initialize handles */
   rc = OCIHandleAlloc( (dvoid *) p_env, (dvoid **) &p_err, OCI_HTYPE_ERROR,
           (size_t) 0, (dvoid **) 0);
   rc = OCIHandleAlloc( (dvoid *) p_env, (dvoid **) &p_svc, OCI_HTYPE_SVCCTX,
           (size_t) 0, (dvoid **) 0);

   /* Connect to database server */
   rc = OCILogon(p_env, p_err, &p_svc, "scott", 5, "tiger", 5, "d458_nat", 8);
   if (rc != 0) {
      OCIErrorGet((dvoid *)p_err, (ub4) 1, (text *) NULL, &errcode, errbuf, (ub4) sizeof(errbuf), OCI_HTYPE_ERROR);
      printf("Error - %.*s\n", 512, errbuf);
      exit(8);
   }

   /* Allocate and prepare SQL statement */
   rc = OCIHandleAlloc( (dvoid *) p_env, (dvoid **) &p_sql,
           OCI_HTYPE_STMT, (size_t) 0, (dvoid **) 0);
   rc = OCIStmtPrepare(p_sql, p_err, "select ename from emp where deptno=:x",
           (ub4) 37, (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT);

   /* Bind the values for the bind variables */
   p_bvi = 10;     /* Use DEPTNO=10 */
   rc = OCIBindByName(p_sql, &p_bnd, p_err, (text *) ":x",
           -1, (dvoid *) &p_bvi, sizeof(int), SQLT_INT, (dvoid *) 0,
           (ub2 *) 0, (ub2 *) 0, (ub4) 0, (ub4 *) 0, OCI_DEFAULT);

   /* Define the select list items */
   rc = OCIDefineByPos(p_sql, &p_dfn, p_err, 1, (dvoid *) &p_sli,
           (sword) 20, SQLT_STR, (dvoid *) 0, (ub2 *)0,
           (ub2 *)0, OCI_DEFAULT);

   /* Execute the SQL statment */
   rc = OCIStmtExecute(p_svc, p_sql, p_err, (ub4) 1, (ub4) 0,
           (CONST OCISnapshot *) NULL, (OCISnapshot *) NULL, OCI_DEFAULT);

   while (rc != OCI_NO_DATA) {             /* Fetch the remaining data */
      printf("%s\n",p_sli);
      rc = OCIStmtFetch(p_sql, p_err, 1, 0, 0);
   }

   rc = OCILogoff(p_svc, p_err);                           /* Disconnect */
   rc = OCIHandleFree((dvoid *) p_sql, OCI_HTYPE_STMT);    /* Free handles */
   rc = OCIHandleFree((dvoid *) p_svc, OCI_HTYPE_SVCCTX);
   rc = OCIHandleFree((dvoid *) p_err, OCI_HTYPE_ERROR);

   return;
}

My recommendation is to use Pro*C precompilers and C, C++ programs.
You have to downlaod (free) from OTN the Pro*C stuff and carefully read
which libraries and how have to use. Also look as alternative
the usage of
1. Forms, Reports as Rapid development Tools
2. VB
3. Java based WEB applications

Begin not from the language you know.
Base on the requirements and functionality of your application:
client/server or WEB.

I'm doing all my GUI stuff in Delphi and am pretty satisfied with that. What I'm looking for is a way to handle the after hours, unattended batch update stuff. C programs running from cron on the RS/6000 is the optimal solution. A couple of questions about your responses:

1) What, exactly, is Pro*C? Is it a full-blown compiler or a pre-compiler that produces files I then pass to my C compiler?

2) In your 3:55 post, I don't see how the program connects to the appropriate database. Where is that specified?

3) The OCI stuff looks like too much for me right now. I would like to pursue the Pro*C option. I've made a quick check of  otn.oracle.com and can't seem to find it. Can you point me in the right direction?

4) Are there 3rd party tools out there that can make this process easier?

Thanks,

Jim

1)Pro*C is precompiler of C programs.

In the example you have the Oracle SELECT statement
 EXEC SQL SELECT ENAME, DEPTNO
          INTO   db_ename, db_deptno
             FROM   EMP
          WHERE  EMPNO = 7369;
which is not C code.
The precompiler compiles this SQL script in the appropriate
C statements and calls to Oracle.

2) You have the connection string
char *connstr = "scott/tiger";

which is used in the created from the precompiler C code to connect to the Oracle instance

3. Be aware that OCI programs are hard to be checked and the practice (make a search here)
shows that there are many issues and very specific and hard to understand parameters, etc.
So its OK that you chose Pro*C
 Sources:
http://otn.oracle.com/documentation/index.html                   (needs free registration only)
http://otn.oracle.com/documentation/database10g.html       for Oracle 10g
      View Library   or Download Library

4. I do not know 3rd party tools, but I am not C profi, I am Oracle DBA.
    Looking in Google will help you to get oriented

I really appreciate your help on this. The links you provided point to documentation. I'm trying to find the actual Pro*C executable(s) for my RS/6000 (AIX 5.1) that are compatible with an Oracle 8.1 database. Should I repost this question in another area? I guess I need to find someone who has actually done this. Any recommendations?

Look in the documentation for PRO*C.
It explains step by step how to create the environment and how to
run programs written in C.

On my docs disk I have the following:

1.Oracle C++ Call Interface, Programmer’s Guide
Release 2 (9.2) March 2002 Part No. A96583-01
2 Pro*C/C++ Precompiler, Programmer’s Guide
Release 9.2 March 2002 Part No. A97269-01

How does one compile a precompiler program?


The following syntax is used to precompile a Pro*C program:
     proc iname=myprog.pc host=C

Oracle includes some sample precompiler programs with it's installation. These are normally located under the $ORACLE_HOME/precomp/demo/proc (or related) directories.
After you've precompiled your program, invoke the standard host language compiler and linker. Also remember, if you modify your source code, change the original precompiled source program. In C, for example, you would modify the *.PC file and not the *.C file. The *.C file gets overwritten every time you precompile your program.

Look at the following table for other host languages:

Language: Program: Precompiler: Precompiled to:  

C  *.pc  proc  *.c  
COBOL  *.pco  procob  *.cob  
FORTRAN  *.pfo  profor  *.for  
PASCAL  *.ppa  propas  *.pas  
ADA  *.pad  proada  *.ada  


How does one compile a PRO*C program?

You can study the sample makefile provided by Oracle and construct your own, but it would probably be better to just use "make" to precompile and then compile your program in one step. Look at this Unix examples:
Oracle7:

   make -f $ORACLE_HOME/precomp/demo/proc/proc.mk build EXE=myprog OBJS=myprog.o

Oracle8:

   make -f $ORACLE_HOME/precomp/demo/proc/demo_proc.mk build EXE=myprog OBJS=myprog.o

NOTE: OBJS and EXE has to be entered in caps!!!


How can I tell Pro*C what directory my header files are in?

Is there some way to tell the Pro*C preprocessor to search other directories for include (*.h) files, similar to the C compiler switch "-I"? I am hoping to avoid having to change all of my #include statements...
As documented in the Pro*C manual (in the chapter called "Running the Precompiler") there is an INCLUDE flag that can be used for this. Look at this example:


   proc iname=inputfile.pc include=directory1 include=directory2...


What is an indicator variable and why should I use it?

Indicator variables are used to explicitly handle NULL values. When you SELECT or FETCH a NULL value into a host variable/array an "ORA-01405: fetched column values is NULL" run-time error will result.
This behaviour was introduced with Oracle7 when you recompiled your programs with the DBMS=V7 (the default) precompiler option.

One workaround for this is to use the NVL() function to prevent the selection of NULL values.

Example:


   EXEC SQL SELECT ENAME, SAL, COMM
            INTO   :emp_name, :emp_sal, :emp_comm:comm_indicator
            FROM   emp;
   if (comm_indicator == -1)
       pay = emp_sal;
   else
       pay = emp_sal + emp_comm;

How does one execute PL/SQL code from Pro*C?

Look at this example:
        EXEC SQL EXECUTE
             begin
               dbms_application_info.set_client_info('My C Program with embedded PL/SQL');
             end;
        END-EXEC;



Look also here:
http://www-db.stanford.edu/~ullman/fcdb/oracle/or-proc.html

Those are good but I don't think I have the Pro*C compiler executables installed on my system. Other research I've done suggests that the Pro*C program is called "proc" and I don't have the file on my computer. Do you know if Pro*C is normally included with an Oracle 8 installation (perhaps as an optional package) or if it must be purchased separately? Do you have access to an RS/6000 with Oracle 8 installed?

No, I am using Windows.
But you can download the installation CD from OTN (if you do not have CD).
Also launch the Oracle Installer and click "Installed Products'
Figure out if Pro*C compiler is installed.
If not - then chose "Custom Install" and istall it.

you have to use the Client CD:

The pre-compilers comes as the oracle client component. The installation of pre-compiler Pro *c is to de done as post installation (ie) is to be carried out once the databse installation is over.
The installation hence could not be possible in a single step.

I've got CD's for the server (Oracle 8i Standard for AIX) and various CD's for clients. None of them say "Client for AIX". Should I be looking for one of these? I really doubt that a Windows version of proc would run on the RS/6000 and I really don't want to develop these programs on a PC. This really is the crux of the problem. I have to find the AIX version of the Pro*C program to solve this problem. Do you know an AIX guy here at EE who we could help?

No, it is Unix version.