Re: open cursor with hold question

[James D. White]

You could try using two connect calls with two different DB handles. 
Prepare the select using one
DB handle and insert with the other. A commit on the second DB handle 
should not close the
fetchrow_array cursor on the first.

James D. White   ([EMAIL PROTECTED])
Department of Chemistry and Biochemistry
University of Oklahoma
101 E. Constellation, SRTC 2100
Norman, OK 73019
Phone: (405) 325-4912, FAX: (405) 325-7762


Liu, Jenny wrote:

Hi,

How could I declare an open cursor with hold in a perl DBI program?

I have to do a commit for every 30000 insertion to avoid long transaction.
But the commit will close my 
($patkey,$extid) = $sth->fetchrow_array) cursor.  I got an SQL error 400.

Please advise.

Thanks

Jenny



The following is part of the program:

            $Sql_stmt = qq(insert into misys_ifc:ifc_ob_assoc_det  values \( 0,
CURRENT, ?, ?, ?\) );
        my $sth2 = $dbh->prepare($Sql_stmt);
        $Sql_stmt = qq(select  patkey, ext_id from pat_ext_info where
accttype='P'
                                       and co_source=?);
        $sth = $dbh->prepare($Sql_stmt with hold);
        $sth->execute($company);
    my $count=0;
     while (($patkey,$extid) = $sth->fetchrow_array){
               $sth2->execute($patkey, $extid, $p_serial_key);
               $count++;
               if($count%30000==0){
                      $dbh->commit;   #########  THIS IS THE ONE GIVE ME
TROUBLE
               }
       }
       $dbh->commit;
       undef $sth1;
       undef $sth2;