Re: Connect to an Oracle DB from a C program

From: George Dau <gedau_at_isa.mim.com.au>
Date: Tue, 21 Sep 1999 02:52:17 GMT
Message-ID: <37e6f186.14505638@158.54.6.109>

"MV" <mv_at_yahoo.com> wrote:

]Hi:
]I have to connect to an Oracle Database from a C program... Could anyone
]help me finding a pattern program for such a connection.
]Would a shell script be more appropriate (I should not use any Perl
]actually).
]Thanks,
]Edouard
]

Sorry, this doesn't do anything except log in and log out, and probably does not even compile at the moment, but might give you start. What I wanted was ecOCI that did all the real dirty stuff, which I could call easily from a more simple program. Here is ecOCI:

---

#include <oracle/oci.h>

boolean ecOCIloggedon=FALSE;

sword	ecOCIstatus;
OCIEnv		*ecOCIenvhp;
OCISvcCtx	*ecOCIsvchp;
OCIError	*ecOCIerrhp;
OCIStmt		*ecOCIsqlhp;

void ecOCIlogoff () {

if (ecOCIloggedon) OCILogoff(ecOCIsvchp, ecOCIerrhp);
if (ecOCIenvhp) OCIHandleFree(ecOCIenvhp, OCI_HTYPE_ENV);
if (ecOCIerrhp) OCIHandleFree(ecOCIerrhp, OCI_HTYPE_ERROR);
if (ecOCIsqlhp) OCIHandleFree(ecOCIsqlhp, OCI_HTYPE_STMT);

ecOCIloggedon=FALSE;
}

void ecOCIchck(sword status) {
text errbuf[512];
sb4 errcode = 0;

switch (status) {
case OCI_SUCCESS:
  break;
case OCI_SUCCESS_WITH_INFO:
  OCIErrorGet((dvoid *)ecOCIerrhp, (ub4) 1, (text *) NULL, &errcode,

               errbuf, (ub4) sizeof(errbuf), OCI_HTYPE_ERROR);   printf("ecOCI Warn: %s",errcode, errbuf);   break;
default:
  OCIErrorGet((dvoid *)ecOCIerrhp, (ub4) 1, (text *) NULL, &errcode,

               errbuf, (ub4) sizeof(errbuf), OCI_HTYPE_ERROR);   printf("ecOCI Error: %s", errbuf);
  ecOCIlogoff();
  exit (-1); }
}

int ecOCIlogon (text *user, text *pswd ,text *sid) { if (OCIInitialize((ub4) OCI_DEFAULT, (dvoid *) 0, (dvoid * (*) ()) 0, (dvoid * (*) ()) 0, (void (*) ()) 0)) {   printf("ecOCI Error: OCIInitialize failed.\n");   exit (-1);
}
if (OCIEnvInit(&ecOCIenvhp, (ub4) OCI_DEFAULT, (size_t) 0, (dvoid **) 0)) {
  printf("ecOCI Error: OCIEnvInit failed.\n");   if (ecOCIenvhp) OCIHandleFree(ecOCIenvhp, OCI_HTYPE_ENV);   exit (-1);
}
if (OCIHandleAlloc(ecOCIenvhp,(dvoid **) &ecOCIerrhp, (ub4) OCI_HTYPE_ERROR, (size_t) 0, (dvoid **) 0)) {   printf("ecOCI Error: OCIHandleAlloc could not allocate error handle.\n");
  if (ecOCIenvhp) OCIHandleFree(ecOCIenvhp, OCI_HTYPE_ENV);   if (ecOCIerrhp) OCIHandleFree(ecOCIerrhp, OCI_HTYPE_ERROR);   exit (-1);
}
if (OCIHandleAlloc(ecOCIenvhp,(dvoid **) &ecOCIsqlhp, (ub4) OCI_HTYPE_STMT, (size_t) 0, (dvoid **) 0)) {   printf("ecOCI Error: OCIHandleAlloc could not allocate sql statement handle.\n");

  if (ecOCIenvhp) OCIHandleFree(ecOCIenvhp, OCI_HTYPE_ENV);
  if (ecOCIerrhp) OCIHandleFree(ecOCIerrhp, OCI_HTYPE_ERROR);
  if (ecOCIsqlhp) OCIHandleFree(ecOCIsqlhp, OCI_HTYPE_STMT);

  exit (-1);
}
ecOCIchck(OCILogon(ecOCIenvhp, ecOCIerrhp, &ecOCIsvchp, user, strlen(user),

          pswd, strlen(pswd), sid, strlen(sid))); ecOCIloggedon=TRUE;
return(0);}

/*

• ecOCIsql - define the SQL Select statment */ int ecOCIsql(text *select) { ecOCIchck(OCIStmtPrepare(ecOCIsqlhp, ecOCIerrhp, select, strlen( (char *) select), (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT)); return(0);} /*
• ecOCIdefine - bind the C variables with the data from Oracle
• ecOCIdefine(&owner, 1, SQLT_STR, &defnp1);
• ecOCIchck(OCIDefineByPos(ecOCIsqlhp, &defnp1, ecOCIerrhp, 1, (dvoid *) &owner, sizeof(owner),
• SQLT_STR, &ind1, (ub2 *) 0, (ub2 *) 0, OCI_DEFAULT)); */ int ecOCIdefine( (dvoid *) Cvar, pos, type, (dvoid *)defnp, (dvoid *)ind) { ecOCIchck(OCIDefineByPos(ecOCIsqlhp, defnp, ecOCIerrhp, 1, Cvar, sizeof(Cvar), type, ind, (ub2 *) 0, (ub2 *) 0, OCI_DEFAULT)); return(0);}

  ---

  Haven't puked yet? Here's an example of the "simple" program that would use the above to work with a database:

  ---

  #include <stdio.h> #include <stdlib.h> #include <string.h>

int main () {
#include "ecOCI.c"

/*

• Define place-holders for each item to be returned for each row */ text owner[31], table_name[31], tablespace_name[31]; sword num_rows; /*
• Define a "bind handle" and indicator for each of the above (one for each item above) */ static OCIDefine *defnp1 = (OCIDefine *) 0; static OCIDefine *defnp2 = (OCIDefine *) 0; static OCIDefine *defnp3 = (OCIDefine *) 0; static OCIDefine *defnp4 = (OCIDefine *) 0; ub2 ind1, ind2, ind3, ind4; /*
• Now lets get at that data... */ /*
• First we need to log on to the database */ ecOCIlogon("SYSTEM","DESKTOP",""); /*
• Now define the select statement we want to run */ ecOCIsql("SELECT OWNER, TABLE_NAME, TABLESPACE_NAME, INITIAL_EXTENT FROM SYS.DBA_TABLES"); /*
• Now for each column, define the column number, variable to hold that data, and a
• define handle. The column number is relative to the position in the select statemnt,
• i.e. NUM_ROWS is the 19th column in SYS.DBA_TABLES, but the 4th column in the
• select statment above, so it is allocated as column 4 in the ecOCIDefine ecOCIdefine(&owner, 1, SQLT_STR, &defnp1, &ind1); ecOCIdefine(&table_name, 2, SQLT_STR, &defnp2, &ind2); ecOCIdefine(&tablespace_name, 3, SQLT_STR, &defnp3, &ind3); ecOCIdefine(&num_rows, 4, SQLT_INT, &defnp4, &ind4); /*
• ecOCIchck(OCIDefineByPos(ecOCIsqlhp, &defnp1, ecOCIerrhp, 1, (dvoid *) &owner, sizeof(owner),
• SQLT_STR, &ind1, (ub2 *) 0, (ub2 *) 0, OCI_DEFAULT));
• ecOCIchck(OCIDefineByPos(ecOCIsqlhp, &defnp1, ecOCIerrhp, 2, (dvoid *) &table_name, sizeof(table_name),
• SQLT_STR, &ind2, (ub2 *) 0, (ub2 *) 0, OCI_DEFAULT));
• ecOCIchck(OCIDefineByPos(ecOCIsqlhp, &defnp1, ecOCIerrhp, 3, (dvoid *) &tablespace_name, sizeof(tablespace_name),
• SQLT_STR, &ind3, (ub2 *) 0, (ub2 *) 0, OCI_DEFAULT));
• ecOCIchck(OCIDefineByPos(ecOCIsqlhp, &defnp1, ecOCIerrhp, 4, (dvoid *) &num_rows, sizeof(num_rows),
• SQLT_INT, &ind4, (ub2 *) 0, (ub2 *) 0, OCI_DEFAULT));
• printf("Defined\n"); */ /*
• Now Fetch those rows
• while (ecOCIfetch() == 0) {
• printf("%s %s %s %i\n",owner, table_name, tablespace_name, num_rows); */ ecOCIchck(OCIStmtExecute(ecOCIsvchp, ecOCIsqlhp, ecOCIerrhp, (ub4) 0, (ub4) 0, (CONST OCISnapshot *) NULL, (OCISnapshot *) NULL, OCI_DEFAULT)); printf("Executed\n");

ecOCIstatus = 0;
while (ecOCIstatus == 0) {
  ecOCIstatus = OCIStmtFetch(ecOCIsqlhp, ecOCIerrhp, (ub4) 1, (ub4) OCI_FETCH_NEXT, (ub4) OCI_DEFAULT);
  if (ecOCIstatus != 100) {ecOCIchck(ecOCIstatus);}   printf("%d ", ecOCIstatus);
  printf("%s %s %s %d\n",owner,table_name,tablespace_name,num_rows); }

/*

• And now log off */ ecOCIlogoff(); exit (0);}

  ---

  I'll finish it one day; but right now even looking at it makes my skin change colour.

--
 ,-,_|\ George Dau - Unix (Solaris, DEC Unix, Linux), Oracle, Internet. __

/    * \ Home: gedau_at_pobox.com         !  Any views or opinions expressed (OO)
\_,--\_/ Work: gedau_at_isa.mim.com.au    !  above may be mine, but are NOT ( \/ )
      v   WWW: http://pobox.com/~gedau !  necessarily those of M.I.M.     W--W

Received on Mon Sep 20 1999 - 21:52:17 CDT