Beefy Boxes and Bandwidth Generously Provided by pair Networks
Perl Monk, Perl Meditation

Re: nested dbi queries question

by jarich (Curate)
on Feb 02, 2005 at 07:40 UTC ( #427176=note: print w/replies, xml ) Need Help??

in reply to nested dbi queries question

There are a number of problems with your code. For starters your second statement could have problems if spnum or snum ever include non-numerical tables and other such. You're also clobbering $sth inside your while loop so that the second time through the conditional there's nothing to fetch.

I've rewritten this code with comments, so that you can get an idea of an alternative way:

#!/usr/bin/perl -w use strict; use DBI; # Connect to the database, passing in some very important # flags. my $dbh->connect(... {RaiseError => 1, ShowErrorStatement => 1, AutoCommit => 0}); # etc # Select out the entries we wish to move and delete. # This could probably also be done with a selectall my $set_to_move = $dbh->prepare( "SELECT a, b, c, d, snum FROM sp WHERE NOT EXISTS (SELECT 1 FROM sp, supplier WHERE supplier.snum = sp.snum)"); $set_to_move->execute(); # Prepare the statement for the insertion into sp_err # Note that in your code you were then overwriting the # previous statement handle. # By doing it out here, the code is a little more efficient my $insert = $dbh->prepare( "INSERT INTO sp_err (a, b, c, d, snum) VALUES (?, ?, ?, ?)"); # Prepare the delete statement as well. my $delete = $dbh->prepare( "DELETE FROM sp where snum = ?"); while(my @values = $set_to_move->fetchrow_array()) { # Start a transaction. Thus if we can't do both # operations, neither get done. $dbh->begin; $insert->execute(@values); # -1 isn't special here, it's just the index # (in this case the last position) that snum is in. $delete->execute($values[-1]); # If both of those worked, then end the transaction $dbh->commit; }

I haven't tested any of that, there might be a few typos, but you should get the idea.

Good luck


Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://427176]
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others rifling through the Monastery: (6)
As of 2021-01-26 15:16 GMT
Find Nodes?
    Voting Booth?