My update on insert error solution

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

Hi there Monks,

Lately I've been working a lot with storing CSV content (after some parsing of the data) in a database. In a lot of cases the data allready is in the database, in which case I want to update it. Familiar problem I guess. I came up with this solution:

  connectDB();
  my $insert = "Insert Into table (id,name) Values (?,?)";
  my $update = "Update table set name = ?  Where id=?";
  my $sth_insert = $dbh->prepare($insert) or die $dbh->errstr;
  my $sth_update = $dbh->prepare($update) or die $dbh->errstr;

  for my $data (@$data_ref){
    if (! $sth_insert->execute(
                                $$data{'id'},
                                $$data{'name'}
                               ) ){
      print 'Insert error: '.$sth_insert->errstr if $debug;
      print "Doing update\n";
      $sth_update->execute(
                                $$data{'name'},
                                $$data{'id'}
                          ) or die("Insert and Update failed, update: 
+".$sth_update->errstr );
    }
  }
  $sth_insert->finish();
  $sth_update->finish();
  disconnectDB();
[download]

As it turns out the $sth_update is never executed, or at least the database does not reflect it... The insert goes as expected. I do get the "Doing update" message. Any ideas on what Im doing wrong here?

Comment on My update on insert error solution Download Code

Replies are listed 'Best First'.
Re: My update on insert error solution by roboticus (Chancellor) on Dec 30, 2018 at 23:53 UTC
PeterKaagman: It's hard to see how $sth_update wouldn't execute if you're seeing the "Doing update" message. How do you know it's not executing? Could a later update be overwriting the data from an earlier update? I'd suggest making your traces a little more informative, so you can see what's happening: `if (! $sth_insert->execute( $$data{'id'}, $$data{'name'} ) ) { print 'Insert error: '.$sth_insert->errstr if $debug; print "Doing update (name: $$data{name}, id: $$data{id})\n"; $sth_update->execute( $$data{'name'}, $$data{'id'} ) or die("Insert and Update failed, update: +".$sth_update->errstr ); } else { print "Inserted (name: $$data{name}, id:$$data{id})\n"; }` [download] Then you can verify that the values in the table match the last value for the ID found in your debug traces. ...roboticus When your only tool is a hammer, all problems look like your thumb.	[reply] [d/l]
Re: My update on insert error solution by poj (Abbot) on Dec 31, 2018 at 14:03 UTC
If using MySQL then consider REPLACE or INSERT ON DUPLICATE, for example `#!/usr/bin/perl use strict; use DBI; my $dbh = get_dbh(); my $sql = "INSERT INTO id_name (id,name) VALUES (?,?) ON DUPLICATE KEY UPDATE name=?"; my $sth = $dbh->prepare($sql); for my $data (@$data_ref){ $sth->execute( @$data{'id','name','name'} ); } sub get_dbh { my $database = "pm"; my $user = "user"; my $pw = "password"; my $dsn = "dbi:mysql:$database:localhost:3306"; my $dbh = DBI->connect($dsn, $user, $pw, { RaiseError=>1, AutoCommit=>1 } ); return $dbh; }` [download] poj	[reply] [d/l]
Re: My update on insert error solution by bliako (Abbot) on Dec 31, 2018 at 10:35 UTC
The manpage for DBI is quite extensive for what an `execute()` returns. It includes `undef`, integers and `0E0`. It also says that : If execute() is called on a statement handle that's still active ($sth->{Active} is true) then it should effectively call finish() to tidy up the previous execution results before starting this new execution. It is not clear to me if "should effectively call finish()" is yours or execute()'s responsibility. DB errors become fatal if you set `RaiseError=>1`. However, if you see the "Doing update" message then obvioudly this isn't the case. A few days ago I was in similar insert/update situation and decided to check first if a record already exists before updating or inserting using something I found on the net: `SELECT EXISTS(SELECT 1 FROM tablename WHERE abc LIMIT 1)` which returns 0 or 1.	[reply] [d/l] [select]
Re: My update on insert error solution by erix (Prior) on Dec 31, 2018 at 13:53 UTC
Maybe you just need to dbh->commit (to prevent an automatic rollback)?	[reply]
Re: My update on insert error solution by PeterKaagman (Beadle) on Dec 31, 2018 at 15:16 UTC
Not to my surprise: I goofed up I had mix up with the hash indexes: had to add an identifier for location. Which in dutch is a "lokatie". Coming from a dutch database the fields have dutch like names, like "idblok" for "base location identifier". I wrote it in english: "idbloc" which gave me a null vallue. Case solved... ty all for your thoughts on the problem... found out adding the extra debugging info	[reply]
Re: My update on insert error solution by PeterKaagman (Beadle) on Dec 31, 2018 at 14:49 UTC
On the bright side... not doing anything stupic as it seems :S Im sure there should be updates to the records since I goofed on adding a certain field on insert. Will go ahead with the extended debug output, good idea, and try the commit. Am using psql and am trying to keep the sql as general as possible. Perhaps have to use a different sql agent in the future. Anyway... thanks for the help... back to my !favorite hobby: debugging	[reply]