本次将完成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日 星期二
