一个OCI程序小例子

#include <stdio.h>

#include <stdlib.h>

#include <string.h>

#include <oci.h>

static OCIEnv           *p_env;

static OCIError         *p_err;

static OCISvcCtx        *p_svc;

static OCIStmt          *p_sql;

static OCIDefine        *p_dfn1    = (OCIDefine *) 0;

static OCIDefine        *p_dfn2    = (OCIDefine *) 0;

static OCIDefine        *p_dfn3    = (OCIDefine *) 0;

static OCIBind          *p_bnd    = (OCIBind *) 0;

int main()

{

   int             p_bvi;

   int             rc;

   char            errbuf[100];

   int             errcode;

   char            mysql[100];

   int id=0;

   char name[20]={0};

   char date[20]={0};

   /* Initialize OCI evironment*/

   rc = OCIEnvCreate((OCIEnv **) &p_env,OCI_DEFAULT,(dvoid *)0,

          (dvoid * (*)(dvoid *, size_t)) 0,

          (dvoid * (*)(dvoid *, dvoid *, size_t))0,

          (void (*)(dvoid *, dvoid *)) 0,

          (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, "syt", 3, "syt", 3, "171", 3);

   //rc = OCILogon(p_env, p_err, &p_svc, "hr/hr@orcl", 10, NULL, -1, NULL, -1);

   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);

   }

   else

   {

    printf("Connect to orcl successful!\n");

   }

   /* Allocate and prepare SQL statement */

   rc = OCIHandleAlloc((dvoid *) p_env, (dvoid **) &p_sql,

           OCI_HTYPE_STMT, (size_t) 0, (dvoid **) 0);

   /* set my sql statement */

   strcpy(mysql,"select id,name,to_char(dtime, 'yyyy-mm-dd hh24:MI:SS') as dtime from teacher");

   rc = OCIStmtPrepare(p_sql, p_err, mysql,

           (ub4) strlen(mysql), (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT);

   /* Bind the values for the bind variables */

//   p_bvi = 2;  /* Use employee_id=102 */

//   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);

   ub2 dbtype;

   /* Define the select list items */

   dbtype= SQLT_INT;

   rc = OCIDefineByPos(p_sql, &p_dfn1, p_err, 1, (dvoid *) &id,

           (sword)sizeof(int), dbtype, (dvoid *) 0, (ub2 *)0,

           (ub2 *)0, OCI_DEFAULT);

   dbtype= SQLT_STR;

   rc = OCIDefineByPos(p_sql, &p_dfn2, p_err, 2, (dvoid *) &name,

           (sword)sizeof(name), dbtype, (dvoid *) 0, (ub2 *)0,

           (ub2 *)0, OCI_DEFAULT);

   dbtype= SQLT_STR;

   rc = OCIDefineByPos(p_sql, &p_dfn3, p_err, 3, (dvoid *) &date,

           (sword)sizeof(date), dbtype, (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("%d, %s, %s\n", id, name, date);

      id= 0;

      memset(name, '\0', sizeof(name));

      memset(date, '\0', sizeof(date));

      rc= OCIStmtFetch(p_sql, p_err, 1, OCI_FETCH_NEXT, OCI_DEFAULT);

      //rc=OCIStmtFetch2(p_sql, p_err, 1, OCI_FETCH_NEXT, 1, OCI_DEFAULT);

  }

   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 0;

}