I spent some time today wrestling with SQL::Abstract. I've used it and appreciated it for a long time, but have found the syntax too subtle for my tastes, requirng the documentation to be open constantly.
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_interpwhich 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.
It's not on CPAN!
Juerd on 2004-12-03T09:30:15
Which means I'm not going to use it.:( Re:It's not on CPAN!
bart on 2004-12-03T12:18:18
If it's really that useful, then maybe the author should be poked into putting it on CPAN. He does seem to have a PAUSE ID, but he doesn't seem to have actually have done anything with it, until now.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!
Juerd on 2004-12-03T13:11:57
I've already contacted him, just after writing my previous post. Let's see if we can get this thing into CPAN.