[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2052
  • Last Modified:

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
0
gmazza
Asked:
gmazza
1 Solution
 
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
 
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

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now