Beefy Boxes and Bandwidth Generously Provided by pair Networks
good chemistry is complicated,
and a little bit messy -LW

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]
[stevieb]: I am thankful that I fell into Perl years ago in the manner I did. I don't believe there is another language that people care about so much, that they're willing to give everything. Langs come and go, but in Perl, it seems, things remain consistent.
[stevieb]: The people back then (~2000) are the same people now, but with much more experience. I'm appreciative that I fell into the Perl rabbit hole.
[stevieb]: Coding circles around Python devs, showing how unit tests should be done, enabling easy access to everything, learning basic C to wrap for direct hardware access... I am thankful.
[stevieb]: I'm in an appreciative mood. Thank you fellow Monks, for all I know, and for the knowledge which I can pass on

How do I use this? | Other CB clients
Other Users?
Others cooling their heels in the Monastery: (6)
As of 2017-02-25 01:22 GMT
Find Nodes?
    Voting Booth?
    Before electricity was invented, what was the Electric Eel called?

    Results (364 votes). Check out past polls.