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

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 = id

Each 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

AUTHOR

Jacques Deguest <jack@deguest.jp>

COPYRIGHT & LICENSE

You can use, copy, modify and redistribute this package and associated files under the same terms as Perl itself.

To install DB::Object, copy and paste the appropriate command in to your terminal.

cpanm

cpanm DB::Object

CPAN shell

perl -MCPAN -e shell
install DB::Object

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)