Re: Problem with Cursor and Commit

From: Richard Piasecki <ogo_at_mailexcite.com>
Date: 15 Mar 2001 22:36:06 -0600
Message-ID: <3ab19799$0$16679$45beb828@newscene.com>

Rick:

I just wrote a simple little program to test the affect of a submit on an open
cursor under Solaris, and discovered that a commit has no adverse affect on an open cursor. The code appears below. Table "FOO" has eight records of two numbers and table "BAR" has no records. The code segment reads the entries from FOO and places them into BAR, committing after every 2nd row. I don't know, however, if there is a difference under HP/UX.

    EXEC SQL DECLARE MyCur CURSOR FOR
    SELECT * FROM FOO;     EXEC SQL WHENEVER NOT FOUND DO break;     EXEC SQL OPEN MyCur;
    count = 1;
    while(1){

        EXEC SQL FETCH MyCur INTO :f1, :f2;

        EXEC SQL INSERT INTO BAR VALUES (:f1,:f2);

        if(count == 2){
            count = 1;
            EXEC SQL COMMIT;
        }else{
            count++;
        }

    }

    EXEC SQL COMMIT WORK RELEASE;

• Rich

"Rick Agolia @ Home" <sagolia01_at_earthlink.net> wrote in message news:jbWr6.15077$R_6.1563005_at_newsread2.prod.itd.earthlink.net...
> I have a situation where I'm setting a cursor on a very large table
 (between
> 3 and 10 million records). I'm extracting data from each row in that
 table
> and writing it to a second table. I want to do a commit at some interval
> (about 1000 updates). The problem that I'm having is that performing the
> commit on the updates is closing the cursor on the 'input' table.
>
> The apps are written in Pro-C on an HP-Unix box. Help, I don't really
 want
> to go 3 million recs without a COMMIT. Actually, I may be committed if I
> let that go into production. The program was written MODE=ORACLE.
>
>
>
Received on Thu Mar 15 2001 - 22:36:06 CST