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_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 $error_message sql: $sql, binds: [$binds_aref]\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>

LICENSE

This library is free software; you can redistribute it and/or modify it under the same terms as Perl itself.

To install SQL::Executor, copy and paste the appropriate command in to your terminal.

cpanm

cpanm SQL::Executor

CPAN shell

perl -MCPAN -e shell
install SQL::Executor

For more information on module installation, please visit the detailed CPAN module installation guide.

	Global
`s`	Focus search bar
`?`	Bring up this help dialog

	GitHub
`g` `p`	Go to pull requests
`g` `i`	go to github issues (only if github is preferred repository)

	POD
`g` `a`	Go to author
`g` `c`	Go to changes
`g` `i`	Go to issues
`g` `d`	Go to dist
`g` `r`	Go to repository/SCM
`g` `s`	Go to source
`g` `b`	Go to file browse

	Search terms
module: (e.g. module:Plugin)
distribution: (e.g. distribution:Dancer auth)
author: (e.g. author:SONGMU Redis)
version: (e.g. version:1.00)

NAME

SYNOPSIS

DESCRIPTION

METHODS

new($dbh, $option_href)

connect($dsn, $user, $pass, $option_for_dbi, $option_href)

dbh()

select($table_name, $where, $option)

select_row($table_name, $where, $option)

select_all($table_name, $where, $option)

select_itr($table_name, $where, $option)

select_named($sql, $params_href, $table_name)

select_row_named($sql, $params_href, $table_name)

select_all_named($sql, $params_href, $table_name)

select_itr_named($sql, $params_href, $table_name)

named_bind($sql, $params_href, $check_empty_bind)

select_by_sql($sql, \@binds, $table_name)

select_row_by_sql($sql, \@binds, $table_name)

select_all_by_sql($sql, \@binds, $table_name)

select_itr_by_sql($sql, \@binds, $table_name)

select_with_fields($table_name, $fields_aref, $where, $option)

select_row_with_fields($table_name, $fields_aref, $where, $option)

select_all_with_fields($table_name, $fields_aref, $where, $option)

select_itr_with_fields($table_name, $fields_aref, $where, $option)

insert($table_name, $values)

insert_multi($table_name, @args)

insert_on_duplicate($table_name, $insert_value_href, $update_value_href)

delete($table_name, $where)

update($table_name, $set, $where)

execute_query($sql, \@binds)

execute_query_named($sql, $params_href)

disable_callback()

restore_callback()

last_insert_id(@args)

handle_exception($sql, $binds_aref, $err_message)

select_id()

How to use Transaction.

FAQ

Why don't you use DBIx::Simple?

AUTHOR

SEE ALSO

LICENSE

Module Install Instructions