Re: Novice OCI questions (2)

CMSC 424 <nr31_at_marple.umd.edu> writes:

>
>
> 1. When I try to retrieve records using the OCI library, I seem to
> not get back records that have null values in the fields which I am
> selecting. For instance, if I have two records with fields name and
> address, and for the second record I have a null address, the
> following snippet retrieves only the first record. Is this supposed to
> happen, and is their a way I can make it not do this, but retrieve all
> the records, irregardless of what data the have or don't have.
>
> Running:
> Oracle7 Server Release 7.3.2.1.0 with the 64-bit option - Production Release
> PL/SQL Release 2.3.2.0.0 - Production
> Dunno which version of OCI lib...
> -rw-r--r-- 1 oracle dba 4112384 Sep 25 1996 libclntsh.so.1.0
>
> Note: In the database, name is a CHAR(15) and address VARCHAR2(63)..
>
> struct person {
> char name[16];
> char address[64];
> } newPerson;
>
> ub2 lenName,codeName,lenAdd,codeAdd;
>
> if ( oopen (&CDA,&LDA,NULL,-1,-1,NULL,-1) )
> handleError (&LDA,&CDA,"oopen - opening the cursor");
>
> if ( oparse (&CDA,(text *)"select name,address from person_table",
> (sb4)-1,(sword)PARSE_DEFERRED,(ub4)V7_BEHAVIOR) )
> handleError (&LDA,&CDA,"oparse - parsing the query");
>
> if ( odefin (&CDA,(sword)1,(ub1 *)newPerson.name,
> (sword)sizeof (newPerson.name),
> (sword)STRING_TYPE,(sword)-1,NULL,NULL,(sword)-1,
> (sword)-1,&lenName,&codeName) )
> handleError (&LDA,&CDA,"odefin - name");
> if ( odefin (&CDA,(sword)1,(ub1 *)newPerson.address,

This was a typo. I was trying to mock up a short example (since my real query retrieves about 30 values. I *do* number the columns correctly in the real code, so this is not the problem (if it were I would get no rows back at all, I think). Sorry if I misled anyone.

> (sword)sizeof (newPerson.address),
> (sword)STRING_TYPE,(sword)-1,NULL,NULL,(sword)-1,
> (sword)-1,&lenAdd,&codeAdd) )
> handleError (&LDA,&CDA,"odefin - address");
>
> if ( iReturnCode = oexfet (&CDA,(ub4)1,(sword)0,(sword)0) )
> handleError (&LDA,&CDA,"oexfet - executing and fetching");
> while ( iReturnCode == 0 ) {
> iReturnCode = ofetch (&CDA);
> }
>
> 2. According to the manual I have (Oracle Developer Guide), I should
> be able to fetch until I get CDA.rc equal to 1403. However, I never
> get CDA.rc non-zero, even if there are no records retrieved. So I
> have just been fetching until the return code from fetch went
> non-zero, which *it* does when there are no more records. So, uh, is
> this right?
>
> My extreme gratitude for any help,
> John Guthrie
> University of Maryland, College Park
Received on Sat May 03 1997 - 00:00:00 CEST