Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:

I am running a simple loop in perl which does an update to an oracle table. The updates are not taking unless I disconnect from the database and reconnect. I have a finish command at the end of the loop. Any ideas?
$sql="UPDATE $table SET X='$value' WHERE Y='$where'";
$sth = $dbh->prepare($sql);
$sth->execute;
$sth->finish();
#&disconnect;
#&connect; #only works if these two lines are not commented
  • Comment on Multiple Updates with SQL in one connection

Replies are listed 'Best First'.
Re: Multiple Updates with SQL in one connection
by Rhandom (Curate) on Jan 24, 2002 at 02:36 UTC
    If autocommit is not on by default or turned on when you first connect and get your $dbh you will need to commit as in: 

    $dbh->commit() unless $dbh->{AutoCommit};

    [download]

    my @a=qw(random brilliant braindead); print $a[rand(@a)];

[reply]
[d/l]
      Thanks! That was what I needed...
[reply]
Re: Multiple Updates with SQL in one connection
by lachoy (Parson) on Jan 24, 2002 at 02:41 UTC

    You don't mention whether you have AutoCommit set, but this is a common error when it's not. You might want to try something like this, assuming the data you want to update are arrayrefs in @data:

      $dbh->{RaiseError} = 1; # if not already
  my $sql = "UPDATE $table SET X = ? WHERE Y = ?";
  my $sth = $dbh->prepare( $sql );
  eval {
    foreach my $d ( @data ) {
      $sth->execute( $d->[0], $d->[1] );
    }
  };
  if ( $@ ) {
    print "Caught error: $@\nRolling back.\n";
    $dbh->rollback;
  }
  else {
    $dbh->commit;
  }

    [download]

    Chris
    M-x auto-bs-mode

[reply]
[d/l]
Re: Multiple Updates with SQL in one connection
by MungeMeister (Scribe) on Jan 24, 2002 at 03:25 UTC
    You've got the answer of "if AutoCommit is turned off...". So here's how to turn AutoCommit on (which will automatically commit all inserts/updates/deletes). 

    $dbh = DBI->connect($data_source, $user, $pass, { AutoCommit => 1 } );

    [download]
    1 is on, 0 is off.

    This is from Programming the Perl DBI from O'Reilly. It can also be found in perldoc DBI, or here.

    UPDATE: I forgot to mention, this is considered a bad thing to do these days. But, given caution, it can be a nice feature to use on purpose. Unfortunately, the system I'm on has AutoCommit turned on by default, and that's bitten me a couple of times... :-(

    MungeMeister
[reply]
[d/l]
[select]
      The default AutoCommit value (on) is not necessarily bad. This depends on the database system you are connecting to, and how that system handles transactions.

      AutoCommit turned on is the natural mode for Sybase and MS-SQL database servers, for example.

      Michael

[reply]
Re: Multiple Updates with SQL in one connection
by tradez (Pilgrim) on Jan 24, 2002 at 02:49 UTC
    You either must set autocommit on, or do a $dbh->commit();
[reply]

Back to Seekers of Perl Wisdom