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框架领域博主）

/*
  OCI demo program for accessing LONG.

  table 
     CAT_1 (ID NUMBER, CATNAME LONG);
*/

#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <oci.h>

#define DATA_SIZE 5000
#define PIECE_SIZE 30

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


/*---------------------------------------------------------------------------
                     PRIVATE TYPES AND CONSTANTS
  ---------------------------------------------------------------------------*/
static text *username = (text *) "c##fang";
static text *password = (text *) "fang";

/*---------------------------------------------------------------------------
                     STATIC FUNCTION DECLARATIONS
  ---------------------------------------------------------------------------*/
static void initialize(cdemol2lctx *ctxptr);
static void cleanup(cdemol2lctx *ctxptr);
static void checkerr(/*_ OCIError *errhp, sword status _*/);
static void sql_stmt_execute(/*_ cdemol2lctx *ctxptx, text *sql_stmt _*/);
static void select_piecewise_polling(/*_ cdemol2lctx *ctxptx _*/);
int main(/*_ int argc, char *argv[] _*/);


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

  initialize(&ctx);

  select_piecewise_polling(&ctx);
 
  /* clean things up before exhit */
  cleanup(&ctx);

  return 1;

} /*end main*/


/*perform piecewise select with polling*/
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 = %d\n", id);
  printf("checking contents of CATNAME piece by piece\n");
  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 %s\n", j, buf1);  
    memset(buf1, 0, PIECE_SIZE);  
    j++;                 
  }
  if(status == OCI_SUCCESS)
    printf("SUCCESS: fetched all pieces of CATNAME CORRECTLY\n");
} /* end of select_piecewise_polling() */


/*initialize envionment and handler*/
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->errhp\n");

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

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

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

  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 attribute\n");
  
  /*begin log_on part */
  if (OCIAttrSet((dvoid *) ctxptr->authp, (ub4) OCI_HTYPE_SESSION,
                 (dvoid *) username, (ub4) strlen((char *) username),
                 (ub4) OCI_ATTR_USERNAME, ctxptr->errhp))
    printf("FAILED: OCIAttrSet() userid\n");

  if (OCIAttrSet((dvoid *) ctxptr->authp, (ub4) OCI_HTYPE_SESSION,
                 (dvoid *) password, (ub4) strlen((char *) password),
                 (ub4) OCI_ATTR_PASSWORD, ctxptr->errhp))
    printf("FAILED: OCIAttrSet() passwd\n");
      
  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() session\n");
  /* end log_on part */

  /* alocate stmt handle for sql queries */
  
  if (OCIHandleAlloc((dvoid *)ctxptr->envhp, (dvoid **) &ctxptr->stmthp,
                   (ub4)OCI_HTYPE_STMT, (CONST size_t) 0, (dvoid **) 0))
    printf("FAILED: alloc statement handle\n");

} /* end initialize() */


/*check status and print error information*/
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_INFO\n");
    break;
  case OCI_NEED_DATA:
    (void) printf("Error - OCI_NEED_DATA\n");
    break;
  case OCI_NO_DATA:
    (void) printf("Error - OCI_NODATA\n");
    break;
  case OCI_ERROR:
    (void) OCIErrorGet((dvoid *)errhp, (ub4) 1, (text *) NULL, &errcode,
                        errbuf, (ub4) sizeof(errbuf), OCI_HTYPE_ERROR);
    (void) printf("Error - %.*s\n", 512, errbuf);
    break;
  case OCI_INVALID_HANDLE:
    (void) printf("Error - OCI_INVALID_HANDLE\n");
    break;
  case OCI_STILL_EXECUTING:
    (void) printf("Error - OCI_STILL_EXECUTE\n");
    break;
  case OCI_CONTINUE:
    (void) printf("Error - OCI_CONTINUE\n");
    break;
  default:
    break;
  }
} /* end checkerr() */


/*clean up envionment*/
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);

} /* end cleanup() */


/* end of file */

五、编译及运行

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日 星期二