Re: shortcuts for common placeholder idioms...

[Moritz von Schweinitz]

better late than never..

i'd just like to recommend some more hash-friendly routines for these 
kind of operations. i know that they are relativly tricial to code, but 
would make it all seem more "natural" for the beginners, methinks.

something like

$dbh->hash_do("INSERT INTO table (?) VALUES (?)", \%hash);

(where the first ? would be expanded to something along the lines of
'join(',', keys %hash)'
 and the second ? would become something like
'join(',', map($dbh->quote($_), values %hash))'
maybe even some automatic matching of the keys with their appropiate 
cols, so that something like

$dbh->hash_do("INSERT INTO table ?", \%hash);

would take care of 'aligning' the appropiate hash-values with their columns?

i am aware of the performance-hit that must be in there somewhere, but i 
fully admit that IMHO extensive usage of hashes makes DBI code more 
'readable'.

usage of '?' for these purposes is sub-optimal too, of course.

overall, i'd bet that users are more often confronted with information 
in hashes (CGI ?) than in csv format.

but then again, it's 5am, and my coffee machine broke down yesterday :-)

M.

Tim Bunce wrote:
foreach ( $cgi->param() ) {
  push @cols, $_;
  push @vals, $cgi -> param( $_ );
}
my $sql = "INSERT INTO table ( " . join( ", ", @cols ) . " )\n" .
  "   VALUES ( " . join( ", ", map { "?" } @cols ) . " )";


Seeing all that 'line noise' makes me think we need a neater way.

Personally I'd have written it more like

my $sql = qq{
        INSERT INTO table
               ( } . join( ", ",         @cols ) . qq{ )
        VALUES ( } . join( ", ", ("?") x @cols ) . qq{ )
};
but that's not really that much clearer and possibly more
'magical' to the novice who may not be familiar with 'x' as a
list replication operator.
But imagine the DBI provided these two functions:

sub comma_separated_values { join ", ", @_ }
sub comma_separated_placeholders { join ", ", ("?") x @_ }
you could then write the statement like this:
separated
my $sql = qq{
        INSERT INTO table
               ( ${\comma_separated_values(@col)} )
        VALUES ( ${\comma_separated_placeholders(@col)} )
};
The "... ${\...} ..." is a little magical but not hard to master.
It's just a cute way to evaluate expressions inside a double quoted
string. The ${...} part expects a reference to a scalar so that's
what the backslash before the function name is for. The backslash
takes a reference to the scalar value returned by the function and
the ${...} then interpolates that into the string.
Perhaps we could call the functions dbi_csv and dbi_csp then we'd have

my $sql = "INSERT INTO table ( ${\dbi_csv(@col)} ) VALUES ( ${\dbi_csp(@col)} )";

which seems rather neater than where we started:

my $sql = "INSERT INTO table ( " . join( ", ", @cols ) . " )\n" .
   "   VALUES ( " . join( ", ", map { "?" } @cols ) . " )";
Comments?

Tim.