in reply to Dynamic SQL

since I can't use placeholders ... since the query won't be cached

Why not? The example you showed would allow both these things.

I am worried about ... performance

Instead of worrying, implement and measure. Or at least be more specific, e.g. how many of these queries per hour?

Replies are listed 'Best First'.
Re^2: Dynamic SQL
by Anonymous Monk on Apr 06, 2015 at 15:37 UTC
    because the query parameters varry too : 
    if ($x>10) {
$query="(select Name from Customers where CustId='$custid')"
}
else {
 $query="(select Name from Sales where SalesId='$salesid'
and CustId='$custid')"
}

    [download]
    so when preparing and executing the query with the first query: 
    $sth->execute(?)

    [download]
    with the second: 
    $sth->execute(?,?)

    [download]
    so I can't have a single execute covering both occasions
[reply]
[d/l]
[select]

      Actually, it's not too difficult to use the same execute statement:

      $SQL = "select name from customers where 1=1";

if ($fl_have_cust_id) {
   $SQL .= " and custid=?";
   push @args, $cust_id;
}

if ($fl_have_sales_id) {
   $SQL .= " and salesid=?";
   push @args, $sales_id;
}

$ST = $DB->prepare($SQL);
$ST->execute(@args);

      [download]

      We have a hardcoded 1=1 condition so we can just add " and <condition>" to $SQL for each new condition we want.

      ...roboticus

      When your only tool is a hammer, all problems look like your thumb.

[reply]
[d/l]
      Construct placeholders queries with hard coded options on the fly, no injection possible then.

      edit

      If you cache them after first use in a hash, you won't have a performance problem.

      Eg create a custom function getting a hash ( field =>entry,... ) and preparing only if new and returning result.

      Cheers Rolf
      (addicted to the Perl Programming Language and ☆☆☆☆ :)
      Je suis Charlie!

[reply]
[d/l]

In Section Seekers of Perl Wisdom