SQL::Interpolate - request for comments

From: David Manura (dm.list_at_math2.org)
Date: 12/27/03

  • Next message: John: "Re: Automatng import" 
    Date: Fri, 26 Dec 2003 22:39:35 -0500
To: dbi-users@perl.org

    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.) 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. I had
    previously tried using SQL::Abstract for this purpose and with some
    success, but it seemed less expressive than SQL (lacking certain
    constructs), failed under certain cases (e.g. using 'IN' with a
    zero-length list), and its syntax for nested AND/OR expressions wasn't
    as intuitive as in plain SQL.

    =====
    web page: http://www.math2.org/david/sql-interpolate/
    current version: 0.1
    =====
    ABSTRACT

    The purpose of SQL::Interpolate is to make writing SQL queries in Perl
    more natural, less redundant, and less error-prone. SQL::Interpolate
    takes your query specification and generates a correctly formatted SQL
    statement along with a list of bind values. These result values can then
    be passed to DBI or used for another purpose. SQL::Interpolate serves a
    purpose similar to that of SQL::Abstract
    <http://search.cpan.org/%7Enwiger/SQL-Abstract/Abstract.pm> except that
    SQL::Interpolate still exposes and utilizes the full native SQL syntax
    of your database.

    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

  • Next message: John: "Re: Automatng import"

    Relevant Pages

    • Re: use of DBI; I am getting multiple error messages mixed in with the correct output.
      ... T> expected a mature library like DBI to behave like this. ... I learned plenty about the perl side of things from her posts. ... closely represent the SQL idea of a null. ... trivially easy to perform bulk loads using the DB's particular ...
      (comp.lang.perl.misc)
    • ANNOUNCE - Muldis::DB v0.0.0 released, in p5+p6
      ... I am pleased to announce the release of Muldis::DB version 0.0.0 for Perl 5 on CPAN. ... If you want to read all the Muldis::DB documentation that exists now, I recommend doing so in this order: ... Muldis::DB implements a D language as defined by Hugh Darwen and Chris Date, and presents a superior interface for working with the relational model of data, contrasted with SQL. ...
      (perl.dbi.users)
    • Re: use of DBI; I am getting multiple error messages mixed in with the correct output.
      ... %%> Perl was to relate it to uninitialized values in Java or C++. ... %%> The idea of NULL in SQL is very different. ... mapping SQL NULLs to undef was a choice. ...
      (comp.lang.perl.misc)
    • Re: ANNOUNCE: SQL-Interpolate 0.31
      ... SQL-Interpolate does support variable references embedded in strings. ... Since a number of people I talked to distrust source filtering, I moved this capability into the separate module and made it entirely optional. ... 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. ... 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. ...
      (perl.dbi.users)
    • RE: Question with DBI versus PERL
      ... Question with DBI versus PERL ... statement at once (if I parse my SQL file) my pl/sql will work? ... All spool statements will turn to Perl print() calls. ... I want to know how can i run this sql script with DBI. ...
      (perl.dbi.users)