The London Perl and Raku Workshop takes place on 26th Oct 2024. If your company depends on Perl, please consider sponsoring and/or attending.
 / 
DBIx-Wrapper-0.15
River stage one • 1 direct dependent • 1 total dependent
/ DBIx::Wrapper

NAME

DBIx::Wrapper - A wrapper around the DBI

SYNOPSIS

 use DBIx::Wrapper;

 my $db = DBIx::Wrapper->connect($dsn, $user, $auth, \%attr);

 my $db = DBIx::Wrapper->connect($dsn, $user, $auth, \%attr,
          { error_handler => sub { print $DBI::errstr },
            debug_handler => sub { print $DBI::errstr },
          });

 my $dbi_obj = DBI->connect(...)
 my $db = DBIx::Wrapper->newFromDBI($dbi_obj);

 my $dbi_obj = $db->getDBI;

 my $rv = $db->insert($table, { id => 5, val => "myval",
                                the_date => \"NOW()",
                              });
 my $rv = $db->insert($table, { id => 5, val => "myval",
                                the_date => $db->command("NOW()"),
                              });

 my $rv = $db->replace($table, \%data);
 my $rv = $db->smartReplace($table, \%data)
 my $rv = $db->delete($table, \%keys);
 my $rv = $db->update($table, \%keys, \%data);
 my $rv = $db->smartUpdate($table, \%keys, \%data);

 my $row = $db->selectFromHash($table, \%keys);
 my $row = $db->nativeSelect($query, \@exec_args);

 my $loop = $db->nativeSelectExecLoop($query);
 foreach my $val (@vals) {
     my $row = $loop->next([ $val ]);
 }

 my $row = $db->nativeSelectWithArrayRef($query, \@exec_args);

 my $rows = $db->nativeSelectMulti($query, \@exec_args);

 my $loop = $db->nativeSelectMultiExecLoop($query)
 foreach my $val (@vals) {
     my $rows = $loop->next([ $val ]);
 }

 my $rows = $db->nativeSelectMultiWithArrayRef($query, \@exec_args);

 my $hash = $db->nativeSelectMapping($query, \@exec_args);
 my $hash = $db->nativeSelectDynaMapping($query, \@cols, \@exec_args);

 my $hash = $db->nativeSelectRecordMapping($query, \@exec_args);
 my $hash = $db->nativeSelectRecordDynaMapping($query, $col, \@exec_args);

 my $val = $db->nativeSelectValue($query, \@exec_args);

 my $row = $db->abstractSelect($table, \@fields, \%where, \@order);
 my $rows = $db->abstractSelectMulti($table, \@fields, \%where, \@order);

 my $loop = $db->nativeSelectLoop($query, @exec_args);
 while (my $row = $loop->next) {
     my $id = $$row{id};
 }

 my $rv = $db->nativeQuery($query, @exec_args);

 my $loop = $db->nativeQueryLoop("UPDATE my_table SET value=? WHERE id=?");
 $loop->next([ 'one', 1]);
 $loop->next([ 'two', 2]);

 my $id = $db->getLastInsertId;

 $db->debugOn(\*FILE_HANDLE);

 $db->setNameArg($arg)

 $db->commit();
 $db->ping();
 $db->err();

DESCRIPTION

DBIx::Wrapper provides a wrapper around the DBI that makes it a bit easier on the programmer. This module allows you to execute a query with a single method call as well as make inserts easier, etc. It also supports running hooks at various stages of processing a query (see the section on Hooks).

METHODS

connect($data_source, $username, $auth, \%attr, \%params)

Connects to the given database. The first four parameters are the same parameters you would pass to the connect call when using DBI directly.

The %params hash is optional and contains extra parameters to control the behaviour of DBIx::Wrapper itself. Following are the valid parameters.

error_handler and debug_handler

These values should either be a reference to a subroutine, or a reference to an array whose first element is an object and whose second element is a method name to call on that object. The parameters passed to the error_handler callback are the current DBIx::Wrapper object and an error string, usually the query if appropriate. The parameters passed to the debug_handler callback are the current DBIx::Wrapper object, an error string, and the filehandle passed to the debugOn() method (defaults to STDERR). E.g.,

  sub do_error {
      my ($db, $str) = @_;
      print $DBI::errstr;
  }
  sub do_debug {
      my ($db, $str, $fh) = @_;
      print $fh "query was: $str\n";
  }

  my $db = DBIx::Wrapper->connect($ds, $un, $auth, \%attr,
                                  { error_handler => \&do_error,
                                    debug_handler => \&do_debug,
                                  });
db_style

Used to control some database specific logic. The default value is 'mysql'. Currently, this is only used for the getLastInsertId() method. MSSQL is supported with a value of mssql for this parameter.

heavy

If set to a true value, any hashes returned will actually be objects on which you can call methods to get the values back. E.g.,

  my $row = $db->nativeSelect($query);
  my $id = $row->id;
  or
  my $id = $row->{id};

new($data_source, $username, $auth, \%attr, \%params)

An alias for connect().

newFromDBI($dbh)

Returns a new DBIx::Wrapper object from a DBI object that has already been created. Note that when created this way, disconnect() will not be called automatically on the underlying DBI object when the DBIx::Wrapper object goes out of scope.

getDBI()

Return the underlying DBI object used to query the database.

insert($table, \%data)

Insert the provided row into the database. $table is the name of the table you want to insert into. %data is the data you want to insert -- a hash with key/value pairs representing a row to be insert into the database.

replace($table, \%data)

Same as insert(), except does a REPLACE instead of an INSERT for databases which support it.

smartReplace($table, \%data)

 This method is MySQL specific.  If $table has an auto_increment
 column, the return value will be the value of the auto_increment
 column.  So if that column was specified in \%data, that value
 will be returned, otherwise, an insert will be performed and the
 value of LAST_INSERT_ID() will be returned.  If there is no
 auto_increment column, but primary keys are provided, the row
 containing the primary keys will be returned.  Otherwise, a true
 value will be returned upon success.

delete($table, \%keys), delete($table, \@keys)

 Delete rows from table $table using the key/value pairs in %keys
 to specify the WHERE clause of the query.  Multiple key/value
 pairs are joined with 'AND' in the WHERE clause.  The cols
 parameter can optionally be an array ref instead of a hashref.
 E.g.

     $db->delete($table, [ key1 => $val1, key2 => $val2 ])

 This is so that the order of the parameters in the WHERE clause
 are kept in the same order.  This is required to use the correct
 multi field indexes in some databases.

update($table, \%keys, \%data), update($table, \@keys, \%data)

 Update the table using the key/value pairs in %keys to specify
 the WHERE clause of the query.  %data contains the new values
 for the row(s) in the database.  The keys parameter can
 optionally be an array ref instead of a hashref.  E.g.,

     $db->update($table, [ key1 => $val1, key2 => $val2 ], \%data);

 This is so that the order of the parameters in the WHERE clause
 are kept in the same order.  This is required to use the correct
 multi field indexes in some databases.

selectFromHash($table, \%keys);

 Select from table $table using the key/value pairs in %keys to
 specify the WHERE clause of the query.  Multiple key/value pairs
 are joined with 'AND' in the WHERE clause.  Returns a single row
 as a hashref.

smartUpdate($table, \%keys, \%data)

Same as update(), except that a check is first made to see if there are any rows matching the data in %keys. If so, update() is called, otherwise, insert() is called.

nativeSelect($query, \@exec_args)

Executes the query in $query and returns a single row result (as a hash ref). If there are multiple rows in the result, the rest get silently dropped. @exec_args are the same arguments you would pass to an execute() called on a DBI object. Returns undef on error.

nativeSelectExecLoop($query)

 Like nativeSelect(), but returns a loop object that can be used
 to execute the same query over and over with different bind
 parameters.  This does a single DBI prepare() instead of a new
 prepare() for select.

 E.g.,

     my $loop = $db->nativeSelectExecLoop("SELECT * FROM mytable WHERE id=?");
     foreach my $id (@ids) {
         my $row = $loop->next([ $id ]);
     }

nativeSelectWithArrayRef($query, \@exec_args)

 Like nativeSelect(), but return a reference to an array instead
 of a hash.  Returns undef on error.  If there are no results
 from the query, a reference to an empty array is returned.

nativeSelectMulti($query, \@exec_args)

 Executes the query in $query and returns an array of rows, where
 each row is a hash representing a row of the result.  Returns
 undef on error.  If there are no results for the query, an empty
 array ref is returned.

nativeSelectMultiExecLoop($query)

 Like nativeSelectExecLoop(), but returns an array of rows, where
 each row is a hash representing a row of the result.

nativeSelectMultiWithArrayRef($query, \@exec_args)

 Like nativeSelectMulti(), but return a reference to an array of
 arrays instead of to an array of hashes.  Returns undef on error.

nativeSelectMapping($query, \@exec_args)

 Executes the given query and returns a reference to a hash
 containing the first and second columns of the results as
 key/value pairs.

nativeSelectDynaMapping($query, \@cols, \@exec_args)

 Similar to nativeSelectMapping() except you specify which
 columns to use for the key/value pairs in the return hash.  If
 the first element of @cols starts with a digit, then @cols is
 assumed to contain indexes for the two columns you wish to use.
 Otherwise, @cols is assumed to contain the field names for the
 two columns you wish to use.

 For example,

     nativeSelectMapping($query, \@exec_args) is

  equivalent (and in fact calls) to

     nativeSelectDynaMapping($query, [ 0, 1 ], $exec_args).

nativeSelectRecordMapping($query, \@exec_args)

 Similar to nativeSelectMapping(), except the values in the hash
 are references to the corresponding record (as a hash).

nativeSelectRecordDynaMapping($query, $col, \@exec_args)

 Similar to nativeSelectRecordMapping(), except you specify
 which column is the key in each key/value pair in the hash.  If
 $col starts with a digit, then it is assumed to contain the
 index for the column you wish to use.  Otherwise, $col is
 assumed to contain the field name for the two columns you wish
 to use.

nativeSelectValue($query, \@exec_args)

 Returns a single value, the first column from the first row of
 the result.  Returns undef on error or if there are no rows in
 the result.  Note this may be the same value returned for a NULL
 value in the result.

abstractSelect($table, \@fields, \%where, \@order)

Same as nativeSelect() except uses SQL::Abstract to generate the SQL. See the POD for SQL::Abstract for usage. You must have SQL::Abstract installed for this method to work.

abstractSelectMulti($table, \@fields, \%where, \@order)

Same as nativeSelectMulti() except uses SQL::Abstract to generate the SQL. See the POD for SQL::Abstract for usage. You must have SQL::Abstract installed for this method to work.

nativeSelectLoop($query, @exec_args)

Executes the query in $query, then returns an object that allows you to loop through one result at a time, e.g.,

    my $loop = $db->nativeSelectLoop("SELECT * FROM my_table");
    while (my $row = $loop->next) {
        my $id = $$row{id};
    }

    To get the number of rows selected, you can call the
    rowCountCurrent() method on the loop object, e.g.,

    my $loop = $db->nativeSelectLoop("SELECT * FROM my_table");
    my $rows_in_result = $loop->rowCountCurrent;

    The count() method is an alias for rowCountCurrent().


    To get the number of rows returned by next() so far, use the
    rowCountTotal() method.

nativeQuery($query, @exec_args)

Executes the query in $query and returns true if successful. This is typically used for deletes and is a catchall for anything the methods provided by this module don't take into account.

nativeQueryLoop($query)

A loop on nativeQuery, where any placeholders you have put in your query are bound each time you call next(). E.g.,

    my $loop = $db->nativeQueryLoop("UPDATE my_table SET value=? WHERE id=?");
    $loop->next([ 'one', 1]);
    $loop->next([ 'two', 2]);

newCommand($cmd)

This method is deprecated. Use $db->command($cmd_str) instead.

This creates a literal SQL command for use in insert(), update(), and related methods, since if you simply put something like "CUR_DATE()" as a value in the %data parameter passed to insert, the function will get quoted, and so will not work as expected. Instead, do something like this:

    my $data = { file => 'my_document.txt',
                 the_date => $db->newCommand('CUR_DATE()')
               };
    $db->insert('my_doc_table', $data);

This can also be done by passing a reference to a string with the SQL command, e.g.,

    my $data = { file => 'my_document.txt',
                 the_date => \'CUR_DATE()'
               };
    $db->insert('my_doc_table', $data);

command($cmd_string)

This creates a literal SQL command for use in insert(), update(), and related methods, since if you simply put something like "CUR_DATE()" as a value in the %data parameter passed to insert, the function will get quoted, and so will not work as expected. Instead, do something like this:

    my $data = { file => 'my_document.txt',
                 the_date => $db->command('CUR_DATE()')
               };
    $db->insert('my_doc_table', $data);

This can also be done by passing a reference to a string with the SQL command, e.g.,

    my $data = { file => 'my_document.txt',
                 the_date => \'CUR_DATE()'
               };
    $db->insert('my_doc_table', $data);

This is currently how command() is implemented.

debugOn(\*FILE_HANDLE)

Turns on debugging output. Debugging information will be printed to the given filehandle.

debugOff()

Turns off debugging output.

setNameArg($arg)

This is the argument to pass to the fetchrow_hashref() call on the underlying DBI object. By default, this is 'NAME_lc', so that all field names returned are all lowercase to provide for portable code. If you want to make all the field names return be uppercase, call $db->setNameArg('NAME_uc') after the connect() call. And if you really want the case of the field names to be what the underlying database driveer returns them as, call $db->setNameArg('NAME').

err()

Calls err() on the underlying DBI object, which returns the native database engine error code from the last driver method called.

errstr()

Calls errstr() on the underlying DBI object, which returns the native database engine error message from the last driver method called.

DBI methods

 The following method calls are just passed through to the
 underlying DBI object for convenience.  See the documentation
 for DBI for details.
prepare
 This method may call hooks in the future.  Use
 prepare_no_hooks() if you want to ensure that it will be a
 simple DBI call.
selectrow_arrayref
selectrow_hashref
selectall_arrayref
selectall_hashref
selectcol_arrayref
do
quote
commit
begin_work
rollback
ping

getLastInsertId(), get_last_insert_id(), last_insert_id()

 Returns the last_insert_id.  The default is to be MySQL
 specific.  It just runs the query "SELECT LAST_INSERT_ID()".
 However, it will also work with MSSQL with the right parameters
 (see the db_style parameter in the section explaining the
 connect() method).

Hooks

DBIx::Wrapper supports hooks that get called just before and just after various query operations. The add*Hook methods take a single argument that is either a code reference (e.g., anonymous subroutine reference), or an array whose first element is an object and whose second element is the name of a method to call on that object.

The hooks will be called with a request object as the first argument. See DBIx::Wrapper::Request.

The two expected return values are $request->OK and $request->DECLINED. The first tells DBIx::Wrapper that the current hook has done everything that needs to be done and doesn't call any other hooks in the stack for the current request. DECLINED tells DBIx::Wrapper to continue down the hook stack as if the current handler was never invoked.

See DBIx::Wrapper::Request for example hooks.

addPrePrepareHook($hook)

Specifies a hook to be called just before any SQL statement is prepare()'d.

addPostPrepareHook($hook)

Specifies a hook to be called just after any SQL statement is prepare()'d.

addPreExecHook($hook)

Specifies a hook to be called just before any SQL statement is execute()'d.

addPostExecHook($hook)

Adds a hook to be called just after a statement is execute()'d.

addPreFetchHook($hook)

Adds a hook to be called just before data is fetch()'d from the server.

addPostFetchHook($hook)

Adds a hook to be called just after data is fetch()'d from the server.

There are also underscore_separated versions of these methods.

    E.g., nativeSelectLoop() becomes native_select_loop()

ACKNOWLEDGEMENTS

    Others who have contributed ideas and/or code for this module:

    Kevin Wilson
    Mark Stosberg

AUTHOR

    Don Owens <don@owensnet.com>

COPYRIGHT

    Copyright (c) 2003-2005 Don Owens

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

VERSION

    0.15

1 POD Error

The following errors were encountered while parsing the POD:

Around line 2078:

You forgot a '=back' before '=head2'