OCI8: Retrieving row count after OCIStmtFetch()

I'm using OCI8 with VC++.

As a memory saver, OCIStmtFetch() automatically frees the statement handle after all rows are fetched. (I've verified that behavior in my code, and the Oracle OCI8 documentation for OCIStmtExecute() says that this happens with that call.)

But I am bulk record fetching (100 at a time), and the last iteration I need to know how many (37? 80?) were in that last batch.

Since the statement handle is freed after the last batch, I can't call OCIAttrGet to get the OCI_ATTR_ROW_COUNT attribute of the statement handle. So I have no idea how many of the retrieved rows are valid in the last batch. (For batches prior to the last, I will correctly get row count = 100, 200, 300, etc. What I'm trying to get is a last value of, say, 337. This is easy in OCI7.3, where the "rc" attribute of the Cda_Def structure held the row count even after the fetch was finished.)

Question:
1) How do I get the total row count after last batch of records has been retrieved, given that the statement handle is freed?

or:

2) How do I stop OCI's default behavior of freeing the statement handle when all rows have been retrieved, so I can retrieve the OCI_ATTR_ROW_COUNT attribute of the statement handle?

Thanks,
Glen Mazza
gmazzaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

HaukeCommented:
Hi Glen,

I have no problem with OCIAttrGet/OCI_ATTR_ROW_COUNT after the last call of OCIStmtFetch in the following example.

Hauke.

#include <stdio.h>
#include <stdlib.h>
#include <ctype.h>
#include <string.h>
#include <orl.h>

void main( void)
{
   char dbn[]= "", usr[]= "system", pwd[]= "manager", sqlstm[]= "select rownum from tab where rownum <= 14";

   OCIEnv      *envhp;         /* Environment handle    */
   OCISvcCtx   *svchp;         /* Service handle        */
   OCIServer   *srvhp;         /* Server handles        */
   OCIError    *errhp;         /* Error handle          */
   OCISession  *authp;         /* Authentication handle */
   OCIStmt     *stmthp;
   OCIDefine   *defp;

   long  vars[4];
   short inds[4];                /* Indicator buffer     */

   long parnum, rowcount;

   sword oret;

   oret= OCIInitialize( (ub4)OCI_DEFAULT, (dvoid *)0, (dvoid * (*)(dvoid *, size_t)) 0, (dvoid * (*)(dvoid *, dvoid *, size_t))0, (void (*)(dvoid *, dvoid *)) 0);

   oret= OCIEnvInit( &envhp, (ub4)OCI_DEFAULT, (size_t) 0, (dvoid **) 0 );


   oret= OCIHandleAlloc( (dvoid *)envhp, (dvoid **)&errhp, (ub4)OCI_HTYPE_ERROR, (size_t)0, (dvoid **) NULL);
   oret= OCIHandleAlloc( (dvoid *)envhp, (dvoid **)&srvhp, (ub4)OCI_HTYPE_SERVER, (size_t)0, (dvoid **) NULL);
   oret= OCIHandleAlloc( (dvoid *)envhp, (dvoid **)&svchp, (ub4)OCI_HTYPE_SVCCTX, (size_t)0, (dvoid **) NULL);
   oret= OCIHandleAlloc( (dvoid *)envhp, (dvoid **)&authp, (ub4)OCI_HTYPE_SESSION, (size_t)0, (dvoid **) NULL);

   oret= OCIServerAttach( srvhp, errhp, (text *)dbn, (sb4)strlen( dbn), (ub4)OCI_DEFAULT);

   oret= OCIAttrSet( (dvoid *)svchp, (ub4)OCI_HTYPE_SVCCTX, (dvoid *)srvhp, (ub4)0, (ub4)OCI_ATTR_SERVER, errhp);

   oret= OCIAttrSet( (dvoid *)authp, (ub4)OCI_HTYPE_SESSION, (dvoid *)usr, (ub4)strlen( usr), (ub4)OCI_ATTR_USERNAME, errhp);
   oret= OCIAttrSet( (dvoid *)authp, (ub4)OCI_HTYPE_SESSION, (dvoid *)pwd, (ub4)strlen( pwd), (ub4)OCI_ATTR_PASSWORD, errhp);

   oret= OCISessionBegin( svchp, errhp, authp, (ub4)OCI_CRED_RDBMS, (ub4)OCI_DEFAULT);

   oret= OCIAttrSet( (dvoid *)svchp, (ub4)OCI_HTYPE_SVCCTX , (dvoid *)authp, (ub4)0, (ub4)OCI_ATTR_SESSION, errhp);

   oret= OCIHandleAlloc( (dvoid *)envhp, (dvoid **)&stmthp, (ub4)OCI_HTYPE_STMT, (size_t)0, (dvoid **) NULL);

   oret= OCIStmtPrepare( stmthp, errhp, (CONST OraText *)sqlstm, (ub4)strlen( sqlstm), (ub4)OCI_NTV_SYNTAX, (ub4)OCI_DEFAULT);

   oret= OCIDefineByPos( stmthp, &defp, errhp, (ub4)1, vars, (sb4)sizeof( *vars), (ub2)SQLT_INT, inds, (ub2 *) 0, (ub2 *) 0, (ub4)OCI_DEFAULT);

   oret= OCIStmtExecute( svchp, stmthp, errhp, (ub4)0, (ub4)0, (CONST OCISnapshot*) 0, (OCISnapshot*) 0, (ub4) OCI_DEFAULT);

   oret= OCIAttrGet( stmthp, OCI_HTYPE_STMT, (dvoid*)&parnum, (ub4 *)0, (ub4)OCI_ATTR_PARAM_COUNT, errhp);
   oret= OCIAttrGet( stmthp, OCI_HTYPE_STMT, (dvoid*)&rowcount, (ub4 *)0, (ub4)OCI_ATTR_ROW_COUNT, errhp);

   for ( ;; )
   {
      oret= OCIStmtFetch( stmthp, errhp, (ub4)4, (ub4)OCI_FETCH_NEXT, (ub4)OCI_DEFAULT);

      if (oret == OCI_NO_DATA) break;

      oret= OCIAttrGet( stmthp, OCI_HTYPE_STMT, (dvoid*)&rowcount, (ub4 *)0, (ub4)OCI_ATTR_ROW_COUNT, errhp);
   }

   oret= OCIAttrGet( stmthp, OCI_HTYPE_STMT, (dvoid*)&rowcount, (ub4 *)0, (ub4)OCI_ATTR_ROW_COUNT, errhp);

   // rowcount == 14

   // .......
}
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
gmazzaAuthor Commented:
Excellent--thanks for a great example.  Actually, the OCIAttrGet/OCI_ATTR_ROW_COUNT does work perfectly after all rows are fetched.

What happened is that I just had a single scalar variable (and not the necessary array) for the null indicator column in the define statement.  So when I fetched several rows, it overwrote nearby variables--including the statement handle--declared in the class definition.  Bug fixed.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.