NAME
Filter::SQL - embedded SQL for perl
SYNOPSIS
# set env. var. FILTER_SQL_DBI to DBI URI of the database
use Filter::SQL;
EXEC CREATE TABLE t (v int not null);;
$v = 12345;
INSERT INTO t (v) VALUES ($v);;
foreach my $row (SELECT * FROM t;) {
print "v: $row[0]\n";
}
if (SELECT ROW COUNT(*) FROM t; == 1) {
print "1 row in table\n";
}
foreach my $row (SELECT AS HASH * FROM t;) {
print "---\n";
foreach my $name (sort keys %$row) {
print "$name: $row->{$name}\n";
}
}SYNTAX
Filter::SQL recognizes portion of source code starting from one of the keywords below as an SQL statement, terminated by a semicolon.
SELECT
SELECT ROW
EXEC
INSERT
UPDATE
DELETE
REPLACE"SELECT" statement
Executes a SQL SELECT statement. Returns an array of rows.
my @row = SELECT * FROM t;;"SELECT ROW" statement
Executes a SQL SELECT statement and returns the first row.
my @column_values = SELECT ROW * FROM t;;
my $sum = SELECT ROW SUM(v) FROM t;;"EXEC" statement
Executes following string as a SQL statement and returns statement handle.
EXEC DROP TABLE t;;
my $sth = EXEC SELECT * FROM t;;
while (my @row = $sth->fetchrow_array) {
...
}"INSERT" statement
"UPDATE" statement
"DELETE" statement
"REPLACE" statement
Executes a SQL statement and returns statement handle.
VARIABLE SUBSTITUTION
Within a SQL statement, scalar perl variables may be used. They are automatically quoted and passed to the database engine.
my @rows = SELECT v FROM t WHERE v<$min_v;;
my @rows = SELECT v FROM t WHERE s LIKE "abc%$str";;A string between curly brackets it considered as a perl expression.
my $t = 'hello';
print SELECT ROW {$t . ' world'};; # hello worldACCESSORS
dbh =head2 dbh($new_dbh) =head2 dbh(sub { ... })
When called with no parameters, returns a database handle currently assigned. When given a parameter, registers the value as the assigned database handle. When a subref is being assigned, Filter::SQL invokes the subroutine everytime dbh is called to obtain the current database handle. The function is exported by :dbh tag.
mysql_insert_id
Accessor to $dbh->{mysql_insertid} of DBD::mysql. The function is exported by :mysql tag.
AUTHOR
Kazuho Oku
COPYRIGHT AND LICENSE
Copyright (C) 2008 by Cybozu Labs, Inc.
This library is free software; you can redistribute it and/or modify it under the same terms as Perl itself, either Perl version 5.8.6 or, at your option, any later version of Perl 5 you may have available.