OCI 8.0 Programming Questions

From: Mr. Blue <car_at_access2.digex.net>
Date: 1998/11/30
Message-ID: <73u87k$hr0_at_access2.digex.net>#1/1

I am new to Oracle OCI 8.0 programming and have a few questions. I'm trying to get my first program working and I'm running into some problems.

QUESTION 1:
Is there some really good OCI 8.0 documentation out there with examples? I'm using the HTML documentation and examples that come with Oracle 8.0, but it is not very good at all.

QUESTION 2:
How do I run a SELECT statement, bind the retrieved columns to C/C++ variables, loop through the result set, and ask OCI if the column value is NULL or not? Here is a snippet of code I have:

/*****************************************************************************/

// Allocate statement

OCIStmt* theStatement = NULL;
sword rc = OCIHandleAlloc(Env, (void**)&theStatement, OCI_HTYPE_STMT, 0, 0);
// check rc

// Prepare SQL Statement

string SQLString = "SELECT BASEID from MyTable"; rc = OCIStmtPrepare(theStatement, errorHandle,

                    (unsigned char*)SQLString.c_str(),
                    (ub4) SQLString.length(),
                    (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT);

// check rc

// Bind variable to column

long BaseID = 0;
OCIDefine* define1 = NULL;
rc = OCIDefineByPos(theStatement, &define1, errorHandle, 1,

                    (dvoid *)&BaseID, sizeof(BaseID), SQLT_INT,
                    (dvoid *)0, (ub2 *)0, (ub2 *)0, OCI_DEFAULT);

// check rc

// Execute statement

rc = OCIStmtExecute(contextHandle, theStatement, errorHandle,
                   (ub4)1, (ub4)0,
                   (CONST OCISnapshot *)NULL,
                   (OCISnapshot *) NULL, OCI_DEFAULT);

// check rc

// Loop through results

while (rc == OCI_SUCCESS || rc == OCI_SUCCESS_WITH_INFO) {
  rc = OCIStmtFetch(theStatement, errorHandle,

                   (ub4)1, (ub4)OCI_FETCH_NEXT, (ub4)OCI_DEFAULT);

  // check rc
}

/*****************************************************************************/

When I run this I get an error on the OICStmtExecute that says: ORA-01405: fetched column value is NULL

What am I doing wrong?

QUESTION 3:
How do I retrieve a DATE column using OCI? I have a column in my table defined by this DDL:

create table myTable(
// other columns ...

date thedate NULL
// other columns ...

);

I want to bind a C/C++ variable to this column and fetch it using OCI. Here is what I have:

/*****************************************************************************/

// Allocate statement

OCIStmt* theStatement = NULL;
sword rc = OCIHandleAlloc(Env, (void**)&theStatement, OCI_HTYPE_STMT, 0, 0);
// check rc

// Prepare SQL Statement

string SQLString = "SELECT thedate from MyTable"; rc = OCIStmtPrepare(theStatement, errorHandle,

                    (unsigned char*)SQLString.c_str(),
                    (ub4) SQLString.length(),
                    (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT);

// check rc

// Bind variable to column

OCIDate theDate;
OCIDefine* define1 = NULL;
rc = OCIDefineByPos(theStatement, &define1, errorHandle, 1,

                    (dvoid *)&theDate, sizeof(theDate), SQLT_DAT,
                    (dvoid *)0, (ub2 *)0, (ub2 *)0, OCI_DEFAULT);

// check rc

// Execute statement

rc = OCIStmtExecute(contextHandle, theStatement, errorHandle,
                   (ub4)1, (ub4)0,
                   (CONST OCISnapshot *)NULL,
                   (OCISnapshot *) NULL, OCI_DEFAULT);

// check rc

// Loop through results

while (rc == OCI_SUCCESS || rc == OCI_SUCCESS_WITH_INFO) {
  rc = OCIStmtFetch(theStatement, errorHandle,

                   (ub4)1, (ub4)OCI_FETCH_NEXT, (ub4)OCI_DEFAULT);

  // check rc

  // Display Date

  cout   << "\nDATE.year  is: " << theDate.OCIDateYYYY << endl
         << "\nDATE.month is: " << theDate.OCIDateMM << endl
         << "\nDATE.day   is: " << theDate.OCIDateDD << endl
         << "\nDATE.hour  is: " << theDate.OCIDateTime.OCITimeHH << endl
         << "\nDATE.min   is: " << theDate.OCIDateTime.OCITimeMI << endl
         << "\nDATE.sec   is: " << theDate.OCIDateTime.OCITimeSS << endl
         << endl;

}

/*****************************************************************************/

However when I print the date information, I get junk. What am I doing wrong?

Thanks!
Email replies appreciated!

Chris Received on Mon Nov 30 1998 - 00:00:00 CET