Re: A more elegant solution?

[runrig]

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);
}

[merlyn]

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

[runrig]

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 :)

[merlyn]

You can use prepare_cached to cache it automatically. Let DBI do the hard work for you!

-- Randal L. Schwartz, Perl hacker

[dkubb]

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?)

[da]

Here's what happens under oracle:

# fails under oracle
my $sth = $dbh->prepare("select name, age from ?")     

# does not fail under oracle, but gives wrong results.
my $sth = $dbh->prepare("select name, ? from table")  

# outputs n copies of the string "age", for n rows in table.
$sth->execute("age")

___
-DA

[Cirollo]

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"?

[runrig]

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.

[htoug]

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!

[runrig]

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. I've never yet been unable to hold a reference, you can store them in package variables or closures. I'd say its more for when its inconvienient to store the handle, so you lazily decide to let DBI do it for you :)

And I've seen this happen occasionally when people have hashes of column names and values which is when this solution is usually ideal.