Re^2: Superior way to update SQL tables

The methodology you describe is exactly what I am doing. The idea of attempting the update from within the fail block is interesting, and something I hadn't considered. Thank you!

I note that you use $dbh->do rather than prepare/execute. Obviously, that is easier, but are there caveats to running several million do's as opposed to one prepare and millions of execute(@values)?

Also, is there a good reason not to do:

foreach (my @row = $sth->fetchrow_array) {
    eval {
        $dbh2->do( $insert_statement, {}, @row );
    }; if ($@) {
        eval {
            $dbh2->do( $update_statement, {}, @row );
        }; if ($@) {
            addto_Skip(@row);
        }
    }
}
[download]

^{--

$me = rand($hacker{perl});}

Comment on Re^2: Superior way to update SQL tables Download Code

Replies are listed 'Best First'.
Re^3: Superior way to update SQL tables by dragonchild (Archbishop) on Sep 08, 2004 at 15:49 UTC
I used do() for illustrative purposes. I would actually recommend using prepare_cached()/execute() instead of prepare()/execute(). do() has to re-prepare the statement, which can take time. As for your addto_Skip(@row) thing, that's up to you. If you expect to have rows that you cannot insert or update, that would be a useful thing to have. You can also do something like: `while (my @row = $sth->fetchrow_array ) { eval { $sth_insert->execute( @row ) }; next unless $@; eval { $sth_update->execute( @row ) }; next unless $@; addto_Skip( @row ); }` [download] Though, personally, I would look at using fetch() with bind_columns(), as the best performance option that DBI supports. This is instead of the fetchrow_array(). ------ We are the carpenters and bricklayers of the Information Age. Then there are Damian modules.... sigh* ... that's not about being less-lazy -- that's about being on some really good drugs -- you know, there is no spoon.* - flyingmoose I shouldn't have to say this, but any code, unless otherwise stated, is untested	[reply] [d/l]

Replies are listed 'Best First'.

Re^3: Superior way to update SQL tables
by dragonchild (Archbishop) on Sep 08, 2004 at 15:49 UTC

As for your addto_Skip(@row) thing, that's up to you. If you expect to have rows that you cannot insert or update, that would be a useful thing to have.

You can also do something like:

while (my @row = $sth->fetchrow_array )
{
    eval { $sth_insert->execute( @row ) };
    next unless $@;

    eval { $sth_update->execute( @row ) };
    next unless $@;

    addto_Skip( @row );
}
[download]

Though, personally, I would look at using fetch() with bind_columns(), as the best performance option that DBI supports. This is instead of the fetchrow_array().

------
We are the carpenters and bricklayers of the Information Age.

Then there are Damian modules.... *sigh* ... that's not about being less-lazy -- that's about being on some really good drugs -- you know, there is no spoon. - flyingmoose

I shouldn't have to say this, but any code, unless otherwise stated, is untested

[reply]
[d/l]