statement-commit-statement and INGRES

[Robert Hufsky]

Hello to all,

I have a rather strange problem when using DBI/DBD Ingres 034 on INGRES 2

I want to process a file line by line and perform certain database actions
(insert, delete) according to the data in the files.

I want to commit all changes made after processing one line, so the sequence
goes

prepare SQLstatement 1
prepare SQLstatement 2

while data available {
        interpret data
        execute SQLstatement 1
        execute SQLstatement 2
        commit
}

After I issuing the commit the next statement gives me

"DBD::Ingres: Attempt to execute a statement after commit at  ..."

I just can not believe that I can not issue SQL statements after a commit.

Any hints out there?

A code snipped is appended to this message

kind regards

Robert

----

#!/usr/local/bin/perl -w

use DBI;
use DBI qw(:sql_types);

open (INFILE, "<x1.cbl") or die "Error opening $infile_name";

$db_handle = DBI->connect("dbi:Ingres:plzdb", "plzdba", "", { AutoCommit =>
0, RaiseError => 1 }) or die "Error connecting db";

eval {
        $cc_check_card  = $db_handle->prepare_cached ("select count (*) from
gesp_kre where knr = ? and loc <> 'FMG'");
        $cc_insert_card = $db_handle->prepare_cached ("insert into gesp_kre
(kge, knr, g_d) values (?, ?, ?)");

        $myline = <INFILE>;

        while ($myline = <INFILE>) {

#               ...

                $cc_check_card->execute ('TEST DATA');
                $cc_check_card->bind_col (1, \$this_card_count);
                $cc_check_card->finish;
#               ...

                $cc_insert_card->execute ("DATA", 'MORE DATA', 'SOME MORE');
                $cc_insert_card->finish;

                $db_handle->commit;
        }
        $db_handle->disconnect;
};

if ( $@ ) {
#       $db_handle->rollback;

        printf "Error: $@\n";

        if ( defined $DBI::errstr ) {
                printf "Database Error: $DBI::errstr\n";
        }
}