SQL::Interpolate 0.33

SQL::Interpolate is a Perl module to interpolate Perl variables into SQL statements.

SYNOPSIS

use SQL::Interpolate qw(:all);

# Some sample data to interpolate:
my $s = blue; my @v = (5, 6);

# Variable references are transformed into bind parameters.
# The most basic usage involves scalarrefs (as well as arrayrefs
# preceeded by "IN").
my ($sql, @bind) = sql_interp
SELECT * FROM table WHERE x = , $s, AND y IN, @v;
# RESULT:
# $sql = SELECT * FROM mytable WHERE x = ? AND y IN (?, ?)
# @bind = ($s, @v);

# In certain contexts, an arrayref or hashref acts as a single tuple:
my ($sql, @bind) = sql_interp
INSERT INTO table, {x => $s, y => 1};
# RESULT:
# $sql = INSERT INTO mytable (x, y) VALUES(?, ?);
# @bind = ($s, 1);
my ($sql, @bind) = sql_interp
UPDATE table SET, {x => $s, y => 1}, WHERE y <> , 2;
# RESULT:
# $sql = UPDATE mytable SET x = ?, y = ? WHERE y <> ?;
# @bind = ($s, 1, 2);

# In general, a hashref provides a shortcut for specifying
# a logical-AND construction:
my ($sql, @bind) = sql_interp
SELECT * FROM table WHERE, {x => $s, y => @v};
# RESULT:
# $sql = SELECT * FROM mytable WHERE (x = ? AND y IN (?, ?));
# @bind = ($s, @v);

# In general, an arrayref acts as a result set or reference to
# a temporary table:
my ($sql, @bind) = sql_interp
[[1, 2], [4, 5]], UNION, [{x => 2, y => 3}, {x => 5, y => 6};
# RESULT:
# $sql = (SELECT ?, ? UNION ALL SELECT ?, ?) UNION
# (SELECT ? AS x, ? AS y UNION ALL SELECT ?, ?);
# @bind = (1,2,4,5, 2,3,5,6);
my ($sql, @bind) = sql_interp
SELECT * FROM, [[1, 2], [4, 5]]
# RESULT:
# $sql = SELECT * FROM (SELECT ?, ? UNION ALL SELECT ?, ?) AS tbl0;
# @bind = (1,2,4,5);

# Each result above is suitable for passing to DBI:
my $res = $dbh->selectall_arrayref($sql, undef, @bind);

# Besides these simple techniques shown, SQL-Interpolate includes
# various optional modules to further integrate SQL::Interpolate with
# DBI and streamline the syntax with source filtering and macros (see
# the L section):

use DBIx::Interpolate FILTER => 1;
...
my $rows = $dbx->selectall_arrayref(sql[
SELECT thid, date, title, subject
FROM threads
WHERE date > $x AND subject IN @subjects
]);