Re: DBI - Handling NULL values

Yes. Use placeholders.

unshift @fields, qw(hostname host_id);
my $insert = "INSERT IGNORE INTO $thisTable (" . join(", ", @fields) .
+ ") VALUES (" . join(",", ('?') x @fields) . ")";

while (my @row = $dbq->fetchrow_array) {
  my $this_insert = [ $site_hostname, $site_id, @row ];
  push @central_insert, $this_insert;
}
[download]

Then you will have to prepare the $insert, and execute that statement handle while passing in the variables:

my $sth = $dbh->prepare($insert);
$sth->execute(@$_) for @central_insert;
[download]

Of course, add in some error handling, and probably turn off autocommit if there's lots of inserts going on at a time.

Try a Super Search for placeholders - should turn up a lot of hits.

Comment on Re: DBI - Handling NULL values Select or Download Code

Replies are listed 'Best First'.
Re^2: DBI - Handling NULL values by McDarren (Abbot) on Sep 29, 2005 at 06:03 UTC
Excellent, thanks - that works a treat:) I'd actually used placeholders in other parts of the same script, but it hadn't occurred to me to use them here. And of course by using placeholders, the line: `join("," , map {qq("$_")} @row)` [download] in my original example becomes no longer necessary, as the quoting is automagically taken care of by DBI. Thanks again, --Darren	[reply] [d/l]
Re^2: DBI - Handling NULL values by davidrw (Prior) on Sep 29, 2005 at 12:33 UTC
An alternative to the `join`'s and building the multiple `?`'s is using SQL::Abstract: `use SQL::Abstract; my $SA = SQL::Abstract->new; while (my @row = $dbq->fetchrow_array) { my %fields; @fields{@fields} = ( $site_hostname, $site_id, @row ); # hash slic +e to set %fields my ($sql, @bind) = $SA->insert( $thisTable, \%fields ); push @central_insert, [ $sql, \@bind ]; } ... $dbh->do( $_->[0], {}, @{$_->[1]} ) for @central_insert;` [download] In this case, you lose the ability to prepare the statement, but in general SQL::Abstract can be very handy... Good example is with dynamic where clauses. (i'm also not sure why OP needs to queue up the insert's for later and just doesn't do it in the main loop...)	[reply] [d/l] [select]