Beefy Boxes and Bandwidth Generously Provided by pair Networks
laziness, impatience, and hubris
 
PerlMonks  

Perl style/best practice question: how to [better] embed SQL in code ?

by Krambambuli (Deacon)
on Dec 16, 2008 at 17:29 UTC ( #730700=perlquestion: print w/ replies, xml ) Need Help??
Krambambuli has asked for the wisdom of the Perl Monks concerning the following question:

Dear monks,

although I have a a few solutions for my problem, I have none with which I would be really happy, so I'm coming to ask around:

how do You embed SQL in your code?

My current way to do it - I'm referring mostly to complex queries, usually with a subroutine build around them - is something like
my $sql = qq/ SELECT a.somedata1, a.somedata2, a.somedata3, [...] FROM table1 a, table2 b, table3 c WHERE a.somedata4 > ? /; $sql .= qq/ AND a.somedata5 <= ? AND [...] / if some_perl_code_condition1; $sql .= qq/ AND b.somedata6 >= ? [...] / if some_perl_code_condition2; $sql .= qq/ORDER BY somedata7 /;
That's as readable as I could make it. Still, if later on, after being built, the resulting sql string is printed out for testing or is logged, the embedded newlines and spaces make the shown SQL string rather less appealing.

Clearly, I do want to get an optimum of somewhat opposite requirements - keeping the SQL readable in Perl, but also rather easy to cut-and-paste into/from SQL for testing/editing. Giving up indentation to use HERE documents seems not an real alternative, as I'd like to _keep_ the subroutine code nicely indented.

How are You solving this conundrum in the code you write?

Many thanks,

Krambambuli
---

Comment on Perl style/best practice question: how to [better] embed SQL in code ?
Download Code
Re: Perl style/best practice question: how to [better] embed SQL in code ?
by Tanktalus (Canon) on Dec 16, 2008 at 17:49 UTC

    How I do it is:

    if (some_perl_code_condition) { push @where, 'a.somedata5 <= ?'; push @bind, $somedata5_max } # ... $sql = $select . join(' AND ', @where) . $order; # and use @bind for the execute statement
    How I'd like to do it is abstract it away into comparison objects where stringifying it will mix in the AND and ORs, and maybe a list context for binding. But I've not stopped to think about it long enough to write any code for it :-)

    Of course, I'll end up with a unique statement nearly each time through, so preparing becomes nearly useless (from a performance perspective anyway - almost may as well use do...), which may be a different concern.

    Update: Added italicised pieces in last paragraph in response to graff.

      That's more or less how I usually do it too.

      Of course, I'll end up with a unique statement nearly each time through, so preparing becomes useless...

      I don't get what you mean by "useless" -- preparing is never useless. It's kind of mandatory, esp. when using placeholders, which should be "whenever possible". Even if you think you won't create the same SQL statement more than once in a given run, you can still use:

      my $sth = $dbh->prepare_cached( $sql_statement );
      That's quite painless, and if you happen to come up with the same statement more than once, you'll just get the same handle you got the last time you prepared that statement.
        And if your code is query-heavy you could even Memoize the cache!
        --
        seek $her, $from, $everywhere if exists $true{love};
Re: Perl style/best practice question: how to [better] embed SQL in code ?
by ccn (Vicar) on Dec 16, 2008 at 17:52 UTC
    my ($sid, $age) = $dbh->selectrow_array(<<'SQL', undef, $uid); SELECT SessionID, datediff(minute, TimeStamp, getdate()) FROM Sessions WHERE UserID = ? AT ISOLATION READ UNCOMMITTED SQL my %data = ( Column1 => $value1, Column2 => $value2, # ... ColumnN => $valueN, ); my $rv = $dbh->do(<<"SQL", undef, values %data); INSERT INTO TableName (@{[ join ', ' keys %data ]}) VALUES ( @{[join ', ', map {'?'} keys %data ]}) SQL
Re: Perl style/best practice question: how to [better] embed SQL in code ?
by kennethk (Monsignor) on Dec 16, 2008 at 18:01 UTC

    There are obvious constraints on what you can do with embedded SQL to make it readable. I personally would format the lines above as

    my $sql = qq{ SELECT a.somedata1, } . qq{ a.somedata2, } . qq{ a.somedata3, } . qq{ [...] } . qq{ FROM table1 a, } . qq{ table2 b, } . qq{ table3 c } . qq{ WHERE a.somedata4 > ? } ; $sql .= qq{ AND a.somedata5 <= ? } . qq{ AND [...] } if some_perl_code_condition1; $sql .= qq{ AND b.somedata6 >= ? } . qq{ [...] } if some_perl_code_condition2; $sql .= qq{ ORDER BY somedata7 };

    It removes a lot of the white space and all the new lines, but I'm pretty sure that my floating ; is not PBP. I'll play with field widths depending on case, but always put starting period and the closing brace at tab stops. One nice thing about this format is that mixing and matching is generally easy and safe, and changing width tends to be very fast.

      Sorry, but I think that formatting is horrible. Just horrible. Why? Because who said you have to remove white space and new lines? The idea is to not make the output pretty for the SQL engine, but to make changes to your code painless, simple and efficient. And having to concatenate like that is none of the above for me. No, I would not change the original formatting at all. I cannot possible see how leading periods and closing brace at the tab stops beats ... nothing!

      my $sql = qq/ SELECT a.somedata1, a.somedata2, a.somedata3, [...] FROM table1 a, table2 b, table3 c WHERE a.somedata4 > ? /;
      See? No periods. No braces. No nothing. Just SQL.


      jeffa

      L-LL-L--L-LL-L--L-LL-L--
      -R--R-RR-R--R-RR-R--R-RR
      B--B--B--B--B--B--B--B--
      H---H---H---H---H---H---
      (the triplet paradiddle with high-hat)
      
        The OP specifically requested removing new lines and white space for testing and logging. The format is busy, but it also sets the SQL clearly apart from the surrounding Perl.
Re: Perl style/best practice question: how to [better] embed SQL in code ?
by perrin (Chancellor) on Dec 16, 2008 at 18:35 UTC

    I might try it like this, which will look nice in Emacs cperl-mode:

    my $clause1 = ''; if ($some_condition) { $clause1 = <<'END_SQL'; AND a.somedata5 <= ? AND [...] END_SQL my $clause2 = ''; if ($some_condition) { $clause2 = <<'END_SQL'; AND b.somedata6 >= ? [...] END_SQL my $order_by = ''; if ($some_condition) { $order_by = <<'END_SQL'; ORDER BY somedata7 END_SQL my $sql = <<END_SQL; SELECT a.somedata1, a.somedata2, a.somedata3, [...] FROM table1 a, table2 b, table3 c WHERE a.somedata4 > ? $clause1 $clause2 $order_by END_SQL
Re: Perl style/best practice question: how to [better] embed SQL in code ?
by chromatic (Archbishop) on Dec 16, 2008 at 19:03 UTC
      Class::DBI had a neat way of modularizing SQL... does DBIx::Class have something similar?
        here is how Class::DBI did it... I dont see anything similar for DBIx::Class

Log In?
Username:
Password:

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

How do I use this? | Other CB clients
Other Users?
Others imbibing at the Monastery: (5)
As of 2014-09-21 05:49 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

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











    Results (166 votes), past polls