Re: SQL::Interpolate - request for comments

• Previous message: Terrence Brannon: "[ANNOUNCE] DBIx::Recordset 0.25 now on CPAN"
• In reply to: David Manura: "SQL::Interpolate - request for comments"
• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]

Date: Tue, 30 Dec 2003 22:55:17 -0500
To: dm.list.perl@math2.org

David Manura wrote:

> 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:

module-authors@perl.org
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], \@colors, 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], \@colors, qq[
> AND y = ], \$x
> );
> $d->prepare($sql);
> $d->execute(@bind);
>
> =====
>
> - david
>
>

• Previous message: Terrence Brannon: "[ANNOUNCE] DBIx::Recordset 0.25 now on CPAN"
• In reply to: David Manura: "SQL::Interpolate - request for comments"
• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]