Re: OCI, how to detect a no_data

From: Andy Hassall <andy_at_andyh.co.uk>
Date: Thu, 10 Jun 2004 21:23:23 +0100
Message-ID: <a8ghc0hp84afn86a9dp5nch2oenlei0jl3@4ax.com>

On Wed, 9 Jun 2004 23:19:58 +0200, "José Delfosse" <jdelfosse_at_free.fr> wrote:

>I have a C++ application developed on IBM AIX 4.3.3 with Oracle 8.1.7.4 that
>use OCI to access the database.
>
>I have a loop on an update-statement that update different rows in a table.
>
>During the loop, it might happen that a row is not to be updated because it
>does not exist.
>
>After each OCIStmtExecute(), I call the OCIAttrGet() APÏ to retrieve the
>OCI_ATTR_ROW_COUNT attribute in order to know
>how many rows were updated (I expected one or zero in my case). My goal is
>to detect which rows were not updated (because they don't exist).
>
>While it returns 0 before the first OCIStmtExecute() , I found out that this
>attribute thereafter always return 1 even if the update found no rows.

That's odd.

>Moreover, OCIStmtExecute() returns OCI_SUCCESS even when the update found no
>rows.

That's expected.

>In fact, the OCI_ATTR_ROW_COUNT seems to work fine only with
>fetch-statement.

It works for updates and deletes too.

>So, using OCI, does anyone know how to detect that an update statement
>found no rows ?
>
>(just like the WHEN NO_DATA_FOUND of Pro*C or PL/SQL)

NO_DATA_FOUND is not raised when an update updates zero rows.

OCI_ATTR_ROW_COUNT works fine for me on 10g. Given the following:

[andyh_at_testbox oci]$ sqlplus test/test

SQL*Plus: Release 10.1.0.2.0 - Production on Thu Jun 10 21:14:27 2004

Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production With the Partitioning and Data Mining options

SQL> select * from test;

ID X
---------- ----------

10 1

The program following produces these (expected) results:

[andyh_at_testbox oci]$ ./rowcount

id= 9 rowcount=0
id=10 rowcount=1
id=11 rowcount=0

exiting...

Here's the code, adapted from the cdemo81.c demo:

rowcount.c:

#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <oci.h>

static text *username = (text *) "TEST"; static text *password = (text *) "TEST";

static text *update = (text *) "UPDATE test SET x = x + 1 WHERE id = :id";

static OCIEnv *envhp;
static OCIError *errhp;

static void checkerr(OCIError *errhp, sword status); static void cleanup(void);

static sword status;

int main(void)
{

sword id;
sword rowcount;

    OCISession *authp = (OCISession *) 0;
    OCIServer  *srvhp;
    OCISvcCtx  *svchp;
    OCIStmt    *updatehp;

OCIBind *bnd1p = (OCIBind *) 0;

OCIInitialize(OCI_DEFAULT, NULL, NULL, NULL, NULL); OCIEnvInit(&envhp, OCI_DEFAULT, 0, NULL);

    OCIHandleAlloc(envhp, (dvoid**)&errhp, OCI_HTYPE_ERROR,  0, NULL);
    OCIHandleAlloc(envhp, (dvoid**)&srvhp, OCI_HTYPE_SERVER, 0, NULL);
    OCIHandleAlloc(envhp, (dvoid**)&svchp, OCI_HTYPE_SVCCTX, 0, NULL);

OCIServerAttach(srvhp, errhp, "", strlen(""), 0);

OCIAttrSet(svchp, OCI_HTYPE_SVCCTX, (dvoid *)srvhp,

0, OCI_ATTR_SERVER, errhp);

OCIHandleAlloc(envhp, (dvoid **)&authp, OCI_HTYPE_SESSION, 0, NULL);

OCIAttrSet(authp, OCI_HTYPE_SESSION,

               username, strlen(username),
               OCI_ATTR_USERNAME, errhp);

    OCIAttrSet(authp, OCI_HTYPE_SESSION,
               password, strlen(password),
               OCI_ATTR_PASSWORD, errhp);

    checkerr(errhp, OCISessionBegin(svchp, errhp, authp, OCI_CRED_RDBMS,
                                    OCI_DEFAULT));

    OCIAttrSet(svchp, OCI_HTYPE_SVCCTX,
               authp, 0,
               OCI_ATTR_SESSION, errhp);

    checkerr(errhp, OCIHandleAlloc(envhp, (dvoid **)&updatehp,
                                   OCI_HTYPE_STMT, 0, NULL));

    checkerr(errhp, OCIStmtPrepare(updatehp, errhp, update, strlen(update), 
                                   OCI_NTV_SYNTAX, OCI_DEFAULT));

    checkerr(errhp, OCIBindByName(updatehp, &bnd1p, errhp, ":ID", -1, 
                                  (dvoid *) &id, sizeof(id), SQLT_INT, NULL,
                                  NULL, NULL, 0, NULL, OCI_DEFAULT));

for (id=9; id<=11; id++)
{

printf("id=%2d ", id);

        checkerr(errhp, OCIStmtExecute(svchp, updatehp, errhp, 1, 0, 
                                       NULL, NULL, OCI_DEFAULT));
        checkerr(errhp, OCIAttrGet(updatehp, OCI_HTYPE_STMT,
                                   (dvoid *)&rowcount, NULL, 
                                   OCI_ATTR_ROW_COUNT, errhp));
        printf("rowcount=%d\n", rowcount);

}

/* Commit the change. */
if (status = OCITransCommit(svchp, errhp, 0)) {

        checkerr(errhp, status);
        cleanup();
        return OCI_ERROR;

}
printf("exiting...\n");
}

void checkerr(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(errhp, 1, NULL, &errcode,
                           errbuf, 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;

}
}

void cleanup()
{

if (envhp)

OCIHandleFree(envhp, OCI_HTYPE_ENV); return;
}

--
Andy Hassall <andy_at_andyh.co.uk> / Space: disk usage analysis tool
http://www.andyh.co.uk         / http://www.andyhsoftware.co.uk/space

Received on Thu Jun 10 2004 - 15:23:23 CDT

Re: OCI, how to detect a no_data_found with an update