Details are given below on a simple Perl module I wrote called SQL::Interpolate. I'm interested if others find this useful, find it redundant to existing modules, or have better ideas for designing it, as I'm considering submitting it to CPAN. (And if this is not the best place to propose this question, please let me know.)
other good places:
[EMAIL PROTECTED] http://www.perlmonks.org
This module arose as I was writing a lot of SQL construction/variable binding code (similar to Recipe 14.13--Building Queries Programmatically, Perl Cookbook, 2nd ed., Christiansen & Torkington) that was able to be more simply expressed using a "dbi_interpolate" function as shown.
isnt that a long word to type every time? I envision subroutines with an interpolating property:
sub get_authors : dbi_interp {
my ($author_list) = @_;
qq[SELECT * FROM AUTHORS WHERE author_id IN ], $author_list ;
}
my $sql = get_authors (10 .. 12);
but this is just brainstorming
except that SQL::Interpolate still exposes and utilizes the full native SQL syntax of your database.
this means you must write custom SQL for each database the code is to work on... for example LIMIT is handled very differently by Postgres and MySQL... it might be nice if LIMIT
were a subroutine which were translated to the proper syntax... in fact I have just started on a module which generates SQL snippets based on the DBD of the $dbh.
There could possibly be some useful synergy between DBIx::Declare and this module... DBIx::Declare has a nice syntax for stating the input and output requirements of a body of SQL. SQL::Interpolate has a nice way of dynamically creating SQL.
I find it interesting that you require scalars to be passed by reference (e.g., \$x) , but this creates consistency.
Nice clean syntax. I could've used something like this on my last assignment.
SYNOPSIS
use DBI;
use SQL::Interpolate;
my $rows = $dbh->selectall_arrayref(dbi_interpolate qq[
SELECT * FROM table
WHERE color IN], [EMAIL PROTECTED], qq[
AND y = ], \$x, qq[
LIMIT], [1, 10]
);
$dbh->do(dbi_interpolate qq[
INSERT INTO table ], {
color => $new_color,
shape => $new_shape}
);
$dbh->do(dbi_interpolate qq[
UPDATE table SET ], {
color => $new_color,
shape => $new_shape}, qq[
WHERE color <> ], \$color
);
my($sql, @bind) = sql_interpolate qq[
SELECT * FROM table WHERE color IN], [EMAIL PROTECTED], qq[
AND y = ], \$x
);
$d->prepare($sql);
$d->execute(@bind);
=====
- david
