NAME
DB::Object::Statement - Statement Object
SYNOPSIS
say $sth->as_string;
$sth->bind_param( 2, $binded_value );
$sth->bind_param( 2, $binded_value, $binded_type );
$sth->commit;
my $dbh = $sth->database_object;
$sth->distinct;
say $sth->dump;
say $sth->execute;
$sth->execute( $val1, $val2 ) || die( $sth->error );
# explicitly specify types
# Here in this mixed example, $val1 and $val3 have known types
$tbl->where( $dbh->AND(
$tbl->fo->name == '?',
$tbl->fo->city == '?',
'?' == $dbh->ANY( $tbl->fo->alias )
) );
my $sth = $tbl->select || die( $tbl->error );
$sth->execute( $val1, $val2, { $val3 => 'varchar' } ) || die( $sth->error );
my $ref = $sth->fetchall_arrayref;
my $val = $sth->fetchcol;
my %hash = $sth->fetchhash;
my @values = $sth->fetchrow;
my $ref = $sth->fetchrow_hashref;
my $obj = $sth->fetchrow_object;
$sth->finish;
$sth->ignore;
$sth->join( $join_condition );
my $qo = $sth->query_object;
$sth->rollback;
my $rows = $sth->rows;
my $dbi_sth = $sth->sth;
my $tbl = $sth->table_object;VERSION
v0.6.2
DESCRIPTION
This is the statement object package from which other driver specific packages inherit from.
METHODS
as_string
Returns the current statement object as a string.
bind_param
Provided with a list of arguments and they will be passed to "bind_param" in DBI
If an error occurred, an error is returned, otherwise the return value of calling bind_param is returned.
commit
If the statement parameter autocommit is true, a COMMIT statement will be prepared and executed.
The current object is returned.
database_object
Sets or gets the current database object.
distinct
Assuming a query object property has already been set previously, this will add the DISTINCT keyword to it if not already set.
If "distinct" is called in void context, the query is executed immediately.
The query statement is returned.
dump
Provided with a file and this will print on STDOUT the columns used, separated by a tab and then will process each rows fetched with DBI::fetchrow and will join the column valus with a tab before printing it out to STDOUT.
It returns the current object for chaining.
exec
This is an alias for "execute"
execute
$sth->execute || die( $sth->error );
$sth->execute( $val1, $val2 ) || die( $sth->error );
# explicitly specify types
# Here in this mixed example, $val1 and $val3 have known types
$tbl->where( $dbh->AND(
$tbl->fo->name == '?',
$tbl->fo->city == '?',
'?' == $dbh->ANY( $tbl->fo->alias )
) );
my $sth = $tbl->select || die( $tbl->error );
$sth->execute( $val1, $val2, { $val3 => 'varchar' } ) || die( $sth->error );If binded values have been prepared, they are applied here before executing the query.
Sometime, you need to clearly specify what the datatype are for the value provided with execute, because DB::Object::Query could not figure it out.
Thus, if you do:
$tbl->where(
$tbl->fo->name == '?'
);DB::Object::Query knows the datatype, because you are using a field object (fo), but if you were doing:
$tbl->where(
'?' == $dbh->ANY( $tbl->fo->alias )
);In this scenario, DB::Object::Query does not know what the bind value would be, although we could venture a guess by looking at the right-hand side, but this is a bit hazardous. So you are left with a placeholder, but no datatype. So you would execute like:
$sth->execute({ $val => 'varchar' });If the total number of binded values does not match the total number of binded type, this will trigger a warning.
"execute" in DBI will be called with the binded values and if this method was called in an object context, the current object is returned, otherwise the returned value from "execute" in DBI is returned.
With the version 0.5.0 of this module, this method is more able to find out the data type of the table field. To achieve this, it uses the field object set in each element object. Those element objects are instantiated upon insert or update query.
Also, if you provide a value during an insert or update for a field that the database expects an array, this method will automatically convert it into an array.
Likewise, if the table field is of type json or jsonb and an hash reference value is provided, this method will encode the hash reference into a JSON string.
executed
Returns true if this statement has already been executed, and false otherwise.
fetchall_arrayref
Similar to "fetchall_arrayref" in DBI, this will execute the query and return an array reference of data.
fetchcol
Provided with an integer that represents a column number, starting from 0, and this will get each row of results and add the value for the column at the given offset.
it returns a list of those column value fetched.
fetchhash
This will retrieve an hash reference for the given row and return it as a regular hash.
fetchrow
This will retrieve the data from database using "fetchrow_arrayref" and return the list of data as array in list context, or the first entry of the array in scalar context.
fetchrow_hashref
This will retrieve the data from the database as an hash reference.
It will convert any data from json to hash reference if "auto_decode_json" in DB::Object is set to true.
it will also convert any datetime data into a DateTime object if "auto_convert_datetime_to_object" in DB::Object is true.
It returns the hash reference retrieved.
fetchrow_object
This will create dynamically a package named DB::Object::Postgres::Result::SomeTable for example and load the hash reference retrieved from the database into this dynamically created packackage.
It returns the object thus created.
finish
Calls "finish" in DBI and return the returned value, or an error if an error occurred.
ignore
This will change the query prepared and add the keyword IGNORE.
If called in void context, this will execute the resulting statement handler immediately.
join
Provided with a target and an hash reference, or list or array reference of condition for the join and this will prepare the join statement.
If the original query is not of type select, this will trigger an error.
The target mentioned above can be either a DB::Object::Statement object, or a table object (DB::Object::Tables), or even just a string representing the name of a table.
$tbl->select->join( $sth );
$tbl->select->join( $other_tbl );
$tbl->select->join( 'table_name' );The condition mentioned above can be a DB::Object::Operator (AND, OR or NOT), in which case the actual condition will be taken from that operator embedded value.
The condition can also be a DB::Object::Fields::Overloaded object, which implies a table field with some operator and some value.
$tbl->select->join( $other_tbl, $other_tbl->fo->id == 2 );Here $other_tbl-fo->id == 2> will become a DB::Object::Fields::Overloaded object.
The condition can also be an array reference or array object of conditions and implicitly the array entry will be joined with AND:
$tbl->select->join( $other_tbl, ["user = 'joe'", $other_tbl->fo->id == 2] );The condition can also be an hash reference with each key being a table name to join and each value an hash reference of condition for that particular join with each key being a column name and each value the value of the join for that column.
my $tbl = $dbh->first_table;
$tbl->select->join({
other_table =>
{
id => 'first_table.id',
user => 'first_table.user',
},
yet_another_table =>
{
id => 'other_table.id',
},
});would become something like:
SELECT *
FROM first_table
LEFT JOIN other_table ON
first_table.id = id AND
first_table.user = user
LEFT JOIN yet_another_table ON
other_table.id = idEach condition will be formatted assuming an AND expression, so this is less flexible than using operator objects and table field objects.
If no condition is provided, this is taken to be a straight join.
$tbl->where( $tbl->fo->id == 2 );
$other_tbl->where( $other_tbl->fo->user 'john' );
$tbl->select->join( $other_tbl );Would become something like:
SELECT *
FROM first_table, other_table
WHERE id = 2 AND user = 'john'If called in void context, this will execute the resulting statement handler immediately.
It returns the resulting statement handler.
It returns the statement handler.
object
Returns the statement object explicitly.
my $sth = $tbl->select->object;which is really equivalent to:
my $sth = $tbl->select;priority
Provided with a priority integer that can be 0 or 1 with 0 being LOW_PRIORITY and 1 being HIGH_PRIORITY and this will adjust the query formatted to add the priority. This works only on Mysql drive though.
If used on queries other than DELETE, INSERT, REPLACE, SELECT, UPDATE an error will be returned.
If called in void context, this will execute the newly create statement handler immediately.
It returns the newly create statement handler.
promise
This the same as calling "execute", except that the query will be executed asynchronously and a Promise::Me object will be returned, so you can do asynchronous queries like this:
my $sth = $dbh->prepare( "SELECT some_slow_function(?)" ) || die( $dbh->error );
my $p = $sth->promise(10)->then(sub
{
my $st = shift( @_ );
my $ref = $st->fetchrow_hashref;
my $obj = My::Module->new( %$ref );
})->catch(sub
{
$log->warn( "Failed to execute query: ", @_ );
});
my( $obj ) = await( $p );query
Sets or gets the previously formatted query as a regular string.
query_object
Sets or gets the query object used in this query.
query_time
Sets or gets the query time as a DateTime object.
rollback
If there is a statement handler and the database parameter autocommit is set to true, this will prepare a ROLLBACK query and execute it.
rows
Returns the number of rows affected by the last query.
sth
Sets or gets the DBI statement handler.
table
Sets or gets the table object (DB::Object::Tables) for this query.
table_object
Sets or get the table object (DB::Object::Tables)
undo
This is an alias for "rollback"
wait
The implementation is driver dependent, and in this case, this is implemented only in DB::Object::Mysql
_convert_datetime2object
A convenient short to enable or disable "_convert_datetime2object" in DB::Object
_convert_json2hash
A convenient short to enable or disable "_convert_json2hash" in DB::Object
SEE ALSO
DB::Object::Query, DB::Object::Mysql::Query, DB::Object::Postgres::Query, DB::Object::SQLite::Query
AUTHOR
Jacques Deguest <jack@deguest.jp>
COPYRIGHT & LICENSE
Copyright (c) 2019-2021 DEGUEST Pte. Ltd.
You can use, copy, modify and redistribute this package and associated files under the same terms as Perl itself.