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' 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. 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. 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. 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 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.