All the Perl that's Practical to Extract and Report
use Perl Log In
markjugg (792)
{mark} {at} {summersault.com}
http://mark.stosberg.com/
AOL IM: markjugg (Add Buddy, Send Message)
Cheerleading SQL::Interpolate
Deep into a nested 'WHERE' clause, I found that SQL::Abstract didn't seem to support the kind of AND/OR nesting I needed. Although it was slightly complex, nesting AND and OR clauses is not exotic SQL.
I got on the research train and rode it until I found SQL::Interpolate. I tried, love it, and recommend it. Actually, I only tried one simple method,
sql_interp
which may be the only one I ever need.
You won't need to keep the manual handling when using this module, because the syntax is very natural.
The core need for a SQL-generation module in the first place is that bind variables are a pain in the ass to manage. That, and a group of key/value pairs is more natural to manage as a hash.
With SQL::Interpolate, you can just write out normal SQL, and then drop in a Perl data structure where you would need to manage a bind variable, or when a hashref would be a handy way to express yourself. It will best illustrated by example:
my ($stmt,@bind) = sql_interp
"INSERT INTO table", {color => $new_color,
shape => $new_shape};
my ($stmt,@bind) = sql_interp qq[
UPDATE table SET ],
{color => $new_color,
shape => $new_shape}, qq[
WHERE color <> ], \$color
my($sql, @bind) = sql_interp qq[
SELECT * FROM table WHERE color IN], \@colors, qq[
AND y = ], \$x
It has some more advanced variations to make this even easier, but I'm satisfied with this technique for now.
--Larry Wall
Very nice indeed (Score:2)
Or possibly as a different method because abstraction hurts performance. I'll have to think about this, and bench it a little.
Thanks for sharing this!
It's not on CPAN! (Score:2)
Re:It's not on CPAN! (Score:2)
Perhaps he's just wary of the hassle of putting something on CPAN for the very first time, from scratch. In which case he might welcome a guiding hand. :) (I know I'm like that.)
Re:It's not on CPAN! (Score:2)
What about support for joins (Score:1)
This looks like a useful module. Thanks for sharing it. It still appears that supporting joins continues to elude authors of SQL interpolation modules.
I've spoken with Nathan Wiger, the author of SQL::Abstract, and he'd like to support joins but does not have a good idea of how to implement this support. David does not mention anything about limitations of his module or support for joins. I wonder what his position on this support is.
William