Release 8.0
A58234-01
 Library |
 Product |
 Contents |
 Index |
| Oracle Call Interface Programmer's Guide, Volumes 1 & 2 Release 8.0 A58234-01 |
|
This Appendix contains code examples illustrating the use of OCI calls. These programs are provided for demonstration purposes, and are not guaranteed to run on all platforms. When a specific header or SQL file is required by the application, its listing is included after the application code.
These and other demonstration programs may be available in the demo directory of your Oracle installation.
/* * -- cdemo81.c -- * An example program which adds new employee * records to the personnel data base. Checking * is done to insure the integrity of the data base. * The employee numbers are automatically selected using * the current maximum employee number as the start. * * The program queries the user for data as follows: * * Enter employee name: * Enter employee job: * Enter employee salary: * Enter employee dept: * * The program terminates if return key (CR) is entered * when the employee name is requested. * * If the record is successfully inserted, the following is printed: * * "ename" added to department "dname" as employee # "empno" */ #include <stdio.h> #include <stdlib.h> #include <string.h> #include <oci.h> static text *username = (text *) "SCOTT"; static text *password = (text *) "TIGER"; /* Define SQL statements to be used in program. */ static text *insert = (text *) "INSERT INTO emp(empno, ename, job, sal, \ deptno)VALUES (:empno, :ename, :job, :sal, :deptno)"; static text *seldept = (text *) "SELECT dname FROM dept WHERE deptno = :1"; static text *maxemp = (text *) "SELECT NVL(MAX(empno), 0) FROM emp"; static text *selemp = (text *) "SELECT ename, job FROM emp"; static OCIEnv *envhp; static OCIServer *srvhp; static OCIError *errhp; static OCISvcCtx *svchp; static OCIStmt *stmthp, *stmthp1; static OCIDefine *defnp = (OCIDefine *) 0; static OCIBind *bnd1p = (OCIBind *) 0; /* the first bind handle */ static OCIBind *bnd2p = (OCIBind *) 0; /* the second bind handle */ static OCIBind *bnd3p = (OCIBind *) 0; /* the third bind handle */ static OCIBind *bnd4p = (OCIBind *) 0; /* the fourth bind handle */ static OCIBind *bnd5p = (OCIBind *) 0; /* the fifth bind handle */ static OCIBind *bnd6p = (OCIBind *) 0; /* the sixth bind handle */ static void checkerr(/*_ OCIError *errhp, sword status _*/); static void cleanup(/*_ void _*/); static void myfflush(/*_ void _*/); int main(/*_ int argc, char *argv[] _*/); static sword status; int main(argc, argv) int argc; char *argv[]; { sword empno, sal, deptno; sword len, len2, rv, dsize, dsize2; sb4 enamelen = 10; sb4 joblen = 9; sb4 deptlen = 14; sb2 sal_ind, job_ind; sb2 db_type, db2_type; sb1 name_buf[20], name2_buf[20]; text *cp, *ename, *job, *dept; sb2 ind[2]; ub2 alen[2]; ub2 rlen[2]; OCIDescribe *dschndl1 = (OCIDescribe *) 0, *dschndl2 = (OCIDescribe *) 0, *dschndl3 = (OCIDescribe *) 0; OCISession *authp = (OCISession *) 0; (void) OCIInitialize((ub4) OCI_DEFAULT, (dvoid *)0, (dvoid * (*)(dvoid *, size_t)) 0, (dvoid * (*)(dvoid *, dvoid *, size_t))0, (void (*)(dvoid *, dvoid *)) 0 ); (void) OCIEnvInit( (OCIEnv **) &envhp, OCI_DEFAULT, (size_t) 0, (dvoid **) 0 ); (void) OCIHandleAlloc( (dvoid *) envhp, (dvoid **) &errhp, OCI_HTYPE_ERROR, (size_t) 0, (dvoid **) 0); /* server contexts */ (void) OCIHandleAlloc( (dvoid *) envhp, (dvoid **) &srvhp, OCI_HTYPE_SERVER, (size_t) 0, (dvoid **) 0); (void) OCIHandleAlloc( (dvoid *) envhp, (dvoid **) &svchp, OCI_HTYPE_SVCCTX, (size_t) 0, (dvoid **) 0); (void) OCIServerAttach( srvhp, errhp, (text *)"inst1_alias", strlen("inst1_alias"), 0); /* set attribute server context in the service context */ (void) OCIAttrSet( (dvoid *) svchp, OCI_HTYPE_SVCCTX, (dvoid *)srvhp, (ub4) 0, OCI_ATTR_SERVER, (OCIError *) errhp); (void) OCIHandleAlloc((dvoid *) envhp, (dvoid **)&authp, (ub4) OCI_HTYPE_SESSION, (size_t) 0, (dvoid **) 0); (void) OCIAttrSet((dvoid *) authp, (ub4) OCI_HTYPE_SESSION, (dvoid *) username, (ub4) strlen((char *)username), (ub4) OCI_ATTR_USERNAME, errhp); (void) OCIAttrSet((dvoid *) authp, (ub4) OCI_HTYPE_SESSION, (dvoid *) password, (ub4) strlen((char *)password), (ub4) OCI_ATTR_PASSWORD, errhp); checkerr(errhp, OCISessionBegin ( svchp, errhp, authp, OCI_CRED_RDBMS, (ub4) OCI_DEFAULT)); (void) OCIAttrSet((dvoid *) svchp, (ub4) OCI_HTYPE_SVCCTX, (dvoid *) authp, (ub4) 0, (ub4) OCI_ATTR_SESSION, errhp); checkerr(errhp, OCIHandleAlloc( (dvoid *) envhp, (dvoid **) &stmthp, OCI_HTYPE_STMT, (size_t) 0, (dvoid **) 0)); checkerr(errhp, OCIHandleAlloc( (dvoid *) envhp, (dvoid **) &stmthp1, OCI_HTYPE_STMT, (size_t) 0, (dvoid **) 0)); /* Retrieve the current maximum employee number. */ checkerr(errhp, OCIStmtPrepare(stmthp, errhp, maxemp, (ub4) strlen((char *) maxemp), (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT)); /* bind the input variable */ checkerr(errhp, OCIDefineByPos(stmthp, &defnp, errhp, 1, (dvoid *) &empno, (sword) sizeof(sword), SQLT_INT, (dvoid *) 0, (ub2 *)0, (ub2 *)0, OCI_DEFAULT)); /* execute and fetch */ if (status = OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4) 0, (CONST OCISnapshot *) NULL, (OCISnapshot *) NULL, OCI_DEFAULT)) { if (status == OCI_NO_DATA) empno = 10; else { checkerr(errhp, status); cleanup(); return OCI_ERROR; } } checkerr(errhp, OCIStmtPrepare(stmthp, errhp, insert, (ub4) strlen((char *) insert), (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT)); checkerr(errhp, OCIStmtPrepare(stmthp1, errhp, seldept, (ub4) strlen((char *) seldept), (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT)); /* Allocate output buffers. Allow for \n and '\0'. */ ename = (text *) malloc((size_t) enamelen + 2); job = (text *) malloc((size_t) joblen + 2); /* Bind the placeholders in the INSERT statement. */ if ((status = OCIBindByName(stmthp, &bnd1p, errhp, (text *) ":ENAME", -1, (dvoid *) ename, enamelen+1, SQLT_STR, (dvoid *) 0, (ub2 *) 0, (ub2 *) 0, (ub4) 0, (ub4 *) 0, OCI_DEFAULT)) || (status = OCIBindByName(stmthp, &bnd2p, errhp, (text *) ":JOB", -1, (dvoid *) job, joblen+1, SQLT_STR, (dvoid *) &job_ind, (ub2 *) 0, (ub2 *) 0, (ub4) 0, (ub4 *) 0, OCI_DEFAULT)) || (status = OCIBindByName(stmthp, &bnd3p, errhp, (text *) ":SAL", -1, (dvoid *) &sal, (sword) sizeof(sal), SQLT_INT, (dvoid *) &sal_ind, (ub2 *) 0, (ub2 *) 0, (ub4) 0, (ub4 *) 0, OCI_DEFAULT)) || (status = OCIBindByName(stmthp, &bnd4p, errhp, (text *) ":DEPTNO", -1, (dvoid *) &deptno, (sword) sizeof(deptno), SQLT_INT, (dvoid *) 0, (ub2 *) 0, (ub2 *) 0, (ub4) 0, (ub4 *) 0, OCI_DEFAULT)) || (status = OCIBindByName(stmthp, &bnd5p, errhp, (text *) ":EMPNO", -1, (dvoid *) &empno, (sword) sizeof(empno), SQLT_INT, (dvoid *) 0, (ub2 *) 0, (ub2 *) 0, (ub4) 0, (ub4 *) 0, OCI_DEFAULT))) { checkerr(errhp, status); cleanup(); return OCI_ERROR; } /* Bind the placeholder in the "seldept" statement. */ if (status = OCIBindByPos(stmthp1, &bnd6p, errhp, 1, (dvoid *) &deptno, (sword) sizeof(deptno),SQLT_INT, (dvoid *) 0, (ub2 *) 0, (ub2 *) 0, (ub4) 0, (ub4 *) 0,OCI_DEFAULT)) { checkerr(errhp, status); cleanup(); return OCI_ERROR; } /* Allocate the dept buffer now that you have length. */ /* the deptlen should eventually get from dschndl3. */ deptlen = 14; dept = (text *) malloc((size_t) deptlen + 1); /* Define the output variable for the select-list. */ if (status = OCIDefineByPos(stmthp1, &defnp, errhp, 1, (dvoid *) dept, deptlen+1, SQLT_STR, (dvoid *) 0, (ub2 *) 0, (ub2 *) 0, OCI_DEFAULT)) { checkerr(errhp, status); cleanup(); return OCI_ERROR; } for (;;) { /* Prompt for employee name. Break on no name. */ printf("\nEnter employee name (or CR to EXIT): "); fgets((char *) ename, (int) enamelen+1, stdin); cp = (text *) strchr((char *) ename, '\n'); if (cp == ename) { printf("Exiting... "); cleanup(); return OCI_SUCCESS; } if (cp) *cp = '\0'; else { printf("Employee name may be truncated.\n"); myfflush(); } /* Prompt for the employee's job and salary. */ printf("Enter employee job: "); job_ind = 0; fgets((char *) job, (int) joblen + 1, stdin); cp = (text *) strchr((char *) job, '\n'); if (cp == job) { job_ind = -1; /* make it NULL in table */ printf("Job is NULL.\n");/* using indicator variable */ } else if (cp == 0) { printf("Job description may be truncated.\n"); myfflush(); } else *cp = '\0'; printf("Enter employee salary: "); scanf("%d", &sal); myfflush(); sal_ind = (sal <= 0) ? -2 : 0; /* set indicator variable */ /* * Prompt for the employee's department number, and verify * that the entered department number is valid * by executing and fetching. */ do { printf("Enter employee dept: "); scanf("%d", &deptno); myfflush(); if ((status = OCIStmtExecute(svchp, stmthp1, errhp, (ub4) 1, (ub4) 0, (CONST OCISnapshot *) NULL, (OCISnapshot *) NULL, OCI_DEFAULT)) && (status != OCI_NO_DATA)) { checkerr(errhp, status); cleanup(); return OCI_ERROR; } if (status == OCI_NO_DATA) printf("The dept you entered doesn't exist.\n"); } while (status == OCI_NO_DATA); /* * Increment empno by 10, and execute the INSERT * statement. If the return code is 1 (duplicate * value in index), then generate the next * employee number. */ empno += 10; if ((status = OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4) 0, (CONST OCISnapshot *) NULL, (OCISnapshot *) NULL, OCI_DEFAULT)) && status != 1) { checkerr(errhp, status); cleanup(); return OCI_ERROR; } while (status == 1) { empno += 10; if ((status = OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4) 0, (CONST OCISnapshot *) NULL, (OCISnapshot *) NULL, OCI_DEFAULT)) && status != 1) { checkerr(errhp, status); cleanup(); return OCI_ERROR; } } /* end for (;;) */ /* Commit the change. */ if (status = OCITransCommit(svchp, errhp, 0)) { checkerr(errhp, status); cleanup(); return OCI_ERROR; } printf("\n\n%s added to the %s department as employee number %d\n", ename, dept, empno); } } 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; } } /* * Exit program with an exit code. */ void cleanup() { if (stmthp) checkerr(errhp, OCIHandleFree((dvoid *) stmthp, OCI_HTYPE_STMT)); if (stmthp1) checkerr(errhp, OCIHandleFree((dvoid *) stmthp1, OCI_HTYPE_STMT)); if (errhp) (void) OCIServerDetach( srvhp, errhp, OCI_DEFAULT ); if (srvhp) checkerr(errhp, OCIHandleFree((dvoid *) srvhp, OCI_HTYPE_SERVER)); if (svchp) (void) OCIHandleFree((dvoid *) svchp, OCI_HTYPE_SVCCTX); if (errhp) (void) OCIHandleFree((dvoid *) errhp, OCI_HTYPE_ERROR); return; } void myfflush() { eb1 buf[50]; fgets((char *) buf, 50, stdin); }
/* NAME cdemo82.c - oci object sample program ; run cdemo82.sql */ #ifndef CDEMO82_ORACLE #include <cdemo82.h> #endif #define SCHEMA "CDEMO82" /*******************************************************************/ static void pin_display_addr(envhp, errhp, addrref) OCIEnv *envhp; OCIError *errhp; OCIRef *addrref; { sword status; address *addr = (address *)0; checkerr(errhp, OCIObjectPin(envhp, errhp, addrref, (OCIComplexObject *)0, OCI_PIN_ANY, OCI_DURATION_SESSION, OCI_LOCK_NONE, (dvoid **)&addr)); if (addr) { printf("address.state = %.2s address.zip = %.10s\n", OCIStringPtr(envhp, addr->state), OCIStringPtr(envhp, addr->zip)); } else { printf("Pinned address pointer is null\n"); } checkerr(errhp, OCIObjectUnpin(envhp, errhp, (dvoid *) addr)); } /*****************************************************************/ static void checkerr(errhp, status) OCIError *errhp; sword status; { text errbuf[512]; ub4 buflen; ub4 errcode; switch (status) { case OCI_SUCCESS: break; case OCI_SUCCESS_WITH_INFO: printf("Error - OCI_SUCCESS_WITH_INFO\n"); break; case OCI_NEED_DATA: printf("Error - OCI_NEED_DATA\n"); break; case OCI_NO_DATA: printf("Error - OCI_NO_DATA\n"); break; case OCI_ERROR: OCIErrorGet ((dvoid *) errhp, (ub4) 1, (text *) NULL, &errcode, errbuf, (ub4) sizeof(errbuf), (ub4) OCI_HTYPE_ERROR); printf("Error - %s\n", errbuf); break; case OCI_INVALID_HANDLE: printf("Error - OCI_INVALID_HANDLE\n"); break; case OCI_STILL_EXECUTING: printf("Error - OCI_STILL_EXECUTE\n"); break; case OCI_CONTINUE: printf("Error - OCI_CONTINUE\n"); break; default: break; } } /****************************************************************/ /* ** execute "selvalstmt" statement -- selects from a table with an object. ** */ static void selectval(envhp, svchp, stmthp, errhp) OCIEnv *envhp; OCISvcCtx *svchp; OCIStmt *stmthp; OCIError *errhp; { OCIType *addr_tdo = (OCIType *) 0; OCIDefine *defn1p = (OCIDefine *) 0, *defn2p = (OCIDefine *) 0; address *addr = (address *)NULL; sword custno =0; int i = 0; OCIRef *addrref = (OCIRef *) 0; OCIRef *type_ref = (OCIRef *) 0; sb4 status; OCIDescribe *dschp = (OCIDescribe *) 0; OCIParam *parmp; /* allocate describe handle for OCIDescribeAny */ checkerr(errhp, OCIHandleAlloc((dvoid *) envhp, (dvoid **) &dschp, (ub4) OCI_HTYPE_DESCRIBE, (size_t) 0, (dvoid **) 0)); /* define the application request */ checkerr(errhp, OCIStmtPrepare(stmthp, errhp, (text *) selvalstmt, (ub4) strlen(selvalstmt), (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT)); /* bind the input variable */ checkerr(errhp, OCIDefineByPos(stmthp, &defn1p, errhp, (ub4) 1, (dvoid *) &custno, (sb4) sizeof(sword), SQLT_INT, (dvoid *) 0, (ub2 *)0, (ub2 *)0, (ub4) OCI_DEFAULT)); checkerr(errhp, OCIDefineByPos(stmthp, &defn2p, errhp, (ub4) 2, (dvoid *) 0, (sb4) 0, SQLT_NTY, (dvoid *) 0, (ub2 *)0, (ub2 *)0, (ub4) OCI_DEFAULT)); /* checkerr(errhp, OCITypeByName(envhp, errhp, svchp, (const text *) 0, (ub4) 0, (const text *) "ADDRESS_VALUE", (ub4) strlen((const char *) "ADDRESS_VALUE"), (CONST text *) 0, (ub4) 0, OCI_DURATION_SESSION, OCI_TYPEGET_HEADER, &addr_tdo)); */ checkerr(errhp, OCIDescribeAny(svchp, errhp, (text *)"ADDRESS_VALUE", (ub4) strlen((char *)"ADDRESS_VALUE"), OCI_OTYPE_NAME, (ub1)1, (ub1) OCI_PTYPE_TYPE, dschp)); checkerr(errhp, OCIAttrGet((dvoid *) dschp, (ub4) OCI_HTYPE_DESCRIBE, (dvoid *)&parmp, (ub4 *)0, (ub4)OCI_ATTR_PARAM, errhp)); checkerr(errhp, OCIAttrGet((dvoid*) parmp, (ub4) OCI_DTYPE_PARAM, (dvoid*) &type_ref, (ub4 *) 0, (ub4) OCI_ATTR_REF_TDO, (OCIError *) errhp)); checkerr(errhp, OCIObjectPin(envhp, errhp, type_ref, (OCIComplexObject *) 0, OCI_PIN_ANY, OCI_DURATION_SESSION, OCI_LOCK_NONE, (dvoid **)&addr_tdo)); if(!addr_tdo) { printf("NULL tdo returned\n"); goto done_selectval; } checkerr(errhp, OCIDefineObject(defn2p, errhp, addr_tdo, (dvoid **) &addr, (ub4 *) 0, (dvoid **) 0, (ub4 *) 0)); checkerr(errhp, OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4) 0, (OCISnapshot *) NULL, (OCISnapshot *) NULL, (ub4) OCI_DEFAULT)); /* execute and fetch */ do { if (addr) printf("custno = %d address.state = %.2s address.zip = %.10s\n", custno, OCIStringPtr(envhp, addr->state), OCIStringPtr(envhp, addr->zip)); else printf("custno = %d fetched address is NULL\n", custno); addr = (address *)NULL; } while ((status = OCIStmtFetch(stmthp, errhp, (ub4) 1, (ub4) OCI_FETCH_NEXT, (ub4) OCI_DEFAULT)) == OCI_SUCCESS || status == OCI_SUCCESS_WITH_INFO); if ( status!= OCI_NO_DATA ) checkerr(errhp, status); printf("\n\n"); done_selectval: checkerr(errhp, OCIHandleFree((dvoid *) defn1p, (ub4) OCI_HTYPE_DEFINE)); checkerr(errhp, OCIHandleFree((dvoid *) defn2p, (ub4) OCI_HTYPE_DEFINE)); } /**************************************************************** ** execute "selobjstmt" -- selects records from a table with a REF. */ static void selectobj(envhp, svchp, stmthp, errhp) OCIEnv *envhp; OCISvcCtx *svchp; OCIStmt *stmthp; OCIError *errhp; { OCIType *addr_tdo = (OCIType *) 0; OCIDefine *defn1p = (OCIDefine *) 0, *defn2p = (OCIDefine *) 0; sword status; OCIRef *addrref = (OCIRef *) 0, *addrref1 = (OCIRef *) 0; sword custno =0; int i = 0; address *addr; ub4 ref_len; /* define the application request */ checkerr(errhp, OCIStmtPrepare(stmthp, errhp, (text *) selobjstmt, (ub4) strlen(selobjstmt), (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT)); checkerr(errhp, OCIDefineByPos(stmthp, &defn1p, errhp, (ub4) 1, (dvoid *) &custno, (sb4) sizeof(sword), SQLT_INT, (dvoid *) 0, (ub2 *)0, (ub2 *)0, (ub4) OCI_DEFAULT)); addrref = (OCIRef *)NULL; checkerr(errhp, OCIDefineByPos(stmthp, &defn2p, errhp, (ub4) 2, (dvoid *) NULL, (sb4) 0, SQLT_REF, (dvoid *) 0, (ub2 *)0, (ub2 *)0, (ub4) OCI_DEFAULT)); checkerr(errhp, OCIDefineObject(defn2p, errhp, (OCIType *)NULL, (dvoid **)&addrref, &ref_len, (dvoid **)0, (ub4 *)0)); checkerr(errhp, OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4) 0, (OCISnapshot *) NULL, (OCISnapshot *) NULL, (ub4) OCI_DEFAULT)); do { printf("custno = %d fetched address\n", custno); if ( addrref ) { pin_display_addr(envhp, errhp, addrref); } else printf("Address ref is NULL\n"); } while ((status = OCIStmtFetch(stmthp, errhp, (ub4) 1, (ub4) OCI_FETCH_NEXT, (ub4) OCI_DEFAULT)) == OCI_SUCCESS || status == OCI_SUCCESS_WITH_INFO); if ( status != OCI_NO_DATA ) checkerr(errhp, status); printf("\n\n"); checkerr(errhp, OCIHandleFree((dvoid *) defn1p, (ub4) OCI_HTYPE_DEFINE)); checkerr(errhp, OCIHandleFree((dvoid *) defn2p, (ub4) OCI_HTYPE_DEFINE)); } /*******************************************************************/ /*******************************************************************/ /* ** execute "insstmt" ** */ static void insert(envhp, svchp, stmthp, errhp, insstmt, nrows) OCIEnv *envhp; OCISvcCtx *svchp; OCIStmt *stmthp; OCIError *errhp; text *insstmt; ub2 nrows; { OCIType *addr_tdo = (OCIType *) 0; address addrs; null_address naddrs; address *addr = &addrs; null_address *naddr = &naddrs; sword custno =300; OCIBind *bnd1p = (OCIBind *) 0, *bnd2p = (OCIBind *) 0; char buf[20]; ub2 i; OCIRef *type_ref = (OCIRef *) 0; OCIDescribe *dschp = (OCIDescribe *) 0; OCIParam *parmp; /* allocate describe handle for OCIDescribeAny */ checkerr(errhp, OCIHandleAlloc((dvoid *) envhp, (dvoid **) &dschp, (ub4) OCI_HTYPE_DESCRIBE, (size_t) 0, (dvoid **) 0)); /* define the application request */ checkerr(errhp, OCIStmtPrepare(stmthp, errhp, (text *) insstmt, (ub4) strlen(insstmt), (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT)); /* bind the input variable */ checkerr(errhp, OCIBindByName(stmthp, &bnd1p, errhp, (text *) ":custno", (sb4) -1, (dvoid *) &custno, (sb4) sizeof(sword), SQLT_INT, (dvoid *) 0, (ub2 *)0, (ub2 *)0, (ub4) 0, (ub4 *) 0, (ub4) OCI_DEFAULT)); checkerr(errhp, OCIBindByName(stmthp, &bnd2p, errhp, (text *) ":addr", (sb4) -1, (dvoid *) 0, (sb4) 0, SQLT_NTY, (dvoid *) 0, (ub2 *)0, (ub2 *)0, (ub4) 0, (ub4 *) 0, (ub4) OCI_DEFAULT)); /* checkerr(errhp, OCITypeByName(envhp, errhp, svchp, (const text *) 0, (ub4) 0, (const text *) "ADDRESS_VALUE", (ub4) strlen((const char *) "ADDRESS_VALUE"), (CONST text *) 0, (ub4) 0, OCI_DURATION_SESSION, OCI_TYPEGET_HEADER, &addr_tdo)); */ checkerr(errhp, OCIDescribeAny(svchp, errhp, (text *)"ADDRESS_VALUE", (ub4) strlen((char *)"ADDRESS_VALUE"), OCI_OTYPE_NAME, (ub1)1, (ub1) OCI_PTYPE_TYPE, dschp)); checkerr(errhp, OCIAttrGet((dvoid *) dschp, (ub4) OCI_HTYPE_DESCRIBE, (dvoid *)&parmp, (ub4 *)0, (ub4)OCI_ATTR_PARAM, errhp)); checkerr(errhp, OCIAttrGet((dvoid*) parmp, (ub4) OCI_DTYPE_PARAM, (dvoid*) &type_ref, (ub4 *) 0, (ub4) OCI_ATTR_REF_TDO, (OCIError *) errhp)); checkerr(errhp, OCIObjectPin(envhp, errhp, type_ref, (OCIComplexObject *) 0, OCI_PIN_ANY, OCI_DURATION_SESSION, OCI_LOCK_NONE, (dvoid **)&addr_tdo)); if(!addr_tdo) { printf("Null tdo returned\n"); goto done_insert; } checkerr(errhp, OCIBindObject(bnd2p, errhp, addr_tdo, (dvoid **) &addr, (ub4 *) 0, (dvoid **) &naddr, (ub4 *) 0)); for(i = 0; i <= nrows; i++) { addr->state = (OCIString *) 0; sprintf(buf, "%cA", 65+i%27); checkerr(errhp, OCIStringAssignText(envhp, errhp, (CONST text*) buf, 2, &addr->state)); addr->zip = (OCIString *) 0; sprintf(buf, "94%d ", i+455); checkerr(errhp, OCIStringAssignText(envhp, errhp, (CONST text*) buf, 10, &addr->zip)); naddr->null_object = 0; naddr->null_state = 0; naddr->null_zip = 0; checkerr(errhp, OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4) 0, (OCISnapshot *) NULL, (OCISnapshot *) NULL, (ub4) OCI_DEFAULT)); } checkerr(errhp, OCITransCommit(svchp, errhp, (ub4) 0)); done_insert: checkerr(errhp, OCIHandleFree((dvoid *) bnd1p, (ub4) OCI_HTYPE_BIND)); checkerr(errhp, OCIHandleFree((dvoid *) bnd2p, (ub4) OCI_HTYPE_BIND)); } /****************************************************************/ int main() { OCIEnv *envhp; OCIServer *srvhp; OCIError *errhp; OCISvcCtx *svchp; OCIStmt *stmthp; OCISession *usrhp; OCIInitialize((ub4) OCI_THREADED | OCI_OBJECT, (dvoid *)0, (dvoid * (*)()) 0,(dvoid * (*)()) 0, (void (*)()) 0 ); OCIHandleAlloc( (dvoid *) NULL, (dvoid **) &envhp, (ub4) OCI_HTYPE_ENV, 52, (dvoid **) &tmp); OCIEnvInit( &envhp, (ub4) OCI_DEFAULT, 21, (dvoid **) &tmp ); OCIHandleAlloc( (dvoid *) envhp, (dvoid **) &errhp, (ub4) OCI_HTYPE_ERROR, 52, (dvoid **) &tmp); OCIHandleAlloc( (dvoid *) envhp, (dvoid **) &srvhp, (ub4) OCI_HTYPE_SERVER, 52, (dvoid **) &tmp); OCIServerAttach( srvhp, errhp, (text *) 0, (sb4) 0, (ub4) OCI_DEFAULT); OCIHandleAlloc( (dvoid *) envhp, (dvoid **) &svchp, (ub4) OCI_HTYPE_SVCCTX, 52, (dvoid **) &tmp); /* set attribute server context in the service context */ OCIAttrSet( (dvoid *) svchp, (ub4) OCI_HTYPE_SVCCTX, (dvoid *) srvhp, (ub4) 0, (ub4) OCI_ATTR_SERVER, (OCIError *) errhp); /* allocate a user context handle */ OCIHandleAlloc((dvoid *)envhp, (dvoid **)&usrhp, (ub4) OCI_HTYPE_SESSION, (size_t) 0, (dvoid **) 0); OCIAttrSet((dvoid *)usrhp, (ub4)OCI_HTYPE_SESSION, (dvoid *)"cdemo82", (ub4)strlen("cdemo82"), OCI_ATTR_USERNAME, errhp); OCIAttrSet((dvoid *)usrhp, (ub4)OCI_HTYPE_SESSION, (dvoid *)"cdemo82", (ub4)strlen("cdemo82"), OCI_ATTR_PASSWORD, errhp); checkerr(errhp, OCISessionBegin (svchp, errhp, usrhp, OCI_CRED_RDBMS, OCI_DEFAULT)); OCIAttrSet((dvoid *)svchp, (ub4)OCI_HTYPE_SVCCTX, (dvoid *)usrhp, (ub4)0, OCI_ATTR_SESSION, errhp); checkerr(errhp, OCIHandleAlloc( (dvoid *) envhp, (dvoid **) &stmthp, (ub4) OCI_HTYPE_STMT, 50, (dvoid **) &tmp)); /* execute "insstmt" */ printf("--- Test insertion into extent table.\n"); insert(envhp, svchp, stmthp, errhp, insstmt, 26); /* execute "selstmt" */ printf("--- Test selection of a table with one object column.\n"); selectval(envhp, svchp, stmthp, errhp); /* execute "selobjstmt" */ printf("--- Test selection of a table with one object REF.\n"); selectobj(envhp, svchp, stmthp, errhp); checkerr(errhp, OCIHandleFree((dvoid *) stmthp, (ub4) OCI_HTYPE_STMT)); OCISessionEnd(svchp, errhp, usrhp, (ub4)OCI_DEFAULT); OCIServerDetach( srvhp, errhp, (ub4) OCI_DEFAULT ); checkerr(errhp, OCIHandleFree((dvoid *) srvhp, (ub4) OCI_HTYPE_SERVER)); checkerr(errhp, OCIHandleFree((dvoid *) svchp, (ub4) OCI_HTYPE_SVCCTX)); checkerr(errhp, OCIHandleFree((dvoid *) errhp, (ub4) OCI_HTYPE_ERROR)); }
/* NAME cdemo82.h - header file for oci object sample program */ #ifndef CDEMO82_ORACLE # define CDEMO82_ORACLE #ifndef OCI_ORACLE #include <oci.h> #endif /*------------------------------------------------------------------- PRIVATE TYPES AND CONSTANTS ------------------------------------------------------------------*/ #define SERVER "ORACLE" #define ADDRESS_TYPE_NAME "ADDRESS_OBJECT" #define EMB_ADDRESS_TYPE_NAME "EMBEDDED_ADDRESS" #define ADDREXT "ADDREXT" #define EMBADDREXT "EMBADDREXT" #define RETURN_ON_ERROR(error) if (error) return (error) #define BIG_RECORD_SIZE 1000 struct address { OCIString *state; OCIString *zip; }; typedef struct address address; struct null_address { sb4 null_object; sb4 null_state; sb4 null_zip; }; typedef struct null_address null_address; struct embaddress { OCIString *state; OCIString *zip; OCIRef *preaddrref; }; typedef struct embaddress embaddress; struct null_embaddress { sb4 null_state; sb4 null_zip; sb4 null_preaddrref; }; typedef struct null_embaddress null_embaddress; struct person { OCIString *name; OCINumber age; address addr; }; typedef struct person person; struct null_person { sb4 null_name; sb4 null_age; null_address null_addr; }; typedef struct null_person null_person; static const text *const names[] = {(text *) "CUSTOMERVAL", (text *) "ADDRESS", (text *) "STATE"}; static const text *const selvalstmt = (text *) "SELECT custno, addr FROM customerval"; static const text *const selobjstmt = (text *) "SELECT custno, addr FROM customerobj"; static const text *const selref = (text *) "SELECT REF(extaddr) from extaddr"; static const text *const deleteref = (text *) "DELETE extaddr"; static const text *const insertref = (text *) "insert into extaddr values(address_object('CA', '98765'))"; static const text *const modifyref = (text *) "update extaddr set object_column = address_object('TX', '61111')"; static const text *const selembref = (text *) "SELECT REF(exbextaddr) from embextaddr"; static const text *const bndref = (text *) "update extaddr set object_column.state = 'GA' where object_column = :addrref"; static const text *const insstmt = (text *)"INSERT INTO customerval (custno, addr) values (:custno, :addr)"; dvoid *tmp; /*-------------------------------------------------------------------- PUBLIC FUNCTIONS -----------------------------------------------------------------*/ OCIRef *cbfunc(/*_ dvoid *context _*/); /*----------------------------------------------------------------- PRIVATE FUNCTIONS -----------------------------------------------------------------*/ static void checkerr(/*_ OCIError *errhp, sword status _*/); static void selectval(/*_ OCIEnv *envhp, OCISvcCtx *svchp, OCIStmt *stmthp, OCIError *errhp _*/); static void selectobj(/*_ OCIEnv *envhp, OCISvcCtx *svchp, OCIStmt *stmthp, OCIError *errhp _*/); static void insert(/*_ OCIEnv *envhp, OCISvcCtx *svchp, OCIStmt *stmthp, OCIError *errhp, text *insstmt, ub2 nrows _*/); static void pin_display_addr(/*_ OCIEnv *envhp, OCIError *errhp, OCIRef *addrref _*/); int main(/*_ void _*/);
Rem cdemo82.sql Rem Rem NAME Rem cdemo82.sql - sql to be executed before cdemo82 Rem set echo on; connect internal; drop user cdemo82 cascade; create user cdemo82 identified by cdemo82; grant connect, resource to cdemo82; connect cdemo82/cdemo82; drop table customerval; drop table customerobj; drop table extaddr; drop table embextaddr; drop type embedded_address; drop type address_object; drop type person; drop table emp; create type address_object as object (state char(2), zip char(10)); create type embedded_address as object (state char(2), zip char(10), preaddr REF address_object); drop type address_value; create type address_value as object (state char(2), zip char(10)); create table customerval (custno number, addr address_value); insert into customerval values(100, address_value('CA', '94065')); create table extaddr of address_object; create table customerobj (custno number, addr REF address_object); insert into extaddr values (address_object('CA', '94065')); insert into customerobj values(1000, null); update customerobj set addr = (select ref(extaddr) from extaddr where zip='94065'); insert into extaddr values (address_object('CA', '98765')); insert into extaddr values (address_object('CA', '95117')); select REFTOHEX(ref(extaddr)) from extaddr; create table embextaddr of embedded_address; insert into embextaddr values (embedded_address('CA', '95117', NULL)); select objectTOHEX(p) from embextaddr p; drop table extper; drop table empref; drop table emp; drop type person; create type person as object ( name char(20), age number, address address_object ); create table emp (emp_id number, emp_info person); create table empref (emp_id number, emp_info REF person); create table extper of person; create or replace procedure upd_addr(addr IN OUT address_object) is begin addr.state := 'CA'; addr.zip := '95117'; end; / commit; set echo off;
/* NAME cdemord1.c - C DEMO program for DML with RETURNING clause - #1. DESCRIPTION This Demo program demonstrates the use of INSERT/UPDATE/DELETE statements with a RETURNING clause in it. */ #include <cdemodr1.h> /*------------------------ Global Variables -------------------------------*/ static boolean logged_on = FALSE; /* TAB1 columns */ static int in1[MAXITER]; /* for INTEGER */ static text in2[MAXITER][40]; /* for CHAR(40) */ static text in3[MAXITER][40]; /* for VARCHAR2(40) */ static float in4[MAXITER]; /* for FLOAT */ static int in5[MAXITER]; /* for DECIMAL */ static float in6[MAXITER]; /* for DECIMAL(8,3) */ static int in7[MAXITER]; /* for NUMERIC */ static float in8[MAXITER]; /* for NUMERIC(7,2) */ static ub1 in9[MAXITER][7]; /* for DATE */ static ub1 in10[MAXITER][40]; /* for RAW(40) */ /* output buffers */ static int *p1[MAXITER]; /* for INTEGER */ static text *p2[MAXITER]; /* for CHAR(40) */ static text *p3[MAXITER]; /* for VARCHAR2(40) */ static float *p4[MAXITER]; /* for FLOAT */ static int *p5[MAXITER]; /* for DECIMAL */ static float *p6[MAXITER]; /* for DECIMAL(8,3) */ static int *p7[MAXITER]; /* for NUMERIC */ static float *p8[MAXITER]; /* for NUMERIC(7,2) */ static ub1 *p9[MAXITER]; /* for DATE */ static ub1 *p10[MAXITER]; /* for RAW(40) */ static short *ind[MAXCOLS][MAXITER]; /* indicators */ static ub2 *rc[MAXCOLS][MAXITER]; /* return codes */ static ub4 *rl[MAXCOLS][MAXITER]; /* return lengths */ /* skip values for binding TAB1 */ static ub4 s1 = (ub4) sizeof(in1[0]); static ub4 s2 = (ub4) sizeof(in2[0]); static ub4 s3 = (ub4) sizeof(in3[0]); static ub4 s4 = (ub4) sizeof(in4[0]); static ub4 s5 = (ub4) sizeof(in5[0]); static ub4 s6 = (ub4) sizeof(in6[0]); static ub4 s7 = (ub4) sizeof(in7[0]); static ub4 s8 = (ub4) sizeof(in8[0]); static ub4 s9 = (ub4) sizeof(in9[0]); static ub4 s10= (ub4) sizeof(in10[0]); /* Rows returned in each iteration */ static ub2 rowsret[MAXITER]; /* indicator skips */ static ub4 indsk[MAXCOLS] = { 0, 0, 0, 0, 0, 0, 0, 0, 0, 0 }; /* return length skips */ static ub4 rlsk[MAXCOLS] = { 0, 0, 0, 0, 0, 0, 0, 0, 0, 0 }; /* return code skips */ static ub4 rcsk[MAXCOLS] = { 0, 0, 0, 0, 0, 0, 0, 0, 0, 0 }; static int lowc1[MAXITER], highc1[MAXITER]; static ub4 pos[MAXCOLS]; static OCIError *errhp; /*------------------------end of Global variables--------------------*/ /*========================== UTILITY FUNCTIONS ======================*/ /* * These functions are generic functions that can be used in any * OCI program. */ /* ----------------------------------------------------------------- */ /* Initialize environment, allocate handles */ /* ----------------------------------------------------------------- */ sword init_handles(envhp, svchp, errhp, srvhp, authp, init_mode) OCIEnv **envhp; OCISvcCtx **svchp; OCIError **errhp; OCIServer **srvhp; OCISession **authp; ub4 init_mode; { (void) printf("Environment setup ....\n"); /* Initialize the OCI Process */ if (OCIInitialize(init_mode, (dvoid *)0, (dvoid * (*)(dvoid *, size_t)) 0, (dvoid * (*)(dvoid *, dvoid *, size_t))0, (void (*)(dvoid *, dvoid *)) 0 )) { (void) printf("FAILED: OCIInitialize()\n"); return OCI_ERROR; } /* Inititialize the OCI Environment */ if (OCIEnvInit((OCIEnv **) envhp, (ub4) OCI_DEFAULT, (size_t) 0, (dvoid **) 0 )) { (void) printf("FAILED: OCIEnvInit()\n"); return OCI_ERROR; } /* Allocate a service handle */ if (OCIHandleAlloc((dvoid *) *envhp, (dvoid **) svchp, (ub4) OCI_HTYPE_SVCCTX, (size_t) 0, (dvoid **) 0)) { (void) printf("FAILED: OCIHandleAlloc() on svchp\n"); return OCI_ERROR; } /* Allocate an error handle */ if (OCIHandleAlloc((dvoid *) *envhp, (dvoid **) errhp, (ub4) OCI_HTYPE_ERROR, (size_t) 0, (dvoid **) 0)) { (void) printf("FAILED: OCIHandleAlloc() on errhp\n"); return OCI_ERROR; } /* Allocate a server handle */ if (OCIHandleAlloc((dvoid *) *envhp, (dvoid **) srvhp, (ub4) OCI_HTYPE_SERVER, (size_t) 0, (dvoid **) 0)) { (void) printf("FAILED: OCIHandleAlloc() on srvhp\n"); return OCI_ERROR; } /* Allocate a authentication handle */ if (OCIHandleAlloc((dvoid *) *envhp, (dvoid **) authp, (ub4) OCI_HTYPE_SESSION, (size_t) 0, (dvoid **) 0)) { (void) printf("FAILED: OCIHandleAlloc() on authp\n"); return OCI_ERROR; } return OCI_SUCCESS; } /* ----------------------------------------------------------------- */ /* Attach to server with a given mode. */ /* ----------------------------------------------------------------- */ sword attach_server(mode, srvhp, errhp, svchp) ub4 mode; OCIServer *srvhp; OCIError *errhp; OCISvcCtx *svchp; { text *cstring = (text *)""; if (OCIServerAttach(srvhp, errhp, (text *) cstring, (sb4) strlen((char *)cstring), (ub4) OCI_DEFAULT)) { (void) printf("FAILED: OCIServerAttach()\n"); return OCI_ERROR; } /* Set the server handle in the service handle */ if (OCIAttrSet((dvoid *) svchp, (ub4) OCI_HTYPE_SVCCTX, (dvoid *) srvhp, (ub4) 0, (ub4) OCI_ATTR_SERVER, errhp)) { (void) printf("FAILED: OCIAttrSet() server attribute\n"); return OCI_ERROR; } return OCI_SUCCESS; } /* ----------------------------------------------------------------- */ /* Logon to the database using given username, password & credentials*/ /* ----------------------------------------------------------------- */ sword log_on(authp, errhp, svchp, uid, pwd, credt, mode) OCISession *authp; OCIError *errhp; OCISvcCtx *svchp; text *uid; text *pwd; ub4 credt; ub4 mode; { /* Set attributes in the authentication handle */ if (OCIAttrSet((dvoid *) authp, (ub4) OCI_HTYPE_SESSION, (dvoid *) uid, (ub4) strlen((char *) uid), (ub4) OCI_ATTR_USERNAME, errhp)) { (void) printf("FAILED: OCIAttrSet() userid\n"); return OCI_ERROR; } if (OCIAttrSet((dvoid *) authp, (ub4) OCI_HTYPE_SESSION, (dvoid *) pwd, (ub4) strlen((char *) pwd), (ub4) OCI_ATTR_PASSWORD, errhp)) { (void) printf("FAILED: OCIAttrSet() passwd\n"); return OCI_ERROR; } (void) printf("Logging on as %s ....\n", uid); if (OCISessionBegin(svchp, errhp, authp, credt, mode)) { (void) printf("FAILED: OCIAttrSet() passwd\n"); return OCI_ERROR; } (void) printf("%s logged on.\n", uid); /* Set the authentication handle in the Service handle */ if (OCIAttrSet((dvoid *) svchp, (ub4) OCI_HTYPE_SVCCTX, (dvoid *) authp, (ub4) 0, (ub4) OCI_ATTR_SESSION, errhp)) { (void) printf("FAILED: OCIAttrSet() session\n"); return OCI_ERROR; } return OCI_SUCCESS; } /*---------------------------------------------------------------------*/ /* Allocate all required bind handles */ /*---------------------------------------------------------------------*/ sword init_bind_handle(stmthp, bndhp, nbinds) OCIStmt *stmthp; OCIBind *bndhp[]; int nbinds; { int i; /* * This function init the specified number of bind handles * from the given statement handle. */ for (i = 0; i < nbinds; i++) bndhp[i] = (OCIBind *) 0; return OCI_SUCCESS; } /* ----------------------------------------------------------------- */ /* Print the returned raw data. */ /* ----------------------------------------------------------------- */ void print_raw(raw, rawlen) ub1 *raw; ub4 rawlen; { ub4 i; ub4 lim; ub4 clen = 0; if (rawlen > 120) { ub4 llen = rawlen; while (llen > 120) { lim = clen + 120; for(i = clen; i < lim; ++i) (void) printf("%02.2x", (ub4) raw[i] & 0xFF); (void) printf("\n"); llen -= 120; clen += 120; } lim = clen + llen; } else lim = rawlen; for(i = clen; i < lim; ++i) (void) printf("%02.2x", (ub4) raw[i] & 0xFF); (void) printf("\n"); return; } /* ----------------------------------------------------------------- */ /* Free the specified handles */ /* ----------------------------------------------------------------- */ void free_handles(envhp, svchp, srvhp, errhp, authp, stmthp) OCIEnv *envhp; OCISvcCtx *svchp; OCIServer *srvhp; OCIError *errhp; OCISession *authp; OCIStmt *stmthp; { (void) printf("Freeing handles ...\n"); if (srvhp) (void) OCIHandleFree((dvoid *) srvhp, (ub4) OCI_HTYPE_SERVER); if (svchp) (void) OCIHandleFree((dvoid *) svchp, (ub4) OCI_HTYPE_SVCCTX); if (errhp) (void) OCIHandleFree((dvoid *) errhp, (ub4) OCI_HTYPE_ERROR); if (authp) (void) OCIHandleFree((dvoid *) authp, (ub4) OCI_HTYPE_SESSION); if (stmthp) (void) OCIHandleFree((dvoid *) stmthp, (ub4) OCI_HTYPE_STMT); if (envhp) (void) OCIHandleFree((dvoid *) envhp, (ub4) OCI_HTYPE_ENV); return; } /* ----------------------------------------------------------------- */ /* Print the error message */ /* ----------------------------------------------------------------- */ void report_error(errhp) OCIError *errhp; { text msgbuf[512]; sb4 errcode = 0; (void) OCIErrorGet((dvoid *) errhp, (ub4) 1, (text *) NULL, &errcode, msgbuf, (ub4) sizeof(msgbuf), (ub4) OCI_HTYPE_ERROR); (void) printf("ERROR CODE = %d\n", errcode); (void) printf("%.*s\n", 512, msgbuf); return; } /*-------------------------------------------------------------------*/ /* Logout and detach from the server */ /*-------------------------------------------------------------------*/ void logout_detach_server(svchp, srvhp, errhp, authp, userid) OCISvcCtx *svchp; OCIServer *srvhp; OCIError *errhp; OCISession *authp; text *userid; { if (OCISessionEnd(svchp, errhp, authp, (ub4) 0)) { (void) printf("FAILED: OCISessionEnd()\n"); report_error(errhp); } (void) printf("%s Logged off.\n", userid); if (OCIServerDetach(srvhp, errhp, (ub4) OCI_DEFAULT)) { (void) printf("FAILED: OCISessionEnd()\n"); report_error(errhp); } (void) printf("Detached from server.\n"); return; } /*---------------------------------------------------------------------*/ /* Finish demo and clean up */ /*---------------------------------------------------------------------*/ sword finish_demo(loggedon, envhp, svchp, srvhp, errhp, authp, stmthp, userid) boolean loggedon; OCIEnv *envhp; OCISvcCtx *svchp; OCIServer *srvhp; OCIError *errhp; OCISession *authp; OCIStmt *stmthp; text *userid; { if (loggedon) logout_detach_server(svchp, srvhp, errhp, authp, userid); free_handles(envhp, svchp, srvhp, errhp, authp, stmthp); return OCI_SUCCESS; } /*===================== END OF UTILITY FUNCTIONS ======================*/ /*========================= MAIN ======================================*/ int main(argc, argv) int argc; char *argv[]; { text *username = (text *)"scott"; text *password = (text *)"tiger"; OCIEnv *envhp; OCIServer *srvhp; OCISvcCtx *svchp; OCISession *authp; OCIStmt *stmthp; OCIBind *bndhp[MAXBINDS]; int i; /* Initialize the Environment and allocate handles */ if (init_handles(&envhp, &svchp, &errhp, &srvhp, &authp, (ub4)OCI_DEFAULT)) { (void) printf("FAILED: init_handles()\n"); return finish_demo(logged_on, envhp, svchp, srvhp, errhp, authp, stmthp, username); } /* Attach to the database server */ if (attach_server((ub4) OCI_DEFAULT, srvhp, errhp, svchp)) { (void) printf("FAILED: attach_server()\n"); return finish_demo(logged_on, envhp, svchp, srvhp, errhp, authp, stmthp, username); } /* Logon to the server and begin a session */ if (log_on(authp, errhp, svchp, username, password, (ub4) OCI_CRED_RDBMS, (ub4) OCI_DEFAULT)) { (void) printf("FAILED: log_on()\n"); return finish_demo(logged_on, envhp, svchp, srvhp, errhp, authp, stmthp, username); } logged_on = TRUE; /* Allocate a statement handle */ if (OCIHandleAlloc((dvoid *)envhp, (dvoid **) &stmthp, (ub4)OCI_HTYPE_STMT, (CONST size_t) 0, (dvoid **) 0)) { (void) printf("FAILED: alloc statement handle\n"); return finish_demo(logged_on, envhp, svchp, srvhp, errhp, authp, stmthp, username); } /* bind handles will be implicitly allocated in the bind calls */ /* need to initialize them to null prior to first usage in bind calls */ for (i = 0; i < MAXBINDS; i++) bndhp[i] = (OCIBind *) 0; /* Demonstrate INSERT with RETURNING clause */ if (demo_insert(svchp, stmthp, bndhp, errhp)) (void) printf("FAILED: demo_insert()\n"); else (void) printf("SUCCESS: demo_insert()\n"); /* Demonstrate UPDATE with RETURNING clause */ if (demo_update(svchp, stmthp, bndhp, errhp)) (void) printf("FAILED: demo_update()\n"); else (void) printf("SUCCESS: demo_update()\n"); /* Demonstrate DELETE with RETURNING clause */ if (demo_delete(svchp, stmthp, bndhp, errhp)) (void) printf("FAILED: demo_delete()\n"); else (void) printf("SUCCESS: demo_delete()\n"); /* clean up */ return finish_demo(logged_on, envhp, svchp, srvhp, errhp, authp, stmthp, username); } /* =================== End Main =====================================*/ /* ===================== Local Functions ============================*/ /* ----------------------------------------------------------------- */ /* bind all the columns of TAB1 by positions. */ /* ----------------------------------------------------------------- */ static sword bind_pos(OCIStmt *stmthp, OCIBind *bndhp[], OCIError *errhp) { if (OCIBindByPos(stmthp, &bndhp[0], errhp, (ub4) 1, (dvoid *) &in1[0], (sb4) sizeof(in1[0]), SQLT_INT, (dvoid *) 0, (ub2 *)0, (ub2 *)0, (ub4) 0, (ub4 *) 0, (ub4) OCI_DEFAULT) || OCIBindByPos(stmthp, &bndhp[1], errhp, (ub4) 2, (dvoid *) in2[0], (sb4) sizeof(in2[0]), SQLT_AFC, (dvoid *) 0, (ub2 *)0, (ub2 *)0, (ub4) 0, (ub4 *) 0, (ub4) OCI_DEFAULT) || OCIBindByPos(stmthp, &bndhp[2], errhp, (ub4) 3, (dvoid *) in3[0], (sb4) sizeof(in3[0]), SQLT_CHR, (dvoid *) 0, (ub2 *)0, (ub2 *)0, (ub4) 0, (ub4 *) 0, (ub4) OCI_DEFAULT) || OCIBindByPos(stmthp, &bndhp[3], errhp, (ub4) 4, (dvoid *) &in4[0], (sb4) sizeof(in4[0]), SQLT_FLT, (dvoid *) 0, (ub2 *)0, (ub2 *)0, (ub4) 0, (ub4 *) 0, (ub4) OCI_DEFAULT) || OCIBindByPos(stmthp, &bndhp[4], errhp, (ub4) 5, (dvoid *) &in5[0], (sb4) sizeof(in5[0]), SQLT_INT, (dvoid *) 0, (ub2 *)0, (ub2 *)0, (ub4) 0, (ub4 *) 0, (ub4) OCI_DEFAULT) || OCIBindByPos(stmthp, &bndhp[5], errhp, (ub4) 6, (dvoid *) &in6[0], (sb4) sizeof(in6[0]), SQLT_FLT, (dvoid *) 0, (ub2 *)0, (ub2 *)0, (ub4) 0, (ub4 *) 0, (ub4) OCI_DEFAULT) || OCIBindByPos(stmthp, &bndhp[6], errhp, (ub4) 7, (dvoid *) &in7[0], (sb4) sizeof(in7[0]), SQLT_INT, (dvoid *) 0, (ub2 *)0, (ub2 *)0, (ub4) 0, (ub4 *) 0, (ub4) OCI_DEFAULT) || OCIBindByPos(stmthp, &bndhp[7], errhp, (ub4) 8, (dvoid *) &in8[0], (sb4) sizeof(in8[0]), SQLT_FLT, (dvoid *) 0, (ub2 *)0, (ub2 *)0, (ub4) 0, (ub4 *) 0, (ub4) OCI_DEFAULT) || OCIBindByPos(stmthp, &bndhp[8], errhp, (ub4) 9, (dvoid *) in9[0], (sb4) sizeof(in9[0]), SQLT_DAT, (dvoid *) 0, (ub2 *)0, (ub2 *)0, (ub4) 0, (ub4 *) 0, (ub4) OCI_DEFAULT) || OCIBindByPos(stmthp, &bndhp[9], errhp, (ub4) 10, (dvoid *) in10[0], (sb4) sizeof(in10[0]), SQLT_BIN, (dvoid *) 0, (ub2 *)0, (ub2 *)0, (ub4) 0, (ub4 *) 0, (ub4) OCI_DEFAULT)) { (void) printf("FAILED: OCIBindByPos()\n"); report_error(errhp); return OCI_ERROR; } return OCI_SUCCESS; } /* ----------------------------------------------------------------- */ /* bind all the columns of TAB1 by name. */ /* ----------------------------------------------------------------- */ static sword bind_name(OCIStmt *stmthp, OCIBind *bndhp[], OCIError *errhp) { if (OCIBindByName(stmthp, &bndhp[10], errhp, (text *) ":out1", (sb4) strlen((char *) ":out1"), (dvoid *) 0, (sb4) sizeof(int), SQLT_INT, (dvoid *) 0, (ub2 *)0, (ub2 *)0, (ub4) 0, (ub4 *) 0, (ub4) OCI_DATA_AT_EXEC) || OCIBindByName(stmthp, &bndhp[11], errhp, (text *) ":out2", (sb4) strlen((char *) ":out2"), (dvoid *) 0, (sb4) MAXCOLLEN, SQLT_AFC, (dvoid *) 0, (ub2 *)0, (ub2 *)0, (ub4) 0, (ub4 *) 0, (ub4) OCI_DATA_AT_EXEC) || OCIBindByName(stmthp, &bndhp[12], errhp, (text *) ":out3", (sb4) strlen((char *) ":out3"), (dvoid *) 0, (sb4) MAXCOLLEN, SQLT_CHR, (dvoid *) 0, (ub2 *)0, (ub2 *)0, (ub4) 0, (ub4 *) 0, (ub4) OCI_DATA_AT_EXEC) || OCIBindByName(stmthp, &bndhp[13], errhp, (text *) ":out4", (sb4) strlen((char *) ":out4"), (dvoid *) 0, (sb4) sizeof(float), SQLT_FLT, (dvoid *) 0, (ub2 *)0, (ub2 *)0, (ub4) 0, (ub4 *) 0, (ub4) OCI_DATA_AT_EXEC) || OCIBindByName(stmthp, &bndhp[14], errhp, (text *) ":out5", (sb4) strlen((char *) ":out5"), (dvoid *) 0, (sb4) sizeof(int), SQLT_INT, (dvoid *) 0, (ub2 *)0, (ub2 *)0, (ub4) 0, (ub4 *) 0, (ub4) OCI_DATA_AT_EXEC) || OCIBindByName(stmthp, &bndhp[15], errhp, (text *) ":out6", (sb4) strlen((char *) ":out6"), (dvoid *) 0, (sb4) sizeof(float), SQLT_FLT, (dvoid *) 0, (ub2 *)0, (ub2 *)0, (ub4) 0, (ub4 *) 0, (ub4) OCI_DATA_AT_EXEC) || OCIBindByName(stmthp, &bndhp[16], errhp, (text *) ":out7", (sb4) strlen((char *) ":out7"), (dvoid *) 0, (sb4) sizeof(int), SQLT_INT, (dvoid *) 0, (ub2 *)0, (ub2 *)0, (ub4) 0, (ub4 *) 0, (ub4) OCI_DATA_AT_EXEC) || OCIBindByName(stmthp, &bndhp[17], errhp, (text *) ":out8", (sb4) strlen((char *) ":out8"), (dvoid *) 0, (sb4) sizeof(float), SQLT_FLT, (dvoid *) 0, (ub2 *)0, (ub2 *)0, (ub4) 0, (ub4 *) 0, (ub4) OCI_DATA_AT_EXEC) || OCIBindByName(stmthp, &bndhp[18], errhp, (text *) ":out9", (sb4) strlen((char *) ":out9"), (dvoid *) 0, (sb4) DATBUFLEN, SQLT_DAT, (dvoid *) 0, (ub2 *)0, (ub2 *)0, (ub4) 0, (ub4 *) 0, (ub4) OCI_DATA_AT_EXEC) || OCIBindByName(stmthp, &bndhp[19], errhp, (text *) ":out10", (sb4) strlen((char *) ":out10"), (dvoid *) 0, (sb4) MAXCOLLEN, SQLT_BIN, (dvoid *) 0, (ub2 *)0, (ub2 *)0, (ub4) 0, (ub4 *) 0, (ub4) OCI_DATA_AT_EXEC)) { (void) printf("FAILED: OCIBindByName()\n"); report_error(errhp); return OCI_ERROR; } return OCI_SUCCESS; } /* ----------------------------------------------------------------- */ /* bind array structs for TAB1 columns. */ /* ----------------------------------------------------------------- */ static sword bind_array(OCIBind *bndhp[], OCIError *errhp) { if (OCIBindArrayOfStruct(bndhp[0], errhp, s1, indsk[0], rlsk[0], rcsk[0]) || OCIBindArrayOfStruct(bndhp[1], errhp, s2, indsk[1], rlsk[1], rcsk[1]) || OCIBindArrayOfStruct(bndhp[2], errhp, s3, indsk[2], rlsk[2], rcsk[2]) || OCIBindArrayOfStruct(bndhp[3], errhp, s4, indsk[3], rlsk[3], rcsk[3]) || OCIBindArrayOfStruct(bndhp[4], errhp, s5, indsk[4], rlsk[4], rcsk[4]) || OCIBindArrayOfStruct(bndhp[5], errhp, s6, indsk[5], rlsk[5], rcsk[5]) || OCIBindArrayOfStruct(bndhp[6], errhp, s7, indsk[6], rlsk[6], rcsk[6]) || OCIBindArrayOfStruct(bndhp[7], errhp, s8, indsk[7], rlsk[7], rcsk[7]) || OCIBindArrayOfStruct(bndhp[8], errhp, s9, indsk[8], rlsk[8], rcsk[8]) || OCIBindArrayOfStruct(bndhp[9], errhp, s10, indsk[9], rlsk[9], rcsk[9])) { (void) printf("FAILED: OCIBindArrayOfStruct()\n"); report_error(errhp); return OCI_ERROR; } return OCI_SUCCESS; } /* ----------------------------------------------------------------- */ /* bind dynamic for returning TAB1 columns. */ /* ----------------------------------------------------------------- */ static sword bind_dynamic(OCIBind *bndhp[], OCIError *errhp) { /* * Note here that both IN & OUT BIND callback functions have to be * provided. However, since the bind variables in the RETURNING * clause are pure OUT Binds the IN callback fuctions (cbf_no_data) * is essentially a "do-nothing" function. * * Also note here that although in this demonstration the IN and OUT * callback functions are same, in practice you can have a different * callback function for each bind handle. */ ub4 i; for (i = 0; i < MAXCOLS; i++) pos[i] = i; if (OCIBindDynamic(bndhp[10], errhp, (dvoid *) &pos[0], cbf_no_data, (dvoid *) &pos[0], cbf_get_data) || OCIBindDynamic(bndhp[11], errhp, (dvoid *) &pos[1], cbf_no_data, (dvoid *) &pos[1], cbf_get_data) || OCIBindDynamic(bndhp[12], errhp, (dvoid *) &pos[2], cbf_no_data, (dvoid *) &pos[2], cbf_get_data) || OCIBindDynamic(bndhp[13], errhp, (dvoid *) &pos[3], cbf_no_data, (dvoid *) &pos[3], cbf_get_data) || OCIBindDynamic(bndhp[14], errhp, (dvoid *) &pos[4], cbf_no_data, (dvoid *) &pos[4], cbf_get_data) || OCIBindDynamic(bndhp[15], errhp, (dvoid *) &pos[5], cbf_no_data, (dvoid *) &pos[5], cbf_get_data) || OCIBindDynamic(bndhp[16], errhp, (dvoid *) &pos[6], cbf_no_data, (dvoid *) &pos[6], cbf_get_data) || OCIBindDynamic(bndhp[17], errhp, (dvoid *) &pos[7], cbf_no_data, (dvoid *) &pos[7], cbf_get_data) || OCIBindDynamic(bndhp[18], errhp, (dvoid *) &pos[8], cbf_no_data, (dvoid *) &pos[8], cbf_get_data) || OCIBindDynamic(bndhp[19], errhp, (dvoid *) &pos[9], cbf_no_data, (dvoid *) &pos[9], cbf_get_data)) { (void) printf("FAILED: OCIBindDynamic()\n"); report_error(errhp); return OCI_ERROR; } return OCI_SUCCESS; } /* ----------------------------------------------------------------- */ /* bind input variables. */ /* ----------------------------------------------------------------- */ static sword bind_input(OCIStmt *stmthp, OCIBind *bndhp[], OCIError *errhp) { /* bind the input data by positions */ if (bind_pos(stmthp, bndhp, errhp)) return OCI_ERROR; /* bind input array attributes*/ return (bind_array(bndhp, errhp)); } /* ----------------------------------------------------------------- */ /* bind output variables. */ /* ----------------------------------------------------------------- */ static sword bind_output(OCIStmt *stmthp, OCIBind *bndhp[], OCIError *errhp) { /* bind the returning bind buffers by names */ if (bind_name(stmthp, bndhp, errhp)) return OCI_ERROR; /* bind the returning bind buffers dynamically */ return (bind_dynamic(bndhp, errhp)); } /* ----------------------------------------------------------------- */ /* bind row indicator variables. */ /* ----------------------------------------------------------------- */ static sword bind_low_high(OCIStmt *stmthp, OCIBind *bndhp[], OCIError *errhp) { if (OCIBindByName(stmthp, &bndhp[23], errhp, (text *) ":low", (sb4) strlen((char *) ":low"), (dvoid *) &lowc1[0], (sb4) sizeof(lowc1[0]), SQLT_INT, (dvoid *) 0, (ub2 *)0, (ub2 *)0, (ub4) 0, (ub4 *) 0, (ub4) OCI_DEFAULT) || OCIBindByName(stmthp, &bndhp[24], errhp, (text *) ":high", (sb4) strlen((char *) ":high"), (dvoid *) &highc1[0], (sb4) sizeof(highc1[0]), SQLT_INT, (dvoid *) 0, (ub2 *)0, (ub2 *)0, (ub4) 0, (ub4 *) 0, (ub4) OCI_DEFAULT)) { (void) printf("FAILED: OCIBindByName()\n"); report_error(errhp); return OCI_ERROR; } if (OCIBindArrayOfStruct(bndhp[23], errhp, s1, indsk[0], rlsk[0], rcsk[0]) || OCIBindArrayOfStruct(bndhp[24], errhp, s1, indsk[0], rlsk[0], rcsk[0])) { (void) printf("FAILED: OCIBindArrayOfStruct()\n"); report_error(errhp); return OCI_ERROR; } return OCI_SUCCESS; } /* ----------------------------------------------------------------- */ /* Demontrate INSERT with RETURNING clause. */ /* ----------------------------------------------------------------- */ static sword demo_insert(OCISvcCtx *svchp, OCIStmt *stmthp, OCIBind *bndhp[], OCIError *errhp) { int i, j; /* * This function inserts values for 10 columns in table TAB1 and * uses the RETURN clause to get back the inserted column values. * It inserts MAXITER (10) such rows. Thus it expects MAXITER values * for each column to be returned. */ /* The Insert Statement with RETURNING clause */ text *sqlstmt = (text *) "INSERT INTO TAB1 VALUES (:1, :2, :3, :4, :5, :6, :7, :8, :9, :10) \ RETURNING C1, C2, C3, C4, C5, C6, C7, C8, C9, C10 \ INTO :out1, :out2, :out3, :out4, :out5, :out6, \ :out7, :out8, :out9, :out10"; /* Prepare the statement */ if (OCIStmtPrepare(stmthp, errhp, sqlstmt, (ub4)strlen((char *)sqlstmt), (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT)) { (void) printf("FAILED: OCIStmtPrepare() insert\n"); report_error(errhp); return OCI_ERROR; } /* Initialise the buffers for update */ for (i = 0; i < MAXITER; i++) { in1[i] = i + 1; memset((void *)in2[i], (int) 'A'+i%26, (size_t) 40); memset((void *)in3[i], (int) 'a'+i%26, (size_t) 40); in4[i] = 400.555 + (float) i; in5[i] = 500 + i; in6[i] = 600.250 + (float) i; in7[i] = 700 + i; in8[i] = 800.350 + (float) i; in9[i][0] = 119; in9[i][1] = 185 + (ub1)i%10; in9[i][2] = (ub1)i%12 + 1; in9[i][3] = (ub1)i%25 + 1; in9[i][4] = 0; in9[i][5] = 0; in9[i][6] = 0; for (j = 0; j < 40; j++) in10[i][j] = (ub1) (i%0x10); rowsret[i] = 0; } /* Bind all the input buffers to place holders (:1, :2. :3, etc ) */ if (bind_input(stmthp, bndhp, errhp)) return OCI_ERROR; /* Bind all the output buffers to place holders (:out1, :out2 etc */ if (bind_output(stmthp, bndhp, errhp)) return OCI_ERROR; /* Execute the Insert statement */ if (OCIStmtExecute(svchp, stmthp, errhp, (ub4) MAXITER, (ub4) 0, (CONST OCISnapshot*) 0, (OCISnapshot*) 0, (ub4) OCI_DEFAULT)) { (void) printf("FAILED: OCIStmtExecute() insert\n"); report_error(errhp); return OCI_ERROR; } /* Commit the changes */ (void) OCITransCommit(svchp, errhp, (ub4) 0); /* Print out the values in the return rows */ (void) printf("\n\n DEMONSTRATING INSERT....RETURNING \n"); (void) print_return_data((int)MAXITER); return OCI_SUCCESS; } /* ----------------------------------------------------------------- */ /* Demonstrate UPDATE with RETURNING clause. */ /* ----------------------------------------------------------------- */ static sword demo_update(OCISvcCtx *svchp, OCIStmt *stmthp, OCIBind *bndhp[], OCIError *errhp) { int i, j; int range_size = 3; /* iterations */ /* * This function updates columns in table TAB1, for certain rows * depending on the values of the :low and :high values in * in the WHERE clause. It executes this statement 3 times, (3 iterations) * each time with a different set of values for :low and :high * Thus for each iteration, multiple rows are returned depending * on the number of rows that matched the WHERE clause. * * The rows it updates here are the rows that were inserted by the * cdemodr1.sql script. */ /* The Update Statement with RETURNING clause */ text *sqlstmt = (text *) "UPDATE TAB1 SET C1 = C1 + :1, C2 = :2, C3 = :3, \ C4 = C4 + :4, C5 = C5 + :5, C6 = C6 + :6, \ C7 = C7 + :7, C8 = C8 + :8, C9 = :9, C10 = :10 \ WHERE C1 >= :low AND C1 <= :high \ RETURNING C1, C2, C3, C4, C5, C6, C7, C8, C9, C10 \ INTO :out1, :out2, :out3, :out4, :out5, :out6, \ :out7, :out8, :out9, :out10"; /* Prepare the statement */ if (OCIStmtPrepare(stmthp, errhp, sqlstmt, (ub4)strlen((char *)sqlstmt), (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT)) { (void) printf("FAILED: OCIStmtPrepare() update\n"); report_error(errhp); return OCI_ERROR; } /* Initialise the buffers for insertion */ for (i = 0; i < MAXITER; i++) { in1[i] = 300 + i; memset((void *)in2[i], (int) 'a'+i%26, (size_t) 40); memset((void *)in3[i], (int) 'A'+i%26, (size_t) 40); in4[i] = 400.555 + (float)i; in5[i] = 500 + i; in6[i] = 600.280 + (float)i; in7[i] = 700 + i; in8[i] = 800.620 + (float)i; in9[i][0] = 119; in9[i][1] = 185 - (ub1)i%10; in9[i][2] = (ub1)i%12 + 1; in9[i][3] = (ub1)i%25 + 1; in9[i][4] = 0; in9[i][5] = 0; in9[i][6] = 0; for (j = 0; j < 40; j++) in10[i][j] = (ub1) (i%0x08); rowsret[i] =0; } /* Bind all the input buffers to place holders (:1, :2. :3, etc ) */ if (bind_input(stmthp, bndhp, errhp)) return OCI_ERROR; /* Bind all the output buffers to place holders (:out1, :out2 etc */ if (bind_output(stmthp, bndhp, errhp)) return OCI_ERROR; /* bind row indicator low, high */ if (bind_low_high(stmthp, bndhp, errhp)) return OCI_ERROR; /* update rows between 101 and 103; -- expecting 3 rows returned (update 3 rows) between 105 and 106; -- expecting 2 rows returned (update 2 rows) between 109 and 113; -- expecting 5 rows returned (update 5 rows) */ lowc1[0] = 101; highc1[0] = 103; lowc1[1] = 105; highc1[1] = 106; lowc1[2] = 109; highc1[2] = 113; (void) printf("\n\n DEMONSTRATING UPDATE....RETURNING \n"); if (OCIStmtExecute(svchp, stmthp, errhp, (ub4) range_size, (ub4) 0, (CONST OCISnapshot*) 0, (OCISnapshot*) 0, (ub4) OCI_DEFAULT)) { (void) printf("FAILED: OCIStmtExecute() update\n"); report_error(errhp); return OCI_ERROR; } /* Commit the changes */ (void) OCITransCommit(svchp, errhp, (ub4) 0); /* Print out the values in the return rows */ (void) print_return_data(range_size); return OCI_SUCCESS; } /* ----------------------------------------------------------------- */ /* Demonstrate DELETE with RETURNING clause. */ /* ----------------------------------------------------------------- */ static sword demo_delete(OCISvcCtx *svchp, OCIStmt *stmthp, OCIBind *bndhp[], OCIError *errhp) { int i, range_size = 3; /* iterations */ sword retval; /* * This function deletes certain rows from table TAB1 * depending on the values of the :low and :high values in * the WHERE clause. It executes this statement 3 times, (3 iterations) * each time with a different set of values for :low and :high * Thus for each iteration, multiples rows are returned depending * on the number of rows that matched the WHERE clause. * * The rows it deletes here are the rows that were inserted by the * cdemodr1.sql script. */ /* The Delete Statement with RETURNING clause */ text *sqlstmt = (text *) "DELETE FROM TAB1 WHERE C1 >= :low AND C1 <= :high \ RETURNING C1, C2, C3, C4, C5, C6, C7, C8, C9, C10 \ INTO :out1, :out2, :out3, :out4, :out5, :out6, \ :out7, :out8, :out9, :out10"; /* Prepare the statement */ if (OCIStmtPrepare(stmthp, errhp, sqlstmt, (ub4)strlen((char *)sqlstmt), (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT)) { (void) printf("FAILED: OCIStmtPrepare() delete\n"); report_error(errhp); return OCI_ERROR; } /* Bind all the output buffers to place holders (:out1, :out2 etc */ if (bind_output(stmthp, bndhp, errhp)) return OCI_ERROR; /* bind row indicator low, high */ if (bind_low_high(stmthp, bndhp, errhp)) return OCI_ERROR; /* delete rows between 201 and 203; -- expecting 3 rows returned (3 rows deleted) between 205 and 209; -- expecting 5 rows returned (2 rows deleted) between 211 and 213; -- expecting 3 rows returned (5 rows deleted) */ lowc1[0] = 201; highc1[0] = 203; lowc1[1] = 205; highc1[1] = 209; lowc1[2] = 211; highc1[2] = 213; for (i=0; i<MAXITER; i++) rowsret[i] = 0; (void) printf("\n\n Demonstrating DETELE....RETURNING \n"); if ((retval = OCIStmtExecute(svchp, stmthp, errhp, (ub4) range_size, (ub4) 0, (CONST OCISnapshot*) 0, (OCISnapshot*) 0, (ub4) OCI_DEFAULT)) != OCI_SUCCESS && retval != OCI_SUCCESS_WITH_INFO) { (void) printf("FAILED: OCIStmtExecute() delete, retval = %d\n", retval); report_error(errhp); } /* Commit the changes */ (void) OCITransCommit(svchp, errhp, (ub4) 0); /* Print out the values in the return rows */ (void) print_return_data(range_size); return OCI_SUCCESS; } /* ----------------------------------------------------------------- */ /* IN bind callback that does not do any data input. */ /* ----------------------------------------------------------------- */ static sb4 cbf_no_data(dvoid *ctxp, OCIBind *bindp, ub4 iter, ub4 index, dvoid **bufpp, ub4 *alenpp, ub1 *piecep, dvoid **indpp) { /* * This is a dummy input callback function that provides input data * for the bind variables in the RETURNING clause. */ *bufpp = (dvoid *) 0; *alenpp = 0; *indpp = (dvoid *) 0; *piecep = OCI_ONE_PIECE; return OCI_CONTINUE; } /* ----------------------------------------------------------------- */ /* Outbind callback for returning data. */ /* ----------------------------------------------------------------- */ static sb4 cbf_get_data(dvoid *ctxp, OCIBind *bindp, ub4 iter, ub4 index, dvoid **bufpp, ub4 **alenp, ub1 *piecep, dvoid **indpp, ub2 **rcodepp) { /* * This is the callback function that is called to receive the OUT * bind values for the bind variables in the RETURNING clause */ static ub4 rows = 0; ub4 pos = *((ub4 *)ctxp); /* For each iteration the OCI_ATTR_ROWS_RETURNED tells us the number * of rows returned in that iteration. So we can use this information * to dynamically allocate storage for all the returned rows for that * bind. */ if (index == 0) { (void) OCIAttrGet((CONST dvoid *)bindp, OCI_HTYPE_BIND, (dvoid *)&rows, (ub4 *) sizeof(ub4), OCI_ATTR_ROWS_RETURNED, errhp); rowsret[iter] = (ub2)rows; /* Dynamically allocate storage */ if (alloc_buffer(pos, iter, rows)) return OCI_ERROR; } /* Provide the address of the storage where the data is to be returned */ switch(pos) { case 0: rl[pos][iter][index] = sizeof(int); *bufpp = (dvoid *) (p1[iter]+ index); break; case 1: rl[pos][iter][index] = (ub4) MAXCOLLEN; *bufpp = (dvoid *) (p2[iter]+(index * MAXCOLLEN)); break; case 2: rl[pos][iter][index] = (ub4) MAXCOLLEN; *bufpp = (dvoid *) (p3[iter]+(index * MAXCOLLEN)); break; case 3: rl[pos][iter][index] = sizeof(float); *bufpp = (dvoid *) (p4[iter]+ index); break; case 4: rl[pos][iter][index] = sizeof(int); *bufpp = (dvoid *) (p5[iter]+index); break; case 5: rl[pos][iter][index] = sizeof(float); *bufpp = (dvoid *) (p6[iter]+index ); break; case 6: rl[pos][iter][index] = sizeof(int); *bufpp = (dvoid *) (p7[iter]+ index); break; case 7: rl[pos][iter][index] = sizeof(float); *bufpp = (dvoid *) (p8[iter]+index); break; case 8: rl[pos][iter][index] = DATBUFLEN; *bufpp = (dvoid *) (p9[iter]+(index * DATBUFLEN)); break; case 9: rl[pos][iter][index] = (ub4) MAXCOLLEN; *bufpp = (dvoid *) (p10[iter]+(index * MAXCOLLEN)); break; default: *bufpp = (dvoid *) 0; *alenp = (ub4 *) 0; (void) printf("ERROR: invalid position number: %d\n", *((ub2 *)ctxp)); } *piecep = OCI_ONE_PIECE; /* provide address of the storage where the indicator will be returned */ ind[pos][iter][index] = 0; *indpp = (dvoid *) &ind[pos][iter][index]; /* provide address of the storage where the return code will be returned */ rc[pos][iter][index] = 0; *rcodepp = &rc[pos][iter][index]; /* * provide address of the storage where the actual length will be * returned */ *alenp = &rl[pos][iter][index]; return OCI_CONTINUE; } /* ----------------------------------------------------------------- */ /* allocate buffers for callback. */ /* ----------------------------------------------------------------- */ static sword alloc_buffer(ub4 pos, ub4 iter, ub4 rows) { switch(pos) { case 0: p1[iter] = (int *) malloc(sizeof(int) * rows); break; case 1: p2[iter] = (text *) malloc(rows * MAXCOLLEN); break; case 2: p3[iter] = (text *) malloc(rows * MAXCOLLEN); break; case 3: p4[iter] = (float *) malloc(sizeof(float) * rows); break; case 4: p5[iter] = (int *) malloc(sizeof(int) * rows); break; case 5: p6[iter] = (float *) malloc(sizeof(float) * rows); break; case 6: p7[iter] = (int *) malloc(sizeof(int) * rows); break; case 7: p8[iter] = (float *) malloc(sizeof(float) * rows); break; case 8: p9[iter] = (ub1 *) malloc(rows * DATBUFLEN); break; case 9: p10[iter] = (ub1 *) malloc(rows * MAXCOLLEN); break; default: (void) printf("ERROR: invalid position number: %d\n", pos); return OCI_ERROR; } ind[pos][iter] = (short *) malloc(rows * sizeof(short)); rc[pos][iter] = (ub2 *) malloc(rows * sizeof(ub2)); rl[pos][iter] = (ub4 *) malloc(rows * sizeof(ub4)); return OCI_SUCCESS; } /* ----------------------------------------------------------------- */ /* print the returned data. */ /* ----------------------------------------------------------------- */ static sword print_return_data(iters) int iters; { int i, j; for (i = 0; i < iters; i++) { (void) printf("\n*** ITERATION *** : %d\n", i); (void) printf("(...returning %d rows)\n", rowsret[i]); for (j = 0; j < rowsret[i] ; j++) { /* Column 1 */ (void) printf("COL1 [%d]: ind = %d, rc = %d, retl = %d\n", j, ind[0][i][j], rc[0][i][j], rl[0][i][j]); if (ind[0][i][j] == -1) (void) printf("COL1 [%d]: NULL\n", j); else (void) printf("COL1 [%d]: %d\n", j, *(p1[i]+j) ); /* Column 2 */ (void) printf("COL2 [%d]: ind = %d, rc = %d, retl = %d\n", j, ind[1][i][j], rc[1][i][j], rl[1][i][j]); if (ind[1][i][j] == -1) (void) printf("COL2 [%d]: NULL\n", j); else (void) printf("COL2 [%d]: %.*s\n", j, rl[1][i][j],p2[i]+(j*MAXCOLLEN) ); /* Column 3 */ (void) printf("COL3 [%d]: ind = %d, rc = %d, retl = %d\n", j, ind[2][i][j], rc[2][i][j], rl[2][i][j]); if (ind[2][i][j] == -1) (void) printf("COL3 [%d]: NULL\n", j); else (void) printf("COL3 [%d]: %.*s\n", j, rl[2][i][j],p3[i]+(j*MAXCOLLEN) ); /* Column 4 */ (void) printf("COL4 [%d]: ind = %d, rc = %d, retl = %d\n", j, ind[3][i][j], rc[3][i][j], rl[3][i][j]); if (ind[3][i][j] == -1) (void) printf("COL4 [%d]: NULL\n", j); else (void) printf("COL4 [%d]: %8.3f\n", j, *(p4[i]+j) ); /* Column 5 */ (void) printf("COL5 [%d]: ind = %d, rc = %d, retl = %d\n", j, ind[4][i][j], rc[4][i][j], rl[4][i][j]); if (ind[4][i][j] == -1) (void) printf("COL5 [%d]: NULL\n", j); else (void) printf("COL5 [%d]: %d\n", j, *(p5[i]+j) ); /* Column 6 */ (void) printf("COL6 [%d]: ind = %d, rc = %d, retl = %d\n", j, ind[5][i][j], rc[5][i][j], rl[5][i][j]); if (ind[5][i][j] == -1) (void) printf("COL6 [%d]: NULL\n", j); else (void) printf("COL6 [%d]: %8.3f\n", j, *(p6[i]+j) ); /* Column 7 */ (void) printf("COL7 [%d]: ind = %d, rc = %d, retl = %d\n", j, ind[6][i][j], rc[6][i][j], rl[6][i][j]); if (ind[6][i][j] == -1) (void) printf("COL7 [%d]: NULL\n", j); else (void) printf("COL7 [%d]: %d\n", j, *(p7[i]+j) ); /* Column 8 */ (void) printf("COL8 [%d]: ind = %d, rc = %d, retl = %d\n", j, ind[7][i][j], rc[7][i][j], rl[7][i][j]); if (ind[7][i][j] == -1) (void) printf("COL8 [%d]: NULL\n", j); else (void) printf("COL8 [%d]: %8.3f\n", j, *(p8[i]+j) ); /* Column 9 */ (void) printf("COL9 [%d]: ind = %d, rc = %d, retl = %d\n", j, ind[8][i][j], rc[8][i][j], rl[8][i][j]); if (ind[8][i][j] == -1) (void) printf("COL9 [%d]: NULL\n", j); else (void) printf("COL9 [%d]: %u-%u-%u%u\n", j, *(p9[i]+(j*DATBUFLEN+3)), *(p9[i]+(j*DATBUFLEN+2)), *(p9[i]+(j*DATBUFLEN+0)) - 100, *(p9[i]+(j*DATBUFLEN+1)) - 100 ); /* Column 10 */ (void) printf("COL10 [%d]: ind = %d, rc = %d, retl = %d\n", j, ind[9][i][j], rc[9][i][j], rl[9][i][j]); if (ind[9][i][j] == -1) (void) printf("COL10 [%d]: NULL\n", j); else { (void) printf("COL10 [%d]: ", j); print_raw(p10[i]+(j*MAXCOLLEN), rl[9][i][j]); } (void) printf("\n"); } } return OCI_SUCCESS; }
/*---------------------------------------------------------- * Include Files */ #include <stdio.h> #include <string.h> #include <oci.h> /*------------------------------------------------------------------ * Define Constants */ #define MAXBINDS 25 #define MAXROWS 5 /* max no of rows returned per iter */ #define MAXCOLS 10 #define MAXITER 10 /* max no of iters in execute */ #define MAXCOLLEN 40 /* if changed, update cdemodr1.sql */ #define DATBUFLEN 7 int main(/*_ int argc, char *argv[] _*/); static sword init_handles(/*_ OCIEnv **envhp, OCISvcCtx **svchp, OCIError **errhp, OCIServer **svrhp, OCISession **authp, ub4 mode _*/); static sword attach_server(/*_ ub4 mode, OCIServer *srvhp, OCIError *errhp, OCISvcCtx *svchp _*/); static sword log_on(/*_ OCISession *authp, OCIError *errhp, OCISvcCtx *svchp, text *uid, text *pwd, ub4 credt, ub4 mode _*/); static sword alloc_bind_handle(/*_ OCIStmt *stmthp, OCIBind *bndhp[], int nbinds _*/); static void print_raw(/*_ ub1 *raw, ub4 rawlen _*/); static void free_handles(/*_ OCIEnv *envhp, OCISvcCtx *svchp, OCIServer *srvhp, OCIError *errhp, OCISession *authp, OCIStmt *stmthp _*/); void report_error(/*_ OCIError *errhp _*/); void logout_detach_server(/*_ OCISvcCtx *svchp, OCIServer *srvhp, OCIError *errhp, OCISession *authp, text *userid _*/); sword finish_demo(/*_ boolean loggedon, OCIEnv *envhp, OCISvcCtx *svchp, OCIServer *srvhp, OCIError *errhp, OCISession *authp, OCIStmt *stmthp, text *userid _*/); static sword demo_insert(/*_ OCISvcCtx *svchp, OCIStmt *stmthp, OCIBind *bndhp[], OCIError *errhp _*/); static sword demo_update(/*_ OCISvcCtx *svchp, OCIStmt *stmthp, OCIBind *bndhp[], OCIError *errhp _*/); static sword demo_delete(/*_ OCISvcCtx *svchp, OCIStmt *stmthp, OCIBind *bndhp[], OCIError *errhp _*/); static sword bind_name(/*_ OCIStmt *stmthp, OCIBind *bndhp[], OCIError *errhp _*/); static sword bind_pos(/*_ OCIStmt *stmthp, OCIBind *bndhp[], OCIError *errhp _*/); static sword bind_input(/*_ OCIStmt *stmthp, OCIBind *bndhp[], OCIError *errhp _*/); static sword bind_output(/*_ OCIStmt *stmthp, OCIBind *bndhp[], OCIError *errhp _*/); static sword bind_array(/*_ OCIBind *bndhp[], OCIError *errhp _*/); static sword bind_dynamic(/*_ OCIBind *bndhp[], OCIError *errhp _*/); static sb4 cbf_no_data(/*_ dvoid *ctxp, OCIBind *bindp, ub4 iter, ub4 index, dvoid **bufpp, ub4 *alenpp, ub1 *piecep, dvoid **indpp _*/); static sb4 cbf_get_data(/*_ dvoid *ctxp, OCIBind *bindp, ub4 iter, ub4 index, dvoid **bufpp, ub4 **alenpp, ub1 *piecep, dvoid **indpp, ub2 **rcodepp _*/); static sword alloc_buffer(/*_ ub4 pos, ub4 iter, ub4 rows _*/); static sword print_return_data(/*_ int iter _*/);
/* NAME cdemodsc.c DESCRIPTION Tests OCIDescribeAny() on an object. cdemodsc takes the user name and password and a type name (created in the database) as command line arguments and dumps all the information about the type -- its attribute types, methods, method parameters, etc. */ #ifndef CDEMODSC_ORACLE #include "cdemodsc.h" #endif /*******************************************************************/ static void checkerr(errhp, status) OCIError *errhp; sword status; { text errbuf[512]; sb4 errcode; switch (status) { case OCI_SUCCESS: break; case OCI_SUCCESS_WITH_INFO: break; case OCI_NEED_DATA: break; case OCI_NO_DATA: break; case OCI_ERROR: DISCARD OCIErrorGet ((dvoid *) errhp, (ub4) 1, (text *) NULL, &errcode, errbuf, (ub4) sizeof(errbuf), (ub4) OCI_HTYPE_ERROR); DISCARD printf("Error - %s\n", errbuf); exit(1); break; case OCI_INVALID_HANDLE: break; case OCI_STILL_EXECUTING: break; case OCI_CONTINUE: break; default: break; } } /*----------------------------------------------------------------*/ static void chk_methodlst(envhp, errhp, svchp, parmp, count, comment) OCIEnv *envhp; OCIError *errhp; OCISvcCtx *svchp; dvoid *parmp; ub4 count; const text *comment; { sword retval; ub4 pos; dvoid *parmdp; for (pos = 1; pos <= count; pos++) { checkerr(errhp, OCIParamGet((dvoid *)parmp, (ub4) OCI_DTYPE_PARAM, errhp, (dvoid *)&parmdp, (ub4) pos)); chk_method(envhp, errhp, svchp, parmdp, comment); } } /*----------------------------------------------------------------*/ static void chk_method(envhp, errhp, svchp, parmp, comment) OCIEnv *envhp; OCIError *errhp; OCISvcCtx *svchp; dvoid *parmp; const text *comment; { sword retval; text method[MAXNAME], *namep; ub4 size; ub4 num_arg; ub1 has_result, is_selfish, is_virtual, is_inline, is_constructor, is_destructor, is_constant, is_operator, is_map, is_order, is_rnds, is_rnps, is_wnds, is_wnps; OCITypeEncap encap; dvoid *list_arg; /* get name of the method */ checkerr(errhp, OCIAttrGet((dvoid*) parmp, (ub4) OCI_DTYPE_PARAM, (dvoid*) &namep, (ub4 *) &size, (ub4) OCI_ATTR_NAME, (OCIError *) errhp)); (void) strncpy((char *)method, (char *)namep, (size_t) size); method[size] = '\0'; /* get the number of arguments */ checkerr(errhp, OCIAttrGet((dvoid*) parmp, (ub4) OCI_DTYPE_PARAM, (dvoid*) &num_arg, (ub4 *) 0, (ub4) OCI_ATTR_NUM_ARGS, (OCIError *) errhp)); /* encapsulation (public?) */ checkerr(errhp, OCIAttrGet((dvoid*) parmp, (ub4) OCI_DTYPE_PARAM, (dvoid*) &encap, (ub4 *) 0, (ub4) OCI_ATTR_ENCAPSULATION, (OCIError *) errhp)); /* has result */ checkerr(errhp, OCIAttrGet((dvoid *) parmp, (ub4) OCI_DTYPE_PARAM, (dvoid *)&has_result, (ub4 *)0, (ub4)OCI_ATTR_HAS_RESULT, (OCIError *) errhp)); /* map method */ checkerr(errhp, OCIAttrGet((dvoid *) parmp, (ub4) OCI_DTYPE_PARAM, (dvoid *)&is_map, (ub4 *)0, (ub4)OCI_ATTR_IS_MAP, (OCIError *) errhp)); /* order method */ checkerr(errhp, OCIAttrGet((dvoid *) parmp, (ub4) OCI_DTYPE_PARAM, (dvoid *)&is_order, (ub4 *)0, (ub4)OCI_ATTR_IS_ORDER, (OCIError *) errhp)); /* selfish method */ checkerr(errhp, OCIAttrGet((dvoid *) parmp, (ub4) OCI_DTYPE_PARAM, (dvoid *)&is_selfish, (ub4 *)0, (ub4)OCI_ATTR_IS_SELFISH, (OCIError *) errhp)); /* virtual method */ checkerr(errhp, OCIAttrGet((dvoid *) parmp, (ub4) OCI_DTYPE_PARAM, (dvoid *)&is_virtual, (ub4 *)0, (ub4)OCI_ATTR_IS_VIRTUAL, (OCIError *) errhp)); /* inline method */ checkerr(errhp, OCIAttrGet((dvoid *) parmp, (ub4) OCI_DTYPE_PARAM, (dvoid *)&is_inline, (ub4 *)0, (ub4)OCI_ATTR_IS_INLINE, (OCIError *) errhp)); /* constant method */ checkerr(errhp, OCIAttrGet((dvoid *) parmp, (ub4) OCI_DTYPE_PARAM, (dvoid *)&is_constant, (ub4 *)0, (ub4)OCI_ATTR_IS_CONSTANT, (OCIError *) errhp)); /* operator */ checkerr(errhp, OCIAttrGet((dvoid *) parmp, (ub4) OCI_DTYPE_PARAM, (dvoid *)&is_operator, (ub4 *)0, (ub4)OCI_ATTR_IS_OPERATOR, (OCIError *) errhp)); /* constructor method */ checkerr(errhp, OCIAttrGet((dvoid *) parmp, (ub4) OCI_DTYPE_PARAM, (dvoid *)&is_constructor, (ub4 *)0, (ub4)OCI_ATTR_IS_CONSTRUCTOR, (OCIError *) errhp)); /* destructor method */ checkerr(errhp, OCIAttrGet((dvoid *) parmp, (ub4) OCI_DTYPE_PARAM, (dvoid *)&is_destructor, (ub4 *)0, (ub4)OCI_ATTR_IS_DESTRUCTOR, (OCIError *) errhp)); checkerr(errhp, OCIAttrGet((dvoid *) parmp, (ub4) OCI_DTYPE_PARAM, (dvoid *)&is_rnds, (ub4 *)0, (ub4)OCI_ATTR_IS_RNDS, (OCIError *) errhp)); checkerr(errhp, OCIAttrGet((dvoid *) parmp, (ub4) OCI_DTYPE_PARAM, (dvoid *)&is_rnps, (ub4 *)0, (ub4)OCI_ATTR_IS_RNPS, (OCIError *) errhp)); checkerr(errhp, OCIAttrGet((dvoid *) parmp, (ub4) OCI_DTYPE_PARAM, (dvoid *)&is_wnds, (ub4 *)0, (ub4)OCI_ATTR_IS_WNDS, (OCIError *) errhp)); checkerr(errhp, OCIAttrGet((dvoid *) parmp, (ub4) OCI_DTYPE_PARAM, (dvoid *)&is_wnps, (ub4 *)0, (ub4)OCI_ATTR_IS_WNPS, (OCIError *) errhp)); /* get list of arguments */ checkerr(errhp, OCIAttrGet((dvoid *) parmp, (ub4) OCI_DTYPE_PARAM, (dvoid *)&list_arg, (ub4 *)0, (ub4)OCI_ATTR_LIST_ARGUMENTS, (OCIError *) errhp)); SPACING; printf ( "\n%s\n", comment); SPACING; printf ( "Name: %s\n", method); SPACING; printf ( "Number of args: %d\n", num_arg); SPACING; printf ( "Encapsulation: %s\n", (encap==OCI_TYPEENCAP_PUBLIC) ? "public" : "private"); SPACING; printf ( "Has result: %d\n", has_result); SPACING; printf ( "Is selfish: %d\n", is_selfish); SPACING; printf ( "Is virtual: %d\n", is_virtual); SPACING; printf ( "Is inline: %d\n", is_inline); SPACING; printf ( "Is constructor: %d\n", is_constructor); SPACING; printf ( "Is desctructor: %d\n", is_destructor); SPACING; printf ( "Is constant: %d\n", is_constant); SPACING; printf ( "Is operator: %d\n", is_operator); SPACING; printf ( "Is map: %d\n", is_map); SPACING; printf ( "Is order: %d\n", is_order); SPACING; printf ( "Is RNDS: %d\n", is_rnds); SPACING; printf ( "Is RNPS: %d\n", is_rnps); SPACING; printf ( "Is WNPS: %d\n", is_wnps); printf("\n"); if (has_result) chk_arg(envhp, errhp, svchp, list_arg, OCI_PTYPE_TYPE_RESULT, 0, 1); if (num_arg > 0) chk_arg(envhp, errhp, svchp, list_arg, OCI_PTYPE_TYPE_ARG, 1, num_arg + 1); } /*------------------------------------------------------------------*/ static void chk_arglst(envhp, errhp, svchp, parmp) OCIEnv *envhp; OCIError *errhp; OCISvcCtx *svchp; dvoid *parmp; { dvoid *arglst; ub4 numargs; ub1 ptype; sword retval; /* get list of arguments */ checkerr(errhp, OCIAttrGet((dvoid*) parmp, (ub4) OCI_DTYPE_PARAM, (dvoid*) &arglst, (ub4 *) 0, (ub4) OCI_ATTR_LIST_ARGUMENTS, (OCIError *) errhp)); /* get number of parameters */ checkerr(errhp, OCIAttrGet((dvoid*) arglst, (ub4) OCI_DTYPE_PARAM, (dvoid*) &numargs, (ub4 *) 0, (ub4) OCI_ATTR_NUM_PARAMS, (OCIError *) errhp)); checkerr(errhp, OCIAttrGet((dvoid*) parmp, (ub4) OCI_DTYPE_PARAM, (dvoid*) &ptype, (ub4 *) 0, (ub4) OCI_ATTR_PTYPE, (OCIError *) errhp)); switch (ptype) { case OCI_PTYPE_FUNC: chk_arg (envhp, errhp, svchp, arglst, OCI_PTYPE_ARG, 0, numargs); break; case OCI_PTYPE_PROC: chk_arg (envhp, errhp, svchp, arglst, OCI_PTYPE_ARG, 1, numargs); } } /*-----------------------------------------------------------------*/ static void chk_arg (envhp, errhp, svchp, parmp, type, start, end) OCIEnv *envhp; OCIError *errhp; OCISvcCtx *svchp; dvoid *parmp; ub1 type; ub4 start; ub4 end; { text argname[NPOS][30]; text *namep; ub4 sizep; ub2 collen[NPOS]; ub2 coldesr[NPOS]; dvoid *parmdp; ub4 i, pos; sword retval; ub2 level[NPOS]; ub1 radix[NPOS], def[NPOS]; ub4 iomode[NPOS]; ub1 precision[NPOS], scale[NPOS], isnull[NPOS]; for (pos = start; pos < end; pos++) { checkerr(errhp, OCIParamGet((dvoid *)parmp, (ub4) OCI_DTYPE_PARAM, errhp, (dvoid *)&parmdp, (ub4) pos)); /* get data type */ checkerr(errhp, OCIAttrGet((dvoid*) parmdp, (ub4) OCI_DTYPE_PARAM, (dvoid*) &coldesr[pos], (ub4 *) 0, (ub4) OCI_ATTR_DATA_TYPE, (OCIError *) errhp)); /* method's result has no name */ iomode[pos] = 0; def[pos] = 0; sizep = 0; if (type != OCI_PTYPE_TYPE_RESULT) { /* has default */ checkerr(errhp, OCIAttrGet((dvoid *)parmdp, (ub4) OCI_DTYPE_PARAM, (dvoid *)&def[pos], (ub4 *)0, (ub4)OCI_ATTR_HAS_DEFAULT, (OCIError *) errhp)); /* get iomode */ checkerr(errhp, OCIAttrGet((dvoid*) parmdp, (ub4) OCI_DTYPE_PARAM, (dvoid*) &iomode[pos], (ub4 *) 0, (ub4) OCI_ATTR_IOMODE, (OCIError *) errhp)); /* get argument name */ checkerr(errhp, OCIAttrGet((dvoid*) parmdp, (ub4) OCI_DTYPE_PARAM, (dvoid*) &namep, (ub4 *) &sizep, (ub4) OCI_ATTR_NAME, (OCIError *) errhp)); (void) strncpy((char *)argname[pos], (char *)namep, (size_t) sizep); } argname[pos][sizep] = '\0'; /* the following are not for type arguments and results */ precision[pos] = 0; scale[pos] = 0; collen[pos] = 0; level[pos] = 0; radix[pos] = 0; isnull[pos] = FALSE; if (type != OCI_PTYPE_TYPE_ARG && type != OCI_PTYPE_TYPE_RESULT) { /* get the data size */ checkerr(errhp, OCIAttrGet((dvoid*) parmdp, (ub4) OCI_DTYPE_PARAM, (dvoid*) &collen[pos], (ub4 *) 0, (ub4) OCI_ATTR_DATA_SIZE, (OCIError *) errhp)); /* get the precision of the attribute */ checkerr(errhp, OCIAttrGet((dvoid*) parmdp, (ub4) OCI_DTYPE_PARAM, (dvoid*) &precision, (ub4 *) 0, (ub4) OCI_ATTR_PRECISION, (OCIError *) errhp)); /* get the scale of the attribute */ checkerr(errhp, OCIAttrGet((dvoid*) parmdp, (ub4) OCI_DTYPE_PARAM, (dvoid*) &scale, (ub4 *) 0, (ub4) OCI_ATTR_SCALE, (OCIError *) errhp)); /* get the level of the attribute */ checkerr(errhp, OCIAttrGet((dvoid*) parmdp, (ub4) OCI_DTYPE_PARAM, (dvoid*) &level[pos], (ub4 *) 0, (ub4) OCI_ATTR_LEVEL, (OCIError *) errhp)); /* get the radix of the attribute */ checkerr(errhp, OCIAttrGet((dvoid*) parmdp, (ub4) OCI_DTYPE_PARAM, (dvoid*) &radix[pos], (ub4 *) 0, (ub4) OCI_ATTR_RADIX, (OCIError *) errhp)); /* is null */ checkerr(errhp, OCIAttrGet((dvoid*) parmdp, (ub4) OCI_DTYPE_PARAM, (dvoid*) &isnull, (ub4 *) 0, (ub4) OCI_ATTR_IS_NULL, (OCIError *) errhp)); /* should get error 24328 */ if (OCIAttrGet((dvoid*) parmp, (ub4) OCI_DTYPE_PARAM, (dvoid*) &isnull, (ub4 *) 0, (ub4) OCI_ATTR_INDEX_ONLY, (OCIError *) errhp) != OCI_ERROR) printf("ERROR: should get error here\n"); } } SPACING; (void) printf("Argument Name Length Datatype Level Radix Default Iomode Prec Scl Null\n"); SPACING; (void) printf ("_____________________________________________________________\n"); for (i = start; i < end; i++) { SPACING; (void) printf( "%15s%6d%8d%6d%6d %c%6d%9d%4d%4d\n", argname[i], collen[i], coldesr[i], level[i], radix[i], (def[i])?'y':'n', iomode[i], precision[i], scale[i], isnull[i]); } printf("\n"); } static void chk_collection (envhp, errhp, svchp, parmp, is_array) OCIEnv *envhp; OCIError *errhp; OCISvcCtx *svchp; dvoid *parmp; sword is_array; { text schema[MAXNAME], type[MAXNAME], *namep; ub4 size; ub2 len; ub4 num_elems; OCITypeCode typecode; sword retval; /* get the data size */ checkerr(errhp, OCIAttrGet((dvoid*) parmp, (ub4) OCI_DTYPE_PARAM, (dvoid*) &len, (ub4 *) 0, (ub4) OCI_ATTR_DATA_SIZE, (OCIError *) errhp)); /* get the name of the collection */ checkerr(errhp, OCIAttrGet((dvoid*) parmp, (ub4) OCI_DTYPE_PARAM, (dvoid*) &namep, (ub4 *) &size, (ub4) OCI_ATTR_TYPE_NAME, (OCIError *) errhp)); (void) strncpy((char *)type, (char *)namep, (size_t) size); type[size] = '\0'; /* get the name of the schema */ checkerr(errhp, OCIAttrGet((dvoid*) parmp, (ub4) OCI_DTYPE_PARAM, (dvoid*) &namep, (ub4 *) &size, (ub4) OCI_ATTR_SCHEMA_NAME, (OCIError *) errhp)); (void) strncpy((char *)schema, (char *)namep, (size_t) size); schema[size] = '\0'; /* get the data type */ checkerr(errhp, OCIAttrGet((dvoid*) parmp, (ub4) OCI_DTYPE_PARAM, (dvoid*) &typecode, (ub4 *) 0, (ub4) OCI_ATTR_DATA_TYPE, (OCIError *) errhp)); num_elems = 0; if (is_array) /* get the number of elements */ checkerr(errhp, OCIAttrGet((dvoid*) parmp, (ub4) OCI_DTYPE_PARAM, (dvoid*) &num_elems, (ub4 *) 0, (ub4) OCI_ATTR_NUM_ELEMS, (OCIError *) errhp)); SPACING; (void) printf ( "Schema Type Length Datatype Elements\n"); SPACING; (void) printf ( "____________________________________________________\n"); SPACING; (void) printf( "%10s%16s%6d%11d%9d\n", schema, type, len, typecode, num_elems); printf("\n"); } /*-----------------------------------------------------------------*/ static void chk_column(envhp, errhp, svchp, parmp, parmcnt) OCIEnv *envhp; OCIError *errhp; OCISvcCtx *svchp; dvoid *parmp; ub4 parmcnt; { text colname1[NPOS][30], colname2[NPOS][30], colname3[NPOS][30]; text *namep; ub4 sizep; ub2 collen[NPOS]; ub2 coldesr[NPOS]; dvoid *parmdp; ub4 i, pos; sword retval; /* loop through all the attributes in the type and get all information */ for (pos = 1; pos <= parmcnt; pos++) { /* get the parameter list for each attribute */ checkerr(errhp, OCIParamGet((dvoid *) parmp, (ub4) OCI_DTYPE_PARAM, errhp, (dvoid *)&parmdp, (ub4) pos)); /* size of the attribute (non object or REF) objects */ checkerr(errhp, OCIAttrGet((dvoid*) parmdp, (ub4) OCI_DTYPE_PARAM, (dvoid*) &collen[pos-1], (ub4 *) 0, (ub4) OCI_ATTR_DATA_SIZE, (OCIError *) errhp)); /* name of the attribute */ checkerr(errhp, OCIAttrGet((dvoid*) parmdp, (ub4) OCI_DTYPE_PARAM, (dvoid*) &namep, (ub4 *) &sizep, (ub4) OCI_ATTR_NAME, (OCIError *) errhp)); (void) strncpy((char *)colname1[pos-1], (char *)namep, (size_t) sizep); colname1[pos-1][sizep] = '\0'; /* get the schema name */ checkerr(errhp, OCIAttrGet((dvoid*) parmdp, (ub4) OCI_DTYPE_PARAM, (dvoid*) &namep, (ub4 *) &sizep, (ub4) OCI_ATTR_SCHEMA_NAME, (OCIError *) errhp)); (void) strncpy((char *)colname2[pos-1], (char *)namep, (size_t) sizep); colname2[pos-1][sizep] = '\0'; /* name of the attribute */ checkerr(errhp, OCIAttrGet((dvoid*) parmdp, (ub4) OCI_DTYPE_PARAM, (dvoid*) &namep, (ub4 *) &sizep, (ub4) OCI_ATTR_TYPE_NAME, (OCIError *) errhp)); (void) strncpy((char *)colname3[pos-1], (char *)namep, (size_t) sizep); colname3[pos-1][sizep] = '\0'; /* get data type */ checkerr(errhp, OCIAttrGet((dvoid*) parmdp, (ub4) OCI_DTYPE_PARAM, (dvoid*) &coldesr[pos-1], (ub4 *) 0, (ub4) OCI_ATTR_DATA_TYPE, (OCIError *) errhp)); if (coldesr[pos-1] == SQLT_NTY || coldesr[pos-1] == SQLT_REF) { /* call tst_desc_type here if the type is object or REF */ tab += 5; SPACING; printf("!!!!ATTRIBUTE IS A TYPE OR REF!!!!\n"); SPACING; printf("ATTRIBUTE NAME IS %s\n", colname3[pos-1]); SPACING; printf("ATTRIBUTE TYPE IS %d\n", coldesr[pos-1]); tst_desc_type(envhp, errhp, svchp, colname3[pos-1]); tab -= 5; printf("\n"); } } SPACING; (void) printf ( "Column Name Schema Type Length Datatype\n"); SPACING; (void) printf ( "__________________________________________________________\n"); for (i = 1; i <= parmcnt; i++) { SPACING; (void) printf( "%15s%10s%16s%6d%8d\n", colname1[i-1], colname2[i-1], colname3[i-1], collen[i-1], coldesr[i-1] ); } printf("\n"); } /*-----------------------------------------------------------------*/ static void tst_desc_type(envhp, errhp, svchp, objname) OCIEnv *envhp; OCIError *errhp; OCISvcCtx *svchp; text *objname; { OCIDescribe *dschp = (OCIDescribe *) 0; sword retval; OCITypeCode typecode, collection_typecode; text schema[MAXNAME], version[MAXNAME], *namep, *type_name; ub4 size, text_len; OCIRef *type_ref; ub2 num_attr, num_method; ub1 is_incomplete, is_system, is_predefined, is_transient, is_sysgen, has_table, has_lob, has_file; dvoid *list_attr, *list_method, *map_method, *order_method, *collection_dschp, *some_object; OCIParam *parmp; ub1 objtype; /* must allocate describe handle first for OCIDescribeAny */ checkerr(errhp, OCIHandleAlloc((dvoid *) envhp, (dvoid **) &dschp, (ub4) OCI_HTYPE_DESCRIBE, (size_t) 0, (dvoid **) 0)); /* call OCIDescribeAny and passing in the type name */ checkerr(errhp, OCIDescribeAny(svchp, errhp, (text *)objname, (ub4) strlen((char *)objname), OCI_OTYPE_NAME, (ub1)1, (ub1) OCI_PTYPE_TYPE, dschp)); /* get the parameter list for the requested type */ checkerr(errhp, OCIAttrGet((dvoid *) dschp, (ub4) OCI_HTYPE_DESCRIBE, (dvoid *)&parmp, (ub4 *)0, (ub4)OCI_ATTR_PARAM, errhp)); /* get the schema name for the requested type */ checkerr(errhp, OCIAttrGet((dvoid*) parmp,(ub4) OCI_DTYPE_PARAM, (dvoid*) &namep, (ub4 *) &size, (ub4) OCI_ATTR_SCHEMA_NAME, (OCIError *) errhp)); (void) strncpy((char *)schema, (char *)namep, (size_t) size); schema[size] = '\0'; /* get the type code for the requested type */ checkerr(errhp, OCIAttrGet((dvoid*) parmp, (ub4) OCI_DTYPE_PARAM, (dvoid*) &typecode, (ub4 *) 0, (ub4) OCI_ATTR_TYPECODE, (OCIError *) errhp)); /* get other information for collection type */ if (typecode == OCI_TYPECODE_NAMEDCOLLECTION) { checkerr(errhp, OCIAttrGet((dvoid *) parmp, (ub4) OCI_DTYPE_PARAM, (dvoid *)&collection_typecode, (ub4 *)0, (ub4)OCI_ATTR_COLLECTION_TYPECODE, (OCIError *)errhp)); checkerr(errhp, OCIAttrGet((dvoid *) parmp, (ub4) OCI_DTYPE_PARAM, (dvoid *)&collection_dschp, (ub4 *)0, (ub4)OCI_ATTR_COLLECTION_ELEMENT, (OCIError *)errhp)); checkerr(errhp, OCIAttrGet((dvoid *) parmp, (ub4) OCI_DTYPE_PARAM, (dvoid *)&collection_dschp, (ub4 *)0, (ub4)OCI_ATTR_COLLECTION_ELEMENT, (OCIError *)errhp)); } /* get the ref to the type descriptor */ checkerr(errhp, OCIAttrGet((dvoid*) parmp, (ub4) OCI_DTYPE_PARAM, (dvoid*) &type_ref, (ub4 *) 0, (ub4) OCI_ATTR_REF_TDO, (OCIError *) errhp)); /* get the type version */ checkerr(errhp, OCIAttrGet((dvoid*) parmp, (ub4) OCI_DTYPE_PARAM, (dvoid*) &namep, (ub4 *) &size, (ub4) OCI_ATTR_VERSION, (OCIError *) errhp)); (void) strncpy((char *)version, (char *)namep, (size_t) size); version[size] = '\0'; /* incomplete type */ checkerr(errhp, OCIAttrGet((dvoid*) parmp, (ub4) OCI_DTYPE_PARAM, (dvoid*) &is_incomplete, (ub4 *) 0, (ub4) OCI_ATTR_IS_INCOMPLETE_TYPE, (OCIError *) errhp)); /* system type */ checkerr(errhp, OCIAttrGet((dvoid*) parmp, (ub4) OCI_DTYPE_PARAM, (dvoid*) &is_system, (ub4 *) 0, (ub4) OCI_ATTR_IS_SYSTEM_TYPE, (OCIError *) errhp)); /* predefined type */ checkerr(errhp, OCIAttrGet((dvoid*) parmp, (ub4) OCI_DTYPE_PARAM, (dvoid*) &is_predefined, (ub4 *) 0, (ub4) OCI_ATTR_IS_PREDEFINED_TYPE, (OCIError *) errhp)); /* transient type */ checkerr(errhp, OCIAttrGet((dvoid*) parmp, (ub4) OCI_DTYPE_PARAM, (dvoid*) &is_transient, (ub4 *) 0, (ub4) OCI_ATTR_IS_TRANSIENT_TYPE, (OCIError *) errhp)); /* system generated type */ checkerr(errhp, OCIAttrGet((dvoid*) parmp, (ub4) OCI_DTYPE_PARAM, (dvoid*) &is_sysgen, (ub4 *) 0, (ub4) OCI_ATTR_IS_SYSTEM_GENERATED_TYPE, (OCIError*) errhp)); /* has nested table */ checkerr(errhp, OCIAttrGet((dvoid*) parmp, (ub4) OCI_DTYPE_PARAM, (dvoid*) &has_table, (ub4 *) 0, (ub4) OCI_ATTR_HAS_NESTED_TABLE, (OCIError *) errhp)); /* has lob */ checkerr(errhp, OCIAttrGet((dvoid*) parmp, (ub4) OCI_DTYPE_PARAM, (dvoid*) &has_lob, (ub4 *) 0, (ub4) OCI_ATTR_HAS_LOB, (OCIError *) errhp)); /* has file */ checkerr(errhp, OCIAttrGet((dvoid*) parmp, (ub4) OCI_DTYPE_PARAM, (dvoid*) &has_file, (ub4 *) 0, (ub4) OCI_ATTR_HAS_FILE, (OCIError *) errhp)); /* get the list of attributes */ checkerr(errhp, OCIAttrGet((dvoid *) parmp, (ub4) OCI_DTYPE_PARAM, (dvoid *)&list_attr, (ub4 *)0, (ub4)OCI_ATTR_LIST_TYPE_ATTRS, (OCIError *)errhp)); /* number of attributes */ checkerr(errhp, OCIAttrGet((dvoid*) parmp, (ub4) OCI_DTYPE_PARAM, (dvoid*) &num_attr, (ub4 *) 0, (ub4) OCI_ATTR_NUM_TYPE_ATTRS, (OCIError *) errhp)); /* get method list */ checkerr(errhp, OCIAttrGet((dvoid *) parmp, (ub4) OCI_DTYPE_PARAM, (dvoid *)&list_method, (ub4 *)0, (ub4)OCI_ATTR_LIST_TYPE_METHODS, (OCIError *)errhp)); /* get number of methods */ checkerr(errhp, OCIAttrGet((dvoid*) parmp, (ub4) OCI_DTYPE_PARAM, (dvoid*) &num_method, (ub4 *) 0, (ub4) OCI_ATTR_NUM_TYPE_METHODS, (OCIError *) errhp)); /* get map method list */ checkerr(errhp, OCIAttrGet((dvoid*) parmp, (ub4) OCI_DTYPE_PARAM, (dvoid*) &map_method, (ub4 *) 0, (ub4) OCI_ATTR_MAP_METHOD, (OCIError *) errhp)); /* get order method list*/ checkerr(errhp, OCIAttrGet((dvoid*) parmp, (ub4) OCI_DTYPE_PARAM, (dvoid*) &order_method, (ub4 *) 0, (ub4) OCI_ATTR_ORDER_METHOD, (OCIError *) errhp)); SPACING; printf ( "TYPE : Attributes : \n"); SPACING; printf ( "Schema: %s\n", schema); SPACING; printf ( "Typecode: %d\n", typecode); if (typecode == OCI_TYPECODE_NAMEDCOLLECTION) { SPACING; printf ( "Collection typecode: %d\n", collection_typecode); } SPACING; printf ( "Version: %s\n", version); SPACING; printf ( "Number of attrs: %d\n", num_attr); SPACING; printf ( "Number of methods: %d\n", num_method); SPACING; printf ( "Is incomplete: %d\n", is_incomplete); SPACING; printf ( "Is system: %d\n", is_system); SPACING; printf ( "Is predefined: %d\n", is_predefined); SPACING; printf ( "Is sys-gen: %d\n", is_sysgen); SPACING; printf ( "Is transient: %d\n", is_transient); SPACING; printf ( "Has nested table: %d\n", has_table); SPACING; printf ( "Has LOB: %d\n", has_lob); SPACING; printf ( "Has file: %d\n", has_file); printf("\n"); if (num_attr > 0) chk_column(envhp, errhp, svchp, list_attr, num_attr); else if (typecode == OCI_TYPECODE_NAMEDCOLLECTION) chk_collection(envhp, errhp, svchp, collection_dschp, collection_typecode == OCI_TYPECODE_VARRAY); if (map_method != (dvoid *)0) chk_method(envhp, errhp, svchp, map_method, "TYPE MAP METHOD\n---------------"); if (order_method != (dvoid *)0) chk_method(envhp, errhp, svchp, order_method, "TYPE ORDER METHOD\n-----------------"); if (num_method > 0) chk_methodlst(envhp, errhp, svchp, list_method, num_method, "TYPE METHOD\n-----------"); } /********************************************************************/ int main(int argc, char *argv[]) { OCIEnv *envhp = (OCIEnv *) 0; OCIServer *srvhp = (OCIServer *) 0; OCIError *errhp = (OCIError *) 0; OCISvcCtx *svchp = (OCISvcCtx *) 0; OCISession *usrhp = (OCISession *) 0; dvoid *tmp; int i; tab = 0; if (argc < 4) { (void) printf("Usage -- cdemort <username> <password> <upper case typename>\n"); return (0); } (void) OCIInitialize((ub4) OCI_THREADED | OCI_OBJECT, (dvoid *)0, (dvoid * (*)()) 0, (dvoid * (*)()) 0, (void (*)()) 0 ); (void) OCIHandleAlloc( (dvoid *) NULL, (dvoid **) &envhp, (ub4) OCI_HTYPE_ENV, 52, (dvoid **) &tmp); (void) OCIEnvInit( &envhp, (ub4) OCI_DEFAULT, 21, (dvoid **) &tmp ); (void) OCIHandleAlloc( (dvoid *) envhp, (dvoid **) &errhp, (ub4) OCI_HTYPE_ERROR, 52, (dvoid **) &tmp); (void) OCIHandleAlloc( (dvoid *) envhp, (dvoid **) &srvhp, (ub4) OCI_HTYPE_SERVER, 52, (dvoid **) &tmp); checkerr(errhp, OCIServerAttach( srvhp, errhp, (text *) "", (sb4) strlen(""), (ub4) OCI_DEFAULT)); checkerr(errhp, OCIHandleAlloc( (dvoid *) envhp, (dvoid **) &svchp, (ub4) OCI_HTYPE_SVCCTX, 52, (dvoid **) &tmp)); checkerr(errhp, OCIAttrSet( (dvoid *) svchp, (ub4) OCI_HTYPE_SVCCTX, (dvoid *) srvhp, (ub4) 0, (ub4) OCI_ATTR_SERVER, (OCIError *) errhp)); checkerr(errhp, OCIHandleAlloc((dvoid *)envhp, (dvoid **)&usrhp, (ub4)OCI_HTYPE_SESSION, 0, (dvoid **)0)); checkerr(errhp, OCIAttrSet((dvoid *) usrhp, (ub4)OCI_HTYPE_SESSION, (dvoid *)argv[1], (ub4)strlen(argv[1]), (ub4)OCI_ATTR_USERNAME, errhp)); checkerr(errhp, OCIAttrSet((dvoid *) usrhp, (ub4)OCI_HTYPE_SESSION, (dvoid *)argv[2], (ub4)strlen(argv[2]), (ub4)OCI_ATTR_PASSWORD, errhp)); checkerr(errhp, OCISessionBegin (svchp, errhp, usrhp, OCI_CRED_RDBMS, OCI_DEFAULT)); checkerr(errhp, OCIAttrSet((dvoid *)svchp, (ub4)OCI_HTYPE_SVCCTX, (dvoid *)usrhp, (ub4)0, (ub4)OCI_ATTR_SESSION, errhp)); /* dump an object with all the types */ SPACING; (void) printf("%s\n", argv[3]); tst_desc_type(envhp, errhp, svchp, argv[3]); printf("\n"); checkerr(errhp, OCISessionEnd (svchp, errhp, usrhp, OCI_DEFAULT)); (void) OCIServerDetach( srvhp, errhp, (ub4) OCI_DEFAULT ); checkerr(errhp, OCIHandleFree((dvoid *) svchp, (ub4) OCI_HTYPE_SVCCTX)); checkerr(errhp, OCIHandleFree((dvoid *) errhp, (ub4) OCI_HTYPE_ERROR)); checkerr(errhp, OCIHandleFree((dvoid *) srvhp, (ub4) OCI_HTYPE_SERVER)); return (0); } /* end of file cdemodsc.c */
/* NAME cdemodsc.h - header file for cdemodsc.c */ /*-----------------------------------------------------------------*/ #ifndef CDEMODSC #define CDEMODSC #ifndef S #include <s.h> #endif #ifndef HSTDEF #include <hstdef.h> #endif #ifndef SQLDEF #include <sqldef.h> #endif #ifndef OCIDEF #include <ocidef.h> #endif #ifndef OCI_ORACLE #include <oci.h> #endif #include <lnx.h> /*---------------------------------------------------------------*/ /* ** #define */ #define MAXNAME 30 #define MAXOBJLEN 60 #define MAXOBJS 7 #define NPOS 40 #define SPACING for (glindex = 0; glindex < tab; glindex++)\ printf(" ") /*------------------------------------------------------------------*/ /* ** Prototypes for functions in cdemodsc.c */ static void chk_column(/*_ OCIEnv *envhp, OCIError *errhp, OCISvcCtx *svchp, dvoid *dschp, ub4 parmcnt _*/); static void chk_method(/*_ OCIEnv *envhp, OCIError *errhp, OCISvcCtx *svchp, dvoid *dschp, const text *comment _*/); static void chk_methodlst(/*_ OCIEnv *envhp, OCIError *errhp, OCISvcCtx *svchp, dvoid *dschp, ub4 count, const text *comment _*/); static void chk_arglst(/*_ OCIEnv *envhp, OCIError *errhp, OCISvcCtx *svchp, dvoid *dschp _*/); static void chk_arg(/*_ OCIEnv *envhp, OCIError *errhp, OCISvcCtx *svchp, dvoid *dschp, ub1 type, ub4 start, ub4 end _*/); static void chk_collection (/*_ OCIEnv *envhp, OCIError *errhp, OCISvcCtx *svchp, dvoid *dschp, sword is_array _*/); static void tst_desc_type(/*_ OCIEnv *envhp, OCIError *errhp, OCISvcCtx *svchp, text *objname _*/); static void checkerr(/*_ OCIError *errhp, sword status _*/); /* Prototype for main function */ int main(/*_ int argc, char *argv[] _*/); int tab; int glindex; #endif /* CDEMODSC */
/* NAME cdemolb2.c - Demonstrates writing and reading of CLOB/BLOB columns with stream mode and with callback functions. DESCRIPTION This program takes 2 input files (the first a text file and the second a binary file) and stores the files into CLOB, BLOB columns. On output, the program reads the newly populated CLOB/BLOB columns and writes them to the output files (txtfile1.log, binfile1.log, txtfile2.log, binfile2.log), where txtfile1.log -- created for stream reading CLOB contents to it binfile1.log -- created for stream reading BLOB contents to it txtfile2.log -- created for callback reading CLOB contents to it binfile2.log -- created for callback reading BLOB contents to it Sample usage: cdemolb2 cdemolb.dat giffile.dat cdemolb.dat -- a text file in the demo directory giffile.dat -- a gif file in the demo directory After successful execution of the program, the files, cdemolb.dat, txtfile1.log, and txtfile2.log should be identical. giffile.dat, binfile1.log, and binfile2.log should be identical. */ #include <stdio.h> #include <oci.h> static sb4 init_handles(/*_ void _*/); static sb4 log_on(/*_ void _*/); static sb4 setup_table(/*_ void _*/); static sb4 select_locator(/*_ int rowind _*/); static ub4 file_length(/*_ FILE *fp _*/); static sb4 test_file_to_lob(/*_ int rowind, char *tfname, char *bfname _*/); static void test_lob_to_file(/*_ int rowind _*/); static void stream_write_lob(/*_ int rowind, OCILobLocator *lobl, FILE *fp, ub4 filelen _*/); static void callback_write_lob(/*_ int rowind, OCILobLocator *lobl, FILE *fp, ub4 filelen _*/); static void stream_read_lob(/*_ int rowind, OCILobLocator *lobl, FILE *fp _*/); static void callback_read_lob(/*_ int rowind, OCILobLocator *lobl,FILE *fp _*/); static sb4 cbk_fill_buffer(/*_ dvoid *ctxp, dvoid *bufxp, ub4 *lenp, ub1 *piece _*/); static sb4 cbk_write_buffer(/*_ dvoid *ctxp, CONST dvoid *bufxp, ub4 lenp, ub1 piece _*/); static void logout(/*_ void _*/); static void drop_table(/*_ void _*/); static void report_error(/*_ void _*/); int main(/*_ int argc, char *argv[] _*/); #define TRUE 1 #define FALSE 0 #define MAXBUFLEN 5000 static OCIEnv *envhp; static OCIServer *srvhp; static OCISvcCtx *svchp; static OCIError *errhp; static OCISession *authp; static OCIStmt *stmthp; static OCILobLocator *clob, *blob; static OCIDefine *defnp1 = (OCIDefine *) 0, *defnp2 = (OCIDefine *) 0; static OCIBind *bndhp = (OCIBind *) 0; static FILE *fp1, *fp2; static ub4 txtfilelen = 0; static ub4 binfilelen = 0; static boolean istxtfile; static boolean tab_exists = FALSE; /*------------------------end of Inclusions-----------------------------*/ int main(argc, argv) int argc; char *argv[]; { int rowind; if (argc != 3) { (void) printf("Usage: %s txtfilename binfilename\n", argv[0]); return 0; } if (init_handles()) { (void) printf("FAILED: init_handles()\n"); return OCI_ERROR; } if (log_on()) { (void) printf("FAILED: log_on()\n"); return OCI_ERROR; } if (setup_table()) { (void) printf("FAILED: setup_table()\n"); logout(); return OCI_ERROR; } tab_exists = TRUE; for (rowind = 1; rowind <= 2; rowind++) { if (select_locator(rowind)) { (void) printf("FAILED: select_locator()\n"); logout(); return OCI_ERROR; } if (test_file_to_lob(rowind, argv[1], argv[2])) { (void) printf("FAILED: load files to lobs\n"); logout(); return OCI_ERROR; } test_lob_to_file(rowind); } logout(); return OCI_SUCCESS; } /* ----------------------------------------------------------------- */ /* initialize environment, allocate handles, etc. */ /* ----------------------------------------------------------------- */ sb4 init_handles() { if (OCIInitialize((ub4) OCI_DEFAULT, (dvoid *)0, (dvoid * (*)(dvoid *, size_t)) 0, (dvoid * (*)(dvoid *, dvoid *, size_t))0, (void (*)(dvoid *, dvoid *)) 0 )) { (void) printf("FAILED: OCIInitialize()\n"); return OCI_ERROR; } /* initialize environment handle */ if (OCIEnvInit((OCIEnv **) &envhp, (ub4) OCI_DEFAULT, (size_t) 0, (dvoid **) 0 )) { (void) printf("FAILED: OCIEnvInit()\n"); return OCI_ERROR; } if (OCIHandleAlloc((dvoid *) envhp, (dvoid **) &svchp, (ub4) OCI_HTYPE_SVCCTX, (size_t) 0, (dvoid **) 0)) { (void) printf("FAILED: OCIHandleAlloc()\n"); return OCI_ERROR; } if (OCIHandleAlloc((dvoid *) envhp, (dvoid **) &errhp, (ub4) OCI_HTYPE_ERROR, (size_t) 0, (dvoid **) 0)) { (void) printf("FAILED: OCIHandleAlloc()\n"); return OCI_ERROR; } if (OCIHandleAlloc((dvoid *) envhp, (dvoid **) &stmthp, (ub4) OCI_HTYPE_STMT, (size_t) 0, (dvoid **) 0)) { (void) printf("FAILED: OCIHandleAlloc()\n"); return OCI_ERROR; } if (OCIHandleAlloc((dvoid *) envhp, (dvoid **) &srvhp, (ub4) OCI_HTYPE_SERVER, (size_t) 0, (dvoid **) 0)) { (void) printf("FAILED: OCIHandleAlloc()\n"); return OCI_ERROR; } if (OCIHandleAlloc((dvoid *) envhp, (dvoid **) &authp, (ub4) OCI_HTYPE_SESSION, (size_t) 0, (dvoid **) 0)) { (void) printf("FAILED: OCIHandleAlloc()\n"); return OCI_ERROR; } if (OCIDescriptorAlloc((dvoid *) envhp, (dvoid **) &clob, (ub4)OCI_DTYPE_LOB, (size_t) 0, (dvoid **) 0)) { (void) printf("FAILED: OCIDescriptorAlloc()\n"); return OCI_ERROR; } /* allocate the lob locator variables */ if (OCIDescriptorAlloc((dvoid *) envhp, (dvoid **) &blob, (ub4)OCI_DTYPE_LOB, (size_t) 0, (dvoid **) 0)) { (void) printf("FAILED: OCIDescriptorAlloc()\n"); return OCI_ERROR; } return OCI_SUCCESS; } /* ----------------------------------------------------------------- */ /* attach to the server and log on as SCOTT/TIGER */ /* ----------------------------------------------------------------- */ sb4 log_on() { text *uid = (text *)"SCOTT"; text *pwd = (text *)"TIGER"; text *cstring = (text *) ""; /* attach to the server */ if (OCIServerAttach(srvhp, errhp, (text *) cstring, (sb4) strlen((char *)cstring), (ub4) OCI_DEFAULT)) { (void) printf("FAILED: OCIServerAttach()\n"); return OCI_ERROR; } if (OCIAttrSet((dvoid *) authp, (ub4) OCI_HTYPE_SESSION, (dvoid *) uid, (ub4) strlen((char *)uid), (ub4) OCI_ATTR_USERNAME, errhp)) { (void) printf("FAILED: OCIAttrSet()\n"); return OCI_ERROR; } if (OCIAttrSet((dvoid *) authp, (ub4) OCI_HTYPE_SESSION, (dvoid *) pwd, (ub4) strlen((char *)pwd), (ub4) OCI_ATTR_PASSWORD, errhp)) { (void) printf("FAILED: OCIAttrSet()\n"); return OCI_ERROR; } /* set the server attribute in the service context */ if (OCIAttrSet((dvoid *) svchp, (ub4) OCI_HTYPE_SVCCTX, (dvoid *) srvhp, (ub4) 0, (ub4) OCI_ATTR_SERVER, errhp)) { (void) printf("FAILED: OCIAttrSet()\n"); return OCI_ERROR; } /* log on */ if (OCISessionBegin(svchp, errhp, authp, (ub4) OCI_CRED_RDBMS, (ub4) OCI_DEFAULT)) { (void) printf("FAILED: OCISessionBegin()\n"); return OCI_ERROR; } /* set the session attribute in the service context */ if (OCIAttrSet((dvoid *) svchp, (ub4) OCI_HTYPE_SVCCTX, (dvoid *) authp, (ub4) 0, (ub4) OCI_ATTR_SESSION, errhp)) { (void) printf("FAILED: OCIAttrSet()\n"); return OCI_ERROR; } return OCI_SUCCESS; } /* ----------------------------------------------------------------- */ /* Create table FOO with CLOB, BLOB columns and insert one row. */ /* Both columns are empty lobs, not null lobs. */ /* ----------------------------------------------------------------- */ sb4 setup_table() { int colc; text *crtstmt = (text *) "CREATE TABLE FOO (A CLOB, B BLOB, C INTEGER)"; text *insstmt = (text *) "INSERT INTO FOO VALUES (EMPTY_CLOB(), EMPTY_BLOB(), :1)"; if (OCIStmtPrepare(stmthp, errhp, crtstmt, (ub4) strlen((char *) crtstmt), (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT)) { (void) printf("FAILED: OCIStmtPrepare() crtstmt\n"); return OCI_ERROR; } if (OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4) 0, (CONST OCISnapshot *) 0, (OCISnapshot *) 0, (ub4) OCI_DEFAULT)) { (void) printf("FAILED: OCIStmtExecute() crtstmt\n"); return OCI_ERROR; } if (OCIStmtPrepare(stmthp, errhp, insstmt, (ub4) strlen((char *) insstmt), (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT)) { (void) printf("FAILED: OCIStmtPrepare() insstmt\n"); return OCI_ERROR; } if (OCIBindByPos(stmthp, &bndhp, errhp, (ub4) 1, (dvoid *) &colc, (sb4) sizeof(colc), SQLT_INT, (dvoid *) 0, (ub2 *)0, (ub2 *)0, (ub4) 0, (ub4 *) 0, (ub4) OCI_DEFAULT)) { (void) printf("FAILED: OCIBindByPos()\n"); return OCI_ERROR; } for (colc = 1; colc <= 2; colc++) { if (OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4) 0, (CONST OCISnapshot *) 0, (OCISnapshot *) 0, (ub4) OCI_DEFAULT)) { (void) printf("FAILED: OCIStmtExecute() insstmt\n"); return OCI_ERROR; } } (void) OCITransCommit(svchp, errhp, (ub4)0); return OCI_SUCCESS; } /*---------------------------------------------------------------------*/ /* Select lob locators from the CLOB, BLOB columns. */ /* We need the 'FOR UPDATE' clause since we need to write to the lobs. */ /*---------------------------------------------------------------------*/ sb4 select_locator(int rowind) { int colc = rowind; text *sqlstmt = (text *)"SELECT A, B FROM FOO WHERE C = :1 FOR UPDATE"; if (OCIStmtPrepare(stmthp, errhp, sqlstmt, (ub4) strlen((char *)sqlstmt), (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT)) { (void) printf("FAILED: OCIStmtPrepare() sqlstmt\n"); return OCI_ERROR; } if (OCIBindByPos(stmthp, &bndhp, errhp, (ub4) 1, (dvoid *) &colc, (sb4) sizeof(colc), SQLT_INT, (dvoid *) 0, (ub2 *)0, (ub2 *)0, (ub4) 0, (ub4 *) 0, (ub4) OCI_DEFAULT)) { (void) printf("FAILED: OCIBindByPos()\n"); return OCI_ERROR; } if (OCIDefineByPos(stmthp, &defnp1, errhp, (ub4) 1, (dvoid *) &clob, (sb4) -1, (ub2) SQLT_CLOB, (dvoid *) 0, (ub2 *) 0, (ub2 *) 0, (ub4) OCI_DEFAULT) || OCIDefineByPos(stmthp, &defnp2, errhp, (ub4) 2, (dvoid *) &blob, (sb4) -1, (ub2) SQLT_BLOB, (dvoid *) 0, (ub2 *) 0, (ub2 *) 0, (ub4) OCI_DEFAULT)) { (void) printf("FAILED: OCIDefineByPos()\n"); return OCI_ERROR; } /* execute the select and fetch one row */ if (OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4) 0, (CONST OCISnapshot*) 0, (OCISnapshot*) 0, (ub4) OCI_DEFAULT)) { (void) printf("FAILED: OCIStmtExecute() sqlstmt\n"); return OCI_ERROR; } return OCI_SUCCESS; } /* ----------------------------------------------------------------- */ /* Read operating system files into local buffers and then write the */ /* buffers to lobs. */ /* ----------------------------------------------------------------- */ sb4 test_file_to_lob(int rowind, char *txtfile, char *binfile) { (void) printf("\n===> Testing loading files into lobs .....\n\n"); fp1 = fopen((const char *)txtfile, (const char *) "r"); fp2 = fopen((const char *)binfile, (const char *) "rb"); if ( !(fp1 && fp2)) { (void) printf("ERROR: Failed to open file(s).\n"); return -1; } txtfilelen = file_length(fp1); binfilelen = file_length(fp2); switch (rowind) { case 1: stream_write_lob(rowind, clob, fp1, txtfilelen); stream_write_lob(rowind, blob, fp2, binfilelen); break; case 2: istxtfile = TRUE; callback_write_lob(rowind, clob, fp1, txtfilelen); istxtfile = FALSE; callback_write_lob(rowind, blob, fp2, binfilelen); break; default: (void) printf("ERROR: Invalid row indicator.\n"); break; } (void) fclose(fp1); (void) fclose(fp2); return 0; } /* ----------------------------------------------------------------- */ /* get the length of the input file. */ /* ----------------------------------------------------------------- */ ub4 file_length(FILE *fp) { fseek(fp, 0, SEEK_END); return (ub4) (ftell(fp)); } /* ----------------------------------------------------------------- */ /* Read operating system files into local buffers and then write the */ /* buffers to lobs using stream mode. */ /* ----------------------------------------------------------------- */ void stream_write_lob(int rowind, OCILobLocator *lobl, FILE *fp, ub4 filelen) { ub4 offset = 1; ub4 loblen = 0; ub1 bufp[MAXBUFLEN]; ub4 amtp = filelen; ub1 piece; sword retval; int readval; ub4 len = 0; ub4 nbytes; ub4 remainder = filelen; (void) printf("--> To do streamed write lob, amount = %d\n", filelen); (void) OCILobGetLength(svchp, errhp, lobl, &loblen); (void) printf("Before stream write, LOB length = %d\n\n", loblen); (void) fseek(fp, 0, 0); if (filelen > MAXBUFLEN) nbytes = MAXBUFLEN; else nbytes = filelen; if (fread((void *)bufp, (size_t)nbytes, 1, fp) != 1) { (void) printf("ERROR: read file.\n"); return; } remainder -= nbytes; if (remainder == 0) /* exactly one piece in the file */ { (void) printf("Only one piece, no need for stream write.\n"); if (retval = OCILobWrite(svchp, errhp, lobl, &amtp, offset, (dvoid *) bufp, (ub4) nbytes, OCI_ONE_PIECE, (dvoid *)0, (sb4 (*)(dvoid *, dvoid *, ub4 *, ub1 *)) 0, (ub2) 0, (ub1) SQLCS_IMPLICIT) != OCI_SUCCESS) { (void) printf("ERROR: OCILobWrite(), retval = %d\n", retval); return; } } else /* more than one piece */ { if (OCILobWrite(svchp, errhp, lobl, &amtp, offset, (dvoid *) bufp, (ub4) MAXBUFLEN, OCI_FIRST_PIECE, (dvoid *)0, (sb4 (*)(dvoid *, dvoid *, ub4 *, ub1 *)) 0, (ub2) 0, (ub1) SQLCS_IMPLICIT) != OCI_NEED_DATA) { (void) printf("ERROR: OCILobWrite().\n"); return; } piece = OCI_NEXT_PIECE; do { if (remainder > MAXBUFLEN) nbytes = MAXBUFLEN; else { nbytes = remainder; piece = OCI_LAST_PIECE; } if (fread((void *)bufp, (size_t)nbytes, 1, fp) != 1) { (void) printf("ERROR: read file.\n"); piece = OCI_LAST_PIECE; } retval = OCILobWrite(svchp, errhp, lobl, &amtp, offset, (dvoid *) bufp, (ub4) nbytes, piece, (dvoid *)0, (sb4 (*)(dvoid *, dvoid *, ub4 *, ub1 *)) 0, (ub2) 0, (ub1) SQLCS_IMPLICIT); remainder -= nbytes; } while (retval == OCI_NEED_DATA && !feof(fp)); } if (retval != OCI_SUCCESS) { (void) printf("Error: stream writing LOB.\n"); return; } (void) OCILobGetLength(svchp, errhp, lobl, &loblen); (void) printf("After stream write, LOB length = %d\n\n", loblen); return; } /* ----------------------------------------------------------------- */ /* Read operating system files into local buffers and then write the */ /* buffers to lobs using callback function. */ /* ----------------------------------------------------------------- */ void callback_write_lob(int rowind, OCILobLocator *lobl, FILE *fp, ub4 filelen) { ub4 offset = 1; ub4 loblen = 0; ub1 bufp[MAXBUFLEN]; ub4 amtp = filelen; ub4 nbytes; sword retval; (void) printf("--> To do callback write lob, amount = %d\n", filelen); (void) OCILobGetLength(svchp, errhp, lobl, &loblen); (void) printf("Before callback write, LOB length = %d\n\n", loblen); (void) fseek(fp, 0, 0); if (filelen > MAXBUFLEN) nbytes = MAXBUFLEN; else nbytes = filelen; if (fread((void *)bufp, (size_t)nbytes, 1, fp) != 1) { (void) printf("ERROR: read file.\n"); return; } if (filelen < MAXBUFLEN) /* exactly one piece in the file */ { (void) printf("Only one piece, no need for callback write.\n"); if (retval = OCILobWrite(svchp, errhp, lobl, &amtp, offset, (dvoid *) bufp, (ub4) nbytes, OCI_ONE_PIECE, (dvoid *)0, (sb4 (*)(dvoid *, dvoid *, ub4 *, ub1 *)) 0, (ub2) 0, (ub1) SQLCS_IMPLICIT) != OCI_SUCCESS) { (void) printf("ERROR: OCILobWrite().\n"); return; } } else /* more than one piece */ { if (retval = OCILobWrite(svchp, errhp, lobl, &amtp, offset, (dvoid *)bufp, (ub4)nbytes, OCI_FIRST_PIECE, (dvoid *)0, cbk_fill_buffer, (ub2) 0, (ub1) SQLCS_IMPLICIT)) { (void) printf("ERROR: OCILobWrite().\n"); report_error(); return; } } (void) OCILobGetLength(svchp, errhp, lobl, &loblen); (void) printf("After callback write, LOB length = %d\n\n", loblen); return; } /* ----------------------------------------------------------------- */ /* callback function to read the file into buffer. */ /* ----------------------------------------------------------------- */ sb4 cbk_fill_buffer(ctxp, bufxp, lenp, piece) dvoid *ctxp; dvoid *bufxp; ub4 *lenp; ub1 *piece; { FILE *fp = (istxtfile ? fp1 : fp2); ub4 filelen = (istxtfile ? txtfilelen : binfilelen); ub4 nbytes; static ub4 len = MAXBUFLEN; /* because 1st piece has been written */ if ((filelen - len) > MAXBUFLEN) nbytes = MAXBUFLEN; else nbytes = filelen - len; *lenp = nbytes; if (fread((void *)bufxp, (size_t)nbytes, 1, fp) != 1) { (void) printf("ERROR: read file. Abort callback fill buffer\n"); *piece = OCI_LAST_PIECE; len = MAXBUFLEN; /* reset it for the next callback_write_lob() */ return OCI_CONTINUE; } len += nbytes; if (len == filelen) /* entire file has been read */ { *piece = OCI_LAST_PIECE; len = MAXBUFLEN; /* reset it for the next callback_write_lob() */ } else *piece = OCI_NEXT_PIECE; return OCI_CONTINUE; } /* ----------------------------------------------------------------- */ /* Read lobs into local buffers and then write them to operating */ /* system files. */ /* ----------------------------------------------------------------- */ void test_lob_to_file(int rowind) { ub4 offset = 1; ub4 loblen = 0; ub1 bufp[MAXBUFLEN]; ub4 amtp = MAXBUFLEN; text txtfilename[20], binfilename[20]; (void) sprintf((char *) txtfilename, (char *)"txtfile%d.log", rowind); (void) sprintf((char *) binfilename, (char *)"binfile%d.log", rowind); (void) printf("\n===> Testing writing lobs to files .....\n\n"); fp1 = fopen((char *)txtfilename, (const char *) "w"); fp2 = fopen((char *)binfilename, (const char *) "wb"); if ( !(fp1 && fp2)) { (void) printf("ERROR: Failed to open file(s).\n"); return; } switch (rowind) { case 1: stream_read_lob(rowind, clob, fp1); stream_read_lob(rowind, blob, fp2); break; case 2: istxtfile = TRUE; callback_read_lob(rowind, clob, fp1); istxtfile = FALSE; callback_read_lob(rowind, blob, fp2); break; default: (void) printf("ERROR: Invalid row indicator.\n"); break; } (void) fclose(fp1); (void) fclose(fp2); return; } /* ----------------------------------------------------------------- */ /* Read lobs using stream mode into local buffers and then write */ /* them to operating system files. */ /* ----------------------------------------------------------------- */ void stream_read_lob(int rowind, OCILobLocator *lobl, FILE *fp) { ub4 offset = 1; ub4 loblen = 0; ub1 bufp[MAXBUFLEN]; ub4 amtp = 4096000000; sword retval; ub4 piece = 0; ub4 remainder; /* the number of bytes for the last piece */ (void) OCILobGetLength(svchp, errhp, lobl, &loblen); /*amtp = loblen;*/ (void) printf("--> To stream read LOB, loblen = %d.\n", loblen); memset(bufp, '\0', MAXBUFLEN); retval = OCILobRead(svchp, errhp, lobl, &amtp, offset, (dvoid *) bufp, (loblen < MAXBUFLEN ? loblen : MAXBUFLEN), (dvoid *)0, (sb4 (*)(dvoid *, const dvoid *, ub4, ub1)) 0, (ub2) 0, (ub1) SQLCS_IMPLICIT); (void) printf(" amtp passed in was 4gb, amtp returned is %u\n", amtp); switch (retval) { case OCI_SUCCESS: /* only one piece */ (void) printf("stream read %d th piece\n", ++piece); (void) fwrite((void *)bufp, (size_t)loblen, 1, fp); break; case OCI_ERROR: report_error(); break; case OCI_NEED_DATA: /* there are 2 or more pieces */ remainder = loblen; (void) fwrite((void *)bufp, MAXBUFLEN, 1, fp); /* full buffer to write */ do { memset(bufp, '\0', MAXBUFLEN); /*amtp = 0;*/ remainder -= MAXBUFLEN; retval = OCILobRead(svchp, errhp, lobl, &amtp, offset, (dvoid *) bufp, (ub4) MAXBUFLEN, (dvoid *)0, (sb4 (*)(dvoid *, const dvoid *, ub4, ub1)) 0, (ub2) 0, (ub1) SQLCS_IMPLICIT); /* the amount read returned is undefined for FIRST, NEXT pieces */ (void) printf("stream read %d th piece, amtp = %u\n", ++piece, amtp); if (remainder < MAXBUFLEN) /* last piece not a full buffer piece */ (void) fwrite((void *)bufp, (size_t)remainder, 1, fp); else (void) fwrite((void *)bufp, MAXBUFLEN, 1, fp); } while (retval == OCI_NEED_DATA); break; default: (void) printf("Unexpected ERROR: OCILobRead() LOB.\n"); break; } return; } /* ----------------------------------------------------------------- */ /* Read lobs using callback function into local buffers and */ /* then write them to operating system files. */ /* ----------------------------------------------------------------- */ void callback_read_lob(int rowind, OCILobLocator *lobl, FILE *fp) { ub4 offset = 1; ub4 loblen = 0; ub1 bufp[MAXBUFLEN]; ub4 amtp = 4096000000; sword retval; (void) OCILobGetLength(svchp, errhp, lobl, &loblen); (void) printf("--> To callback read LOB, loblen = %d.\n", loblen); if (retval = OCILobRead(svchp, errhp, lobl, &amtp, offset, (dvoid *) bufp, (ub4) MAXBUFLEN, (dvoid *) bufp, cbk_write_buffer, (ub2) 0, (ub1) SQLCS_IMPLICIT)) { (void) printf("ERROR: OCILobRead() LOB.\n"); report_error(); } return; } /* ----------------------------------------------------------------- */ /* callback function to write buffer to the file. */ /* ----------------------------------------------------------------- */ sb4 cbk_write_buffer(ctxp, bufxp, lenp, piece) dvoid *ctxp; CONST dvoid *bufxp; ub4 lenp; ub1 piece; { static ub4 piece_count = 0; FILE *fp = (istxtfile ? fp1 : fp2); piece_count++; switch (piece) { case OCI_LAST_PIECE: (void) fwrite((void *)bufxp, (size_t)lenp, 1, fp); (void) printf("callback read the %d th piece\n\n", piece_count); piece_count = 0; return OCI_CONTINUE; case OCI_FIRST_PIECE: case OCI_NEXT_PIECE: (void) fwrite((void *)bufxp, (size_t)lenp, 1, fp); break; default: (void) printf("callback read error: unkown piece = %d.\n", piece); return OCI_ERROR; } (void) printf("callback read the %d th piece\n", piece_count); return OCI_CONTINUE; } /*-------------------------------------------------------------------*/ /* Drop table FOO before logging off from the server. */ /*-------------------------------------------------------------------*/ void drop_table() { text *sqlstmt = (text *) "DROP TABLE FOO"; if (OCIStmtPrepare(stmthp, errhp, sqlstmt, (ub4) strlen((char *) sqlstmt), (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT)) { (void) printf("FAILED: OCIStmtPrepare() sqlstmt\n"); return; } if (OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4) 0, (CONST OCISnapshot *) 0, (OCISnapshot *) 0, (ub4) OCI_DEFAULT)) (void) printf("FAILED: OCIStmtExecute() sqlstmt\n"); return; } /*-------------------------------------------------------------------*/ /* Logoff and disconnect from the server. Free handles. */ /*-------------------------------------------------------------------*/ void logout() { if (tab_exists) drop_table(); (void) OCISessionEnd(svchp, errhp, authp, (ub4) 0); (void) OCIServerDetach(srvhp, errhp, (ub4) OCI_DEFAULT); (void) printf("Logged off and detached from server.\n"); (void) OCIHandleFree((dvoid *) srvhp, (ub4) OCI_HTYPE_SERVER); (void) OCIHandleFree((dvoid *) svchp, (ub4) OCI_HTYPE_SVCCTX); (void) OCIHandleFree((dvoid *) errhp, (ub4) OCI_HTYPE_ERROR); (void) OCIHandleFree((dvoid *) authp, (ub4) OCI_HTYPE_SESSION); (void) OCIDescriptorFree((dvoid *) clob, (ub4) OCI_DTYPE_LOB); (void) OCIDescriptorFree((dvoid *) blob, (ub4) OCI_DTYPE_LOB); (void) OCIHandleFree((dvoid *) stmthp, (ub4) OCI_HTYPE_STMT); (void) printf("All handles freed\n"); return; } /* ----------------------------------------------------------------- */ /* retrieve error message and print it out. */ /* ----------------------------------------------------------------- */ void report_error() { text msgbuf[512]; sb4 errcode = 0; (void) OCIErrorGet((dvoid *) errhp, (ub4) 1, (text *) NULL, &errcode, msgbuf, (ub4) sizeof(msgbuf), (ub4) OCI_HTYPE_ERROR); (void) printf("ERROR CODE = %d\n", errcode); (void) printf("%.*s\n", 512, msgbuf); return; }
/* NAME cdemolbs.c - Demonstrates reading and writing to LOBs through the LOB Buffering Subsystem. DESCRIPTION This program reads from an input binary/text file, writing into an initialized B/CLOB column in buffered mode. It then reads in buffered mode from the B/CLOB column and populates an output file. After building the executable (assume it is called cdemolbs), run program as follows: cdemolbs src.txt src.bin dst.txt dst.bin where src.txt and src.bin are text and binary files of size <= 512Kbytes. IMPORTANT: . This program works only for single-byte CLOBs. . Before running this program, ensure that the database is started up and a table FOO does not exist in the SCOTT/ TIGER sample account. */ #include <stdio.h> #include <string.h> #include <oci.h> /---------- Public Constants and Variables ----------------------*/ /* Constants */ #define TRUE 1 #define FALSE 0 #define MAXBUFLEN 32768 #define MAXLBSLEN 524288 /* OCI Handles */ static OCIEnv *envhp; static OCIServer *srvhp; static OCISvcCtx *svchp; static OCIError *errhp; static OCISession *authp; static OCIStmt *stmthp; static OCILobLocator *clob, *blob; static OCIDefine *defnp1 = (OCIDefine *) 0, *defnp2 = (OCIDefine *) 0; static OCIBind *bndhp = (OCIBind *) 0; /* Misellaneous */ static FILE *fp1, *fp2; static ub4 txtfilelen = 0; static ub4 binfilelen = 0; static boolean istxtfile; static boolean tab_exists = FALSE; /*---------- Public functions - Specification --------------------*/ int main (/*_ int argc, char *argv[] _*/); static sb4 init_handles (/*_ void _*/); static sb4 init_table (/*_ void _*/); static sb4 log_on (/*_ void _*/); static void log_off (/*_ void _*/); static sb4 write_lobs (/*_ int rowind, char *txtfile, char *binfile _*/); static sb4 read_lobs (/*_ int rowind, char *txtfile, char *binfile _*/); /*----------- Private functions - Specification -------------------*/ static sb4 select_clob (/*_ int rowind _*/); static sb4 select_blob (/*_ int rowind _*/); static sb4 select_lobs (/*_ int rowind _*/); static sb4 buf_write_lob (/*_ int rowind, OCILobLocator *locator, FILE *fp, ub4 filelen _*/); static sb4 buf_read_lob (/*_ int rowind, OCILobLocator *locator, FILE *fp _*/); static void drop_table (/*_ void _*/); static void report_error (/*_ void _*/); static ub4 file_length (/*_ FILE *fp _*/); /*----------------- Public functions ----------------------------*/ /*----------------------- main -----------------------------------*/ /* main driver */ int main(argc, argv) int argc; char *argv[]; { int rowind; /* validate input arguments */ if (argc != 5) { (void) printf("Usage: %s srctxtfile srcbinfile desttxtfile destbinfile\n", argv[0]); return 0; } /* initialize OCI handles */ if (init_handles()) { (void) printf("FAILED: init_handles()\n"); return OCI_ERROR; } /* log on to server */ if (log_on()) { (void) printf("FAILED: log_on()\n"); return OCI_ERROR; } /* init demo table */ if (init_table()) { (void) printf("FAILED: init_table()\n"); log_off(); return OCI_ERROR; } /* write to LOBs in row 1 through the buffering subsystem, reading from src files*/ rowind = 1; if (write_lobs(rowind, argv[1], argv[2])) { (void) printf("FAILED: write files to lobs\n"); log_off(); return OCI_ERROR; } /* read from LOBs in row 1 through buffering subsystem, writing to dest files */ rowind = 1; if (read_lobs(rowind, argv[3], argv[4])) { (void) printf("FAILED: write lobs to files\n"); log_off(); return OCI_ERROR; } /* clean up and log off from server */ log_off(); return OCI_SUCCESS; } /*------------------- init_handles --------------------------------*/ /* initialize environment, and allocate all handles */ sb4 init_handles() { if (OCIInitialize((ub4) OCI_DEFAULT, (dvoid *)0, (dvoid * (*)(dvoid *, size_t)) 0, (dvoid * (*)(dvoid *, dvoid *, size_t))0, (void (*)(dvoid *, dvoid *)) 0 )) { (void) printf("FAILED: OCIInitialize()\n"); return OCI_ERROR; } /* initialize environment handle */ if (OCIEnvInit((OCIEnv **) &envhp, (ub4) OCI_DEFAULT, (size_t) 0, (dvoid **) 0 )) { (void) printf("FAILED: OCIEnvInit()\n"); return OCI_ERROR; } /* initialize service context */ if (OCIHandleAlloc((dvoid *) envhp, (dvoid **) &svchp, (ub4) OCI_HTYPE_SVCCTX, (size_t) 0, (dvoid **) 0)) { (void) printf("FAILED: OCIHandleAlloc()\n"); return OCI_ERROR; } /* initialize error handle */ if (OCIHandleAlloc((dvoid *) envhp, (dvoid **) &errhp, (ub4) OCI_HTYPE_ERROR, (size_t) 0, (dvoid **) 0)) { (void) printf("FAILED: OCIHandleAlloc()\n"); return OCI_ERROR; } /* initialize statement handle */ if (OCIHandleAlloc((dvoid *) envhp, (dvoid **) &stmthp, (ub4) OCI_HTYPE_STMT, (size_t) 0, (dvoid **) 0)) { (void) printf("FAILED: OCIHandleAlloc()\n"); return OCI_ERROR; } /* initialize server handle */ if (OCIHandleAlloc((dvoid *) envhp, (dvoid **) &srvhp, (ub4) OCI_HTYPE_SERVER, (size_t) 0, (dvoid **) 0)) { (void) printf("FAILED: OCIHandleAlloc()\n"); return OCI_ERROR; } /* initialize session/authentication handle */ if (OCIHandleAlloc((dvoid *) envhp, (dvoid **) &authp, (ub4) OCI_HTYPE_SESSION, (size_t) 0, (dvoid **) 0)) { (void) printf("FAILED: OCIHandleAlloc()\n"); return OCI_ERROR; } /* allocate the lob locator variables */ if (OCIDescriptorAlloc((dvoid *) envhp, (dvoid **) &clob, (ub4)OCI_DTYPE_LOB, (size_t) 0, (dvoid **) 0)) { (void) printf("FAILED: OCIDescriptorAlloc()\n"); return OCI_ERROR; } if (OCIDescriptorAlloc((dvoid *) envhp, (dvoid **) &blob, (ub4)OCI_DTYPE_LOB, (size_t) 0, (dvoid **) 0)) { (void) printf("FAILED: OCIDescriptorAlloc()\n"); return OCI_ERROR; } return OCI_SUCCESS; } /*---------------------- init_table --------------------------------*/ /* create table FOO with initialized CLOB, BLOB columns, and insert two rows */ sb4 init_table() { int colc; text *crtstmt = (text *) "CREATE TABLE FOO (C1 CLOB, C2 BLOB, C3 INTEGER)"; text *insstmt = (text *) "INSERT INTO FOO VALUES (EMPTY_CLOB(), EMPTY_BLOB(), :1)"; /* prepare create statement */ if (OCIStmtPrepare(stmthp, errhp, crtstmt, (ub4) strlen((char *) crtstmt), (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT)) { (void) printf("FAILED: OCIStmtPrepare() crtstmt\n"); return OCI_ERROR; } /* execute create statement */ if (OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4) 0, (CONST OCISnapshot *) 0, (OCISnapshot *) 0, (ub4) OCI_DEFAULT)) { (void) printf("FAILED: OCIStmtExecute() crtstmt\n"); return OCI_ERROR; } /* prepare insert statement */ if (OCIStmtPrepare(stmthp, errhp, insstmt, (ub4) strlen((char *) insstmt), (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT)) { (void) printf("FAILED: OCIStmtPrepare() insstmt\n"); return OCI_ERROR; } /* associate variable colc with bind placeholder #1 in the SQL statement */ if (OCIBindByPos(stmthp, &bndhp, errhp, (ub4) 1, (dvoid *) &colc, (sb4) sizeof(colc), SQLT_INT, (dvoid *) 0, (ub2 *)0, (ub2 *)0, (ub4) 0, (ub4 *) 0, (ub4) OCI_DEFAULT)) { (void) printf("FAILED: OCIBindByPos()\n"); return OCI_ERROR; } /* insert two rows */ for (colc = 1; colc <= 2; colc++) { if (OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4) 0, (CONST OCISnapshot *) 0, (OCISnapshot *) 0, (ub4) OCI_DEFAULT)) { (void) printf("FAILED: OCIStmtExecute() insstmt\n"); return OCI_ERROR; } } /* commit the Xn */ (void) OCITransCommit(svchp, errhp, (ub4)0); /* set flag to be used by log_off() to drop the table */ tab_exists = TRUE; return OCI_SUCCESS; } /*----------------------- log_on ---------------------------------*/ /* attach to the server and log on as SCOTT/TIGER */ sb4 log_on() { text *uid = (text *)"SCOTT"; text *pwd = (text *)"TIGER"; text *cstring = (text *)"inst1_alias"; /* attach to the server */ if (OCIServerAttach(srvhp, errhp, (text *) cstring, (sb4) strlen((char *)cstring), (ub4) OCI_DEFAULT)) { (void) printf("FAILED: OCIServerAttach()\n"); return OCI_ERROR; } /* set username and password attributes of the server handle */ if (OCIAttrSet((dvoid *) authp, (ub4) OCI_HTYPE_SESSION, (dvoid *) uid, (ub4) strlen((char *)uid), (ub4) OCI_ATTR_USERNAME, errhp)) { (void) printf("FAILED: OCIAttrSet()\n"); return OCI_ERROR; } if (OCIAttrSet((dvoid *) authp, (ub4) OCI_HTYPE_SESSION, (dvoid *) pwd, (ub4) strlen((char *)pwd), (ub4) OCI_ATTR_PASSWORD, errhp)) { (void) printf("FAILED: OCIAttrSet()\n"); return OCI_ERROR; } /* set the server attribute in the service context */ if (OCIAttrSet((dvoid *) svchp, (ub4) OCI_HTYPE_SVCCTX, (dvoid *) srvhp, (ub4) 0, (ub4) OCI_ATTR_SERVER, errhp)) { (void) printf("FAILED: OCIAttrSet()\n"); return OCI_ERROR; } /* log on */ if (OCISessionBegin(svchp, errhp, authp, (ub4) OCI_CRED_RDBMS, (ub4) OCI_DEFAULT)) { (void) printf("FAILED: OCISessionBegin()\n"); return OCI_ERROR; } /* set the session attribute in the service context */ if (OCIAttrSet((dvoid *) svchp, (ub4) OCI_HTYPE_SVCCTX, (dvoid *) authp, (ub4) 0, (ub4) OCI_ATTR_SESSION, errhp)) { (void) printf("FAILED: OCIAttrSet()\n"); return OCI_ERROR; } return OCI_SUCCESS; } /*-------------------------- logoff -------------------------------*/ /* Logoff and disconnect from the server. Free handles */ void log_off() { if (tab_exists) drop_table(); (void) OCISessionEnd(svchp, errhp, authp, (ub4) 0); (void) OCIServerDetach(srvhp, errhp, (ub4) OCI_DEFAULT); (void) printf("Logged off and detached from server.\n"); (void) OCIHandleFree((dvoid *) srvhp, (ub4) OCI_HTYPE_SERVER); (void) OCIHandleFree((dvoid *) svchp, (ub4) OCI_HTYPE_SVCCTX); (void) OCIHandleFree((dvoid *) errhp, (ub4) OCI_HTYPE_ERROR); (void) OCIHandleFree((dvoid *) authp, (ub4) OCI_HTYPE_SESSION); (void) OCIDescriptorFree((dvoid *) clob, (ub4) OCI_DTYPE_LOB); (void) OCIDescriptorFree((dvoid *) blob, (ub4) OCI_DTYPE_LOB); (void) OCIHandleFree((dvoid *) stmthp, (ub4) OCI_HTYPE_STMT); (void) printf("All handles freed\n"); return; } /*-------------------- write_lobs -------------------------------*/ /* write from files to LOBs */ sb4 write_lobs (rowind, txtfile, binfile) int rowind; char *txtfile; char *binfile; { ub4 loblen = 0; text *svptstmt = (text *)"SAVEPOINT cdemolbs_svpt"; text *rlbkstmt = (text *)"ROLLBACK TO SAVEPOINT cdemolbs_svpt"; ub4 txtfilelen = 0; ub4 binfilelen = 0; /* validate row indicator */ if (!rowind || (rowind > 2)) { (void) printf("ERROR: Invalid row indicator.\n"); return OCI_ERROR; } /* open source files */ fp1 = fopen((CONST char *)txtfile, (CONST char *) "r"); fp2 = fopen((CONST char *)binfile, (CONST char *) "r"); if (!(fp1 && fp2)) { (void) printf("ERROR: Failed to open file(s).\n"); return -1; } if ((txtfilelen = file_length(fp1)) > MAXLBSLEN) { (void) printf("ERROR: %s - length > 512Kbytes", txtfile); return -1; } if ((binfilelen = file_length(fp2)) > MAXLBSLEN) { (void) printf("ERROR: %s - length > 512Kbytes", binfile); return -1; } /* reset file pointers to start of file */ (void) fseek(fp1, 0, 0); (void) fseek(fp2, 0, 0); /* set savepoint for Xn before commencing buffered mode operations */ if (OCIStmtPrepare(stmthp, errhp, svptstmt, (ub4) strlen((char *)svptstmt), (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT)) { (void) printf("FAILED: OCIStmtPrepare() svptstmt\n"); return OCI_ERROR; } if (OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4) 0, (CONST OCISnapshot*) 0, (OCISnapshot*) 0, (ub4) OCI_DEFAULT)) { (void) printf("FAILED: OCIStmtExecute() svptstmt\n"); report_error(); return OCI_ERROR; } (void) printf("\n===> Writing CLOB from txtfile in buffered mode.....\n\n"); /* fetch the CLOB's locator from the table for update */ if (select_clob(rowind)) { (void) printf("FAILED: select_clob()\n"); log_off(); return OCI_ERROR; } /* report LOB length before buffered write begins */ (void) OCILobGetLength(svchp, errhp, clob, &loblen); (void) printf("Before buffered write, CLOB length = %d\n\n", loblen); /* enable the CLOB locator for buffering operations */ if (OCILobEnableBuffering(svchp, errhp, clob)) { (void) printf("FAILED: OCILobEnableBuffering() CLOB\n"); return OCI_ERROR; } /* write the text file contents into CLOB through the buffering subsystem */ if (buf_write_lob(rowind, clob, fp1, txtfilelen) > 0) { /* if buffered write operation failed, rollback Xn to savepoint & exit */ if (OCIStmtPrepare(stmthp, errhp, rlbkstmt, (ub4) strlen((char *)rlbkstmt), (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT)) { (void) printf("FAILED: OCIStmtPrepare() rlbkstmt\n"); return OCI_ERROR; } if (OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4) 0, (CONST OCISnapshot*) 0, (OCISnapshot*) 0, (ub4) OCI_DEFAULT)) { (void) printf("FAILED: OCIStmtExecute() rlbkstmt\n"); report_error(); return OCI_ERROR; } (void) printf("FAILED: buf_write_lob() CLOB\n"); return OCI_ERROR; } /* commit the Xn if the CLOB's buffer was flushed successfully */ (void) OCITransCommit(svchp, errhp, (ub4)0); /* disable CLOB locator from buffering */ if (OCILobDisableBuffering(svchp, errhp, clob)) { (void) printf("FAILED: OCILobDisableBuffering() CLOB\n"); return OCI_ERROR; } (void) printf("\n===> Writing BLOB from binfile in buffered mode.....\n\n"); /* fetch the BLOB's locator from the table for update */ if (select_blob(rowind)) { (void) printf("FAILED: select_blob()\n"); log_off(); return OCI_ERROR; } /* report LOB length before buffered write begins */ (void) OCILobGetLength(svchp, errhp, blob, &loblen); (void) printf("Before buffered write, BLOB length = %d\n\n", loblen); /* enable the BLOB locator for buffering operations */ if (OCILobEnableBuffering(svchp, errhp, blob)) { (void) printf("FAILED: OCILobEnableBuffering() BLOB\n"); return OCI_ERROR; } /* write the bin file contents into BLOB through the buffering subsystem */ if (buf_write_lob(rowind, blob, fp2, binfilelen) > 0) { /* if buffered write operation failed, rollback Xn to savepoint & exit */ if (OCIStmtPrepare(stmthp, errhp, rlbkstmt, (ub4) strlen((char *)rlbkstmt), (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT)) { (void) printf("FAILED: OCIStmtPrepare() rlbkstmt\n"); return OCI_ERROR; } if (OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4) 0, (CONST OCISnapshot*) 0, (OCISnapshot*) 0, (ub4) OCI_DEFAULT)) { (void) printf("FAILED: OCIStmtExecute() rlbkstmt\n"); report_error(); return OCI_ERROR; } (void) printf("FAILED: buf_write_lob() BLOB\n"); return OCI_ERROR; } /* commit the Xn if the BLOB's buffer was flushed successfully */ (void) OCITransCommit(svchp, errhp, (ub4)0); /* disable BLOB locator from buffering */ if (OCILobDisableBuffering(svchp, errhp, blob)) { (void) printf("FAILED: OCILobDisableBuffering() BLOB\n"); return OCI_ERROR; } /* close input files */ (void) fclose(fp1); (void) fclose(fp2); return OCI_SUCCESS; } /*--------------------- read_lobs --------------------------------*/ /* read from LOBs into files */ sb4 read_lobs (rowind, txtfile, binfile) int rowind; char *txtfile; char *binfile; { ub4 loblen = 0; text *svptstmt = (text *)"SAVEPOINT cdemolbs_svpt"; text *rlbkstmt = (text *)"ROLLBACK TO SAVEPOINT cdemolbs_svpt"; if (!rowind || (rowind > 2)) { (void) printf("ERROR: Invalid row indicator.\n"); return -1; } /* open destination files */ fp1 = fopen((CONST char *)txtfile, (CONST char *) "w"); fp2 = fopen((CONST char *)binfile, (CONST char *) "w"); if (!(fp1 && fp2)) { (void) printf("ERROR: Failed to open file(s).\n"); return -1; } /* reset file pointers to start of file */ (void) fseek(fp1, 0, 0); (void) fseek(fp2, 0, 0); /* fetch the BLOB's locator from the table for reads */ if (select_lobs(rowind)) { (void) printf("FAILED: select_lobs()\n"); log_off(); return OCI_ERROR; } /* report CLOB length before buffered read begins */ (void) OCILobGetLength(svchp, errhp, clob, &loblen); (void) printf("Before buffered read, CLOB length = %d\n\n", loblen); /* report BLOB length before buffered read begins */ (void) OCILobGetLength(svchp, errhp, blob, &loblen); (void) printf("Before buffered read, BLOB length = %d\n\n", loblen); /* set savepoint for Xn before commencing buffered mode operations */ if (OCIStmtPrepare(stmthp, errhp, svptstmt, (ub4) strlen((char *)svptstmt), (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT)) { (void) printf("FAILED: OCIStmtPrepare() svptstmt\n"); return OCI_ERROR; } if (OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4) 0, (CONST OCISnapshot*) 0, (OCISnapshot*) 0, (ub4) OCI_DEFAULT)) { (void) printf("FAILED: OCIStmtExecute() svptstmt\n"); report_error(); return OCI_ERROR; } /* enable the locators for buffering operations */ if (OCILobEnableBuffering(svchp, errhp, clob)) { (void) printf("FAILED: OCILobEnableBuffering() CLOB\n"); return OCI_ERROR; } if (OCILobEnableBuffering(svchp, errhp, blob)) { (void) printf("FAILED: OCILobEnableBuffering() BLOB\n"); return OCI_ERROR; } (void) printf("\n===> Reading CLOB into dst.txt in buffered mode...\n\n"); /* read the CLOB into buffer and write the contents to a text file */ if (buf_read_lob(rowind, clob, fp1) > 0) { /* if buffered read operation failed, rollback Xn to savepoint & exit */ if (OCIStmtPrepare(stmthp, errhp, rlbkstmt, (ub4) strlen((char *)rlbkstmt), (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT)) { (void) printf("FAILED: OCIStmtPrepare() rlbkstmt\n"); return OCI_ERROR; } if (OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4) 0, (CONST OCISnapshot*) 0, (OCISnapshot*) 0, (ub4) OCI_DEFAULT)) { (void) printf("FAILED: OCIStmtExecute() rlbkstmt\n"); report_error(); return OCI_ERROR; } (void) printf("FAILED: buf_read_lob() CLOB\n"); return OCI_ERROR; } (void) printf("\n===> Reading BLOB into dst.bin in buffered mode...\n\n"); /* read the BLOB into buffer and write the contents to a binary file */ if (buf_read_lob(rowind, blob, fp2) > 0) { /* if buffered read operation failed, rollback Xn to savepoint & exit */ if (OCIStmtPrepare(stmthp, errhp, rlbkstmt, (ub4) strlen((char *)rlbkstmt), (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT)) { (void) printf("FAILED: OCIStmtPrepare() rlbkstmt\n"); return OCI_ERROR; } if (OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4) 0, (CONST OCISnapshot*) 0, (OCISnapshot*) 0, (ub4) OCI_DEFAULT)) { (void) printf("FAILED: OCIStmtExecute() rlbkstmt\n"); report_error(); return OCI_ERROR; } (void) printf("FAILED: buf_read_clob()\n"); return OCI_ERROR; } /* commit the Xn if buffered reads went off successfully */ (void) OCITransCommit(svchp, errhp, (ub4)0); /* disable locator for buffering */ if (OCILobDisableBuffering(svchp, errhp, clob)) { (void) printf("FAILED: OCILobDisableBuffering() \n"); return OCI_ERROR; } if (OCILobDisableBuffering(svchp, errhp, blob)) { (void) printf("FAILED: OCILobDisableBuffering() \n"); return OCI_ERROR; } /* close output files */ (void) fclose(fp1); (void) fclose(fp2); return OCI_SUCCESS; } /*----------------- Public functions ----------------------------*/ /*------------------ select_clob -------------------------------*/ /* select locator from the CLOB column */ sb4 select_clob(rowind) int rowind; { int colc = rowind; text *sqlstmt = (text *)"SELECT C1 FROM FOO WHERE C3 = :1 FOR UPDATE"; /* we need the 'FOR UPDATE' clause since we need to write to the lobs */ /* prepare select statement */ if (OCIStmtPrepare(stmthp, errhp, sqlstmt, (ub4) strlen((char *)sqlstmt), (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT)) { (void) printf("FAILED: OCIStmtPrepare() sqlstmt\n"); return OCI_ERROR; } /* associate variable colc with bind placeholder #1 in the SQL statement */ if (OCIBindByPos(stmthp, &bndhp, errhp, (ub4) 1, (dvoid *) &colc, (sb4) sizeof(colc), SQLT_INT, (dvoid *) 0, (ub2 *)0, (ub2 *)0, (ub4) 0, (ub4 *) 0, (ub4) OCI_DEFAULT)) { (void) printf("FAILED: OCIBindByPos()\n"); return OCI_ERROR; } /* associate clob var with its define handle */ if (OCIDefineByPos(stmthp, &defnp1, errhp, (ub4) 1, (dvoid *) &clob, (sb4) -1, (ub2) SQLT_CLOB, (dvoid *) 0, (ub2 *) 0, (ub2 *) 0, (ub4) OCI_DEFAULT)) { (void) printf("FAILED: OCIDefineByPos() CLOB\n"); return OCI_ERROR; } /* execute the select and fetch one row */ if (OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4) 0, (CONST OCISnapshot*) 0, (OCISnapshot*) 0, (ub4) OCI_DEFAULT)) { (void) printf("FAILED: OCIStmtExecute() sqlstmt\n"); return OCI_ERROR; } return OCI_SUCCESS; } /*------------------------------- select_blob ---------------------------*/ /* select locator from the BLOB column */ sb4 select_blob(rowind) int rowind; { int colc = rowind; text *sqlstmt = (text *)"SELECT C2 FROM FOO WHERE C3 = :1 FOR UPDATE"; /* we need the 'FOR UPDATE' clause since we need to write to the lobs */ /* prepare select statement */ if (OCIStmtPrepare(stmthp, errhp, sqlstmt, (ub4) strlen((char *)sqlstmt), (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT)) { (void) printf("FAILED: OCIStmtPrepare() sqlstmt\n"); return OCI_ERROR; } /* associate variable colc with bind placeholder #1 in the SQL statement */ if (OCIBindByPos(stmthp, &bndhp, errhp, (ub4) 1, (dvoid *) &colc, (sb4) sizeof(colc), SQLT_INT, (dvoid *) 0, (ub2 *)0, (ub2 *)0, (ub4) 0, (ub4 *) 0, (ub4) OCI_DEFAULT)) { (void) printf("FAILED: OCIBindByPos()\n"); return OCI_ERROR; } /* associate blob var with its define handle */ if (OCIDefineByPos(stmthp, &defnp2, errhp, (ub4) 1, (dvoid *) &blob, (sb4) -1, (ub2) SQLT_BLOB, (dvoid *) 0, (ub2 *) 0, (ub2 *) 0, (ub4) OCI_DEFAULT)) { (void) printf("FAILED: OCIDefineByPos()\n"); return OCI_ERROR; } /* execute the select and fetch one row */ if (OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4) 0, (CONST OCISnapshot*) 0, (OCISnapshot*) 0, (ub4) OCI_DEFAULT)) { (void) printf("FAILED: OCIStmtExecute() sqlstmt\n"); return OCI_ERROR; } return OCI_SUCCESS; } /*------------------- select_lobs -------------------------------*/ /* select lob locators from the CLOB, BLOB columns */ sb4 select_lobs(rowind) int rowind; { int colc = rowind; text *sqlstmt = (text *)"SELECT C1, C2 FROM FOO WHERE C3 = :1"; /* we don't need the 'FOR UPDATE' clause since we are just reading the LOBs */ /* prepare select statement */ if (OCIStmtPrepare(stmthp, errhp, sqlstmt, (ub4) strlen((char *)sqlstmt), (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT)) { (void) printf("FAILED: OCIStmtPrepare() sqlstmt\n"); return OCI_ERROR; } /* associate variable colc with bind placeholder #1 in the SQL statement */ if (OCIBindByPos(stmthp, &bndhp, errhp, (ub4) 1, (dvoid *) &colc, (sb4) sizeof(colc), SQLT_INT, (dvoid *) 0, (ub2 *)0, (ub2 *)0, (ub4) 0, (ub4 *) 0, (ub4) OCI_DEFAULT)) { (void) printf("FAILED: OCIBindByPos()\n"); return OCI_ERROR; } /* associate clob and blob vars with their define handles */ if (OCIDefineByPos(stmthp, &defnp1, errhp, (ub4) 1, (dvoid *) &clob, (sb4) -1, (ub2) SQLT_CLOB, (dvoid *) 0, (ub2 *) 0, (ub2 *) 0, (ub4) OCI_DEFAULT) || OCIDefineByPos(stmthp, &defnp2, errhp, (ub4) 2, (dvoid *) &blob, (sb4) -1, (ub2) SQLT_BLOB, (dvoid *) 0, (ub2 *) 0, (ub2 *) 0, (ub4) OCI_DEFAULT)) { (void) printf("FAILED: OCIDefineByPos()\n"); return OCI_ERROR; } /* execute the select and fetch one row */ if (OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4) 0, (CONST OCISnapshot*) 0, (OCISnapshot*) 0, (ub4) OCI_DEFAULT)) { (void) printf("FAILED: OCIStmtExecute() sqlstmt\n"); return OCI_ERROR; } return OCI_SUCCESS; } /*-------------------- buf_write_lob -----------------------------*/ /* * Read operating system files into local buffers and then write these local * buffers to LOBs using buffering system. */ sb4 buf_write_lob(rowind, locator, fp, filelen) int rowind; OCILobLocator *locator; FILE *fp; ub4 filelen; { ub4 offset = 1; ub1 bufp[MAXBUFLEN]; ub4 amtp; ub4 nbytes = 0; ub4 remainder = filelen; /* reset per read/write buffer size and perform the first read */ amtp = nbytes = (filelen > MAXBUFLEN) ? MAXBUFLEN : filelen; /* write into the LOB's client-side buffer (upto max 16 pages of 32K each) */ while (remainder > 0) { if (fread((void *)bufp, (size_t)nbytes, (size_t)1, fp) != 1) { (void) printf("ERROR: read file.\n"); return(OCI_ERROR); } if (feof(fp)) { (void) printf("Exit - End of file reached\n", amtp, offset); break; } (void) printf("Write %d bytes out of remaining %d bytes at off %d\n", amtp, remainder, offset); if (OCILobWrite(svchp, errhp, locator, &amtp, (ub4) offset, (dvoid *) bufp, (ub4) nbytes, OCI_ONE_PIECE, (dvoid *)0, (sb4 (*)(dvoid *, dvoid *, ub4 *, ub1 *)) 0, (ub2) 0, (ub1) SQLCS_IMPLICIT)) { (void) printf("FAILED: OCILobWrite() \n"); return(OCI_ERROR); } if (amtp < nbytes) { (void) printf("FAILED: Full file not written \n"); return(OCI_ERROR); } amtp = nbytes; offset += nbytes; remainder -= nbytes; } /* flush the buffers back to the server */ (void) printf("Flush LOB's buffer to server\n"); if (OCILobFlushBuffer(svchp, errhp, locator, OCI_LOB_BUFFER_NOFREE)) { (void) printf("FAILED: OCILobFlushBuffer() \n"); return OCI_ERROR; } return OCI_SUCCESS; } /*--------------------------------- buf_read_lob ----------------------------*/ /* * Read LOBs using buffered mode into local buffers and writes them into * operating system files. */ sb4 buf_read_lob(rowind, locator, fp) int rowind; OCILobLocator *locator; FILE *fp; { ub4 offset = 1; ub1 bufp[MAXBUFLEN]; ub4 amtp = 0; ub4 nbytes = 0; /* set amount to be read per iteration */ amtp = nbytes = MAXBUFLEN; /* * read from CLOB and write to text file (in the process, populating upto * 16 pages of 32K each in the buffering subsystem). */ while (amtp) { (void) printf("Reading %d bytes from offset %d\n", amtp, offset); if (OCILobRead(svchp, errhp, locator, &amtp, (ub4) offset, (dvoid *) bufp, (ub4) nbytes, (dvoid *)0, (sb4 (*)(dvoid *, CONST dvoid *, ub4, ub1)) 0, (ub2) 0, (ub1) SQLCS_IMPLICIT)) { (void) printf("FAILED: OCILobRead() \n"); return OCI_ERROR; } (void) fwrite((void *)bufp, (size_t)amtp, (size_t)1, fp); /* write buffer to file */ offset += nbytes; } return OCI_SUCCESS; } /*---------------------- drop_table ------------------------------*/ /* Drop table FOO before logging off from the server */ void drop_table() { text *dropstmt = (text *) "DROP TABLE FOO"; /* prepare drop statement */ if (OCIStmtPrepare(stmthp, errhp, dropstmt, (ub4) strlen((char *) dropstmt), (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT)) { (void) printf("FAILED: OCIStmtPrepare() dropstmt\n"); return; } /* execute drop statement */ if (OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4) 0, (CONST OCISnapshot *) 0, (OCISnapshot *) 0, (ub4) OCI_DEFAULT)) { (void) printf("FAILED: OCIStmtExecute() dropstmt\n"); return; } return; } /*---------------------- report_error -----------------------------*/ /* retrieve error message and print it out */ void report_error() { text msgbuf[512]; sb4 errcode = 0; (void) OCIErrorGet((dvoid *) errhp, (ub4) 1, (text *) NULL, &errcode, msgbuf, (ub4) sizeof(msgbuf), (ub4) OCI_HTYPE_ERROR); (void) printf("ERROR CODE = %d\n", errcode); (void) printf("%.*s\n", 512, msgbuf); return; } /*---------------------- file_length ------------------------------*/ /* get the length of the input file */ ub4 file_length(fp) FILE *fp; { fseek(fp, 0, SEEK_END); return (ub4) (ftell(fp)); }
/* NAME cdemobj.c DESCRIPTION Demo of selection of a REF and display the pinned object through navigational interface. */ #ifndef CDEMO_OBJ_ORACLE #include "cdemobj.h" #endif /* statement to select a ref from an extent table customer_tab */ static const text *const selref = (text *) "SELECT REF(customer_tab) from customer_tab"; /* statement to create the type address */ static const text *const create_type_address = (text *) "CREATE TYPE address AS OBJECT (\ no NUMBER,\ street VARCHAR(60),\ state CHAR(2),\ zip CHAR(10)\ )"; /* statement to create the typed table address_tab */ static const text *const create_type_addr_tab = (text *) "create type addr_tab is table of address"; /* statement to create the type person */ static const text *const create_type_person = (text *) "CREATE TYPE person AS OBJECT (\ firstname CHAR(20),\ lastname varchar(20),\ age int,\ salary float,\ bonus double precision,\ retirement_fund int,\ number_of_kids smallint,\ years_of_school numeric(10, 2),\ preaddr addr_tab,\ birthday date,\ number_of_pets real,\ comment1 raw(200),\ comment2 clob,\ comment3 varchar2(200),\ addr ADDRESS\ )"; /* statement to create the type customer */ static const text *const create_type_customer = (text *) "CREATE TYPE customer AS OBJECT (\ account char(20),\ aperson REF person\ )"; /* statement to create the typed table person */ static const text *const create_table_person = (text *) "create table person_tab of person nested table preaddr store as person_preaddr_table"; /* statement to create the typed table customer_tab */ static const text *const create_table_customer = (text *) "create table customer_tab of customer"; /* statement to insert data into table customer_tab */ static const text *const insert_customer = (text *) "insert into customer_tab values('00001', null)"; /* statement to insert data into table person_tab */ static const text *const insert_person = (text *) "insert into person_tab values('Sandy', 'Wood', 25, 32000, 10000, 20000, 3,\ 15, addr_tab(),\ to_date('1961 08 23', 'YYYY MM DD'), 2,\ '1234567890', 'This is a test', 'This is a test',\ ADDRESS(8888, 'Fenley Road', 'CA', '91406'))"; /* statement to insert data into the nested table in person_tab */ static const text *const insert_address1 = (text *) "insert into the (select preaddr from person_tab where\ firstname='Sandy') values\ (715, 'South Henry', 'ca', '95117')"; static const text *const insert_address2 = (text *) "insert into the (select preaddr from person_tab where\ firstname='Sandy') values\ (6830, 'Woodley Ave', 'ca', '90416')"; /* statement to update the ref in the table customer_tab */ static const text *const update_customer = (text *) "update customer_tab set aperson = (select ref(person_tab)\ from person_tab where\ firstname = 'Sandy')"; /*************************************************************************** * Check the error and display the error message * ****************************************************************************/ static void checkerr(errhp, status) OCIError *errhp; sword status; { text errbuf[512]; sb4 errcode; switch (status) { case OCI_SUCCESS: break; case OCI_SUCCESS_WITH_INFO: break; case OCI_NEED_DATA: break; case OCI_NO_DATA: break; case OCI_ERROR: /* get the error back and display on the screen */ (void) OCIErrorGet ((dvoid *) errhp, (ub4) 1, (text *) NULL, &errcode, errbuf, (ub4) sizeof(errbuf), (ub4) OCI_HTYPE_ERROR); (void) printf("Error - %s\n", errbuf); break; case OCI_INVALID_HANDLE: break; case OCI_STILL_EXECUTING: break; case OCI_CONTINUE: break; default: break; } } /**************************************************************************** * Display attribute value of an object * ****************************************************************************/ static void display_attr_val(envhp, errhp, names, typecode, attr_value) OCIEnv *envhp; /* environment handle */ OCIError *errhp; /* error handle */ text *names; /* the name of the attribute */ OCITypeCode typecode; /* the type code */ dvoid *attr_value; /* the value pointer */ { text str_buf[200]; double dnum; ub4 text_len, str_len; OCIRaw *raw = (OCIRaw *) 0; OCIString *vs = (OCIString *) 0; /* display the data based on the type code */ switch (typecode) { case OCI_TYPECODE_DATE : /* fixed length string */ str_len = 200; (void) OCIDateToText(errhp, (CONST OCIDate *) attr_value, (CONST text*) "Month dd, SYYYY, HH:MI A.M.", (ub1) 27, (CONST text*) "American", (ub4) 8, (ub4 *)&str_len, str_buf); str_buf[str_len+1] = '\0'; (void) printf("attr %s = %s\n", names, (text *) str_buf); break; case OCI_TYPECODE_RAW : /* RAW */ raw = *(OCIRaw **) attr_value; (void) printf("attr %s = %s\n", names, (text *) OCIRawPtr(envhp, raw)); break; case OCI_TYPECODE_CHAR : /* fixed length string */ case OCI_TYPECODE_VARCHAR : /* varchar */ case OCI_TYPECODE_VARCHAR2 : /* varchar2 */ vs = *(OCIString **) attr_value; (void) printf("attr %s = %s\n", names, (text *) OCIStringPtr(envhp, vs)); break; case OCI_TYPECODE_SIGNED8: /* BYTE - sb1 */ (void) printf("attr %s = %d\n", names, *(sb1 *) attr_value); break; case OCI_TYPECODE_UNSIGNED8: /* UNSIGNED BYTE - ub1 */ (void) printf("attr %s = %d\n", names, *(ub1 *) attr_value); break; case OCI_TYPECODE_OCTET: /* OCT */ (void) printf("attr %s = %d\n", names, *(ub1 *) attr_value); break; case OCI_TYPECODE_UNSIGNED16: /* UNSIGNED SHORT */ case OCI_TYPECODE_UNSIGNED32: /* UNSIGNED LONG */ case OCI_TYPECODE_REAL: /* REAL */ case OCI_TYPECODE_DOUBLE: /* DOUBLE */ case OCI_TYPECODE_INTEGER: /* INT */ case OCI_TYPECODE_SIGNED16: /* SHORT */ case OCI_TYPECODE_SIGNED32: /* LONG */ case OCI_TYPECODE_DECIMAL: /* DECIMAL */ case OCI_TYPECODE_FLOAT: /* FLOAT */ case OCI_TYPECODE_NUMBER: /* NUMBER */ case OCI_TYPECODE_SMALLINT: /* SMALLINT */ (void) OCINumberToReal(errhp, (CONST OCINumber *) attr_value, (uword)sizeof(dnum), (dvoid *) &dnum); (void) printf("attr %s = %f\n", names, dnum); break; default: (void) printf("attr %s - typecode %d\n", names, typecode); break; } } /**************************************************************************** * Dump the info of any object * ****************************************************************************/ static void dump_object(envhp, errhp, svchp, tdo, obj, null_obj) OCIEnv *envhp; /* environment handle */ OCIError *errhp; /* error handle */ OCISvcCtx *svchp; /* service handle */ OCIType *tdo; /* type descriptor */ dvoid *obj; /* object pointer */ dvoid *null_obj; /* parallel null struct pointer */ { text *names[50]; text *lengths[50]; text *indexes[50]; ub2 count, pos; OCITypeElem *ado; ub4 text_len, str_len; ub4 i; OCITypeCode typecode; OCIInd attr_null_status; dvoid *attr_null_struct; dvoid *attr_value; OCIType *attr_tdo, *element_type; dvoid *object; dvoid *null_object; OCIType *object_tdo; ub1 status; OCIRef *type_ref; text str_buf[200]; double dnum; dvoid *element = (dvoid *) 0, *null_element = (dvoid *) 0; boolean exist, eoc, boc; sb4 index; OCIDescribe *dschp = (OCIDescribe *) 0, *dschp1 = (OCIDescribe *) 0; text *namep, *typenamep; dvoid *list_attr; OCIIter *itr = (OCIIter *) 0; dvoid *parmp = (dvoid *) 0, *parmdp = (dvoid *) 0, *parmp1 = (dvoid *) 0, *parmp2 = (dvoid *) 0; OCIRef *elem_ref = (OCIRef *) 0; checkerr(errhp, OCIHandleAlloc((dvoid *) envhp, (dvoid **) &dschp, (ub4) OCI_HTYPE_DESCRIBE, (size_t) 0, (dvoid **) 0)); checkerr(errhp, OCIDescribeAny(svchp, errhp, (dvoid *) tdo, (ub4) 0, OCI_OTYPE_PTR, (ub1)1, (ub1) OCI_PTYPE_TYPE, dschp)); checkerr(errhp, OCIAttrGet((dvoid *) dschp, (ub4) OCI_HTYPE_DESCRIBE, (dvoid *)&parmp, (ub4 *)0, (ub4)OCI_ATTR_PARAM, errhp)); checkerr(errhp, OCIAttrGet((dvoid*) parmp,(ub4) OCI_DTYPE_PARAM, (dvoid*) &typenamep, (ub4 *) &str_len, (ub4) OCI_ATTR_NAME, (OCIError *) errhp)); typenamep[str_len] = '\0'; printf("starting displaying instance of type '%s'\n", typenamep); /* loop through all attributes in the type */ checkerr(errhp, OCIAttrGet((dvoid*) parmp, (ub4) OCI_DTYPE_PARAM, (dvoid*) &count, (ub4 *) 0, (ub4) OCI_ATTR_NUM_TYPE_ATTRS, (OCIError *) errhp)); checkerr(errhp, OCIAttrGet((dvoid *) parmp, (ub4) OCI_DTYPE_PARAM, (dvoid *)&list_attr, (ub4 *)0, (ub4)OCI_ATTR_LIST_TYPE_ATTRS, (OCIError *)errhp)); /* loop through all attributes in the type */ for (pos = 1; pos <= count; pos++) { checkerr(errhp, OCIParamGet((dvoid *) list_attr, (ub4) OCI_DTYPE_PARAM, errhp, (dvoid *)&parmdp, (ub4) pos)); checkerr(errhp, OCIAttrGet((dvoid*) parmdp, (ub4) OCI_DTYPE_PARAM, (dvoid*) &namep, (ub4 *) &str_len, (ub4) OCI_ATTR_NAME, (OCIError *) errhp)); namep[str_len] = '\0'; /* get the attribute */ if (OCIObjectGetAttr(envhp, errhp, obj, null_obj, tdo, &namep, &str_len, 1, (ub4 *)0, 0, &attr_null_status, &attr_null_struct, &attr_value, &attr_tdo) != OCI_SUCCESS) (void) printf("BUG -- OCIObjectGetAttr, expect OCI_SUCCESS.\n"); /* get the type code of the attribute */ checkerr(errhp, OCIAttrGet((dvoid*) parmdp, (ub4) OCI_DTYPE_PARAM, (dvoid*) &typecode, (ub4 *) 0, (ub4) OCI_ATTR_TYPECODE, (OCIError *) errhp)); /* support only fixed length string, ref and embedded object */ switch (typecode) { case OCI_TYPECODE_OBJECT : /* embedded object */ printf("attribute %s is an embedded object. Display instance ....\n", namep); /* recursive call to dump nested object data */ dump_object(envhp, errhp, svchp, attr_tdo, attr_value, attr_null_struct); break; case OCI_TYPECODE_REF : /* embedded object */ printf("attribute %s is a ref. Pin and display instance ...\n", namep); /* pin the object */ if (OCIObjectPin(envhp, errhp, *(OCIRef **)attr_value, (OCIComplexObject *)0, OCI_PIN_ANY, OCI_DURATION_SESSION, OCI_LOCK_NONE, (dvoid **)&object) != OCI_SUCCESS) (void) printf("BUG -- OCIObjectPin, expect OCI_SUCCESS.\n"); /* allocate the ref */ if (( status = OCIObjectNew(envhp, errhp, svchp, OCI_TYPECODE_REF, (OCIType *)0, (dvoid *)0, OCI_DURATION_DEFAULT, TRUE, (dvoid **) &type_ref)) != OCI_SUCCESS) (void) printf("BUG -- OCIObjectNew, expect OCI_SUCCESS.\n"); /* get the ref of the type from the object */ if (( status = OCIObjectGetTypeRef(envhp, errhp, object, type_ref)) != OCI_SUCCESS) (void) printf("BUG -- ORIOGTR, expect OCI_SUCCESS.\n"); /* pin the type ref to get the type object */ if (OCIObjectPin(envhp, errhp, type_ref, (OCIComplexObject *)0, OCI_PIN_ANY, OCI_DURATION_SESSION, OCI_LOCK_NONE, (dvoid **)&object_tdo) != OCI_SUCCESS) (void) printf("BUG -- OCIObjectPin, expect OCI_SUCCESS.\n"); /* get null struct of the object */ if (( status = OCIObjectGetInd(envhp, errhp, object, &null_object)) != OCI_SUCCESS) (void) printf("BUG -- ORIOGNS, expect OCI_SUCCESS.\n"); /* call the function recursively to dump the pinned object */ dump_object(envhp, errhp, svchp, object_tdo, object, null_object); case OCI_TYPECODE_NAMEDCOLLECTION: checkerr(errhp, OCIHandleAlloc((dvoid *) envhp, (dvoid **) &dschp1, (ub4) OCI_HTYPE_DESCRIBE, (size_t) 0, (dvoid **) 0)); checkerr(errhp, OCIDescribeAny(svchp, errhp, (dvoid *) attr_tdo, (ub4) 0, OCI_OTYPE_PTR, (ub1)1, (ub1) OCI_PTYPE_TYPE, dschp1)); checkerr(errhp, OCIAttrGet((dvoid *) dschp1, (ub4) OCI_HTYPE_DESCRIBE, (dvoid *)&parmp1, (ub4 *)0, (ub4)OCI_ATTR_PARAM, errhp)); /* get the collection type code of the attribute */ checkerr(errhp, OCIAttrGet((dvoid*) parmp1, (ub4) OCI_DTYPE_PARAM, (dvoid*) &typecode, (ub4 *) 0, (ub4) OCI_ATTR_COLLECTION_TYPECODE, (OCIError *) errhp)); switch (typecode) { case OCI_TYPECODE_VARRAY: /* variable array */ (void) printf ("\n---> Dump the table from the top to the bottom.\n"); checkerr(errhp, OCIAttrGet((dvoid*) parmp1, (ub4) OCI_DTYPE_PARAM, (dvoid*) &parmp2, (ub4 *) 0, (ub4) OCI_ATTR_COLLECTION_ELEMENT, (OCIError *) errhp)); checkerr(errhp, OCIAttrGet((dvoid*) parmp2, (ub4) OCI_DTYPE_PARAM, (dvoid*) &elem_ref, (ub4 *) 0, (ub4) OCI_ATTR_REF_TDO, (OCIError *) errhp)); checkerr(OCITypeByRef(envhp, errhp, elem_ref, OCI_PIN_DEFAULT, 0, &element_type)); /* initialize the iterator */ checkerr(errhp, OCIIterCreate(envhp, errhp, (CONST OCIColl*) attr_value, &itr)); /* loop through the iterator */ for(eoc = FALSE;!OCIIterNext(envhp, errhp, itr, (dvoid **) &element, (dvoid **)&null_element, &eoc) && !eoc;) { /* if type is named type, call the same function recursively */ if (typecode == OCI_TYPECODE_OBJECT) dump_object(envhp, errhp, svchp, element_type, element, null_element); else /* else, display the scaler type attribute */ display_attr_val(envhp, errhp, namep, typecode, element); } break; case OCI_TYPECODE_TABLE: /* nested table */ (void) printf ("\n---> Dump the table from the top to the bottom.\n"); /* go to the first element and print out the index */ checkerr(errhp, OCIAttrGet((dvoid*) parmp1, (ub4) OCI_DTYPE_PARAM, (dvoid*) &parmp2, (ub4 *) 0, (ub4) OCI_ATTR_COLLECTION_ELEMENT, (OCIError *) errhp)); checkerr(errhp, OCIAttrGet((dvoid*) parmp2, (ub4) OCI_DTYPE_PARAM, (dvoid*) &elem_ref, (ub4 *) 0, (ub4) OCI_ATTR_REF_TDO, (OCIError *) errhp)); checkerr(errhp, OCITypeByRef(envhp, errhp, elem_ref, OCI_DURATION_SESSION, OCI_TYPEGET_HEADER, &element_type)); attr_value = *(dvoid **)attr_value; /* move to the first element in the nested table */ checkerr(errhp, OCITableFirst(envhp, errhp, (CONST OCITable*) attr_value, &index)); (void) printf (" The index of the first element is : %d.\n", index); /* print out the element */ checkerr(errhp, OCICollGetElem(envhp, errhp, (CONST OCIColl *) attr_value, index, &exist, (dvoid **) &element, (dvoid **) &null_element)); /* if it is named type, recursively call the same function */ checkerr(errhp, OCIAttrGet((dvoid*) parmp2, (ub4) OCI_DTYPE_PARAM, (dvoid*) &typecode, (ub4 *) 0, (ub4) OCI_ATTR_TYPECODE, (OCIError *) errhp)); if (typecode == OCI_TYPECODE_OBJECT) dump_object(envhp, errhp, svchp, element_type, (dvoid *)element, (dvoid *)null_element); else display_attr_val(envhp, errhp, namep, typecode, element); for(;!OCITableNext(envhp, errhp, index, (CONST OCITable *) attr_value, &index, &exist) && exist;) { checkerr(errhp, OCICollGetElem(envhp, errhp, (CONST OCIColl *) attr_value, index, &exist, (dvoid **) &element, (dvoid **) &null_element)); if (typecode == OCI_TYPECODE_OBJECT) dump_object(envhp, errhp, svchp, element_type, (dvoid *)element, (dvoid *)null_element); else display_attr_val(envhp, errhp, namep, typecode, element); } break; default: break; } checkerr(errhp, OCIHandleFree((dvoid *) dschp1, (ub4) OCI_HTYPE_DESCRIBE)); break; default: /* scaler type, display the attribute value */ if (attr_null_status == OCI_IND_NOTNULL) { display_attr_val(envhp, errhp, namep, typecode, attr_value); } else printf("attr %s is null\n", namep); break; } } checkerr(errhp, OCIHandleFree((dvoid *) dschp, (ub4) OCI_HTYPE_DESCRIBE)); printf("finishing displaying instance of type '%s'\n", typenamep); } /**************************************************************************** * Setup the schema and insert the data * *****************************************************************************/ void setup(envhp, svchp, stmthp, errhp) OCIEnv *envhp; OCISvcCtx *svchp; OCIStmt *stmthp; OCIError *errhp; { /* create the schema and populate the data */ checkerr(errhp, OCIStmtPrepare(stmthp, errhp, (text *) create_type_address, (ub4) strlen((const char *) create_type_address), (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT)); checkerr(errhp, OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4) 0, (OCISnapshot *) NULL, (OCISnapshot *) NULL, (ub4) OCI_DEFAULT)); checkerr(errhp, OCIStmtPrepare(stmthp, errhp, (text *) create_type_addr_tab, (ub4) strlen((const char *) create_type_addr_tab), (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT)); checkerr(errhp, OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4) 0, (OCISnapshot *) NULL, (OCISnapshot *) NULL, (ub4) OCI_DEFAULT)); checkerr(errhp, OCIStmtPrepare(stmthp, errhp, (text *) create_type_person, (ub4) strlen((const char *) create_type_person), (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT)); checkerr(errhp, OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4) 0, (OCISnapshot *) NULL, (OCISnapshot *) NULL, (ub4) OCI_DEFAULT)); checkerr(errhp, OCIStmtPrepare(stmthp, errhp, (text *) create_type_customer, (ub4) strlen((const char *) create_type_customer), (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT)); checkerr(errhp, OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4) 0, (OCISnapshot *) NULL, (OCISnapshot *) NULL, (ub4) OCI_DEFAULT)); checkerr(errhp, OCIStmtPrepare(stmthp, errhp, (text *) create_table_person, (ub4) strlen((const char *) create_table_person), (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT)); checkerr(errhp, OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4) 0, (OCISnapshot *) NULL, (OCISnapshot *) NULL, (ub4) OCI_DEFAULT)); checkerr(errhp, OCIStmtPrepare(stmthp, errhp, (text *) create_table_customer, (ub4) strlen((const char *) create_table_customer), (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT)); checkerr(errhp, OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4) 0, (OCISnapshot *) NULL, (OCISnapshot *) NULL, (ub4) OCI_DEFAULT)); checkerr(errhp, OCIStmtPrepare(stmthp, errhp, (text *) insert_customer, (ub4) strlen((const char *) insert_customer), (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT)); checkerr(errhp, OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4) 0, (OCISnapshot *) NULL, (OCISnapshot *) NULL, (ub4) OCI_DEFAULT)); checkerr(errhp, OCIStmtPrepare(stmthp, errhp, (text *) insert_person, (ub4) strlen((const char *) insert_person), (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT)); checkerr(errhp, OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4) 0, (OCISnapshot *) NULL, (OCISnapshot *) NULL, (ub4) OCI_DEFAULT)); checkerr(errhp, OCIStmtPrepare(stmthp, errhp, (text *) insert_address1, (ub4) strlen((const char *) insert_address1), (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT)); checkerr(errhp, OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4) 0, (OCISnapshot *) NULL, (OCISnapshot *) NULL, (ub4) OCI_DEFAULT)); checkerr(errhp, OCIStmtPrepare(stmthp, errhp, (text *) insert_address2, (ub4) strlen((const char *) insert_address2), (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT)); checkerr(errhp, OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4) 0, (OCISnapshot *) NULL, (OCISnapshot *) NULL, (ub4) OCI_DEFAULT)); checkerr(errhp, OCIStmtPrepare(stmthp, errhp, (text *) update_customer, (ub4) strlen((const char *) update_customer), (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT)); checkerr(errhp, OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4) 0, (OCISnapshot *) NULL, (OCISnapshot *) NULL, (ub4) OCI_DEFAULT)); checkerr(errhp, OCITransCommit(svchp, errhp, (ub4) 0)); } / *******************************************************************************/ void select_pin_display(envhp, svchp, stmthp, errhp) OCIEnv *envhp; OCISvcCtx *svchp; OCIStmt *stmthp; OCIError *errhp; { sword status = OCI_SUCCESS; OCIDefine *defnp; OCIRef *custref = (OCIRef *) 0, *per_type_ref = (OCIRef *) 0; OCIRef *cust_type_ref = (OCIRef *) 0; ub4 custsize; customer *cust = (customer *) 0, *custnew = (customer *) 0; null_customer *null_cust = (null_customer *) 0, *null_custnew = (null_customer *) 0; person *per = (person *) 0; null_person *null_per = (null_person *) 0; null_address *nt_null = (null_address *) 0; OCIType *pertdo = (OCIType *) 0, *custtdo = (OCIType *) 0; address *addr = (address *) 0; sb4 index; boolean exist; dvoid *tabobj = (dvoid *) 0; (void) printf("\n=============================================\n"); /* allocate ref */ if (( status = OCIObjectNew(envhp, errhp, svchp, OCI_TYPECODE_REF, (OCIType *)0, (dvoid *)0, OCI_DURATION_DEFAULT, TRUE, (dvoid **) &per_type_ref)) != OCI_SUCCESS) (void) printf("BUG -- OCIObjectNew, expect OCI_SUCCESS.\n"); /* allocate ref */ if (( status = OCIObjectNew(envhp, errhp, svchp, OCI_TYPECODE_REF, (OCIType *)0, (dvoid *)0, OCI_DURATION_DEFAULT, TRUE, (dvoid **) &cust_type_ref)) != OCI_SUCCESS) (void) printf("BUG -- OCIObjectNew, expect OCI_SUCCESS.\n"); /* define the application request */ checkerr(errhp, OCIStmtPrepare(stmthp, errhp, (text *) selref, (ub4) strlen((const char *) selref), (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT)); checkerr(errhp, OCIHandleAlloc( (dvoid *) stmthp, (dvoid **) &defnp, (ub4) OCI_HTYPE_DEFINE, 0, (dvoid **) 0)); checkerr(errhp, OCIDefineByPos(stmthp, &defnp, errhp, (ub4) 1, (dvoid *) 0, (sb4) 0, SQLT_REF, (dvoid *) 0, (ub2 *)0, (ub2 *)0, (ub4) OCI_DEFAULT)); checkerr(errhp, OCIDefineObject(defnp, errhp, (OCIType *) 0, (dvoid **) &custref, &custsize, (dvoid **) 0, (ub4 *) 0)); checkerr(errhp, OCIStmtExecute(svchp, stmthp, errhp, (ub4) 0, (ub4) 0, (OCISnapshot *) NULL, (OCISnapshot *) NULL, (ub4) OCI_DEFAULT)); while ((status = OCIStmtFetch(stmthp, errhp, (ub4) 1, (ub4) OCI_FETCH_NEXT, (ub4) OCI_DEFAULT)) == 0) { (void) printf("\n-----------------------------------------\n"); /* pin the ref and get the typed table to get to person */ checkerr(errhp, OCIObjectPin(envhp, errhp, custref, (OCIComplexObject *)0, OCI_PIN_ANY, OCI_DURATION_SESSION, OCI_LOCK_NONE, (dvoid **) &cust)); (void) printf("The customer account number is %s\n", OCIStringPtr(envhp, cust->account)); if (( status = OCIObjectGetInd(envhp, errhp, (dvoid *) cust, (dvoid **) &null_cust)) != OCI_SUCCESS) { (void) printf("BUG -- ORIOGNS, expect OCI_SUCCESS.\n"); } else { (void) printf("null_cus = %d, null_account = %d, null_aperson = %d\n", null_cust->null_cus, null_cust->null_account, null_cust->null_aperson); } checkerr(errhp, OCIObjectPin(envhp, errhp, cust->aperson, (OCIComplexObject *)0, OCI_PIN_ANY, OCI_DURATION_SESSION, OCI_LOCK_NONE, (dvoid **) &per)); if (( status = OCIObjectGetInd(envhp, errhp, (dvoid *) per, (dvoid **) &null_per)) != OCI_SUCCESS) { (void) printf("BUG -- ORIOGNS, expect OCI_SUCCESS.\n"); } else { checkerr(errhp, OCIObjectGetTypeRef(envhp, errhp, (dvoid *)per, per_type_ref)); checkerr(errhp, OCIObjectPin(envhp, errhp, per_type_ref, (OCIComplexObject *)0, OCI_PIN_ANY, OCI_DURATION_SESSION, OCI_LOCK_NONE, (dvoid **) &pertdo)); dump_object(envhp, errhp, svchp, pertdo, (dvoid *) per, (dvoid *) null_per); } } if ( status != OCI_NO_DATA ) checkerr(errhp, status); (void) printf("\n\n"); } /**************************************************************************** * Clean up the schema and the data * *****************************************************************************/ void cleanup(envhp, svchp, stmthp, errhp) OCIEnv *envhp; OCISvcCtx *svchp; OCIStmt *stmthp; OCIError *errhp; { /* clean up the schema */ checkerr(errhp, OCIStmtPrepare(stmthp, errhp, (text *) "drop table customer_tab", (ub4) strlen((const char *)"drop table customer_tab" ), (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT)); checkerr(errhp, OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4) 0, (OCISnapshot *) NULL, (OCISnapshot *) NULL, (ub4) OCI_DEFAULT)); checkerr(errhp, OCIStmtPrepare(stmthp, errhp, (text *) "drop table person_tab", (ub4) strlen((const char *)"drop table person_tab" ), (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT)); checkerr(errhp, OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4) 0, (OCISnapshot *) NULL, (OCISnapshot *) NULL, (ub4) OCI_DEFAULT)); checkerr(errhp, OCIStmtPrepare(stmthp, errhp, (text *) "drop type customer", (ub4) strlen((const char *)"drop table customer" ), (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT)); checkerr(errhp, OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4) 0, (OCISnapshot *) NULL, (OCISnapshot *) NULL, (ub4) OCI_DEFAULT)); checkerr(errhp, OCIStmtPrepare(stmthp, errhp, (text *) "drop type person", (ub4) strlen((const char *)"drop table person" ), (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT)); checkerr(errhp, OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4) 0, (OCISnapshot *) NULL, (OCISnapshot *) NULL, (ub4) OCI_DEFAULT)); checkerr(errhp, OCIStmtPrepare(stmthp, errhp, (text *) "drop type addr_tab", (ub4) strlen((const char *)"drop table addr_tab" ), (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT)); checkerr(errhp, OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4) 0, (OCISnapshot *) NULL, (OCISnapshot *) NULL, (ub4) OCI_DEFAULT)); checkerr(errhp, OCIStmtPrepare(stmthp, errhp, (text *) "drop type address", (ub4) strlen((const char *) "drop type address"), (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT)); checkerr(errhp, OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4) 0, (OCISnapshot *) NULL, (OCISnapshot *) NULL, (ub4) OCI_DEFAULT)); checkerr(errhp, OCITransCommit(svchp, errhp, (ub4) 0)); } /**********************************************************************/ int main() { OCIEnv *envhp; OCIServer *srvhp; OCIError *errhp; OCISvcCtx *svchp; OCISession *usrhp; OCIStmt *stmthp; dvoid *tmp; /* initialize the process */ (void) OCIInitialize((ub4) OCI_THREADED | OCI_OBJECT, (dvoid *)0, (dvoid * (*)()) 0, (dvoid * (*)()) 0, (void (*)()) 0 ); /* initialize the environmental handle */ (void) OCIEnvInit( &envhp, (ub4) OCI_DEFAULT, 21, (dvoid **) &tmp ); /* get the error handle */ (void) OCIHandleAlloc( (dvoid *) envhp, (dvoid **) &errhp, (ub4) OCI_HTYPE_ERROR, 52, (dvoid **) &tmp); /* two server contexts */ (void) OCIHandleAlloc( (dvoid *) envhp, (dvoid **) &srvhp, (ub4) OCI_HTYPE_SERVER, 52, (dvoid **) &tmp); /* attach the server */ (void) OCIServerAttach( srvhp, errhp, (text *) "", (sb4) 0, (ub4) OCI_DEFAULT); /* get the service handle */ (void) OCIHandleAlloc( (dvoid *) envhp, (dvoid **) &svchp, (ub4) OCI_HTYPE_SVCCTX, 52, (dvoid **) &tmp); /* set attribute server context in the service context */ (void) OCIAttrSet( (dvoid *) svchp, (ub4) OCI_HTYPE_SVCCTX, (dvoid *) srvhp, (ub4) 0, (ub4) OCI_ATTR_SERVER, (OCIError *) errhp); /* get the user handle */ (void) OCIHandleAlloc((dvoid *)envhp, (dvoid **)&usrhp, (ub4)OCI_HTYPE_SESSION, 0, (dvoid **)0); /* set the attribute user name */ (void) OCIAttrSet((dvoid *) usrhp, (ub4)OCI_HTYPE_SESSION, (dvoid *)"scott", (ub4)strlen("scott"), (ub4)OCI_ATTR_USERNAME, errhp); /* set the attribute password */ (void) OCIAttrSet((dvoid *) usrhp, (ub4)OCI_HTYPE_SESSION, (dvoid *)"tiger", (ub4)strlen("tiger"), (ub4)OCI_ATTR_PASSWORD, errhp); /* authenticate */ checkerr(errhp, OCISessionBegin (svchp, errhp, usrhp, OCI_CRED_RDBMS, OCI_DEFAULT)); /* set the attribute user context of the service handle */ (void) OCIAttrSet((dvoid *)svchp, (ub4)OCI_HTYPE_SVCCTX, (dvoid *)usrhp, (ub4)0, (ub4)OCI_ATTR_SESSION, errhp); /* get the statement handle */ checkerr(errhp, OCIHandleAlloc( (dvoid *) envhp, (dvoid **) &stmthp, (ub4) OCI_HTYPE_STMT, 50, (dvoid **) &tmp)); (void) printf("\n*********************************************\n"); (void) printf("--- Setup the schema and insert the data.\n"); setup(envhp, svchp, stmthp, errhp); (void) printf("\n*********************************************\n"); (void) printf("--- Select a REF, pin the REF, then display the object.\n"); select_pin_display(envhp, svchp, stmthp, errhp); (void) printf("\n*********************************************\n"); (void) printf("--- Clean up the schema and the data.\n"); cleanup(envhp, svchp, stmthp, errhp); checkerr(errhp, OCISessionEnd (svchp, errhp, usrhp, OCI_DEFAULT)); /* dettach */ (void) OCIServerDetach( srvhp, errhp, (ub4) OCI_DEFAULT ); checkerr(errhp, OCIHandleFree((dvoid *) stmthp, (ub4) OCI_HTYPE_STMT)); checkerr(errhp, OCIHandleFree((dvoid *) svchp, (ub4) OCI_HTYPE_SVCCTX)); checkerr(errhp, OCIHandleFree((dvoid *) errhp, (ub4) OCI_HTYPE_ERROR)); checkerr(errhp, OCIHandleFree((dvoid *) srvhp, (ub4) OCI_HTYPE_SERVER)); return (0); }
