ANNOUNCE: SQL-Interpolate 0.31

SQL-Interpolate 0.31 is now available on CPAN:
http://search.cpan.org/dist/SQL-Interpolate/ . This includes a new
("WHERE", {x => \@v, ...}) 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).

0.31 2005-09-27

## SQL::Interpolate

- Improved error reporting by sql_interp
(recommended by mark stosberg)

- Added support for ("WHERE", {x => \@v, ...})
==> "WHERE (x IN (?,...) AND ...)".
(recommended by multiple people)

- Generate 'WHERE id = 5 and 1=1' and 'WHERE id = 5 or 1=0'
rather than 'WHERE id = 5 and 1' and 'WHERE id = 5 or 0'
for Oracle compatibility.
(reported by wojciech pietron)

- Fixed some improper handling of "use"
parameters, e.g. $x in "use SQL::Interpolate FILTER=>$x"

- Fixed various Exporter problems from custom import
(reported by mark stosberg)

- sql_interp.t and dbi.t - Fixed test case errors due to
different hash order on MacOS.
(reported by sean davis)

- Documentation improvements.

## SQL::Interpolate::Macro

- Fixed: '^' and '$' are no longer
automatically placed around regexes in 'relations' parameter.
(reported by wojciech pietron)

## DBIx::Interpolate

- Added support for transparent
caching of statement handles (no need to prepare()).

- Fixed error in STX::fetchrow_hashref returning an
arrayref rather than a hashref.
(reported by mark tiefenbruck)

- Added Carp

--davidm

Re: ANNOUNCE: SQL-Interpolate 0.31

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 => \@v, ...}) 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.

Re: ANNOUNCE: SQL-Interpolate 0.31

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/Interpol ate/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/Q uotelike.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 => \@v, ...}) 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.