Dean,
Thanks for the intro to SQL::Preproc. I'll add it to my "see also"
section (possibly along with SQL::String). I wasn't specifically aware
of your module, but I had seen things like it in other languages.
Yes, SQL-Interpolate does support variable references embedded in
strings. However, you need to enable the optional
SQL::Interpolate::Filter module:
http://search.cpan.org/dist/SQL-Interpolate/lib/SQL/Interpolate/Filter.pm
Your example can then be rewritten as something like
use DBIx::Interpolate FILTER => 1, qw(:all);
...
my ($col1, $col2, $col3, $param1, $param2) = (...);
my $ref = $dbx->selectall_arrayref(sql[
select * into $col1, $col2, $col3
from mytable
where somecolumn = $param1 or othercolumn < $param2
]);
Now, the above approach does use source filtering. Since a number of
people I talked to distrust source filtering, I moved this capability
into the separate module and made it entirely optional. The source
filtering I use is quite robust, and I did much testing, but it could
fail on very quite obscure cases. sql[...] behaves like a Perl
"quote-like" operator (similar to that provided in SQL::Quotelike
http://search.cpan.org/~rgarcia/Sub-Quotelike-0.03/lib/Sub/Quotelike.pm
but I found that module's source filtering to be too simplistic). Each
sql[...] operator is translated into an object constructor,
SQL::Interpolate::SQL->(...) and supports the concatenation operator, so
it can be used as almost any standard Perl expression:
return sql[select * from mytable] . sql[where x=$x] if $condition.
_All_ variables referenced within the operator (e.g. "$x" above) are
properly converted to DBI bind variables stored in the object. I'm
somewhat fond of your use of the ":" syntax though since this might
clarify the special meaning and differentiate it from the standard Perl
string interpolation.
Unfortunately, I know no good way around using source filtering here due
to the scoping issues (a subroutines cannot typically see the lexical
variables of its caller). I think PadWalker
(http://search.cpan.org/dist/PadWalker/PadWalker.pm) is promising way
around this, but it was not quite stable enough the last time I tested it.
I believe our modules have a similar aim. Some differences I see are
that the core SQL::Interpolate module is not dependent on source
filtering, and the SQL is treated more as a first-class object separate
from the Perl code. In your module the SQL is more an extension to the
Perl language via source filtering. Further, SQL::Preproc extends the
SQL syntax (e.g. "SELECT * INTO :$x") to support returning variables
from queries. In contrast, SQL::Interpolate does not extend the SQL
syntax (and does it's best to not extend Perl), but it deals only with
interpolating Perl variables into queries, whereas returning variables
from queries is the job of something like DBI, DBIx::Interpolate, or
DBIx::Simple).
best regards, davidm
Dean Arnold wrote:
David Manura wrote:
SQL-Interpolate 0.31 is now available on CPAN:
http://search.cpan.org/dist/SQL-Interpolate/ . This includes a new
("WHERE", {x => [EMAIL PROTECTED], ...}) syntax, bug fixes, and clarified
documentation. Discussions on the module will now take place on the
CPAN::Forum (http://www.cpanforum.com/dist/SQL-Interpolate).
Have you considered just embedding the variable references
in the SQL string (ala SQL::Preproc
http://search.cpan.org/~darnold/SQL-Preproc-0.10/Preproc.pod) ?
E.g.,
my ($col1, $col2, $col3, $param1, $param2);
#...set values...
my $sth = $dbh->prepare(
'select * into :$col1, :$col2, :$col3
from mytable
where somecolumn = :$param1 or othercolumn < :$param2');
While double quoted SQL strings are problematic, single quoted
SQL and heredocs should be OK, (and, of course, escaping
the perl variable in double quoted strings works too).
You should be able to collect/translate the placeholders
at runtime, and use eval {} to apply them to the
translated statement. (which is different than SQL::Prepoc,
which uses a source filter to collect/translate).
Dean Arnold
Presicient Corp.
