OCIStmtPrepare & OCIStmtExecute questions????

From: Kevin Boyes <kboyes_at_mpsrv3.multipath.com>
Date: 12 Apr 1999 13:57:07 -0400
Message-ID: <7etc5j$b72$1@mpsrv3.multipath.com>

I am experimenting with array-bound select statements coded with OCI8. Here is some (pseudo) code:

• code snippet ------------------------

sword r;
text* stmt = "select id from kbtable";

r = OCIStmtPrepare(stmth, errhp, stmt, strlen(text), OCI_NTV_SYNTAX,

                     OCI_DEFAULT);

checkerr(r);

OCIDefine* defnh = NULL;

const int ARRAY_SIZE = 10;
const int vbuf_len = 5 + 1; // smaller than schema field definition

char       vbuf[ ARRAY_SIZE ][ vbuf_len ];
sb2        vind[ ARRAY_SIZE ];

r = OCIDefineByPos(stmth, &defnh, errhp, 1, vbuf, vbuf_len, SQLT_CHR, vind,
                    0, 0, OCI_DEFAULT);

checkerr(r);

r = OCIStmtExecute(svchp, stmth, errhp, ARRAY_SIZE, 0, 0, 0, OCI_DEFAULT); checkerr(r);

• code snippet ------------------------

OCIStmtExecute seems to behave inconsistently with regards to truncating fields. For example, let the length of the KBTABLE.id field be greater than vbuf_len.
 (ie, create table kbtable ( id varchar2(10) );

case 1:
  If the total number of rows in KBTABLE is less than the array size,   then OCIStmtExecute() will return OCI_NO_DATA. The indicator   variables (vind) will be set to 10 (to indicate that   the field has been truncated and gives the length of the field).

case 2:
  If the total number of rows in KBTABLE is greater than (or equal to)   the array size, then OCIStmtExecute() will return OCI_ERROR.   The error returned by OCIErrorGet is

     "ORA-01406: fetched column value was truncated"

This behaviour seems inconsistent to me!

Why doesn't case #2 return OCI_SUCCESS (or maybe _WITH_INFO) and set the   indicator variables appropriately?
Is this an OCI bug?
Can someone please explain how this behaviour is desirable? Why should the 'number of table rows' versus 'the array size' effect the   handling of truncated fields?

Since I'm here, how about another question...

In the code snippet above, what should the scope of the statement text be? What happens if I build the statement dynamically and the buffer containing   the text gets free'd before the statement handle is free'd but after the   call to OCIStmtPrepare()?
I seem to remember a comment in the OCI8 manual regarding this but I have   not been able to find it.
Do I have to keep the statement buffer in scope for the duration of the   statement handle?

Please forward posting replies to: kboyes_at_stardata.ca

In case it matters:
  Oracle8 Enterprise Edition Release 8.0.4.0.0 - Production   Solaris 2.6
  WorkShop Compilers 4.2 30 Oct 1996 C++ 4.2

Many thanks,
Kevin Boyes.
Star Data Systems.
--
--

Kevin Boyes                kcboyes_at_yahoo.com
                           Star Data Systems Inc.

--
Received on Mon Apr 12 1999 - 12:57:07 CDT