Stories
Slash Boxes
Comments
NOTE: use Perl; is on undef hiatus. You can read content, but you can't post it. More info will be forthcoming forthcomingly.

All the Perl that's Practical to Extract and Report

use Perl Log In

Log In

[ Create a new account ]

nkuitse (193)

nkuitse
  (email not shown publicly)
http://www.nkuitse.com/

Journal of nkuitse (193)

Thursday August 07, 2003
10:46 AM

SQL templates

[ #13972 ]

I'm not crazy about positional parameters in DBI, but I don't want to use a heavy-duty module like Tangram or Alzabo* to get away from them.

What I decided was to use simple SQL templates with placeholders like $foo instead of ?, then write a simple module to expand the template given a hash of placeholder names and values.

my $str = 'SELECT id FROM People'
    . 'WHERE age >= $min AND age <= $max';
my $template = SQL::Template->new($str);
my ($sql, @args) = $template->process(
    'min' => 20,
    'max' => 29
);
# $sql = 'SELECT id FROM People'
# . 'WHERE age >= ? AND age <= ?'
# @args = (20, 29)
$dbh->prepare_cached($sql, @args);

That was simple enough. But then I also wanted multiple substring searching like this:

my $str = 'SELECT id FROM People'
    . 'WHERE { name ILIKE $name }:AND';
my $template = SQL::Template->new($str);
my ($sql, @args) = $template->process(
    'name' => 'tolk% %chris%'
);
# $sql = 'SELECT id FROM People'
# . 'WHERE name ILIKE ? AND name ILIKE ?'
# @args = ('tolk%', '%chris%')
$dbh->prepare_cached($sql, @args);

That was easier than I had thought it would be.

The hardest part is what to call my module. SQL::Template is the most obvious choice, but to me that sounds like a big, fancy module whereas mine is very simple (and likely to remain that way).

Meanwhile, the module isn't quite ready for release, since I think there are some Postgresisms to root out.

* Not that there's anything with Tangram or Alzabo!

Update 18 Aug: Fixed the quotes in the sample code (should have been single quotes, not double. D'oh!)

The Fine Print: The following comments are owned by whoever posted them. We are not responsible for them in any way.
 Full
 Abbreviated
 Hidden
More | Login | Reply
Loading... please wait.
  • Not using them means that you have to recreate the query execution plan each time. You get better performance by using them.

    You might want to have a look at Tim Bunce's Advanced DBI talk [gtsm.com]

    -Dom

    • Not using them means that you have to recreate the query execution plan each time.

      Actually, my code generates SQL with positional parameters. It takes a pseudo-SQL template like this:

      SELECT foo
        FROM bar
        WHERE baz = $baz
         AND qux = $qux

      And turns it into this:

      SELECT foo
        FROM bar
        WHERE baz = ?
         AND qux = ?

      Simultaneously taking the name-based parameters that you provide (in a hash, i.e., unordered) and lining their values up in the correct order in a params

  • I'd be happy to help test this with MySQL if you'd like. It sounds a lot like it would be useful for me with an upcoming project I'm working on.
    • I'd be happy to help test this with MySQL if you'd like.

      Sure, thanks. (Though it doesn't really matter which RDBMS is used, since SQL::Template simply spits out SQL and a correctly ordered list of values.)

      You can download version 0.01 here (ca. 3KB) [nkuitse.com] for now. Do the usual to install:

      perl Makefile.PL
      make
      make test
      [sudo] make install

      It sounds a lot like it would be useful for me with an upcoming project I'm working on.

      Glad to hear it!