To OCI developers

From: Ramon F Herrera <ramon_at_conexus.net>
Date: 13 Jul 2003 18:04:27 -0700
Message-ID: <c9bc36ff.0307131704.2de60b2b@posting.google.com>

Are you an experienced C/OCI developer?
Could you please look at the code below and see if you find any problem?

This is related to my posting: "Inconsistent SELECT results".

In a nutshell, the predicted number of records (COUNT(1)) are orders of magnitude larger than those obtained by the SELECT query with fields.

Thanks,

-Ramon F. Herrera

---

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

#ifndef OCI_ORACLE

#include <oci.h>
#endif

[... big chunk cut ...]

int
count_records(OCIEnv *envhp, OCIError *errhp, OCISvcCtx *svchp, char *canonical) {

OCIDefine	*defnp  = NULL;
OCIStmt		*select = NULL;
char		recCounter[80];
sword		result;
int		nofHits;

text *prelude = (text *) "SELECT COUNT(1) FROM claim WHERE parent_id = ";

    if (OCIHandleAlloc(envhp, (dvoid **) &select, OCI_HTYPE_STMT, 0, 0)) {

	fprintf(stderr, "nrec:\n");
        fprintf(stderr, "\tThere was an error on 'OCIHandleAlloc'\n");
	cleanup(envhp);
        do_exit(EXIT_FAILURE);


	}


    memset(recCounter, 0, sizeof(recCounter));

    strcpy(recCounter, prelude);
    strcat(recCounter, canonical);

    // parse the SQL statement
    if (OCIStmtPrepare(select, errhp, recCounter, strlen(recCounter),

	OCI_NTV_SYNTAX, OCI_DEFAULT)) {
	fprintf(stderr, "nrec:\n");
	fprintf(stderr, "\tThere was an error on 'OCIStmtPrepare'\n");
	cleanup(envhp);
	do_exit(EXIT_FAILURE);


	}

    // define output variable
    if (OCIDefineByPos(select, &defnp, errhp, 1,

	&nofHits, sizeof(nofHits), SQLT_INT,
	0, 0, 0, OCI_DEFAULT)) {
	fprintf(stderr, "nrec:\n");
	fprintf(stderr, "\tThere was an error on 'OCIDefineByPos'\n");
	cleanup(envhp);
	do_exit(EXIT_FAILURE);


	}

    memset(&nofHits, 0, sizeof(nofHits));     if ((result = OCIStmtExecute(svchp, select, errhp, 1, 0, NULL, NULL,

	OCI_DEFAULT)) != 0) {
	fprintf(stderr, "nrec:\n");
	fprintf(stderr, "\tThere was an error on 'OCIStmtExecute'\n");
	checkerr(errhp, result);
	cleanup(envhp);
	do_exit(OCI_ERROR);


	}

    return nofHits;
}

int
perform_query(OCIEnv *envhp, OCIError *errhp, OCISvcCtx *svchp,

                text *sqlStatement)
{

OCIStmt		*select  = NULL;
OCIDefine	*defnp1  = NULL;
OCIDefine	*defnp2  = NULL;
sword		result;

char		ssn[10];
char		name[17];

int		counter = 0;


    if (OCIHandleAlloc(envhp, (dvoid **) &select, OCI_HTYPE_STMT, 0, 0)) {
	fprintf(stderr, "nclaims:\n");
	fprintf(stderr, "\tThere was an error on 'OCIHandleAlloc'\n");
	cleanup(envhp);
	do_exit(EXIT_FAILURE);


	}

    /* parse the SQL statement */
    if (OCIStmtPrepare(select, errhp, sqlStatement, strlen(sqlStatement),

	OCI_NTV_SYNTAX, OCI_DEFAULT)) {
	fprintf(stderr, "nclaims:\n");
	fprintf(stderr, "\tThere was an error on 'OCIStmtPrepare'\n");
	cleanup(envhp);
	do_exit(EXIT_FAILURE);


	}


    if (OCIDefineByPos(select, &defnp1, errhp, 1,
	ssn, sizeof(ssn), SQLT_STR, 0, 0, 0, OCI_DEFAULT)) {
	fprintf(stderr, "There was an error on 'OCIDefineByPos 1'\n");
	cleanup(envhp);
	do_exit(EXIT_FAILURE);


	}


    if (OCIDefineByPos(select, &defnp2, errhp, 2,
	name, sizeof(name), SQLT_STR, 0, 0, 0, OCI_DEFAULT)) {
	fprintf(stderr, "There was an error on 'OCIDefineByPos 2'\n");
	cleanup(envhp);
	do_exit(EXIT_FAILURE);


	}


    if ((result = OCIStmtExecute(svchp, select, errhp, 0, 0, NULL, NULL,
	OCI_DEFAULT)) != 0) {
	fprintf(stderr, "There was an error on 'OCIStmtExecute 1'\n");
	checkerr(errhp, result);
	cleanup(envhp);
	do_exit(OCI_ERROR);


	}


    while (OCIStmtFetch(select, errhp, 1, OCI_FETCH_NEXT, OCI_DEFAULT) == 0) {
	counter++;


	}

    return counter;
}

void
prepare_statement(text *statement, text *group) {

    strcpy(statement,
    "SELECT ssn, name FROM claim WHERE parent_id = ");     strcat(statement, group);

    return;
}

int
main(int argc, char *argv[])
{

OCIEnv		*envhp = NULL;
OCIError	*errhp = NULL;
OCISvcCtx	*svchp = NULL;

text		statement[240];
text		group[64];
int		predicted;
int		actual;

    memset(group, 0, sizeof(group));
    memset(statement, 0, sizeof(statement));

    initialize(&envhp, &errhp);
    loginandopen(envhp, errhp, &svchp);

    deal_with_args(argc, argv, envhp, errhp, svchp, group);

    predicted = count_records(envhp, errhp, svchp, group);     printf("predicted: %d\n", predicted);

    prepare_statement(statement, group);     actual = perform_query(envhp, errhp, svchp, statement);     printf("actual: %d\n", actual);

    exit (0);
} Received on Sun Jul 13 2003 - 20:04:27 CDT