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