NAME
SQL::Executor - Thin DBI wrapper using SQL::Maker
SYNOPSIS
use DBI;
use SQL::Executor;
my $dbh = DBI->connect($dsn, $id, $pass);
my $ex = SQL::Executor->new($dbh);
#
# SQL::Maker-like interfaces
my @rows = $ex->select('SOME_TABLE', { id => 123 });
$ex->insert('SOME_TABLE', { id => 124, value => 'xxxx'} );
$ex->update('SOME_TABLE', { value => 'yyyy'}, { id => 124 } );
$ex->delete('SOME_TABLE', { id => 124 } );
#
# select using SQL with named placeholder
my @rows= $ex->select_named('SELECT id, value1 FROM SOME_TABLE WHERE value2 = :arg1', { arg1 => 'aaa' });
DESCRIPTION
SQL::Executor is thin DBI wrapper using SQL::Maker. This module provides
interfaces to make easier access to SQL.
You can execute SQL via SQL::Maker-like interface in select(),
select_row(), select_all(), select_with_fields(),
select_row_with_fields(), select_all_with_fields(), insert(),
insert_multi(), update() and delete().
If you want to use more complex select query, you can use
select_named(), select_row_named() or select_all_named() these execute
SQL with named placeholder. If you don't want to use named placeholder,
you can use select_by_sql(), select_row_by_sql() or select_all_by_sql()
these execute SQL with normal placeholder('?').
METHODS
new($dbh, $option_href)
$dbh: Database Handler $option_href: option
available option is as follows
* allow_empty_condition (BOOL default 1): allow empty condition(where)
in select/delete/update
* callback (coderef): specify callback coderef. callback is called for
each select* method
* check_empty_bind (BOOL default 0): if TRUE(1), select*_named() do
not accept unbound parameter, see named_bind() for detail.
These callbacks are useful for making row object.
my $ex = SQL::Executor->new($dbh, {
callback => sub {
my ($self, $row, $table_name, $select_id) = @_;
return CallBack::Class->new($row);
},
});
my $row = $ex->select_by_sql($sql1, \@binds1, 'TEST');
# $row isa 'CallBack::Class'
connect($dsn, $user, $pass, $option_for_dbi, $option_href)
$dsn: DSN $user: database user $pass: database password
$option_href_for_dbi: options passed to DBI $option_href: option for
SQL::Executor (options are same as new() method)
connect database and create SQL::Executor instance. using this method,
SQL::Executor uses managed connection and transaction via DBIx::Handler
dbh()
return database handler
select($table_name, $where, $option)
select row(s). parameter is the same as select method in SQL::Maker. But
array ref for filed names are not needed. In array context, this method
behaves the same as select_all. In scalar context, this method behaves
the same as select_one
select_row($table_name, $where, $option)
select only one row. parameter is the same as select method in
SQL::Maker. But array ref for filed names are not needed. this method
returns hash ref and it is the same as return value in DBI's
selectrow_hashref/fetchrow_hashref.
select_all($table_name, $where, $option)
select all rows. parameter is the same as select method in SQL::Maker.
But array ref for filed names are not needed. this method returns array
that is composed of hash refs. (hash ref is same as DBI's
selectrow_hashref/fetchrow_hashref).
select_itr($table_name, $where, $option)
select and returns iterator. parameter is the same as select method in
SQL::Maker. But array ref for field names are not needed. Iterator is
SQL::Executor::Iterator object.
my $itr = select_itr('SOME_TABLE', { name => 'aaa' });
while( my $row = $itr->next ) {
# ... using row
}
select_named($sql, $params_href, $table_name)
select row(s). In array context, this method behaves the same as
select_all_with_fields. In scalar context, this method behaves the same
as select_one_with_fileds
You can use named placeholder in SQL like this,
my $ex = SQL::Executor->new($dbh);
my $row = $ex->select_named("SELECT * FROM SOME_TABLE WHERE id = :id", { id => 1234 });
$table_name is used for callback.
select_row_named($sql, $params_href, $table_name)
select only one row. You can use named placeholder in SQL like this,
my $ex = SQL::Executor->new($dbh);
my $row = $ex->select_row_named("SELECT * FROM SOME_TABLE WHERE id = :id", { id => 1234 });
this method returns hash ref and it is the same as return value in DBI's
selectrow_hashref/fetchrow_hashref.
$table_name is used for callback.
select_all_named($sql, $params_href, $table_name)
select all rows. You can use named placeholder in SQL like this,
my $ex = SQL::Executor->new($dbh);
my @rows = $ex->select_all_named("SELECT * FROM SOME_TABLE WHERE id = :id", { id => 1234 });
this method returns array that is composed of hash refs. (hash ref is
same as DBI's selectrow_hashref/fetchrow_hashref). $table_name is used
for callback.
select_itr_named($sql, $params_href, $table_name)
select and returns iterator. You can use named placeholder in SQL like
this,
my $ex = SQL::Executor->new($dbh);
my $itr = $ex->select_itr_named("SELECT * FROM SOME_TABLE WHERE id = :id", { id => 1234 });
$table_name is used for callback.
named_bind($sql, $params_href, $check_empty_bind)
returns sql which is executable in execute_query() and parameters for
bind.
my ($sql, @binds) = named_bind("SELECT * FROM SOME_TABLE WHERE id = :id", { id => 123 });
# $sql => "SELECT * FROM SOME_TABLE WHERE id = ?"
# @binds => (123)
parameter $check_empty_bind is optional. By default (or set
$check_empty_bind=0), named_bind() accepts unbound parameter like this,
my ($sql, @binds) = named_bind("SELECT * FROM SOME_TABLE WHERE id = :id", { });# do not bind :id
# $sql => "SELECT * FROM SOME_TABLE WHERE id = ?"
# @binds => (undef)
if $check_empty_bind is 1, named_bind() dies when unbound parameter is
specified.
select_by_sql($sql, \@binds, $table_name)
select row(s). In array context, this method behaves the same as
select_all_with_fields. In scalar context, this method behaves the same
as select_one_with_fileds
my $ex = SQL::Executor->new($dbh);
my $row = $ex->select_by_sql("SELECT * FROM SOME_TABLE WHERE id = ?", [1234]);
$table_name is only used for callback.
select_row_by_sql($sql, \@binds, $table_name)
select only one row.
my $ex = SQL::Executor->new($dbh);
my $row = $ex->select_row_by_sql("SELECT * FROM SOME_TABLE WHERE id = ?", [1234]);
this method returns hash ref and it is the same as return value in DBI's
selectrow_hashref/fetchrow_hashref.
select_all_by_sql($sql, \@binds, $table_name)
select all rows.
my $ex = SQL::Executor->new($dbh);
my @rows = $ex->select_all_by_sql("SELECT * FROM SOME_TABLE WHERE id = ?", [1234]);
this method returns array that is composed of hash refs. (hash ref is
same as DBI's selectrow_hashref/fetchrow_hashref).
select_itr_by_sql($sql, \@binds, $table_name)
select and returns iterator
my $ex = SQL::Executor->new($dbh);
my $itr = $ex->select_itr_by_sql("SELECT * FROM SOME_TABLE WHERE id = ?", [1234]);
Iterator is SQL::Executor::Iterator object.
select_with_fields($table_name, $fields_aref, $where, $option)
select row(s). parameter is the same as select method in SQL::Maker. In
array context, this method behaves the same as select_all_with_fields.
In scalar context, this method behaves the same as
select_one_with_fileds
select_row_with_fields($table_name, $fields_aref, $where, $option)
select only one row. parameter is the same as select method in
SQL::Maker. this method returns hash ref and it is the same as return
value in DBI's selectrow_hashref/fetchrow_hashref.
select_all_with_fields($table_name, $fields_aref, $where, $option)
select all rows. parameter is the same as select method in SQL::Maker.
But array ref for filed names are not needed. this method returns array
that is composed of hash refs. (hash ref is same as DBI's
selectrow_hashref/fetchrow_hashref).
select_itr_with_fields($table_name, $fields_aref, $where, $option)
select and return iterator object(SQL::Executor::Iterator). parameter is
the same as select method in SQL::Maker.
insert($table_name, $values)
Do INSERT statement. parameter is the same as select method in
SQL::Maker.
insert_multi($table_name, @args)
Do INSERT-multi statement using SQL::Maker::Plugin::InsertMulti.
insert_on_duplicate($table_name, $insert_value_href, $update_value_href)
Do "INSERT ... ON DUPLICATE KEY UPDATE" query (works only MySQL) using
SQL::Maker::Plugin::InsertOnDuplicate.
this method is available when SQL::Maker >= 1.09 is installed. If older
version is installed, you will got error like "Can't locate
SQL/Maker/Plugin/InsertOnDuplicate.pm in @INC ..."
delete($table_name, $where)
Do DELETE statement. parameter is the same as select method in
SQL::Maker.
update($table_name, $set, $where)
Do UPDATE statement. parameter is the same as select method in
SQL::Maker.
execute_query($sql, \@binds)
execute query and returns statement handler($sth).
execute_query_named($sql, $params_href)
execute query with named placeholder and returns statement
handler($sth).
disable_callback()
disable callback temporarily,
restore_callback()
restore disabled callback.
last_insert_id(@args)
If driver is mysql, return $dbh->{mysql_insertid}.If driver is SQLite,
return $dbh->sqlite_last_insert_rowid. If other driver is used, return
$dbh->last_insert_id(@args)
handle_exception($sql, $binds_aref, $err_message)
show error message. you can override this method in subclass to provide
customized error message.
default error message is like this,
Error <I>$error_message</I> sql: <I>$sql</I>, binds:
[<I>$binds_aref</I>]\n
select_id()
generate id for select statament. but by default, id is not generated.
If you want to generate id, please override
How to use Transaction.
When create instance using connect() method, you can use DBIx::Handler's
transaction management,
use SQL::Executor;
my $ex = SQL::Executor->connect($dsn, $id, $pass);
my $txn = $ex->handler->txn_scope();
$ex->insert('SOME_TABLE', { id => 124, value => 'xxxx'} );
$ex->insert('SOME_TABLE', { id => 125, value => 'yyy'} );
$txn->commit();
Or You can use DBI's transaction (begin_work and commit).
use DBI;
use SQL::Executor;
my $dbh = DBI->connect($dsn, $id, $pass);
my $ex = SQL::Executor->new($dbh);
$dbh->begin_work();
$ex->insert('SOME_TABLE', { id => 124, value => 'xxxx'} );
$ex->insert('SOME_TABLE', { id => 125, value => 'yyy'} );
$dbh->commit();
Or you can also use transaction management modules like
DBIx::TransactionManager.
use DBI;
use SQL::Executor;
use DBIx::TransactionManager;
my $dbh = DBI->connect($dsn, $id, $pass);
my $ex = SQL::Executor->new($dbh);
my $tm = DBIx::TransactionManager->new($dbh);
my $txn = $tm->txn_scope;
$ex->insert('SOME_TABLE', { id => 124, value => 'xxxx'} );
$ex->insert('SOME_TABLE', { id => 125, value => 'yyy'} );
$txn->commit;
FAQ
Why don't you use DBIx::Simple?
* I want to use SQL::Maker.
* When I need to use complex query, I want to use named placeholder.
AUTHOR
Takuya Tsuchida <tsucchi {at} cpan.org>
SEE ALSO
DBI, SQL::Maker, DBIx::Simple
Codes for named placeholder is taken from Teng's search_named.
LICENSE
Copyright (C) Takuya Tsuchida
This library is free software; you can redistribute it and/or modify it
under the same terms as Perl itself.