Cirollo has asked for the wisdom of the Perl Monks concerning the following question:
I'm trying to write a subroutine that, given a hash of column=>value pairs, will generate a SQL statement to insert those pairs into a database.
So, a statement might look something like INSERT INTO table (col_1, col_1, col_3) VALUES ('foo', 'bar', 'baz')
So, I would generate that string with the hash:
%data = ('col_1'=>'foo',
'col_2'=>'bar',
'col_3'=>'baz');
And then say
insert_hash("table", \%hash);
Here's my subroutine; it seems to work, but I'm hoping that somebody can give me pointers on any better or more elegant ways of doing it.
sub insert_hash {
my $table_name = shift;
my $rowref = shift;
my %row = %$rowref;
# ?c? and ?v? are placeholders
# Any better ideas for placeholders to pretty up the regexes?
my $sql = "insert into $table_name (?c?) values (?v?)";
foreach (keys %hash) {
$sql =~ s/\?c\?/$_,\?c\?/g;
$sql =~ s/\?v\?/$hash{$_},\?v\?/g;
}
# Pull off the last comma (extraneous) and the placeholders
$sql =~ s/,\?c\?//g;
$sql =~ s/,\?v\?//g;
return $sql;
}
Re: A more elegant solution?
by runrig (Abbot) on Jun 05, 2001 at 01:51 UTC
|
Use placeholders. Substitute your hash keys in the SQL statement, but substitute the values in the execute. Generate the correct number of '?'s based on the number of fields in the hash.
see What are placeholders in DBI, and why would I want to use them?.
Or just execute the statement in the sub.
Update:Took out the map and put in the 'x' operator like merlyn has. sub insert_hash {
my $table = shift;
my $ref = shift;
# Sort fields so we can cache the handle effectively
my @fields = sort keys %$ref;
my @values = @{$ref}{@fields};
my $sql = "insert into $table (" . join(",", @fields).
") values (". join(",", ("?") x @fields).")";
my $sth = $dbh->prepare_cached($sql);
$sth->execute(@values);
}
| [reply] [Watch: Dir/Any] [d/l] |
|
In particular, your code ends up as:
sub insert_hash {
my $table_name = shift;
my $rowref = shift;
my %row = %$rowref;
my $dbh = shift; # extra parameter needed now
my $places = join ",", ("?") x keys %row;
my $sql = "insert into $table_name ($places) values ($places)";
$dbh->do($sql, undef, keys %rows, values %rows);
}
-- Randal L. Schwartz, Perl hacker | [reply] [Watch: Dir/Any] [d/l] |
|
Except I want to sort for handle-caching purposes. It doesn't count as the same handle if keys/values are in a different order, so I sort. No, I'm not sure if the benefit of caching outweights the cost of sorting. I'll let someone else benchmark. Probably depends on the database anyway :)
Update:Umm, I DID use prepare_cached :) DBI won't do the hard work for me, though, unless I make sure the statement stays the same. And the statement won't stay the same unless I sort the field names (DBI's prepared_cached uses the entire SQL statement as a hash key). It also helps, though, to keep statements the same so that the db itself (well, some db's) can cache the statement w/o re-parsing the statement over several executions of the same script (not as efficient as perl/DBI caching, but every bit helps. I did not know you could do that join that way w/o map though :)
| [reply] [Watch: Dir/Any] |
|
|
IIRC, the DBI spec doesn't support using placeholders
for the column names, only values. Programming the Perl DBI, p 221 says the following:
With most drivers, placeholders can't be used for
any element of a statement that would prevent the database
server from validating the statement and creating a
query execution plan for it. For example:
"SELECT name, age FROM ?" #wrong (will probably fail)
"SELECT name, ? FROM people" #wrong (but may not 'fail')
While this may not fail with some databases like MySQL,
it *may* fail on databases like Oracle that natively support
placeholders. (can anyone confirm this?)
| [reply] [Watch: Dir/Any] [d/l] |
|
|
D'oh. *slaps forehead*
Another classic case of focusing on one solution, so the other one that is staring you in the face gets ignored.
But, these hashes will not have a fixed number of elements; do I need to put a string of ?, ?, ... , ? in my statement and then just pass a list through execute? Or is there some placeholder that says "put a list here"?
| [reply] [Watch: Dir/Any] [d/l] |
|
But, these hashes will not have a fixed number of elements; do I need to put a string of ?, ?,
... , ? in my statement and then just pass a list through execute? Or is there some
placeholder that says "put a list here"?
Well, if there is not an unholy number of combinations of
columns, then this is not a bad solution. If there is going to be
a large number of 'different' statements prepared, then
this is still ok, but I would just use prepare() instead of prepare_cached.
| [reply] [Watch: Dir/Any] |
|
Why use prepare_cached?It is mainly of use when you have a few statements, that you execute often, but where you are unable to hold a reference to the $sth.
And it is only usefull if the driver supports it. (Not all drivers do presently).
You should also note that for some databases prepared statements and placeholders take more time than just $dbh->do'ing the statement with values in place. With those databases the only advantage of placeholders is that you don't have to do quoting - which is a big advantage in this situation, as we don't beforehand know the type of the columns, whether numeric or string. And yes that does make a big difference - some databases refuse to accept numbers in quotes as numbers!
| [reply] [Watch: Dir/Any] |
|
| [reply] [Watch: Dir/Any] |
Re: A more elegant solution?
by Brovnik (Hermit) on Jun 05, 2001 at 02:35 UTC
|
Or, use the set notation :
my $sql = "INSERT INTO $table_name SET ";
for (keys %hash)
{
$sql .= "$_ = '$hash{$_}' ,";
}
chop $sql;
Ought to do it. It is better to call $dbh->quote($hash{$_})
to quote the string value if you have that handle.
-- Brovnik | [reply] [Watch: Dir/Any] [d/l] |
|
Not alway legal.Accourding the the databases I use, SET is only allowd in an UPDATE statement.
You should definitely always use $dbh->quote() insted of just adding '-characters. The latter will fail if the string contains any 's (if you get my meaning).
| [reply] [Watch: Dir/Any] |
(dkubb) Re: (1) A more elegant solution?
by dkubb (Deacon) on Jun 05, 2001 at 10:00 UTC
|
sub insert_into {
my $table = shift;
my $row = shift;
return sprintf(
'INSERT INTO %s (%s) VALUES (%s)',
$table,
join(', ', keys %$row),
join(', ', ('?') x keys %$row),
);
}
| [reply] [Watch: Dir/Any] [d/l] |
Re: A more elegant solution?
by princepawn (Parson) on Jun 05, 2001 at 05:39 UTC
|
There are several such modules on CPAN, the most powerful
of which is DBIx::Recordset
| [reply] [Watch: Dir/Any] |
|
|