Oracle Database Development (5). A example for OCI Development
Vert Melon
Jun 24,2007
1.Preface
If you skimmed through some articles wrote in series before, you would find that i am not
very interested in Pro*C , for i didn't use much ink . Frankly speaking , i prefer to use OCI or
OCCI instead , and the mainly purpose of this series is to give some actual help to the developer
turning to OCI or OCCI .
I am not wise ,for i couldn't tell the difference about the efficiency between OCI/OCCI and
Pro*C. The reason i don't feel comfortable with Pro*C is very simple . If you debug a Pro*C project ,
you would be trapped in so many useless lines which build by Pro*C automaticly . Some lines
may like this , listed as follows .
{
struct sqlexd sqlstm;
sqlstm.sqlvsn = 12;
sqlstm.arrsiz = 4;
sqlstm.sqladtp = &sqladt;
sqlstm.sqltdsp = &sqltds;
...
It never appears in OCI or OCCI .
But this is a my own opinion only . Forget it if you do not admit it , then let's make the first
stride . I'll show you a full example using OCI , since I consider the best way to study a new
thing is peruse a good example consulting reference books all the time .
Please get the OCI document named 《Oracle Call Interface Programmer's Guide》.
2.Environment
This example is tested in Oracle9i Enterprise Edition Release 9.2.0.4.0 , Redhat Linux 9
installed in virtual machine . Because i am not good at "vi" in Linux , then i find a method that
Linux and Windows share the same directory including the source file . So i can edit the source
files in Windows , compile the files using user "root" in Linux , and run it using user "oracle" in
Linux . Of course i control the virtual machine by telnet or ssh here .
3.File list
Makefile
Common.h a public shared head file
Exception.h
Exception.cpp a base exception class which do nothing
OCIException.h
OCIException.cpp a OCI exception class inherited by the Exception class , handles
the exceptions that occur in the OCI processes
OCIError.h
OCIError.cpp a OCI error class , prints the information of the OCI error
OCIDB.h
OCIDB.cpp the main class contains lots of functions select or edit data from database
Main.cpp the main entrance of the program
4.File Content
The content of each file is listed below in the order listed above . I ' ll give a particular
comment for each file in flowing articles .
-------------------------------------------------------------------------------------------------------
Oracle數據庫開發(五).OCI開發示例
草木瓜
20070624
一、前言
如果瀏覽過以前的系列文章，你會發現我對ProC的興趣并不是非常大。坦率地講，我
更喜歡OCI和OCCI，這個系列文章的主要目的就是為了給準備使用OCI或者OCCI的開發人
員提供一些實際的幫助。
我水平有限，并不了解OCI/OCCI和Pro*C在效率上有什么差異。我不太喜歡Pro*C的原
因很簡單。當你調試一個Pro*C項目，你可能會限入一堆無用的代碼行，都是自動生成的。
語句和下面列出的很類似：
{
struct sqlexd sqlstm;
sqlstm.sqlvsn = 12;
sqlstm.arrsiz = 4;
sqlstm.sqladtp = &sqladt;
sqlstm.sqltdsp = &sqltds;
...
這種情況在OCI或者OCCI是不會存在的。
不過這只是我的個人觀點。如果不認同就無視它，我們先邁開第一步，這里會給出
一個完整的OCI示例，我一直認為學習新東西的最好方法就是選擇一個典型的示例，結
合相關參考手冊去研究。
請獲取OCI文檔《OCI開發指南》。
二、環境
這個示例在虛擬機 Redhat Linux 9 Oracle9i 企業版 9.2.0.4.0 測試通過。我并不太擅
長在Linux下使用vi，我采用的方案是Linux和Windows共享一個源文件目錄。這樣我可
以在Windows下編輯源文件，在Linux下用root用戶編譯，然后在Linux下用oracle用戶
運行。
當然這里是使用telnet和ssh控制虛擬機。
三、文件列表
Makefile
Common.h 公共頭文件
Exception.h
Exception.cpp 錯誤處理基礎類，無動作
OCIException.h
OCIException.cpp 由Exception類繼承，用于處理OCI過程中的錯誤
OCIError.h
OCIError.cpp OCI錯誤打印類，用于打印錯誤信息
OCIDB.h
OCIDB.cpp 主要的功能函數，完成讀取和編譯數據
Main.cpp 程序的主入口
四、文件內容
按照上面的順序，把每個文件的具體內容全列在下面。在以后的文章中會給出
文件的詳細說明。
附全文件內容：
<Makefile>
-----------------------------------
ORACLE_HOME=/home/ora/ora9/oracle
ORACLE_LIB = $(ORACLE_HOME)/lib
OCI_DEMO_H = $(ORACLE_HOME)/rdbms/demo
OCI_PUBLIC_H = $(ORACLE_HOME)/rdbms/public
CC = g++
OBJS = OCIDB.o OCIException.o Exception.o OCIError.o Main.o
PROG = OCIDB
all:$(PROG)
$(PROG):$(OBJS)
@echo "[link] ... "
@echo "---------------------"
$(CC) -g -o $(PROG) $(OBJS) -L$(ORACLE_LIB) -lclntsh
@echo
clean:
rm -f $(PROG) $(OBJS)
.SUFFIXES: .cpp .c
.cpp.o:
@echo "[$*.o]"
@echo "---------------------"
$(CC) -g -o $*.o -I$(OCI_DEMO_H) -I$(OCI_PUBLIC_H) -c $*.cpp
@echo
<Common.h>
-----------------------------------
#include <unistd.h>
#include <stdio.h>
#include <string.h>
#include <oci.h>
#include <stdlib.h>
#include <ctype.h>
<Exception.h>
-----------------------------------
class Exception {
public:
Exception(int errno);
virtual char * GetErrMsg()=0;
virtual char * GetErrFunc()=0;
virtual int GetErrNo()=0;
protected:
int m_iErrNo;
char m_sErrBuff[512];
char m_sErrFunc[128];
};
<Exception.cpp>
-----------------------------------
#include "Exception.h"
Exception::Exception(int errno) {
this->m_iErrNo = errno;
this->m_sErrBuff[0]=0;
this->m_sErrFunc[0]=0;
}
<OCIException.h>
-----------------------------------
#include "Common.h"
#include "Exception.h"
class OCIException : public Exception {
public:
OCIException(sb4 errno);
OCIException(sb4 errno, char * errfunc);
OCIException(sb4 errno, dvoid * erroci);
OCIException(sb4 errno, dvoid * erroci, char * errfunc);
/**/
char * GetErrMsg();
char * GetErrFunc();
int GetErrNo();
private:
void CheckError(sb4 errno);
void CheckError(sb4 errno, dvoid * erroci);
};
<OCIException.cpp>
-----------------------------------
#include "OCIException.h"
void OCIException::CheckError(sb4 errno) {
text errbuf[512];
switch (errno)
{
case OCI_SUCCESS:
break;
case OCI_SUCCESS_WITH_INFO:
sprintf(this->m_sErrBuff, "OCI_SUCCESS_WITH_INFO");
break;
case OCI_NEED_DATA:
sprintf(this->m_sErrBuff, "OCI_NEED_DATA");
break;
case OCI_NO_DATA:
sprintf(this->m_sErrBuff, "OCI_NODATA");
break;
case OCI_ERROR:
sprintf(this->m_sErrBuff, "OCI_UNKNOWN_ERROR");
break;
case OCI_INVALID_HANDLE:
sprintf(this->m_sErrBuff, "OCI_INVALID_HANDLE");
break;
case OCI_STILL_EXECUTING:
sprintf(this->m_sErrBuff, "OCI_STILL_EXECUTE");
break;
case OCI_CONTINUE:
sprintf(this->m_sErrBuff, "OCI_CONTINUE");
break;
default:
break;
}
}
void OCIException::CheckError(sb4 errno, dvoid * erroci) {
text errbuf[512];
switch (errno)
{
case OCI_SUCCESS:
break;
case OCI_SUCCESS_WITH_INFO:
sprintf(this->m_sErrBuff, "OCI_SUCCESS_WITH_INFO");
break;
case OCI_NEED_DATA:
sprintf(this->m_sErrBuff, "OCI_NEED_DATA");
break;
case OCI_NO_DATA:
sprintf(this->m_sErrBuff, "OCI_NODATA");
break;
case OCI_ERROR:
OCIErrorGet((dvoid *) erroci, (ub4) 1, (text *) NULL, &this->m_iErrNo, errbuf, (ub4) sizeof(errbuf), OCI_HTYPE_ERROR);
sprintf(this->m_sErrBuff, "%.*s", strlen((char *)errbuf)-1, errbuf);
break;
case OCI_INVALID_HANDLE:
sprintf(this->m_sErrBuff, "OCI_INVALID_HANDLE");
break;
case OCI_STILL_EXECUTING:
sprintf(this->m_sErrBuff, "OCI_STILL_EXECUTE");
break;
case OCI_CONTINUE:
sprintf(this->m_sErrBuff, "OCI_CONTINUE");
break;
default:
break;
}
}
OCIException::OCIException(sb4 errno) : Exception((int)errno) {
this->CheckError(errno);
}
OCIException::OCIException(sb4 errno, char * errfunc) : Exception((int)errno) {
strcpy(this->m_sErrFunc, errfunc);
this->CheckError(errno);
}
OCIException::OCIException(sb4 errno, dvoid * erroci) : Exception((int)errno) {
this->CheckError(errno, erroci);
}
OCIException::OCIException(sb4 errno, dvoid * erroci, char * errfunc) : Exception((int)errno) {
strcpy(this->m_sErrFunc, errfunc);
this->CheckError(errno, erroci);
}
char * OCIException::GetErrMsg() {
return this->m_sErrBuff;
}
char * OCIException::GetErrFunc() {
return this->m_sErrFunc;
}
int OCIException::GetErrNo() {
return this->m_iErrNo;
}
<OCIError.h>
-----------------------------------
#include "Common.h"
class OCIError {
public:
static void PrintError(int errn);
static void PrintError(int errno, char * errfunc);
private:
static void CheckError();
static int m_iErrNo;
static char m_sErrBuff[512];
static char m_sErrFunc[128];
};
<OCIError.cpp>
-----------------------------------
#include "OCIError.h"
int OCIError::m_iErrNo = 0;
char OCIError::m_sErrBuff[512] = {0};
char OCIError::m_sErrFunc[128] = {0};
void OCIError::CheckError() {
switch (OCIError::m_iErrNo)
{
case OCI_SUCCESS:
break;
case OCI_SUCCESS_WITH_INFO:
sprintf(OCIError::m_sErrBuff, "OCI_SUCCESS_WITH_INFO");
break;
case OCI_NEED_DATA:
sprintf(OCIError::m_sErrBuff, "OCI_NEED_DATA");
break;
case OCI_NO_DATA:
sprintf(OCIError::m_sErrBuff, "OCI_NODATA");
break;
case OCI_ERROR:
sprintf(OCIError::m_sErrBuff, "OCI_UNKNOWN_ERROR");
break;
case OCI_INVALID_HANDLE:
sprintf(OCIError::m_sErrBuff, "OCI_INVALID_HANDLE");
break;
case OCI_STILL_EXECUTING:
sprintf(OCIError::m_sErrBuff, "OCI_STILL_EXECUTE");
break;
case OCI_CONTINUE:
sprintf(OCIError::m_sErrBuff, "OCI_CONTINUE");
break;
default:
break;
}
}
void OCIError::PrintError(int errno) {
OCIError::m_iErrNo=errno;
OCIError::CheckError();
printf("%s\n", OCIError::m_sErrBuff);
}
void OCIError::PrintError(int errno, char * errfunc) {
OCIError::m_iErrNo=errno;
strcpy(OCIError::m_sErrFunc, errfunc);
OCIError::CheckError();
printf("%s\n", OCIError::m_sErrFunc);
printf("%s\n", OCIError::m_sErrBuff);
}
<OCIDB.h>
-----------------------------------
#include "Common.h"
#define MAX_VAR_LEN 10
typedef struct {
ub2 VarType;
char * VarName;
int VarLen;
union {
char * ValueChar;
float * Valuefloat;
int * ValueInt;
};
} TBindVar;
typedef struct {
char VarName[MAX_VAR_LEN];
unsigned char VarType;
unsigned char VarSize;
unsigned char VarPrecision;
unsigned char VarScale;
unsigned char VarIsNull;
union {
char * ValueChar;
float * ValueFloat;
int * ValueInt;
};
} TSelectVar;
#define MAX_BINDVAR_COUNT 10
#define MAX_SELECTVAR_COUNT 10
#define TYPE_INT 0
#define TYPE_FLOAT 1
#define TYPE_STRING 2
class OCIDB {
private:
OCIEnv * m_pOCIEnv;
OCIError * m_pOCIError;
OCISvcCtx * m_pOCISvcCtx;
OCIServer * m_pOCIServ;
OCISession * m_pOCISession;
OCIStmt * m_pOCIStmt;
OCIBind * m_pOCIBind;
OCIDefine * m_pOCIDefine;
OCIParam * m_pOCIParam;
TBindVar m_BindVars[MAX_BINDVAR_COUNT];
TSelectVar m_SelectVars[MAX_SELECTVAR_COUNT];
int m_iBindVarsCount;
int m_iSelectVarsCount;
char m_sUser[10];
char m_sPwd[10];
char m_sDbName[10];
void inline StrUpper(char * str);
bool inline StrCmp(const char *ori, const char *des);
public:
OCIDB();
~OCIDB();
/* Single User, Single Connection */
int Single_Conn();
void Single_Disc();
/* Multiple Sessions or Connections */
int Multiple_Conn();
void Multiple_Disc();
/* execute sql with none query */
int ExecuteSql(char * sql);
/* execute sql with bind vars */
void BindInitVars();
void BindClearVars();
void BindAddVar(char * name, char * value);
void BindAddVar(char * name, int value);
int BindSql(char * sql);
/* prepare sql */
int UserPrepare(char * sql);
int UserFree();
int UserBind(char * name, char * value);
int UserBind(char * name, int value);
int UserExecute();
int UserCommit();
int UserRollback();
int UserSelect(char * sql);
int UserFetch();
int UserGetInt(int index);
int UserGetInt(char * name);
char * UserGetString(int index);
char * UserGetString(char * name);
float UserGetFloat(int index);
float UserGetFloat(char * name);
int UserSelectFree();
};
<OCIDB.cpp>
-----------------------------------
#include "OCIDB.h"
#include "OCIException.h"
#include "OCIError.h"
OCIDB::OCIDB() {
strcpy(this->m_sUser, "liwei");
strcpy(this->m_sPwd , "liwei");
strcpy(this->m_sDbName, "local");
this->BindInitVars();
}
OCIDB::~OCIDB() {
}
int OCIDB::Single_Conn() {
try {
sword errno;
/*
OCIEnvCreate (&envhp, mode, (CONST dvoid *)0, 0, 0, 0, (size_t)0, (dvoid **)0);
*/
errno = OCIEnvCreate ( &this->m_pOCIEnv, OCI_OBJECT, (dvoid *)0, (dvoid * (*)(dvoid *, size_t)) 0, (dvoid * (*)(dvoid *, dvoid *, size_t))0, (void (*)(dvoid *, dvoid *)) 0, (size_t) 0, (dvoid **) 0 );
if(errno) {
throw OCIException(errno, "OCIDB::Single_Conn OCIEnvCreate");
}
/* Create Error Handle */
errno = OCIHandleAlloc (
(dvoid *) this->m_pOCIEnv, (dvoid **) &this->m_pOCIError, OCI_HTYPE_ERROR, (size_t) 0, (dvoid **) 0 );
if(errno) {
throw OCIException(errno, "OCIDB::Single_Conn OCIHandleAlloc");
}
/* Logon */
errno = OCILogon (
this->m_pOCIEnv, this->m_pOCIError, &this->m_pOCISvcCtx, (const OraText *)this->m_sUser, strlen(this->m_sUser), (const OraText *)this->m_sPwd, strlen(this->m_sPwd), //(const OraText *)this->m_sDbName, strlen(this->m_sDbName)
(const OraText *)"",0 );
if(errno) {
throw OCIException(errno, this->m_pOCIError, "OCIDB::Single_Conn OCILogon");
}
return 0;
}
catch (OCIException &e) {
printf("%s\n%s\n", e.GetErrFunc(), e.GetErrMsg());
return -1;
}
}
void OCIDB::Single_Disc() {
sword errno;
/* Logoff */
errno = OCILogoff (m_pOCISvcCtx, this->m_pOCIError);
if(errno) {
OCIError::PrintError(errno, "OCIDB::Single_Disc OCILogoff");
}
/* Free Error */
errno = OCIHandleFree((dvoid *) this->m_pOCIError, (ub4) OCI_HTYPE_ERROR);
if(errno) {
OCIError::PrintError(errno, "OCIDB::Single_Disc OCIHandleFree");
}
/* Free Env Handle */
errno = OCIHandleFree((dvoid *) this->m_pOCIEnv, (ub4) OCI_HTYPE_ENV);
if(errno) {
OCIError::PrintError(errno, "OCIDB::Single_Disc OCIHandleFree");
}
}
int OCIDB::Multiple_Conn() {
try {
sword errno;
errno = OCIInitialize ( (ub4) OCI_DEFAULT, (dvoid *)0, (dvoid * (*)(dvoid *, size_t)) 0, (dvoid * (*)(dvoid *, dvoid *, size_t))0, (void (*)(dvoid *, dvoid *)) 0 );
if(errno) {
throw OCIException(errno, "OCIDB::Multiple_Conn OCIInitialize");
}
errno = OCIEnvInit (
(OCIEnv **) &this->m_pOCIEnv, OCI_DEFAULT, (size_t) 0, (dvoid **) 0 );
if(errno) {
throw OCIException(errno, "OCIDB::Multiple_Conn OCIEnvInit");
}
/* Allocate Error Handle */
errno = OCIHandleAlloc (
(dvoid *) this->m_pOCIEnv, (dvoid **) &this->m_pOCIError, OCI_HTYPE_ERROR, (size_t) 0, (dvoid **) 0 );
if(errno) {
throw OCIException(errno, "OCIDB::Multiple_Conn OCIHandleAlloc Error");
}
/* Allocate Server Context Handle */
errno = OCIHandleAlloc ( (dvoid *) this->m_pOCIEnv, (dvoid **) &this->m_pOCISvcCtx, OCI_HTYPE_SVCCTX, (size_t) 0, (dvoid **) 0 );
if(errno) {
throw OCIException(errno, "OCIDB::Multiple_Conn OCIHandleAlloc SvcCtx");
}
/* Allocate Server Handle */
errno = OCIHandleAlloc ( (dvoid *) this->m_pOCIEnv, (dvoid **) &this->m_pOCIServ, OCI_HTYPE_SERVER, (size_t) 0, (dvoid **) 0 );
if(errno) {
throw OCIException(errno, "OCIDB::Multiple_Conn OCIHandleAlloc Server");
}
/* Set Server Into ServerContext */
errno = OCIAttrSet ( (dvoid *) this->m_pOCISvcCtx, OCI_HTYPE_SVCCTX, (dvoid *) this->m_pOCIServ, 0, OCI_ATTR_SERVER, (OCIError *) this->m_pOCIError );
if(errno) {
throw OCIException(errno, this->m_pOCIError, "OCIDB::Multiple_Conn OCIAttrSet Server");
}
/* AttachServer */
errno = OCIServerAttach ( this->m_pOCIServ, this->m_pOCIError, (text *)"", strlen(""), OCI_DEFAULT );
if(errno) {
throw OCIException(errno, this->m_pOCIError, "OCIDB::Multiple_Conn OCIServerAttach");
}
/* Allocate Session Handle */
errno = OCIHandleAlloc (
(dvoid *) this->m_pOCIEnv, (dvoid **) &this->m_pOCISession, (ub4) OCI_HTYPE_SESSION, (size_t) 0, (dvoid **) 0 );
if(errno) {
throw OCIException(errno,"OCIDB::Multiple_Conn OCIHandleAlloc Session");
}
/* Set Session Into ServerContext */
errno = OCIAttrSet (
(dvoid *) this->m_pOCISvcCtx, (ub4) OCI_HTYPE_SVCCTX, (dvoid *) this->m_pOCISession, (ub4) 0, (ub4) OCI_ATTR_SESSION, (OCIError *) this->m_pOCIError);
if(errno) {
throw OCIException(errno,"OCIDB::Multiple_Conn OCIAttrSet Session");
}
/* Set Session UserName */
errno = OCIAttrSet ( (dvoid *) this->m_pOCISession, OCI_HTYPE_SESSION, (dvoid *) this->m_sUser, strlen(this->m_sUser), OCI_ATTR_USERNAME, (OCIError *) this->m_pOCIError );
if(errno) {
throw OCIException(errno,"OCIDB::Multiple_Conn OCIAttrSet UserName");
}
/* Set Session Password */
errno = OCIAttrSet ( (dvoid *) this->m_pOCISession, OCI_HTYPE_SESSION, (dvoid *) this->m_sPwd, (ub4) strlen(this->m_sPwd), (ub4) OCI_ATTR_PASSWORD, (OCIError *) this->m_pOCIError );
if(errno) {
throw OCIException(errno,"OCIDB::Multiple_Conn OCIAttrSet Password");
}
/* Session Begin */
errno = OCISessionBegin ( this->m_pOCISvcCtx, this->m_pOCIError, this->m_pOCISession, OCI_CRED_RDBMS, OCI_DEFAULT );
if(errno) {
throw OCIException(errno, this->m_pOCIError, "OCIDB::Multiple_Conn OCISessionBegin");
}
return 0;
}
catch (OCIException &e) {
printf("%s\n%s\n", e.GetErrFunc(), e.GetErrMsg());
return -1;
}
}
void OCIDB::Multiple_Disc() {
sword errno;
/* End Sesson */
errno = OCISessionEnd (this->m_pOCISvcCtx, this->m_pOCIError, this->m_pOCISession, (ub4) OCI_DEFAULT);
if(errno) {
OCIError::PrintError(errno, "OCIDB::Multiple_Disc OCISessionEnd");
}
/* Free Sesson */
errno = OCIHandleFree((dvoid *) this->m_pOCISession, (ub4) OCI_HTYPE_SESSION);
if(errno) {
OCIError::PrintError(errno,"OCIDB::Multiple_Disc OCIHandleFree Sesson");
}
/* Detach Serv */
errno = OCIServerDetach ( this->m_pOCIServ, this->m_pOCIError, OCI_DEFAULT );
if(errno) {
OCIError::PrintError(errno,"OCIDB::Multiple_Disc OCIServerDetach");
}
/* Free Serv */
errno = OCIHandleFree((dvoid *) this->m_pOCIServ, (ub4) OCI_HTYPE_SERVER);
if(errno) {
OCIError::PrintError(errno,"OCIDB::Multiple_Disc OCIHandleFree Serv");
}
/* Free ServContext */
errno = OCIHandleFree((dvoid *) this->m_pOCISvcCtx, (ub4) OCI_HTYPE_SVCCTX);
if(errno) {
OCIError::PrintError(errno,"OCIDB::Multiple_Disc OCIHandleFree ServContext");
}
/* Free Error */
errno = OCIHandleFree((dvoid *) this->m_pOCIError, (ub4) OCI_HTYPE_ERROR);
if(errno) {
OCIError::PrintError(errno,"OCIDB::Multiple_Disc OCIHandleFree Error");
}
/* Free Env */
errno = OCIHandleFree((dvoid *) this->m_pOCIEnv, (ub4) OCI_HTYPE_ENV);
if(errno) {
OCIError::PrintError(errno,"OCIDB::Multiple_Disc OCIHandleFree Env");
}
}
int OCIDB::ExecuteSql(char * sql) {
sword errno;
try {
/* Allocate Stmt */
errno = OCIHandleAlloc ( (dvoid *) this->m_pOCIEnv, (dvoid **) &this->m_pOCIStmt, OCI_HTYPE_STMT, (size_t) 0, (dvoid **) 0 );
if(errno) {
throw OCIException(errno, "OCIDB::ExecuteSql OCIHandleAlloc");
}
errno = OCIStmtPrepare ( this->m_pOCIStmt, this->m_pOCIError, (const OraText*) sql, (ub4) strlen(sql), (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT );
if(errno) {
throw OCIException(errno, this->m_pOCIError, "OCIDB::ExecuteSql OCIStmtPrepare");
}
errno = OCIStmtExecute ( this->m_pOCISvcCtx , this->m_pOCIStmt, this->m_pOCIError, (ub4) 1, (ub4) 0, (const OCISnapshot *) NULL, (OCISnapshot *) NULL, OCI_DEFAULT );
if(errno) {
throw OCIException(errno, this->m_pOCIError, "OCIDB::ExecuteSql OCIStmtExecute");
}
errno = OCITransCommit ( this->m_pOCISvcCtx, this->m_pOCIError, 0 );
if(errno) {
OCIError::PrintError(errno, "OCIDB::ExecuteSql OCIStmtPrepare");
}
errno = OCIHandleFree ( (dvoid *) this->m_pOCIStmt, (ub4) OCI_HTYPE_STMT );
if(errno) {
OCIError::PrintError(errno, "OCIDB::ExecuteSql OCIHandleFree");
}
return 0;
}
catch (OCIException &e) {
errno = OCITransRollback ( this->m_pOCISvcCtx, this->m_pOCIError, 0 );
if(errno) {
OCIError::PrintError(errno, "OCIDB::ExecuteSql Ex OCITransRollback");
}
errno = OCIHandleFree ( (dvoid *) this->m_pOCIStmt, (ub4) OCI_HTYPE_STMT );
if(errno) {
OCIError::PrintError(errno, "OCIDB::ExecuteSql Ex OCIHandleFree");
}
printf("%s\n%s\n", e.GetErrFunc(), e.GetErrMsg());
return -1;
}
}
void OCIDB::BindInitVars() {
int i;
for(i=0;i<=MAX_BINDVAR_COUNT-1;i++) {
this->m_BindVars[i].VarType=0;
this->m_BindVars[i].VarName=0;
this->m_BindVars[i].VarLen=0;
this->m_BindVars[i].ValueChar=0;
}
this->m_iBindVarsCount=0;
}
void OCIDB::BindClearVars() {
int i;
for(i=0;i<=m_iBindVarsCount-1;i++) {
delete [] this->m_BindVars[i].VarName;
if(this->m_BindVars[i].VarType == SQLT_STR)
delete [] this->m_BindVars[i].ValueChar;
if(this->m_BindVars[i].VarType == SQLT_INT)
delete this->m_BindVars[i].ValueInt;
this->m_BindVars[i].VarType=0;
this->m_BindVars[i].VarLen=0;
this->m_BindVars[i].VarName=0;
this->m_BindVars[i].ValueChar=0;
}
this->m_iBindVarsCount=0;
}
void OCIDB::BindAddVar(char * name, char * value) {
if(this->m_iBindVarsCount>=MAX_BINDVAR_COUNT) return;
this->m_BindVars[this->m_iBindVarsCount].VarType=SQLT_STR;
this->m_BindVars[this->m_iBindVarsCount].VarName=new char[strlen(name)+1];
strcpy(this->m_BindVars[this->m_iBindVarsCount].VarName, name);
this->m_BindVars[this->m_iBindVarsCount].ValueChar=new char[strlen(value)+1];
strcpy(this->m_BindVars[this->m_iBindVarsCount].ValueChar, value);
this->m_BindVars[this->m_iBindVarsCount].VarLen=strlen(value)+1;
this->m_iBindVarsCount++;
}
void OCIDB::BindAddVar(char * name,int value) {
if(this->m_iBindVarsCount>=MAX_BINDVAR_COUNT) return;
this->m_BindVars[this->m_iBindVarsCount].VarType=SQLT_INT;
this->m_BindVars[this->m_iBindVarsCount].VarName=new char[strlen(name)+1];
strcpy(this->m_BindVars[this->m_iBindVarsCount].VarName, name);
this->m_BindVars[this->m_iBindVarsCount].ValueInt=new int;
*this->m_BindVars[this->m_iBindVarsCount].ValueInt=value;
this->m_BindVars[this->m_iBindVarsCount].VarLen=sizeof(int);
this->m_iBindVarsCount++;
}
int OCIDB::BindSql(char * sql) {
if(this->m_iBindVarsCount==0) return -1;
sword errno;
try {
/* Allocate Stmt */
errno = OCIHandleAlloc ( (dvoid *) this->m_pOCIEnv, (dvoid **) &this->m_pOCIStmt, OCI_HTYPE_STMT, (size_t) 0, (dvoid **) 0 );
if(errno) {
throw OCIException(errno, "OCIDB::BindSql OCIHandleAlloc Stmt");
}
errno = OCIStmtPrepare ( this->m_pOCIStmt, this->m_pOCIError, (const OraText*) sql, (ub4) strlen(sql), (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT );
if(errno) {
throw OCIException(errno, this->m_pOCIError, "OCIDB::BindSql OCIStmtPrepare");
}
int i;
for(i=0;i<=m_iBindVarsCount-1;i++) {
errno = OCIBindByName ( this->m_pOCIStmt, &this->m_pOCIBind, this->m_pOCIError, (text *) this->m_BindVars[i].VarName, -1, (dvoid *) this->m_BindVars[i].ValueChar, this->m_BindVars[i].VarLen, this->m_BindVars[i].VarType, (dvoid *) 0, (ub2 *) 0, (ub2 *) 0, (ub4) 0, (ub4 *) 0, OCI_DEFAULT
);
/* errno = OCIBindByPos ( this->m_pOCIStmt, &this->m_pOCIBind, this->m_pOCIError, i+1, (dvoid *) this->m_BindVars[i].ValueChar, this->m_BindVars[i].VarLen, this->m_BindVars[i].VarType, (dvoid *) 0, (ub2 *) 0, (ub2 *) 0, (ub4) 0, (ub4 *) 0, OCI_DEFAULT
);*/
if(errno) {
throw OCIException(errno, this->m_pOCIError, "OCIDB::BindSql OCIBindByName");
}
}
errno = OCIStmtExecute ( this->m_pOCISvcCtx , this->m_pOCIStmt, this->m_pOCIError, (ub4) 1, (ub4) 0, (const OCISnapshot *) NULL, (OCISnapshot *) NULL, OCI_DEFAULT );
if(errno) {
throw OCIException(errno, this->m_pOCIError, "OCIDB::BindSql OCIStmtExecute");
}
errno = OCITransCommit ( this->m_pOCISvcCtx, this->m_pOCIError, 0 );
if(errno) {
OCIError::PrintError(errno, "OCIDB::BindSql OCITransCommit");
}
errno = OCIHandleFree ( (dvoid *) this->m_pOCIStmt, (ub4) OCI_HTYPE_STMT );
if(errno) {
OCIError::PrintError(errno, "OCIDB::BindSql OCIHandleFree");
}
this->BindClearVars();
return 0;
}
catch (OCIException &e) {
printf("%s\n%s\n", e.GetErrFunc(), e.GetErrMsg());
this->m_iBindVarsCount=0;
errno = OCITransRollback ( this->m_pOCISvcCtx, this->m_pOCIError, 0 );
if(errno) {
OCIError::PrintError(errno, "OCIDB::BindSql Ex OCITransRollback");
}
errno = OCIHandleFree ( (dvoid *) this->m_pOCIStmt, (ub4) OCI_HTYPE_STMT );
if(errno) {
OCIError::PrintError(errno, "OCIDB::BindSql Ex OCIHandleFree");
}
this->BindClearVars();
return -1;
}
}
int OCIDB::UserPrepare(char * sql) {
sword errno;
try {
/* Allocate Stmt */
errno = OCIHandleAlloc ( (dvoid *) this->m_pOCIEnv, (dvoid **) &this->m_pOCIStmt, OCI_HTYPE_STMT, (size_t) 0, (dvoid **) 0 );
if(errno) {
throw OCIException(errno, "OCIDB::UserPrepare OCIHandleAlloc Stmt");
}
errno = OCIStmtPrepare ( this->m_pOCIStmt, this->m_pOCIError, (const OraText*) sql, (ub4) strlen(sql), (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT );
if(errno) {
throw OCIException(errno, this->m_pOCIError, "OCIDB::UserPrepare OCIStmtPrepare");
}
return 0;
}
catch (OCIException &e) {
printf("%s\n%s\n", e.GetErrFunc(), e.GetErrMsg());
errno = OCIHandleFree ( (dvoid *) this->m_pOCIStmt, (ub4) OCI_HTYPE_STMT );
if(errno) {
OCIError::PrintError(errno, "OCIDB::UserPrepare Ex OCIHandleFree");
}
return -1;
}
}
int OCIDB::UserFree() {
sword errno;
errno = OCIHandleFree ( (dvoid *) this->m_pOCIStmt, (ub4) OCI_HTYPE_STMT );
if(errno) {
OCIError::PrintError(errno, "OCIDB::UserFree OCIHandleFree");
return -1;
}
return 0;
}
int OCIDB::UserBind(char * name, char * value) {
sword errno;
try {
errno = OCIBindByName ( this->m_pOCIStmt, &this->m_pOCIBind, this->m_pOCIError, (text *) name, -1, (dvoid *) value, strlen(value)+1,SQLT_STR, (dvoid *) 0, (ub2 *) 0, (ub2 *) 0, (ub4) 0, (ub4 *) 0, OCI_DEFAULT
);
if(errno) {
throw OCIException(errno, this->m_pOCIError, "OCIDB::UserBind OCIBindByName");
}
return 0;
}
catch (OCIException &e) {
printf("%s\n%s\n", e.GetErrFunc(), e.GetErrMsg());
errno = OCIHandleFree ( (dvoid *) this->m_pOCIStmt, (ub4) OCI_HTYPE_STMT );
if(errno) {
OCIError::PrintError(errno, "OCIDB::UserBind Ex OCIHandleFree");
}
return -1;
}
}
int OCIDB::UserBind(char * name, int value) {
sword errno;
try {
int val =value;
errno = OCIBindByName ( this->m_pOCIStmt, &this->m_pOCIBind, this->m_pOCIError, (text *) name,-1, (dvoid *) &val, sizeof(int),SQLT_INT, (dvoid *) 0, (ub2 *) 0, (ub2 *) 0, (ub4) 0, (ub4 *) 0, OCI_DEFAULT
);
if(errno) {
throw OCIException(errno, this->m_pOCIError, "OCIDB::UserBind OCIBindByName");
}
return 0;
}
catch (OCIException &e) {
printf("%s\n%s\n", e.GetErrFunc(), e.GetErrMsg());
errno = OCIHandleFree ( (dvoid *) this->m_pOCIStmt, (ub4) OCI_HTYPE_STMT );
if(errno) {
OCIError::PrintError(errno, "OCIDB::UserBind Ex OCIHandleFree");
}
return -1;
}
}
int OCIDB::UserExecute() {
sword errno;
try {
errno = OCIStmtExecute ( this->m_pOCISvcCtx , this->m_pOCIStmt, this->m_pOCIError, (ub4) 1, (ub4) 0, (const OCISnapshot *) NULL, (OCISnapshot *) NULL, OCI_DEFAULT );
if(errno) {
throw OCIException(errno, this->m_pOCIError, "OCIDB::UserExecute OCIStmtExecute");
}
return 0;
}
catch (OCIException &e) {
printf("%s\n%s\n", e.GetErrFunc(), e.GetErrMsg());
errno = OCIHandleFree ( (dvoid *) this->m_pOCIStmt, (ub4) OCI_HTYPE_STMT );
if(errno) {
OCIError::PrintError(errno, "OCIDB::UserExecute Ex OCIHandleFree");
}
return -1;
}
}
int OCIDB::UserCommit() {
sword errno;
errno = OCITransCommit ( this->m_pOCISvcCtx, this->m_pOCIError, 0 );
if(errno) {
OCIError::PrintError(errno, "OCIDB::UserCommit OCITransCommit");
return -1;
}
return 0;
}
int OCIDB::UserRollback() {
sword errno;
errno = OCITransRollback ( this->m_pOCISvcCtx, this->m_pOCIError, 0 );
if(errno) {
OCIError::PrintError(errno, "OCIDB::UserRollback OCITransCommit");
return -1;
}
return 0;
}
int OCIDB::UserSelect(char * sql) {
sword errno;
try {
/* Allocate Stmt */
errno = OCIHandleAlloc ( (dvoid *) this->m_pOCIEnv, (dvoid **) &this->m_pOCIStmt, OCI_HTYPE_STMT, (size_t) 0, (dvoid **) 0 );
if(errno) {
throw OCIException(errno, "OCIDB::UserSelect OCIHandleAlloc Stmt");
}
errno = OCIStmtPrepare ( this->m_pOCIStmt, this->m_pOCIError, (const OraText*) sql, (ub4) strlen(sql), (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT );
if(errno) {
throw OCIException(errno, this->m_pOCIError, "OCIDB::UserSelect OCIStmtPrepare");
}
errno = OCIStmtExecute ( this->m_pOCISvcCtx , this->m_pOCIStmt, this->m_pOCIError, (ub4) 0, (ub4) 0, (OCISnapshot *) NULL, (OCISnapshot *) NULL, OCI_DEFAULT );
if(errno) {
throw OCIException(errno, this->m_pOCIError, "OCIDB::UserSelect OCIStmtExecute");
}
errno = OCIAttrGet ( this->m_pOCIStmt, (ub4)OCI_HTYPE_STMT, (dvoid *)&this->m_iSelectVarsCount, (ub4 *) 0, (ub4)OCI_ATTR_PARAM_COUNT, this->m_pOCIError );
if(errno) {
throw OCIException(errno, this->m_pOCIError, "OCIDB::UserSelect OCIAttrGet");
}
int iLen;
char * sName;
ub2 iSize, iPrecision, iType ;
ub1 iScale, iIsNull;
int i;
for(i=0;i<=this->m_iSelectVarsCount-1;i++) {
sName=0;
errno = OCIParamGet ( this->m_pOCIStmt , OCI_HTYPE_STMT,this->m_pOCIError,(dvoid **)&this->m_pOCIParam,i+1);
if(errno) {
throw OCIException(errno, this->m_pOCIError, "OCIDB::UserSelect OCIParamGet");
}
// Column Name
errno = OCIAttrGet ((dvoid*)this->m_pOCIParam, OCI_DTYPE_PARAM, (dvoid**)&sName, (ub4 *)&iLen,OCI_ATTR_NAME,this->m_pOCIError);
if(errno) {
throw OCIException(errno, this->m_pOCIError, "OCIDB::UserSelect OCIAttrGet");
}
strncpy(this->m_SelectVars[i].VarName, sName, iLen);
this->m_SelectVars[i].VarName[iLen]=0;
// Data Size
errno = OCIAttrGet ((dvoid*)this->m_pOCIParam, OCI_DTYPE_PARAM, (dvoid *)&iSize, (ub4 *)0,OCI_ATTR_DATA_SIZE, this->m_pOCIError);
if(errno) {
throw OCIException(errno, this->m_pOCIError, "OCIDB::UserSelect OCIAttrGet");
}
this->m_SelectVars[i].VarSize = iSize;
// Data Precision
errno = OCIAttrGet ((dvoid*)this->m_pOCIParam, OCI_DTYPE_PARAM, (dvoid *)&iPrecision, (ub4 *)0, OCI_ATTR_PRECISION, this->m_pOCIError);
if(errno) {
throw OCIException(errno, this->m_pOCIError, "OCIDB::UserSelect OCIAttrGet");
}
this->m_SelectVars[i].VarPrecision = iPrecision;
// Data Scale
errno = OCIAttrGet ((dvoid*)this->m_pOCIParam, OCI_DTYPE_PARAM, (dvoid *)&iScale, (ub4 *)0, OCI_ATTR_SCALE, this->m_pOCIError);
if(errno) {
throw OCIException(errno, this->m_pOCIError, "OCIDB::UserSelect OCIAttrGet");
}
this->m_SelectVars[i].VarScale = iScale;
// IsNULL
errno = OCIAttrGet ((dvoid*)this->m_pOCIParam, OCI_DTYPE_PARAM, (dvoid *)&iIsNull, (ub4 *)0, OCI_ATTR_IS_NULL, this->m_pOCIError);
if(errno) {
throw OCIException(errno, this->m_pOCIError, "OCIDB::UserSelect OCIAttrGet");
}
this->m_SelectVars[i].VarIsNull = iIsNull;
// Data type:
errno = OCIAttrGet ((dvoid*)this->m_pOCIParam, OCI_DTYPE_PARAM, (dvoid *)&iType, (ub4 *)0, OCI_ATTR_DATA_TYPE, this->m_pOCIError);
if(errno) {
throw OCIException(errno, this->m_pOCIError, "OCIDB::UserSelect OCIAttrGet");
}
this->m_SelectVars[i].VarType = iType;
switch (this->m_SelectVars[i].VarType) {
case SQLT_NUM:
if (this->m_SelectVars[i].VarScale == 0) {
this->m_SelectVars[i].ValueInt = new int;
this->m_SelectVars[i].VarType = TYPE_INT;
errno = OCIDefineByPos ( this->m_pOCIStmt, &this->m_pOCIDefine, this->m_pOCIError, i+1, (dvoid *) this->m_SelectVars[i].ValueInt, sizeof (int), SQLT_INT, (dvoid *) 0, (ub2 *) 0, (ub2 *) 0, OCI_DEFAULT
);
if(errno) {
throw OCIException(errno, this->m_pOCIError, "OCIDB::UserSelect OCIDefineByPos");
}
}
else {
this->m_SelectVars[i].ValueFloat = new float;
this->m_SelectVars[i].VarType = TYPE_FLOAT;
errno = OCIDefineByPos ( this->m_pOCIStmt, &this->m_pOCIDefine, this->m_pOCIError, i+1, (dvoid *) this->m_SelectVars[i].ValueFloat, sizeof (float), SQLT_FLT, (dvoid *) 0, (ub2 *) 0, (ub2 *) 0, OCI_DEFAULT
);
if(errno) {
throw OCIException(errno, this->m_pOCIError, "OCIDB::UserSelect OCIDefineByPos");
}
}
break;
case SQLT_CHR:
this->m_SelectVars[i].ValueChar = new char[this->m_SelectVars[i].VarSize+1];
this->m_SelectVars[i].VarType = TYPE_STRING;
errno = OCIDefineByPos ( this->m_pOCIStmt, &this->m_pOCIDefine, this->m_pOCIError, i+1, (dvoid *) this->m_SelectVars[i].ValueChar, this->m_SelectVars[i].VarSize+1, SQLT_STR, (dvoid *) 0, (ub2 *) 0, (ub2 *) 0, OCI_DEFAULT
);
if(errno) {
throw OCIException(errno, this->m_pOCIError, "OCIDB::UserSelect OCIDefineByPos");
}
break;
}
}
}
catch (OCIException &e) {
printf("%s\n%s\n", e.GetErrFunc(), e.GetErrMsg());
errno = OCIHandleFree ( (dvoid *) this->m_pOCIStmt, (ub4) OCI_HTYPE_STMT );
if(errno) {
OCIError::PrintError(errno, "OCIDB::UserSelect Ex OCIHandleFree");
}
return -1;
}
}
int OCIDB::UserFetch() {
sword errno;
try {
errno = OCIStmtFetch ( this->m_pOCIStmt, this->m_pOCIError, 1, OCI_FETCH_NEXT, OCI_DEFAULT);
if(errno && errno !=OCI_NO_DATA ) {
throw OCIException(errno, this->m_pOCIError, "OCIDB::UserSelect OCIStmtFetch");
}
if(errno==OCI_NO_DATA) return 1;
return 0;
}
catch (OCIException &e) {
//printf("%s\n%s\n", e.GetErrFunc(), e.GetErrMsg());
return -1;
}
}
int OCIDB::UserSelectFree() {
int i;
for(i=0;i<=m_iSelectVarsCount-1;i++)
switch(this->m_SelectVars[i].VarType) {
case TYPE_STRING:
delete [] this->m_SelectVars[i].ValueChar;
break;
case TYPE_FLOAT:
delete this->m_SelectVars[i].ValueFloat;
break;
case TYPE_INT:
delete this->m_SelectVars[i].ValueInt;
break;
}
return this->UserFree();
}
int OCIDB::UserGetInt(int index) {
switch(this->m_SelectVars[index].VarType) {
case TYPE_FLOAT:
return (int)*m_SelectVars[index].ValueFloat;
case TYPE_INT:
return *m_SelectVars[index].ValueInt;
default:
return 0;
}
}
int OCIDB::UserGetInt(char * name) {
int i;
for(i=0;i<=this->m_iSelectVarsCount-1;i++)
if(this->StrCmp(m_SelectVars[i].VarName, name))
switch(this->m_SelectVars[i].VarType) {
case TYPE_FLOAT:
return (int)*m_SelectVars[i].ValueFloat;
case TYPE_INT:
return *m_SelectVars[i].ValueInt;
default:
return 0;
}
return 0;
}
float OCIDB::UserGetFloat(int index) {
switch(this->m_SelectVars[index].VarType) {
case TYPE_FLOAT:
return *m_SelectVars[index].ValueFloat;
case TYPE_INT:
return (float)*m_SelectVars[index].ValueInt;
default:
return 0;
}
}
float OCIDB::UserGetFloat(char * name) {
int i;
for(i=0;i<=this->m_iSelectVarsCount-1;i++)
if(this->StrCmp(m_SelectVars[i].VarName, name))
switch(this->m_SelectVars[i].VarType) {
case TYPE_FLOAT:
return *m_SelectVars[i].ValueFloat;
case TYPE_INT:
return (float)*m_SelectVars[i].ValueInt;
default:
return 0;
}
return 0;
}
char * OCIDB::UserGetString(int index) {
if(this->m_SelectVars[index].VarType==TYPE_STRING)
return m_SelectVars[index].ValueChar;
else
return 0;
}
char * OCIDB::UserGetString(char * name) {
int i;
for(i=0;i<=this->m_iSelectVarsCount-1;i++)
if(this->StrCmp(m_SelectVars[i].VarName, name))
if(this->m_SelectVars[i].VarType==TYPE_STRING)
return m_SelectVars[i].ValueChar;
else
return 0;
return 0;
}
void inline OCIDB::StrUpper(char * str) {
int i;
for(i=0;i<=strlen(str)-1;i++) {
str[i]=toupper(str[i]);
}
}
bool inline OCIDB::StrCmp(const char *ori, const char *des)
{
int iLenOri,iLenDes;
int j;
iLenOri = strlen(ori);
iLenDes = strlen(des);
if (iLenOri!=iLenDes) return false;
for (j=0; j<iLenOri; j++)
if ( toupper(ori[j]) != toupper(des[j]) )
return false;
return true;
}
<Main.cpp>
-----------------------------------
#include "OCIDB.h"
int main() {
OCIDB db;
db.Multiple_Conn();
db.ExecuteSql("update liwei_test set id =123 where note='test' ");
db.Multiple_Disc();
}