Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: oci c unix

Re: oci c unix

From: Tom Barnes <barnest_at_san.rr.com>
Date: 9 Oct 2002 14:01:57 -0700
Message-ID: <ae6b6116.0210091301.c3e4aac@posting.google.com> 





Below is a C++ program I've written that I usually use as a starting
point for my OCI programs. It should be easy to convert to C if you
are out of luck and have to use C. To execute a stored procedure
instead of straight SQL, use a SQL string like this:



string sSQL = "BEGIN mypackage.myprocedure(:param1,:param2); END;";



First, here's how to compile the code (using GCC on Solaris):
g++ -O3 -D_REENTRANT -Wall -D__EXTENSIONS__ -o ocitest ocitest.cpp \

-L/app/oracle/product/8.1.6/lib \
-I/app/oracle/product/8.1.6/rdbms/demo \
-I/app/oracle/product/8.1.6/rdbms/public \
-I/app/oracle/product/8.1.6/network/public -lclntsh \
-lresolv -lnsl -lm -lpthread





The code:

//---------------- ocitest.h -------------------------


#include <string>


#include <oci.h>



using std::string;



struct OCIException


{



  string message;



  OCIException(string mess) : message(mess)
  {


  }


};



class OCITest


{



private:

  string username;
  string password;
  string database;
  OCIEnv    * environment;
  OCIServer * server;
  OCIError  * error;
  OCISvcCtx * serviceContext;
  OCIBind   * bindHandle;
  dvoid     * temp;




  sword ret;



  void checkerr(OCIError * error, sword ret);
  void bindString(short,char*,sword,sb2*,OCIStmt *);
  void bindShort(short,short *,sb2*,OCIStmt *);
  void bindLong(short,long *,sb2*,OCIStmt *);
  void bindDouble(short,double *,sb2*,OCIStmt *);



public:


  OCITest(const string&,const string&,const string&);
  void connect();

  void select();
  void update();
  void insert();




  void dodelete();


  void disconnect();


};

//---------------- ocitest.cpp -------------------------


#include "ocitest.h"


#include <strstream>



OCITest::OCITest(const string& u,const string& p,const string& d)

                                  : username(u), password(p),


database(d)


{



}



void OCITest::connect()


{


  OCIEnvCreate((OCIEnv **)&environment, OCI_DEFAULT, 0,
                  (dvoid * (*)(dvoid *, size_t)) 0,
                  (dvoid * (*)(dvoid *, dvoid *, size_t)) 0,
                  (dvoid (*)(dvoid *, dvoid *)) 0,0,0);

          checkerr(error,




  OCIHandleAlloc((dvoid *) environment,(dvoid **) &error,(ub4)
OCI_HTYPE_ERROR,52,(dvoid **) &temp));



          checkerr(error,


  OCILogon(environment,error,&serviceContext,(unsigned char*)
username.c_str(),strlen((const char*) username.c_str()),(unsigned
char*) password.c_str(),strlen((const char*) password.c_str()),


           (unsigned char*) database.c_str(),strlen((const char*)
database.c_str())));


}



void OCITest::disconnect()


{



        checkerr(error,


  OCILogoff(serviceContext,error));



        checkerr(error,


  OCIHandleFree((dvoid *) error, OCI_HTYPE_ERROR));



  if (environment)


  {


        checkerr(error,


    OCIHandleFree((dvoid *) environment, OCI_HTYPE_ENV));
  }


}



void OCITest::insert()


{



  //Step 1: Allocate handle for SQL statement
  OCIStmt * sqlStatement;


          checkerr(error,


  OCIHandleAlloc((dvoid *) environment,(dvoid **)
&sqlStatement,OCI_HTYPE_STMT,100,(dvoid **) &temp));



  //Step 2: Prepare SQL statement


  string sSQL = "insert into testtable (name,age) values (:1,2)";


               checkerr(error,


  OCIStmtPrepare(sqlStatement,error,(unsigned char *) const_cast<char
*>(sSQL.c_str()),strlen(sSQL.c_str()),OCI_NTV_SYNTAX,OCI_DEFAULT));



  //Step 3 (optional): Bind variables in SQL
  char * param="due";


  signed short nullIndicator=1; //1==NULL
  bindString(1, param,sizeof(param),&nullIndicator,sqlStatement);



  //Step 4: Execute SQL


  ret = OCIStmtExecute(serviceContext,sqlStatement,error,(ub4) 1,(ub4)
0,(CONST OCISnapshot *) NULL,(OCISnapshot *) NULL,OCI_DEFAULT);



  checkerr(error, ret);



  //Step 5: Free the SQL statement handle


          checkerr(error,


  OCIHandleFree((dvoid *) sqlStatement, OCI_HTYPE_STMT));
}



void OCITest::update()


{



  //Step 1: Allocate handle for SQL statement
  OCIStmt * sqlStatement;


          checkerr(error,


  OCIHandleAlloc((dvoid *) environment,(dvoid **)
&sqlStatement,OCI_HTYPE_STMT,100,(dvoid **) &temp));



  //Step 2: Prepare SQL statement


  string sSQL = "update testtable set name = :1  where age=1";


               checkerr(error,


  OCIStmtPrepare(sqlStatement,error,(unsigned char *) const_cast<char
*>(sSQL.c_str()),strlen(sSQL.c_str()),OCI_NTV_SYNTAX,OCI_DEFAULT));



  //Step 3 (optional): Bind variables in SQL
  char * param="uno";


  signed short nullIndicator=0; //1==NULL
  bindString(1, param,sizeof(param),&nullIndicator,sqlStatement);



  //Step 4: Execute SQL


  ret = OCIStmtExecute(serviceContext,sqlStatement,error,(ub4) 1,(ub4)
0,(CONST OCISnapshot *) NULL,(OCISnapshot *) NULL,OCI_DEFAULT);



  checkerr(error, ret);



  //Step 5: Free the SQL statement handle


          checkerr(error,


  OCIHandleFree((dvoid *) sqlStatement, OCI_HTYPE_STMT));
}



void OCITest::dodelete()


{



  //Step 1: Allocate handle for SQL statement
  OCIStmt * sqlStatement;


          checkerr(error,


  OCIHandleAlloc((dvoid *) environment,(dvoid **)
&sqlStatement,OCI_HTYPE_STMT,100,(dvoid **) &temp));



  //Step 2: Prepare SQL statement


  string sSQL = "delete from testtable where name = :1";


               checkerr(error,


  OCIStmtPrepare(sqlStatement,error,(unsigned char *) const_cast<char
*>(sSQL.c_str()),strlen(sSQL.c_str()),OCI_NTV_SYNTAX,OCI_DEFAULT));



  //Step 3 (optional): Bind variables in SQL
  char * param="uno";


  signed short nullIndicator=0; //1==NULL
  bindString(1, param,sizeof(param),&nullIndicator,sqlStatement);



  //Step 4: Execute SQL


  ret = OCIStmtExecute(serviceContext,sqlStatement,error,(ub4) 1,(ub4)
0,(CONST OCISnapshot *) NULL,(OCISnapshot *) NULL,OCI_DEFAULT);



  checkerr(error, ret);



  //Step 5: Free the SQL statement handle


          checkerr(error,


  OCIHandleFree((dvoid *) sqlStatement, OCI_HTYPE_STMT));
}



void OCITest::select()


{



  //Step 1: Allocate handle for SQL statement
  OCIStmt * sqlStatement;


          checkerr(error,


  OCIHandleAlloc((dvoid *) environment,(dvoid **)
&sqlStatement,OCI_HTYPE_STMT,100,(dvoid **) &temp));



  //Step 2: Prepare SQL statement



  string sSQL = "select name,age from testtable where name <> :1";


               checkerr(error,


  OCIStmtPrepare(sqlStatement,error,(unsigned char *) const_cast<char
*>(sSQL.c_str()),strlen(sSQL.c_str()),OCI_NTV_SYNTAX,OCI_DEFAULT));



  //Step 3 (optional): Bind variables in SQL
  char * param="E";


  signed short nullIndicator=0; //1==NULL
  bindString(1, param,sizeof(param),&nullIndicator,sqlStatement);



  //Step 4: Define Output Variables


  OCIDefine * defnp = (OCIDefine *) 0;


  short name_length=20;


  char name[name_length];


              checkerr(error,


  OCIDefineByPos(sqlStatement, &defnp, error, 1, (dvoid *) name,
name_length+1, SQLT_STR, (dvoid *) 0, (ub2 *) 0, (ub2 *) 0,
OCI_DEFAULT));


  int age;


              checkerr(error,


  OCIDefineByPos(sqlStatement, &defnp, error, 2, (dvoid *) &age, (sb4)
sizeof(sword), SQLT_INT, (dvoid *) 0, (ub2 *)0, (ub2 *)0, (ub4)
OCI_DEFAULT));


  //Step 5: Execute query


  ret = OCIStmtExecute(serviceContext,sqlStatement,error,(ub4) 1,(ub4)
0,(CONST OCISnapshot *) NULL,(OCISnapshot *) NULL,OCI_DEFAULT);



  if (ret != OCI_NO_DATA && ret != OCI_SUCCESS_WITH_INFO)
  {


    checkerr(error, ret);


    cout << "Name:" << name << ", Age:" << age << endl;
  }


  else


    cout << "No data or info." << endl;



  //Step 6: Fetch all the data


  while ((ret = OCIStmtFetch(sqlStatement, error, (ub4) 1,  (ub4)
OCI_FETCH_NEXT, (ub4) OCI_DEFAULT)) == OCI_SUCCESS || ret ==
OCI_SUCCESS_WITH_INFO)



  {


    cout << "Name:" << name << ", Age:" << age << endl;
  }



  //Step 7: Free the SQL statement handle


          checkerr(error,


  OCIHandleFree((dvoid *) sqlStatement, OCI_HTYPE_STMT));
}



inline void OCITest::bindShort(short position,short * value,sb2 *
nullInd,OCIStmt * sqlStatement)


{



  int returnCode = OCIBindByPos(sqlStatement,&bindHandle,error,position,

              (ub1 *) value,(sword) sizeof(*value),SQLT_INT,
              nullInd,(ub2 *) 0,(ub2) 0,(ub4) 0,(ub4 *)


0,OCI_DEFAULT);



  checkerr(error,returnCode);


}



inline void OCITest::bindLong(short position,long * value,sb2 *
nullInd,OCIStmt * sqlStatement)


{



  int returnCode = OCIBindByPos(sqlStatement,&bindHandle,error,position,

              (ub1 *) value,(sword) sizeof(*value),SQLT_INT,
              nullInd,(ub2 *) 0,(ub2) 0,(ub4) 0,(ub4 *)


0,OCI_DEFAULT);



  checkerr(error,returnCode);


}



inline void OCITest::bindString(short position,char * value,sword
size,sb2 * nullInd,OCIStmt * sqlStatement)


{



  int returnCode = OCIBindByPos(sqlStatement,&bindHandle,error,position,

              (ub1 *) value,size,SQLT_STR,
              nullInd,(ub2 *) 0,(ub2) 0,(ub4) 0,(ub4 *)


0,OCI_DEFAULT);



  checkerr(error,returnCode);


}



inline void OCITest::bindDouble(short position,double * value,sb2 *
nullInd,OCIStmt * sqlStatement)


{



  int returnCode = OCIBindByPos(sqlStatement,&bindHandle,error,position,

              (ub1 *) value,(sword) sizeof(*value),SQLT_FLT,
              nullInd,(ub2 *) 0,(ub2) 0,(ub4) 0,(ub4 *)


0,OCI_DEFAULT);



  checkerr(error,returnCode);


}



void OCITest::checkerr(OCIError * error, sword ret)


{



  text errbuf[512];


  ub4 errcode;



  switch (ret)


  {


    case OCI_SUCCESS:


      break;


    case OCI_SUCCESS_WITH_INFO:

      throw OCIException("OCIError - OCI_SUCCESS_WITH_INFO\n");
      break;
    case OCI_NEED_DATA:
      throw OCIException("OCIError - OCI_NEED_DATA\n");
      break;
    case OCI_NO_DATA:
      throw OCIException("OCIError - OCI_NODATA\n");
      break;




    case OCI_ERROR:


    {


      (void) OCIErrorGet(error, (ub4) 1, (text *) NULL,(int
*)&errcode, errbuf, (ub4) sizeof(errbuf), OCI_HTYPE_ERROR);

      ostrstream oss;
      oss << "OCIError - " << errbuf << "\n" << ends;
      throw OCIException(oss.str());
      break;




    }


    case OCI_INVALID_HANDLE:

      throw OCIException("OCIError - OCI_INVALID_HANDLE\n");
      break;
    case OCI_STILL_EXECUTING:
      throw OCIException("OCIError - OCI_STILL_EXECUTE\n");
      break;
    default:
      break;




  }


}



int main()


{



  OCITest * myOCITest = new OCITest("username","password","SID");



  myOCITest->connect();


  cout << "Connected!" << endl;

  myOCITest->select();
  myOCITest->insert();
  myOCITest->update();
  myOCITest->dodelete();
  myOCITest->disconnect();




  cout << "Disconnected!" << endl;


  delete myOCITest;


}
Received on Wed Oct 09 2002 - 16:01:57 CDT












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US