Beefy Boxes and Bandwidth Generously Provided by pair Networks
XP is just a number
 
PerlMonks  

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


Comment on Re: Re: A more elegant solution?
Download Code
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")

      ___
      -DA

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://85631]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others lurking in the Monastery: (19)
As of 2015-07-07 21:44 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    The top three priorities of my open tasks are (in descending order of likelihood to be worked on) ...









    Results (93 votes), past polls