Re: OCI 8.0 Programming Questions

Hello Chris,

1. Question: I am not aware of any manual except Oracle 8.0 Programmer's Guide.
2. Question: See Programmer's Guide, page 2-29 (Indicator Variables).
3. Question: Use the SQLT_ODT type mapping, see Programmer's Guide, page 9-7

Regards,
Reinhard

"Mr. Blue" wrote:

> 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 Dec 14 1998 - 00:00:00 CET