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 world

ACCESSORS

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.