Re: Understanding placeholders

instead of

foreach my $record (@filedata) {
    my $sql = "INSERT INTO $table VALUES ($record)";
    my $rows = $dbh->do($sql);
}
[download]

you could do something like:

foreach my $record (@filedata) {
    my @fields = split /,/, $record;
    my $placeholders = "?," x scalar (@fields);
    chop $placeholders; # remove the end ','
    my $sql = "INSERT INTO $table VALUES ($placeholders)";
    my $sth = $dbh->prepare($sql);
    my $rows = $sth->execute(@fields);
    $sth->finish();
}
[download]

and, of course, if all the lines of @filedata are for the same table, you could move some of that code outside the loop.

anders pearson

Comment on Re: Understanding placeholders Select or Download Code

Replies are listed 'Best First'.
Re: Re: Understanding placeholders by perlplexer (Hermit) on May 03, 2003 at 22:04 UTC
I would discourage the usage of "split /,/, $record" as the method for determining the number of fields and, therefore, placeholders. The problem here is that his data fields may contain commas in them; e.g., 'foo','bar',1,23,'foobar,bar,foo' in which case "split /,/, $record" won't return proper results. Text::CSV and DBD::CSV are probably more appropriate. --perlplexer	[reply]
Re: Re: Understanding placeholders by nedals (Deacon) on May 04, 2003 at 16:24 UTC
thraxil: Still have to test, but this looks good. I've recoded, as suggested, to move most of the code outside the loop. perlplexer: There are indeed commas in the data so I plan to change the 'seperator' to '::'. But I'll certainly take a look at the 2 links you provided Thanks to both!	[reply]
Re: Re: Understanding placeholders by dragonchild (Archbishop) on May 06, 2003 at 13:58 UTC
`foreach my $record (@filedata) { my @fields = split /,/, $record; my $placeholders = join ',', (('?') x @fields); my $sql = <<"END_SQL"; INSERT INTO $table VALUES ($placeholders) END_SQL my $sth = $dbh->prepare_cached($sql) \|\| die "Could not prepare '$sql'\n"; $sth->execute(@fields) \|\| die "Could not execute '$sql'\n"; $sth->finish; }` [download] That's the first bit - use join and check the return values. However, `$record` should be an arrayref, not a string. Which would mean that the split disappears. But, that's further back up the chain. ------ We are the carpenters and bricklayers of the Information Age. Don't go borrowing trouble. For programmers, this means Worry only about what you need to implement. Please remember that I'm crufty and crochety. All opinions are purely mine and all code is untested, unless otherwise specified.	[reply] [d/l] [select]