DBIx::Wrapper(3) User Contributed Perl Documentation DBIx::Wrapper(3) NNAAMMEE DBIx::Wrapper - A wrapper around the DBI SSYYNNOOPPSSIISS 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(); DDEESSCCRRIIPPTTIIOONN 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. MMEETTHHOODDSS ccoonnnneecctt(($$ddaattaa__ssoouurrccee,, $$uusseerrnnaammee, $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. Currently the only valid entries are error_handler and debug_handler, the value of which 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 call- back 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 _d_e_b_u_g_O_n_(_) 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, }); nneeww(($$ddaattaa__ssoouurrccee,, $$uusseerrnnaammee, $auth, \%attr) An alias for _c_o_n_n_e_c_t_(_). nneewwFFrroommDDBBII(($$ddbbhh)) Returns a new DBIx::Wrapper object from a DBI object that has already been created. Note that when created this way, _d_i_s_c_o_n_n_e_c_t_(_) will not be called automatically on the underlying DBI object when the DBIx::Wrapper object goes out of scope. _g_e_t_D_B_I_(_) Return the underlying DBI object used to query the database. iinnsseerrtt(($$ttaabbllee,, \\%%ddaattaa)) 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. rreeppllaaccee(($$ttaabbllee,, \\%%ddaattaa)) Same as _i_n_s_e_r_t_(_), except does a REPLACE instead of an INSERT for databases which support it. ssmmaarrttRReeppllaaccee(($$ttaabbllee,, \\%%ddaattaa)) 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. ddeelleettee(($$ttaabbllee,, \\%%kkeeyyss)),, ddeelleettee(($$ttaabbllee,, \\@@kkeeyyss)) 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. uuppddaattee(($$ttaabbllee,, \\%%kkeeyyss,, \\%%ddaattaa)),, uuppddaattee(($$ttaabbllee,, \\@@kkeeyyss,, \\%%ddaattaa)) 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. sseelleeccttFFrroommHHaasshh(($$ttaabbllee,, \\%%kkeeyyss));; 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. ssmmaarrttUUppddaattee(($$ttaabbllee,, \\%%kkeeyyss,, \\%%ddaattaa)) Same as _u_p_d_a_t_e_(_), except that a check is first made to see if there are any rows matching the data in %keys. If so, _u_p_d_a_t_e_(_) is called, otherwise, _i_n_s_e_r_t_(_) is called. nnaattiivveeSSeelleecctt(($$qquueerryy,, \\@@eexxeecc__aarrggss)) 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 _e_x_e_- _c_u_t_e_(_) called on a DBI object. Returns undef on error. nnaattiivveeSSeelleeccttEExxeeccLLoooopp(($$qquueerryy)) 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 ]); } nnaattiivveeSSeelleeccttWWiitthhAArrrraayyRReeff(($$qquueerryy,, \\@@eexxeecc__aarrggss)) 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. nnaattiivveeSSeelleeccttMMuullttii(($$qquueerryy,, \\@@eexxeecc__aarrggss)) 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. nnaattiivveeSSeelleeccttMMuullttiiEExxeeccLLoooopp(($$qquueerryy)) Like nativeSelectExecLoop(), but returns an array of rows, where each row is a hash representing a row of the result. nnaattiivveeSSeelleeccttMMuullttiiWWiitthhAArrrraayyRReeff(($$qquueerryy,, \\@@eexxeecc__aarrggss)) Like nativeSelectMulti(), but return a reference to an array of arrays instead of to an array of hashes. Returns undef on error. nnaattiivveeSSeelleeccttMMaappppiinngg(($$qquueerryy,, \\@@eexxeecc__aarrggss)) Executes the given query and returns a reference to a hash containing the first and second columns of the results as key/value pairs. nnaattiivveeSSeelleeccttDDyynnaaMMaappppiinngg(($$qquueerryy,, \\@@ccoollss,, \\@@eexxeecc__aarrggss)) 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). nnaattiivveeSSeelleeccttRReeccoorrddMMaappppiinngg(($$qquueerryy,, \\@@eexxeecc__aarrggss)) Similar to nativeSelectMapping(), except the values in the hash are references to the corresponding record (as a hash). nnaattiivveeSSeelleeccttRReeccoorrddDDyynnaaMMaappppiinngg(($$qquueerryy,, $$ccooll,, \\@@eexxeecc__aarrggss)) 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. nnaattiivveeSSeelleeccttVVaalluuee(($$qquueerryy,, \\@@eexxeecc__aarrggss)) 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. aabbssttrraaccttSSeelleecctt(($$ttaabbllee,, \\@@ffiieellddss,, \\%%wwhheerree,, \\@@oorrddeerr)) Same as _n_a_t_i_v_e_S_e_l_e_c_t_(_) 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. aabbssttrraaccttSSeelleeccttMMuullttii(($$ttaabbllee,, \\@@ffiieellddss,, \\%%wwhheerree,, \\@@oorrddeerr)) Same as _n_a_t_i_v_e_S_e_l_e_c_t_M_u_l_t_i_(_) 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. nnaattiivveeSSeelleeccttLLoooopp(($$qquueerryy,, @@eexxeecc__aarrggss)) 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. nnaattiivveeQQuueerryy(($$qquueerryy,, @@eexxeecc__aarrggss)) 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. nnaattiivveeQQuueerryyLLoooopp(($$qquueerryy)) A loop on nativeQuery, where any placeholders you have put in your query are bound each time you call _n_e_x_t_(_). E.g., my $loop = $db->nativeQueryLoop("UPDATE my_table SET value=? WHERE id=?"); $loop->next([ ’one’, 1]); $loop->next([ ’two’, 2]); nneewwCCoommmmaanndd(($$ccmmdd)) This method is deprecated. Use $db->command($cmd_str) instead. This creates a literal SQL command for use in _i_n_s_e_r_t_(_), _u_p_d_a_t_e_(_), and related methods, since if you simply put something like "_C_U_R___D_A_T_E_(_)" 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); ccoommmmaanndd(($$ccmmdd__ssttrriinngg)) This creates a literal SQL command for use in _i_n_s_e_r_t_(_), _u_p_d_a_t_e_(_), and related methods, since if you simply put something like "_C_U_R___D_A_T_E_(_)" 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 _c_o_m_m_a_n_d_(_) is implemented. ddeebbuuggOOnn((\\**FFIILLEE__HHAANNDDLLEE)) Turns on debugging output. Debugging information will be printed to the given filehandle. _d_e_b_u_g_O_f_f_(_) Turns off debugging output. sseettNNaammeeAArrgg(($$aarrgg)) This is the argument to pass to the _f_e_t_c_h_r_o_w___h_a_s_h_r_e_f_(_) 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 _c_o_n_n_e_c_t_(_) 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’). _e_r_r_(_) Calls _e_r_r_(_) on the underlying DBI object, which returns the native database engine error code from the last driver method called. _c_o_m_m_i_t_(_) Calls _c_o_m_m_i_t_(_) on the underlying DBI object to commit your transac- tions. _p_i_n_g_(_) Calls _p_i_n_g_(_) on the underlying DBI object to see if the database con- nection is still up. _g_e_t_L_a_s_t_I_n_s_e_r_t_I_d_(_),, _g_e_t___l_a_s_t___i_n_s_e_r_t___i_d_(_),, _l_a_s_t___i_n_s_e_r_t___i_d_(_) Returns the last_insert_id. This is MySQL specific for now. It just runs the query "SELECT LAST_INSERT_ID()". TThheerree aarree aallssoo uunnddeerrssccoorree__sseeppaarraatteedd vveerrssiioonnss ooff tthheessee mmeetthhooddss.. E.g., nativeSelectLoop() becomes native_select_loop() TTOODDOO More logging/debugging options Allow _p_r_e_p_a_r_e_(_) and _e_x_e_c_u_t_e_(_) for easier integration into existing code. AACCKKNNOOWWLLEEDDGGEEMMEENNTTSS People who have contributed ideas and/or code for this module: Kevin Wilson Mark Stosberg AAUUTTHHOORR Don Owens CCOOPPYYRRIIGGHHTT Copyright (c) 2003-2004 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. VVEERRSSIIOONN 0.12 perl v5.8.1 2004-07-20 DBIx::Wrapper(3)