snapdragon has asked for the wisdom of the Perl Monks concerning the following question:

Afternoon chaps,

I'm currently building a medium scale web application and as much as possible I'm trying to keep everything as discrete objects for obvious reasons. However I'm having some difficulty with the SQL module.

I've written an SQL object that contains SQL with placeholders as class data with a single method (extract) to pull this data out for preparing when neccessary. This is working fine for most cases; however sometimes I have to build dynamic SQL that is formed from user input (checkboxes selected etc). In this case I can't use placeholders because I'm not supplying data to be queried but data definition statements that will be queried apon.

At the moment I've ended up with some hellish SQL hardcoded in some places with loads of conditional statements. Is there a better way of doing this? I've had a peek on CPAN but nothing really fits - DBIx::Abstract seems the closest. Any suggestions monks?

Ta.

Replies are listed 'Best First'.
Re: SQL parser from fragments
by talexb (Chancellor) on Apr 18, 2002 at 15:59 UTC
    Sure .. build the SQL statement as you see the need.

    So as you come across a checked checkbox, add the condition that it requires to a list of conditions. At the end, if there are any elements in the condition array, add 

      "where " . join ( " and ", @Conditions )

    [download]
    to the end of your SQL query. If you've got two values and you need to do a range, then you have to do some clever figuring with < and > conditions.

    If you have a specific example that's proving too challenging, let us know.

    --t. alex

    "Nyahhh (munch, munch) What's up, Doc?" --Bugs Bunny

[reply]
[d/l]
Re: SQL parser from fragments
by atcroft (Abbot) on Apr 18, 2002 at 21:33 UTC
    I puzzled with this same issue a few days ago, and received similar advice from a co-worker: put it together as you go. Below is my routine, although I went through and tried to make it more generic (query was for an experimental interface into an internal database used at work). Comments contain more details about what was expected, etc. Still a large chunk of code, if you have lots of fields, but perhaps it might give you an idea. (Comments and suggestions appreciated.)

    Update: samgold, my thanks for your catching that bit of code in your response. As I stated, this was code I had attempted to make more generic before posting. In the original code, there were several such sections. That code, as it appears, should rightly have occurred just above the 

    $lstatement .= "AND " if ($count);
    , as it does in the other if blocks. I commented it out below (rather than removing it, so others could understand the thread), and made the appropriate change. My thanks for catching it.
    Read more... (6 kB)
[reply]
[d/l]
      why do you have:
      $lstatement .= "WHERE " unless ($count);
      on line 8 starting from sub... $count = 0 and nothing changes it between where you declare it and line 8.
      This is reference to the code posted by atcroft.
[reply]

Back to Seekers of Perl Wisdom