Managing Dynamic SQL

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

I need some help managing dynamic SQL statements. Depending on what HTML form options have been chosen, I append elements to a SQL statement:

   if ($time) {
      $sql .= 'AND time = ? ';
   }
   if ($date) {
      $sql .= 'AND date = ? ';
   }
[download]

The problem comes when I go to execute the statement. It's difficult for me to determine which arguments to pass to execute() (the variables for the placeholders in the SQL statement). Is there a philosophy, recommended approach, or module that can help with this? Otherwise, I end up with large if statements calling variations of execute()

Comment on Managing Dynamic SQL Select or Download Code

Replies are listed 'Best First'.
Re: Managing Dynamic SQL by BUU (Prior) on Jun 25, 2005 at 19:35 UTC
`if( $time ) { $sql.= ' AND time = ?'; push @args, $time; } if( $date ) { $sql.= ' AND date = ?'; push @args, $date; } $sth->execute( @args );` [download]	[reply] [d/l]
Re: Managing Dynamic SQL by davidrw (Prior) on Jun 25, 2005 at 19:44 UTC
BUU is right on with keeping a `@args` (i personally use `@bind`) array to push stuff onto as you append to `$sql`, and i use that all the time... I have, however, tried to start using SQL::Abstract when i can. `my %where = (); $where{time} = $time if $time; $where{date} = $date if $date; my $SA = SQL::Abstract->new(); my ($whereSql, @whereBind) = $SA->where(\%where); # also look at the $SA->select(), $SA->update(), etc methods` [download]	[reply] [d/l] [select]
Re^2: Managing Dynamic SQL by bart (Canon) on Jun 26, 2005 at 11:30 UTC
I have, however, tried to start using SQL::Abstract when i can. You can go one step further still, and use DBIx::Simple to actually execute the thusly constructed queries, all in one method call.	[reply]
Re: Managing Dynamic SQL by CountZero (Bishop) on Jun 25, 2005 at 19:46 UTC
Or if you want to go one level higher in abstraction: Class::DBI::AbstractSearch. CountZero "If you have four groups working on a compiler, you'll get a 4-pass compiler." - Conway's Law	[reply]
Re: Managing Dynamic SQL by srdst13 (Pilgrim) on Jun 26, 2005 at 01:51 UTC
I would second moving over to Class::DBI and using SQL::Abstract. In addition, check out SQL::Interpolate--very cool. `use SQL::Interpolate qw(:all); my($sql, @bind) = sql_interp q[SELECT * FROM mytable WHERE ], {'time' => $time, 'date' => $date}; # Result: # $sql = "SELECT * from mytable where time=? and date=?" # @bind = ($time, $date); my $sth = $dbh->prepare($sql); $sth->execute(@bind);` [download] Also, you might look into the related DBIx::Interpolate. Hope this helps. Sean	[reply] [d/l]
Re: Managing Dynamic SQL by NateTut (Deacon) on Jun 25, 2005 at 20:08 UTC
You could also set a flag Default them to 0 first of course. `my $Have_Date=0; my $Have_Time=0;` [download] Then set `$Have_Date=1;` or `$Have_Time=1;` when the user enters the info. Then when its time to do the query: `if ($Have_Date) { # # Bind Date Param Stuff # } if ($Have_Time) { # # Bind Time Param Stuff # }` [download] You get the idea...	[reply] [d/l] [select]
Re: Managing Dynamic SQL by simonm (Vicar) on Jun 26, 2005 at 01:50 UTC
Let onw of the existing modules build the SQL for you. My DBIx::SQLEngine would do the trick, as would half a dozen similar modules...	[reply]