Home » Developer & Programmer » Precompilers, OCI & OCCI » How do I loop through a recordset in OCI (Solaris 10, Oracle 10, C )
How do I loop through a recordset in OCI [message #411171] |
Thu, 02 July 2009 04:10 |
clancypc
Messages: 36 Registered: December 2006
|
Member |
|
|
Hi,
I am probably being obtuse here, but I cannot see from the documentation how to step through the records returned from a select statement.
The select statement is quite straight forward:
SELECT acc_number FROM acc_inf;
This will return about 2.5 million records and I want to take each value for acc_number and use it as the input to another procedure, but I dont know how to get to the next value out of the recordset.
I am guessing that it will probably be a bad idea to pull back all 2.5 million records at once, so I am setting the Prefetch Rows parameter to 1000. So am aware that I will need to use the OCIStmtFetch function, but again I am not entirely sure how to use it in a loop until there is no more data.
Any help will be gratefully accepted.
My code is:
/* Declarations */
char AccountNumber[20] = {""};
char QueryString[500] = {""}; /* String to hold the SQL query */
sb4 RtrnValue;
ub4 MaxNumRows;
OCIDefine *OraDefPtr;
/* Initialisations */
RtrnValue = 0;
NumRecords = 0;
MaxNumRows = 1000;
TRACE(("TRACE: Entering function check_acc_details\n"));
/* Query the database to get the list of account numbers */
TRACE(("TRACE: Build the query string\n"));
strcpy(QueryString, "SELECT acc_number FROM ACC_INF\0");
/* Attach sql statement to statement handle */
TRACE(("TRACE: Preparing statement handle\n"));
OCIStmtPrepare( OraStmntHandlePtr,
OraErrorHandlePtr,
&QueryString,
strlen(QueryString),
OCI_NTV_SYNTAX,
OCI_DEFAULT );
/* Set the prefetch rows attribute */
OCIAttrSet(OraStmntHandlePtr, (ub4) OCI_HTYPE_STMT, (dvoid *) &MaxNumRows, (ub4)sizeof(MaxNumRows), (ub4)OCI_ATTR_PREFETCH_ROWS, OraErrorHandlePtr);
/* Define output variable */
TRACE(("TRACE: Defining the output variable\n"));
RtrnValue = OCIDefineByPos( OraStmntHandlePtr,
&OraDefPtr,
OraErrorHandlePtr,
1,
&AccountNumber, /* destination variable */
sizeof(AccountNumber),
SQLT_STR,
(dvoid *) 0, (ub2 *) 0, (ub2 *) 0, OCI_DEFAULT);
if ( RtrnValue != 0 )
checkerr( OraErrorHandlePtr, RtrnValue);
/* Execute statement */
TRACE(("TRACE: Executing the statement\n"));
RtrnValue = OCIStmtExecute(OraServiceContextHandlePtr,
OraStmntHandlePtr,
OraErrorHandlePtr,
0, /* iters */
0,
(CONST OCISnapshot *) NULL,
(OCISnapshot *) NULL,
OCI_STMT_FETCH_NEXT);
if ( RtrnValue != 0 )
checkerr( OraErrorHandlePtr, RtrnValue);
Thanks
|
|
|
Goto Forum:
Current Time: Mon Nov 04 04:17:19 CST 2024
|