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

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'

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)

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)

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.

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.

How to use Transaction.

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.
I don't want to manage transaction in this module.

AUTHOR

Takuya Tsuchida

LICENSE

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

	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)

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)

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)

delete($table_name, $where)

update($table_name, $set, $where)

execute_query($sql, \@binds)

execute_query_named($sql, $params_href)

disable_callback()

restore_callback()

How to use Transaction.

FAQ

Why don't you use DBIx::Simple?

AUTHOR

SEE ALSO

LICENSE