投稿
  1. 首页
  2. 随笔

Oracle数据库LONG类型字段的完整C代码 *** 作(CREATE INSERT SELECT DROP)之六--分段轮询SELECT

EMa•2022-12-17•随笔•阅读12

Oracle数据库LONG类型字段的完整C代码 *** 作(CREATE INSERT SELECT DROP)之六--分段轮询SELECT,第1张

Oracle数据库LONG类型字段的完整C代码 *** 作(CREATE INSERT SELECT DROP)之六--分段轮询SELECT

本次将完成LONG类型字段的 分段的、轮询 SELECt *** 作。

一、本次目标

查询表CAT_1中ID=2的这行数据，每次获取CATNAME字段的PIECE_SIZE个字符。(PIECE_SIZE = 30)

附：

建表语句：CREATE TABLE CAT_1 (ID NUMBER, CATNAME LONG);

(建表程序参见：https://blog.csdn.net/have_a_cat/article/details/122423884)

二、测试数据准备（CREATE TABLE + INSERT） 
CREATE TABLE CAT_1 (ID NUMBER, CATNAME LONG);
insert into CAT_1 values(2,'1234567890qwertyuiopasdfghjklzxcvbnm1234567890qwertyuiopasdfghjklzxcvbnm');

commit;
三、程序结构及重点说明

（更多博文，欢迎来我的博客学习交流have_a_cat的博客_CSDN博客-PHP,C/C++,Dcat-Admin框架领域博主）

 有两个不太熟悉的函数OCIStmtGetPieceInfo 和 OCIStmtSetPieceInfo。

OCIStmtGetPieceInfo 用于 获取分段 *** 作的每片信息。

sword OCIStmtGetPieceInfo( const OCIStmt  *stmtp,

                         OCIError       *errhp,

                         void          **hndlpp,

                         ub4            *typep,

                         ub1            *in_outp,

                         ub4            *iterp,

                         ub4            *idxp,

                         ub1            *piecep );

stmtp (IN)

the statement executed when returned OCI_NEED_DATA.

errhp (OUT)

an error handle which can be passed to OCIErrorGet() for diagnostic information in the event of an error.

hndlpp (OUT)

returns a pointer to the bind or define handle of the bind or define whose runtime data is required or is being provided.

typep (OUT)

the type of the handle pointed to by hndlpp: OCI_HTYPE_BIND (for a bind handle) or OCI_HTYPE_DEFINE (for a define handle).

in_outp (OUT)

returns OCI_PARAM_IN if the data is required for an IN bind value. Returns OCI_PARAM_OUT if the data is available as an OUT bind variable or a define position value.

iterp (OUT)

returns the row number of a multiple row operation.

idxp (OUT)

the index of an array element of a PL/SQL array bind operation.

piecep (OUT)

returns one of the following defined values - OCI_ONE_PIECE, OCI_FIRST_PIECE, OCI_NEXT_PIECE and OCI_LAST_PIECE. The default value is always OCI_ONE_PIECE.

OCIStmtSetPieceInfo 用于 设置分段 *** 作的每片信息。详细讲解见Oracle数据库LONG类型字段的完整C代码 *** 作(CREATE INSERT SELECT DROP)之五--分段轮询INSERT_have_a_cat的博客-CSDN博客中第三部分

四、完整代码

（更多博文，欢迎来我的博客学习交流have_a_cat的博客_CSDN博客-PHP,C/C++,Dcat-Admin框架领域博主）


#include 
#include 
#include 
#include 

#define DATA_SIZE 5000
#define PIECE_SIZE 30

typedef struct cdemol2lctx
{
  OCIEnv *envhp;
  OCIServer *srvhp;
  OCISvcCtx *svchp;
  OCIError *errhp;
  OCISession *authp;
  OCIStmt *stmthp;
} cdemol2lctx;



static text *username = (text *) "c##fang";
static text *password = (text *) "fang";


static void initialize(cdemol2lctx *ctxptr);
static void cleanup(cdemol2lctx *ctxptr);
static void checkerr();
static void sql_stmt_execute();
static void select_piecewise_polling();
int main();


int main(argc, argv)
int argc;
char *argv[];
{
  cdemol2lctx ctx;
  printf("n ######## start DEMO program ############ n");

  initialize(&ctx);

  select_piecewise_polling(&ctx);
 
  
  cleanup(&ctx);

  return 1;

} 



void select_piecewise_polling(ctxptr)
cdemol2lctx *ctxptr;
{ 
  text *sel_stmt1 = (text *)"SELECT * FROM CAT_1 where ID=2";
  OCIDefine *defnp1 = (OCIDefine *) NULL;
  OCIDefine *defnp2 = (OCIDefine *) NULL;
  ub4 i;
  sword status, id;
  char buf1[PIECE_SIZE];
  ub4   alen  = PIECE_SIZE;
  ub1   piece = OCI_FIRST_PIECE;
  dvoid *hdlptr = (dvoid *) 0;
  ub4 hdltype = OCI_HTYPE_DEFINE, iter = 0, idx = 0;
  ub1   in_out = 0;
  sb2   indptr = 0;
  ub2   rcode = 0;
  int j = 0;

  checkerr(ctxptr->errhp, OCIStmtPrepare(ctxptr->stmthp, ctxptr->errhp, 
                          sel_stmt1, (ub4) strlen((char *)sel_stmt1), 
                          (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT));

  printf("nBEGINING SELECt PIECEWISE WITH POLLING OF CAT_1 ... n");

  checkerr(ctxptr->errhp,OCIDefineByPos(ctxptr->stmthp, &defnp1,
                         ctxptr->errhp, (ub4) 1, (dvoid*) &id, 
                         (sb4) sizeof(id), (ub2)SQLT_INT, (dvoid*) 0, 
                         (ub2 *) 0, (ub2 *) 0, OCI_DEFAULT));

  checkerr(ctxptr->errhp, OCIDefineByPos(ctxptr->stmthp, &defnp2, 
                          ctxptr->errhp, (ub4) 2, (dvoid *) 0, 
                          (sb4) DATA_SIZE, (ub2)SQLT_CHR, (dvoid *)0, 
                          (ub2 *) 0, (ub2 *)0, (ub4)OCI_DYNAMIC_FETCH));

  checkerr(ctxptr->errhp, OCIStmtExecute(ctxptr->svchp,
                          ctxptr->stmthp,
                          ctxptr->errhp, (ub4) 0, (ub4)0,
                          (OCISnapshot *) NULL, (OCISnapshot *) NULL,
                          OCI_DEFAULT)); 

  status = OCIStmtFetch(ctxptr->stmthp, ctxptr->errhp,
                          (ub4) 1, (ub2) OCI_FETCH_NEXT, (ub4) OCI_DEFAULT);
  checkerr(ctxptr->errhp, status);

  printf("ID = %dn", id);
  printf("checking contents of CATNAME piece by piecen");
  while (status == OCI_NEED_DATA)
  {
    checkerr(ctxptr->errhp, OCIStmtGetPieceInfo(ctxptr->stmthp, 
                            ctxptr->errhp, &hdlptr, &hdltype,
                            &in_out, &iter, &idx, &piece));
     
    alen  = PIECE_SIZE;
    checkerr(ctxptr->errhp, OCIStmtSetPieceInfo((dvoid *)hdlptr, (ub4)hdltype,
                                  ctxptr->errhp, (dvoid *) &buf1, &alen, piece,
                                  (dvoid *)&indptr, &rcode));
                            
    status = OCIStmtFetch(ctxptr->stmthp,ctxptr->errhp, (ub4) 1, 
                          (ub2) OCI_FETCH_NEXT, (ub4) OCI_DEFAULT);
    printf("the piece %d is %sn", j, buf1);  
    memset(buf1, 0, PIECE_SIZE);  
    j++;                 
  }
  if(status == OCI_SUCCESS)
    printf("SUCCESS: fetched all pieces of CATNAME CORRECTLYn");
} 



void initialize(ctxptr)
cdemol2lctx *ctxptr;
{

  if (OCIEnvCreate((OCIEnv **) &ctxptr->envhp,
                   (ub4)OCI_THREADED|OCI_OBJECT, (dvoid *)0,
                    (dvoid * (*)(dvoid *, size_t)) 0,
                   (dvoid * (*)(dvoid *, dvoid *, size_t))0,
                   (void (*)(dvoid *, dvoid *)) 0,
                   (size_t) 0, (dvoid **) 0 ))
    printf("FAILED: OCIEnvCreate()n");


  printf("n ######## Connect to server ############# n");

  if (OCIHandleAlloc((dvoid *) ctxptr->envhp,
                     (dvoid **) &ctxptr->errhp,
                     (ub4) OCI_HTYPE_ERROR, (size_t) 0, (dvoid **) 0))
    printf("FAILED: OCIHandleAlloc() on ctxptr->errhpn");

  if (OCIHandleAlloc((dvoid *) ctxptr->envhp,
                     (dvoid **) &ctxptr->srvhp,
                     (ub4) OCI_HTYPE_SERVER, (size_t) 0, (dvoid **) 0))
    printf("FAILED: OCIHandleAlloc() on ctxptr->srvhpn");

  if (OCIHandleAlloc((dvoid *) ctxptr->envhp,
                     (dvoid **) &ctxptr->svchp,
                     (ub4) OCI_HTYPE_SVCCTX, (size_t) 0, (dvoid **) 0))
    printf("FAILED: OCIHandleAlloc() on ctxptr->svchpn");

  if (OCIHandleAlloc((dvoid *) ctxptr->envhp,
                     (dvoid **) &ctxptr->authp,
                     (ub4) OCI_HTYPE_SESSION, (size_t) 0, (dvoid **) 0))
    printf("FAILED: OCIHandleAlloc() on ctxptr->authpn");

  if (OCIServerAttach(ctxptr->srvhp, ctxptr->errhp,
                      (text *) "", (sb4) strlen((char *) ""),
                      (ub4) OCI_DEFAULT))
    printf("FAILED: OCIServerAttach()n");

  if (OCIAttrSet((dvoid *) ctxptr->svchp, (ub4) OCI_HTYPE_SVCCTX,
                 (dvoid *) ctxptr->srvhp, (ub4) 0,
                 (ub4) OCI_ATTR_SERVER, ctxptr->errhp))
    printf("FAILED: OCIAttrSet() server attributen");
  
  
  if (OCIAttrSet((dvoid *) ctxptr->authp, (ub4) OCI_HTYPE_SESSION,
                 (dvoid *) username, (ub4) strlen((char *) username),
                 (ub4) OCI_ATTR_USERNAME, ctxptr->errhp))
    printf("FAILED: OCIAttrSet() useridn");

  if (OCIAttrSet((dvoid *) ctxptr->authp, (ub4) OCI_HTYPE_SESSION,
                 (dvoid *) password, (ub4) strlen((char *) password),
                 (ub4) OCI_ATTR_PASSWORD, ctxptr->errhp))
    printf("FAILED: OCIAttrSet() passwdn");
      
  printf("Logging on as %s  ....n", username);
  
  checkerr(ctxptr->errhp, OCISessionBegin((dvoid *)ctxptr->svchp,
                        ctxptr->errhp, ctxptr->authp,
                       (ub4) OCI_CRED_RDBMS,(ub4) OCI_DEFAULT ));
    
  printf("%s logged on.n", username);
                 
  if (OCIAttrSet((dvoid *) ctxptr->svchp, (ub4) OCI_HTYPE_SVCCTX,
             (dvoid *) ctxptr->authp, (ub4) 0, (ub4) OCI_ATTR_SESSION, 
             ctxptr->errhp))
    printf("FAILED: OCIAttrSet() sessionn");
  

  
  
  if (OCIHandleAlloc((dvoid *)ctxptr->envhp, (dvoid **) &ctxptr->stmthp,
                   (ub4)OCI_HTYPE_STMT, (ConST size_t) 0, (dvoid **) 0))
    printf("FAILED: alloc statement handlen");

} 



void checkerr(errhp, status)
OCIError *errhp;
sword status;
{
  text errbuf[512];
  sb4 errcode = 0;

  switch (status)
  {
  case OCI_SUCCESS:
    break;
  case OCI_SUCCESS_WITH_INFO:
    (void) printf("Error - OCI_SUCCESS_WITH_INFOn");
    break;
  case OCI_NEED_data:
    (void) printf("Error - OCI_NEED_DATAn");
    break;
  case OCI_NO_data:
    (void) printf("Error - OCI_NODATAn");
    break;
  case OCI_ERROR:
    (void) OCIErrorGet((dvoid *)errhp, (ub4) 1, (text *) NULL, &errcode,
                        errbuf, (ub4) sizeof(errbuf), OCI_HTYPE_ERROR);
    (void) printf("Error - %.*sn", 512, errbuf);
    break;
  case OCI_INVALID_HANDLE:
    (void) printf("Error - OCI_INVALID_HANDLEn");
    break;
  case OCI_STILL_EXECUTING:
    (void) printf("Error - OCI_STILL_EXECUTEn");
    break;
  case OCI_CONTINUE:
    (void) printf("Error - OCI_CONTINUEn");
    break;
  default:
    break;
  }
} 



void cleanup(ctxptr)
cdemol2lctx *ctxptr;
{
  printf("n ########## clean up ############ n");

  if (OCISessionEnd(ctxptr->svchp, ctxptr->errhp, 
                      ctxptr->authp, (ub4) 0))
    printf("FAILED: OCISessionEnd()n");

  printf("%s Logged off.n", username);

  if (OCIServerDetach(ctxptr->srvhp, ctxptr->errhp,
                   (ub4) OCI_DEFAULT))
    printf("FAILED: OCIServerDetach()n");

  printf("Detached from server.n");
  
  printf("Freeing handles ...n");
  if (ctxptr->stmthp)
    OCIHandleFree((dvoid *) ctxptr->stmthp, (ub4) OCI_HTYPE_STMT);
  if (ctxptr->errhp)
    OCIHandleFree((dvoid *) ctxptr->errhp, (ub4) OCI_HTYPE_ERROR);   
  if (ctxptr->srvhp)
    OCIHandleFree((dvoid *) ctxptr->srvhp, (ub4) OCI_HTYPE_SERVER);
  if (ctxptr->svchp)
    OCIHandleFree((dvoid *) ctxptr->svchp, (ub4) OCI_HTYPE_SVCCTX);
  if (ctxptr->authp)
    OCIHandleFree((dvoid *) ctxptr->authp, (ub4) OCI_HTYPE_SESSION);
  if (ctxptr->envhp)
    OCIHandleFree((dvoid *) ctxptr->envhp, (ub4) OCI_HTYPE_ENV);

}
五、编译及运行 5.1 将long_polling_select.c放入自己的目录下

5.2 编译 
gcc long_polling_select.c -o long_polling_select -I $ORACLE_HOME/rdbms/public -L $ORACLE_HOME/lib -l clntsh

5.3 运行 
./long_polling_select

（更多博文，欢迎来我的博客学习交流have_a_cat的博客_CSDN博客-PHP,C/C++,Dcat-Admin框架领域博主）

六、可下载的代码包（懒人福音）

可通过下面的链接，免费下载有猫彬为你准备的代码包（已编译好，可直接运行，内含编译

运行命令，直接复制粘贴即可）

https://download.csdn.net/download/have_a_cat/75392645https://download.csdn.net/download/have_a_cat/75392645

这个LONG系列至此完成，最后的最后，特别感谢下 【代码一看就好的某*】，在代码编写过程中给出很多实用的建议，也请大家期待后续更多Oracle代码博文~

----2022年1月11日 星期二

欢迎分享，转载请注明来源：内存溢出

原文地址:https://54852.com/zaji/5703402.html

赞 (0)
打赏 微信扫一扫微信扫一扫 支付宝扫一扫支付宝扫一扫
EMaEMa一级用户组
00
上一篇 2022-12-17
下一篇2022-12-18

发表评论

登录后才能评论

评论列表（0条）

    保存