Oracle中oci关于null的处理方法

参数indp是指示符缓冲区，也是一个数组，每个元素是一个sb2类型的值。一般作输入用，如果此项动态参数会被输出，则也作输出用。在

oci中处理null，必须通过 Indicator 来完成。

使用的Oracle 版本 Oracle Database 11g Enterprise Edition Release 11.1.0.6.0

#include
#include
#include
#include "include\oci.h"
#include "include\ociextp.h"

#define ERRGOTO(Recode) do \
{ \
if (Recode!=0) \
{ \
goto ERR; \
} \
} while (0)

void main()
{

int a = 0 ;
OCIDefine* defhp1 = 0;
OCIDefine* defhp2 = 0;
int blength = 40;
char b[40];
sb2 indicator;

text *select_sql = (text *)"select a,b from teststmt2";

dvoid *tmp;
OCIEnv *envhp;
OCIServer *srvhp;
OCIError *errhp;
OCISvcCtx *svchp;
OCISession *usrhp;
OCIStmt *stmthp;

char serName1[30] ="10.0.4.161:1521/orcl";
char userName1[30] = "cxy";
char pwd1[30] = "cxy";

//!如果没有数据可以使用这个测试用例创建数据表。
//test_BindArrayOfStruct();

memset(b, 0, blength);

//!初始化换环境句柄
ERRGOTO(OCIEnvCreate(&(envhp), OCI_DEFAULT,
NULL, NULL, NULL, NULL, 0, NULL));

//!初始化错误句柄
ERRGOTO(OCIHandleAlloc((dvoid *)envhp,(dvoid **)&errhp,OCI_HTYPE_ERROR,64,(dvoid **) &tmp));

//!分配服务上下文句柄和服务句柄
ERRGOTO(OCIHandleAlloc((dvoid *)envhp,(dvoid **)&srvhp,OCI_HTYPE_SERVER, 64,(dvoid **) &tmp));
ERRGOTO(OCIHandleAlloc((dvoid *)envhp,(dvoid **)&svchp,OCI_HTYPE_SVCCTX, 64,(dvoid **) &tmp));

//!初始化服务器句柄
ERRGOTO(OCIServerAttach( srvhp, errhp, (text *)serName1, (sb4) strlen(serName1), (ub4) OCI_DEFAULT));

//!/将服务器句柄连接到服务上下文句柄
ERRGOTO(OCIAttrSet ((dvoid *)svchp, OCI_HTYPE_SVCCTX,(dvoid *)srvhp, (ub4) 0, OCI_ATTR_SERVER, errhp));

//!分配设置会话句柄，并向里填充用户名和密码
ERRGOTO(OCIHandleAlloc ((dvoid *)envhp, (dvoid **)&usrhp,OCI_HTYPE_SESSION, 0, (dvoid **) 0));
ERRGOTO(OCIAttrSet ((dvoid *)usrhp, OCI_HTYPE_SESSION,(dvoid *)userName1, (ub4)strlen(userName1),OCI_ATTR_USERNAME, errhp));
ERRGOTO(OCIAttrSet ((dvoid *)usrhp, OCI_HTYPE_SESSION,(dvoid *)pwd1, (ub4)strlen(pwd1),OCI_ATTR_PASSWORD, errhp));

//!建立会话
ERRGOTO(OCISessionBegin (svchp, errhp, usrhp,OCI_CRED_RDBMS, OCI_DEFAULT));

//!将会话句柄连接到服务上下文句柄
ERRGOTO( OCIAttrSet ( (dvoid *)svchp, OCI_HTYPE_SVCCTX,(dvoid *)usrhp, (ub4) 0, OCI_ATTR_SESSION, errhp));

//!分配语句句柄
ERRGOTO(OCIHandleAlloc((dvoid *)envhp, (dvoid **)&stmthp, OCI_HTYPE_STMT, 0, 0));

//!开始查询并获得结果
text *delete_table = (text *)"delete from teststmt2";
ERRGOTO(OCIStmtPrepare(stmthp,errhp,(text *)delete_table,strlen((char *)delete_table),OCI_NTV_SYNTAX,OCI_DEFAULT));
ERRGOTO(OCIStmtExecute(svchp,stmthp,errhp,1,0,0,0,OCI_DEFAULT));

int aa = 8;
char bb[] = "bbbbbb";
indicator = -1;
text *insert_sql = (text *)"INSERT INTO teststmt2(a,b) values(:1,:2)";
OCIBind* bindhp = 0;
//!准备数据，，绑定参数
ERRGOTO(OCIStmtPrepare(stmthp,errhp,(text *)insert_sql,strlen((char *)insert_sql),OCI_NTV_SYNTAX,OCI_DEFAULT));
ERRGOTO(OCIBindByPos(stmthp,&bindhp,errhp,1, (dvoid *)&aa,4, SQLT_INT, (void*)&indicator, NULL, NULL,0,0,0));
//OCIBindArrayOfStruct(bindhp,errhp,sizeof(int),0,0,0);

ERRGOTO(OCIBindByPos(stmthp,&bindhp,errhp,2,(dvoid *)&bb, 6, SQLT_CHR,NULL,NULL,NULL,0,0,0));
//OCIBindArrayOfStruct(bindhp,errhp,2,0,0,0);

//！执行语句
ERRGOTO(OCIStmtExecute(svchp,stmthp,errhp,1,0,0,0,0));

indicator = -1;

//!开始查询并获得结果
ERRGOTO(OCIStmtPrepare(stmthp,errhp,(text *)select_sql,strlen((char *)select_sql),OCI_NTV_SYNTAX,OCI_DEFAULT));
//!绑定以一个列
ERRGOTO(OCIDefineByPos(stmthp,&defhp1,errhp,1,&a,sizeof(a),SQLT_INT, (void*)&indicator,0,0,OCI_DEFAULT));
//ERRGOTO(OCIDefineArrayOfStruct(defhp1,errhp,sizeof(int),0,4,0));
//!绑定以二个列

indicator = -2;
ERRGOTO(OCIDefineByPos(stmthp,&defhp2,errhp,2,&b, 20, SQLT_CHR, (void*)&indicator, 0, 0, OCI_DEFAULT));
//ERRGOTO(OCIDefineArrayOfStruct(defhp2,errhp,2,0,2,0));
//!执行语句
ERRGOTO(OCIStmtExecute(svchp,stmthp,errhp,1,0,0,0,OCI_DEFAULT));
//ERRGOTO(OCIStmtFetch(stmthp,errhp,1,OCI_FETCH_NEXT,0));

printf("%d, %s, %d", a, b, indicator);

ERRGOTO(OCITransCommit(svchp,errhp,0));

//!释放各个资源

//ERRGOTO(OCIHandleFree(srvhp,OCI_HTYPE_SERVER));
//ERRGOTO(OCIHandleFree(errhp,OCI_HTYPE_ERROR));
//ERRGOTO(OCIHandleFree(envhp,OCI_HTYPE_ENV));

return ;
ERR:
sb4 errcod=0;
OraText msgerr[200]="";
OraText msgstat[200]="";
OCIErrorGet(errhp,1,msgstat,&errcod,msgerr,200,OCI_HTYPE_ERROR);
printf((char *)msgerr);
return;

}