Beefy Boxes and Bandwidth Generously Provided by pair Networks
more useful options

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

Log In?

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

How do I use this? | Other CB clients
Other Users?
Others taking refuge in the Monastery: (13)
As of 2017-07-26 13:26 GMT
Find Nodes?
    Voting Booth?
    I came, I saw, I ...

    Results (393 votes). Check out past polls.