Oracle 中oci 關於null 的處理方法

oci中處理null，必須通過 Indicator 來完成。

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

#include <stdlib.h>
#include <stdio.h>
#include <string.h>
#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;

 

}

1 2 下一頁