Beefy Boxes and Bandwidth Generously Provided by pair Networks
Think about Loose Coupling
 
PerlMonks  

Re: Simplifying queries in DBI

by graff (Chancellor)
on Mar 15, 2014 at 16:27 UTC ( #1078461=note: print w/ replies, xml ) Need Help??


in reply to Simplifying queries in DBI

Given that placeholders can only represent data values in query statement, a distinct statement must be prepared for each query that differs in items other than data values. If the differences from one query to the next involve the conditions used in the "where" clause, you just need to organize your code to provide for the various combinations of "where" conditions, and prepare a statement for each of those.

The OP is pretty vague about what you're trying to do (and some of the idioms in your SQL syntax are unfamiliar to me), but the following example might be relevant:

my %where_type = ( type1 => { _1_job => 'LIKE', _2_host_name => 'LIKE', _3_server => +'LIKE' }, type2 => { _1_job => '=', _2_host_name => '=', _3_time_stamp => 'L +IKE' }, type3 => { _1_user_id => '!=', _2_user_id => 'LIKE', _3_time_stamp + => '>' }, ); sub build_sql { my ( $type ) = @_; my $sql = "SELECT job,time_stamp,host_name,ip_address,server FROM +cf_status_log"; my @where_clauses = (); if ( exists( $where_type{$type} )) { for my $key ( sort keys %{$where_type{$type}} ) { ( my $fld = $key ) =~ s/^_\d_//; push @where_clauses, "$fld $where_type{$type}{$key} ?"; } } $sql .= " where ".join( " and ", @where_clauses ) if @where_clause +s; }
If you think that sort of approach is worth trying, you'll want to elaborate it to include some consideration of the values that are going to be passed along when these constructed queries are executed. At the very least, you want to avoid confusion about how many placeholder/parameter values get passed along with a given statement handle when it's executed. (If number of parameters passed on execution doesn't match the number of "?" placeholders, that can be fatal. -- UPDATE: and you want to make damn sure that you don't get confused about the ordering of parameters for a given statement. -- I updated the code snippet to avoid that sort of mistake; actually, I had to make a second update, to include "sort" in the for loop.)

Also, depending on how many times you have to build/prepare statements in your code, you may want to look at the "prepare_cached" function in DBI, so that you can avoid re-preparing a statement that was already used in a previous iteration. (You don't need to call "finish" on a statement handle until the process is completely done - but don't forget to do that before you disconnect.)


Comment on Re: Simplifying queries in DBI
Download Code

Log In?
Username:
Password:

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

How do I use this? | Other CB clients
Other Users?
Others perusing the Monastery: (5)
As of 2014-09-19 00:03 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    How do you remember the number of days in each month?











    Results (128 votes), past polls