Re: sqlplus acting differently than embedded SQL in C

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Wed, 04 Nov 1998 14:10:54 GMT
Message-ID: <36415f9b.2631173@192.86.155.100>

A copy of this was sent to reilly_at_news.dca.net (G B Reilly) (if that email address didn't require changing) On 3 Nov 1998 17:11:41 +0500, you wrote:

>I have a problem performing a FETCH into host arrays
>when the key in the WHERE clause is a foreign key.
>When I try to use the FETCH in a C program I get a 1403
>When I try the identical command in sqlplus I get
>the correct rows returned. The table I am trying
>to FETCH from has a foreign key where the rows
>can have identical keys but different values in
>other columns. I cannot find anything in the sample
>programs or Oracle manuals about this.
>
>Any hints?

When array fetching, the 1403 will be raised by the fetch that hit the 'end of data' and that fetch will have returned some rows. When array fetching using proc, need to use a loop that resembles something like this:

static void process()
{
EXEC SQL BEGIN DECLARE SECTION;
    int empno[5];
EXEC SQL END DECLARE SECTION;

    int     i;
    int     last_row_cnt;
    int     sqlCode;

    EXEC SQL WHENEVER SQLERROR DO sqlerror_hard();     EXEC SQL DECLARE C CURSOR FOR SELECT EMPNO FROM EMP;     EXEC SQL OPEN C;     for( last_row_cnt = 0;

         sqlca.sqlcode == 0;
         last_row_cnt = sqlca.sqlerrd[2] )
    {
        EXEC SQL WHENEVER NOTFOUND CONTINUE;
        EXEC SQL FETCH C INTO :empno;

        printf( "===========\nSQLCODE = %d\n", sqlca.sqlcode );

        printf( "Last Row Cnt = %d, sqlca.sqlerrd[2] = %d\n",
                 last_row_cnt, sqlca.sqlerrd[2] );

        printf( "that means we got %d rows this time around\n\n",
                 sqlca.sqlerrd[2]-last_row_cnt );

        for( i = 0; i < sqlca.sqlerrd[2]-last_row_cnt; i++ )
        {
            printf( "empno[%d] = %d\n", last_row_cnt+i, empno[i] );
        }

    }
    EXEC SQL CLOSE C;     printf( "Done\n" );
}

You need to remember the number of rows you've already processed (last_row_cnt in this example). You need to ignore the NOTFOUND warning inside the loop and only break out of the loop after youve processed any and all rows that were fetched. You need to process "sqlca.sqlerrd[2] - last_row_cnt" rows after every fetch -- including the one that raises the 1403 error.

I happen to have 14 rows in my emp table. When I run the above routine, I get as output:

---

SQLCODE = 0
Last Row Cnt = 0, sqlca.sqlerrd[2] = 5
that means we got 5 rows this time around

empno[0] = 7369
empno[1] = 7499
empno[2] = 7521
empno[3] = 7566
empno[4] = 7654

---

SQLCODE = 0
Last Row Cnt = 5, sqlca.sqlerrd[2] = 10 that means we got 5 rows this time around

empno[5] = 7698
empno[6] = 7782
empno[7] = 7788
empno[8] = 7839
empno[9] = 7844

---

SQLCODE = 1403
Last Row Cnt = 10, sqlca.sqlerrd[2] = 14 that means we got 4 rows this time around

empno[10] = 7876
empno[11] = 7900
empno[12] = 7902
empno[13] = 7934

Done

---

So, for the first 2 fetches, sqlcode was 0 and rows fetched was 5. For the last, partial, fetch, the SQLCODE was 1403 telling us that we should fetch no more, but the number of rows we fetched was only 4 so only look at four of the array elements this time...

Hope this helps...  

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA

--
http://govt.us.oracle.com/ -- downloadable utilities  

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation  

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Wed Nov 04 1998 - 08:10:54 CST