Beefy Boxes and Bandwidth Generously Provided by pair Networks
Clear questions and runnable code
get the best and fastest answer
 
PerlMonks  

Re: prepare statement within DBI

by gmax (Abbot)
on Aug 25, 2004 at 15:47 UTC ( #385711=note: print w/ replies, xml ) Need Help??


in reply to prepare statement within DBI

Whatever your database driver accepts as a valid SQL statement is fine as far as the DBI is concerned. Thus, if Oracle accepts a query like "SELECT COUNT(*) from t1; SELECT COUNT(*) from t2;" so you can do it.

If that is not the case, you can force that behavior with some SQL features:

my $query = qq{ select "t1" as t, count(*) as c from table_one UNION ALL select "t2", count(*) from table_two }; # Gives # +----+---+ # | t | c | # +----+---+ # | t1 | 4 | # | t2 | 2 | # +----+---+ my $table_status = $dbh->selectall_arrayref($query, {Slice => {}} ); # $table_status = [ # { t => 't1', c => 4 }, # { t => 't2', c => 2 } # ];

Or:

my $query = qq{ SELECT ( select count(*) from table_one ) AS t1, ( select count(*) from table_two ) AS t2; }; # Gives # +----+----+ # | t1 | t2 | # +----+----+ # | 4 | 2 | # +----+----+ my @table_status = $dbh->selectrow_array($query); # @table_status = ( 4, 2 );

I would personally recommend against this, since I prefer to have better control on my SQL statements. However, if you know what you're doing, it's up to you to decide.

 _  _ _  _  
(_|| | |(_|><
 _|   


Comment on Re: prepare statement within DBI
Select or Download Code
Re^2: prepare statement within DBI
by Velaki (Chaplain) on Aug 25, 2004 at 18:09 UTC

    Additionally, consider looking at the results of your database's query optimizer and analyzer to see what the trade-off will be in creating more complex SQL queries to offset the cost on the perl side in making multiple calls.

    Maybe a stored proc is in order?

    Thoughts,
    -v
    "Perl. There is no substitute."

Log In?
Username:
Password:

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

How do I use this? | Other CB clients
Other Users?
Others drinking their drinks and smoking their pipes about the Monastery: (9)
As of 2015-07-03 08:09 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 (49 votes), past polls