Beefy Boxes and Bandwidth Generously Provided by pair Networks
Syntactic Confectionery Delight

Re: Re: A more elegant solution?

by merlyn (Sage)
on Jun 05, 2001 at 02:10 UTC ( #85631=note: print w/replies, xml ) Need Help??

in reply to Re: A more elegant solution?
in thread A more elegant solution?

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

Replies are listed 'Best First'.
Re: Re: Re: A more elegant solution?
by runrig (Abbot) on Jun 05, 2001 at 02:13 UTC
    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 :)

(dkubb) Re: (3) A more elegant solution?
by dkubb (Deacon) on Jun 05, 2001 at 09:53 UTC

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

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


Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://85631]
and all is quiet...

How do I use this? | Other CB clients
Other Users?
Others rifling through the Monastery: (6)
As of 2018-05-27 16:50 GMT
Find Nodes?
    Voting Booth?