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 ]

Journal of markjugg (792)

Thursday December 02, 2004
10:31 PM

Cheerleading SQL::Interpolate

[ #22115 ]
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_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.

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.
  • I'll have to read it and its source before I decide anything, but I think I am going to use this for DBIx::Simple's $db->query method. Or maybe as $db->iquery, in case it is not backwards compatible. It does appear to be, though.

    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!
    • Which means I'm not going to use it. :(
      • 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 [cpan.org], 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.)

  • Hey Mark,

    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