All the Perl that's Practical to Extract and Report
use Perl Log In
nkuitse (193)
SQL templates
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!)
about I/O disciplines and I should retire to the Tassili-in-Aijer
mountains in Sahara as a camel herder :-)
-- Jarkko Hietaniemi
Positional Parameters are a good idea (Score:2)
You might want to have a look at Tim Bunce's Advanced DBI talk [gtsm.com]
-Dom
Re:Positional Parameters are a good idea (Score:1)
Actually, my code generates SQL with positional parameters. It takes a pseudo-SQL template like this:
And turns it into this:
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
need testers? (Score:1)
Re:need testers? (Score:1)
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:
It sounds a lot like it would be useful for me with an upcoming project I'm working on.
Glad to hear it!