Beefy Boxes and Bandwidth Generously Provided by pair Networks
Do you know where your variables are?

Simplifying queries in DBI

by neilwatson (Priest)
on Mar 15, 2014 at 15:01 UTC ( #1078447=perlquestion: print w/replies, xml ) Need Help??
neilwatson has asked for the wisdom of the Perl Monks concerning the following question:


I have some queries like this:

SELECT job,time_stamp,host_name,ip_address,server FROM cf_status_log WHERE job LIKE 'trans01' ESCAPE '!' AND host_name LIKE '%' ESCAPE '!' AND ip_address LIKE '%' ESCAPE '!' AND server LIKE '%' ESCAPE '!' AND time_stamp > '2014-03-12 19:00:12- +0400'::timestamp AND time_stamp < ( '2014-03-12 19:00:12-0400'::timestamp + interval '60 minute' ) ORDER BY job desc,time_stamp desc LIMIT 1000 ;

All the columns referenced on the WHERE side are variable, as are the timestamps and the interval (60). I'm seeking advice on how to build this query in DBI in a sane manner.

Neil Watson

Replies are listed 'Best First'.
Re: Simplifying queries in DBI
by graff (Chancellor) on Mar 15, 2014 at 16:27 UTC
    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.)

Re: Simplifying queries in DBI
by Marshall (Abbot) on Mar 16, 2014 at 07:20 UTC
    I don't know what you mean by "ESCAPE '!' ".
    That is not any standard SQL syntax.
    Where does that come from and why are you using it?

    Start with: "SELECT * FROM cf_status_log".
    Get that to work and then try more complex things.

    Basically start from the whole thing and then refine that to get a subset, then a better subset, etc.

      I don't know what you mean by "ESCAPE '!' ".
      That is not any standard SQL syntax.

      The major players support LIKE ... ESCAPE all in the same way (PostgreSQL, Oracle, SQLite, MS SQL Server, MySQL, IBM DB2). This is a strong hint that it may be standardised. In fact, SQL-92 has it in Chapter 8.5, page 214:

      8.5 <like predicate> Function Specify a pattern-match comparison. Format <like predicate> ::= <match value> [ NOT ] LIKE <pattern> [ ESCAPE <escape character> ] <match value> ::= <character value expression> <pattern> ::= <character value expression> <escape character> ::= <character value expression>


      Today I will gladly share my knowledge and experience, for there are no sweeter words than "I told you so". ;-)
        I learned something, thanks!
Re: Simplifying queries in DBI
by erix (Parson) on Mar 16, 2014 at 16:16 UTC

    Keep in mind that LIKE, to act as a substring search, needs to have % around the search value; either appended, or prepended, or on both sides (three different searches!); i.e.:

    job LIKE 'trans01%' -- , or:

    job LIKE '%trans01' -- , or:

    job LIKE '%trans01%'

    And job LIKE 'trans01' means: job equals 'trans01'. It is possible but unlikely because if you wanted equality you would normally spell it job = 'trans01'

    If you use place holders: column like ( '%' || ? || '%' )

    BTW, it seems you'd better forget about the ESCAPE '!' as they are not used in your statement anyway.

    BTW 2: LIKE '%' doesn't do anything at all it seems so better to leave them out.

Re: Simplifying queries in DBI
by graff (Chancellor) on Mar 15, 2014 at 16:56 UTC
    Another variation on my previous reply that might be relevant: I have a web app with a search page for probing a set of related tables, where the user can fill in search terms - it's a bilingual dictionary system, so search conditions can include things like headword spelling, pronunciation, part-of-speech, translated meaning, etc, in arbitrary combinations.

    When the search form is submitted, the app loops over the fields that have been filled in by the user, and assembles two parallel arrays: "@where_clauses" and "@where_values". The first array is used to build the SQL statement (table names and join conditions are added as needed), and the second array is passed to the execute call for that statement. This way, the ordering of fields and placeholder values is assured.

Re: Simplifying queries in DBI
by CountZero (Bishop) on Mar 15, 2014 at 15:25 UTC
    What have you tried and how did it (not) work?


    A program should be light and agile, its subroutines connected like a string of pearls. The spirit and intent of the program should be retained throughout. There should be neither too little or too much, neither needless loops nor useless variables, neither lack of structure nor overwhelming rigidity." - The Tao of Programming, 4.1 - Geoffrey James

    My blog: Imperial Deltronics

      I was hoping to use place holders, but I don't see any way for the place holders to also contain the text beyond the values. At best I can run a loop to build the string for all the LIKE bits, but it doesn't seem to offer a good gain.


      if ( $query{hostname} ) { $query = $query . " AND host_name LIKE '$query{hostname}' ESCAPE +'!'"; } if ( $query{ip_address} ) { $query = $query . " AND ip_address LIKE '$query{ip_address}' ESCA +PE '!'"; } # etc...

      Neil Watson

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://1078447]
Approved by graff
and all is quiet...

How do I use this? | Other CB clients
Other Users?
Others pondering the Monastery: (3)
As of 2018-03-24 01:29 GMT
Find Nodes?
    Voting Booth?
    When I think of a mole I think of:

    Results (297 votes). Check out past polls.