NAME
DBI::BabyConnect - creates an object that holds a DBI connection to a database
SYNOPSIS
use DBI::BabyConnect;
# get a DBI::BabyConnect object to access the database as described by
# the database descriptor BABYDB_001
my $bbconn = DBI::BabyConnect->new('BABYDB_001');
# direct all STDERR to be appended to /tmp/error.log
$bbconn->HookError(">>/tmp/error.log");
# append trace information to /tmp/db.log and print DBI::trace set to level 1
$bbconn->HookTracing(">>/tmp/db.log",1);
# create the table TABLE1 based on the schema coded in TEST_TABLE.mysql, if
# table TABLE1 is found, then drop it first then recreate it
$bbconn->recreateTable('TEST_TABLE.mysql','TABLE1');
# insert in table TABLE1
$bbconn->do('INSERT INTO TABLE1 VALUES (10,20)');
$bbconn->disconnect();DESCRIPTION
This class is the base class for all DBI connection objects instantiated by the DBI::BabyConnect module. A DBI::BabyConnect instance is an object that holds the database handler attributes and an active DBI connection handle to a specific database. The current module support many drivers that can be loaded by the DBD, but it has been tested using the DBD::MySQL, with a limited testing using DBD::Oracle driver and the DBD::ODBC driver. The class enclude the fundamental methods to insert, update, and get data from the database, and it hides the complexity of the many DBI methods that are required otherwise to be programmed by yourself. Programmers do not need to do binding of data or use the may form of fetch methods. The methods should work for any database, and currently they have been tested with MySQL and Oracle.
NOTE
Before using the module DBI::BabyConnect, make sure that you understand how the module DBI works, and in particular the attributes that can affect a DBI connection as such: RaiseError, AutoCommit ... In addition, if you want to understand how this module work from the inside out, you need to have knowledge about the following Perl programming topics: how to localize a variable, how to tie to a file handle, how to redirect IO, how to redirect Perl signals, and the meaning of exit(), die() and DESTROY.
Nomenclature and Conventions
The following conventions are used in this document:
$bbconn a variable that is assigned an instance of a DBI::BabyConnect object
BABYCONNECT environment variable that is set to the URI where DBI::BabyConnect will find its configuration files
databases.pl the file that contains descriptors, each of which describe how to connect to a database using DBI
globalconf.pl the file that contains settable flags that will control globally the behavior of a DBI::BabyConnect object
BBCO a DBI::BabyConnect objectArchitecture of an Application using DBI::BabyConnect
+-----------------+
|Perl | +----------------+
|script | | |---|BBCO1|--|DBI XYZ Driver|----|XYZ Engine|----|some database|
|using |---+DBI::BabyConnect|---|BBCO2|--|DBI XYZ Driver|----|XYZ Engine|----|some database|
|DBI::BabyConnect | | |--- ...
| | +----------------+
+-----------------+The DBI::BabyConnect defines an instance
The XYZ driver can be any driver that is loaded by DBI. The current distribution has been tested with MySQL and Oracle.
BBCO's do not need to be using the same driver, for instance BBCO1 can be using MySQL driver and BBCO2 can be using an Oracle driver.
USAGE
This class has the following methods:
new( $descriptor )
Given a valid database descriptor name, this method returns a DBI::BabyConnect object connected to the datasource that is described by the database descriptor. In other words, given a valid database descriptor name, this method returns an object blessed into the appropriate DBD driver subclass. The object holds the attributes of the database handle as initially requested when instantiating the connection. The object also holds a pointer or a reference to the active connection.
The class provides methods to alter the attributes of the active connection held in the object, allowing to enable or disable the exceptions raised by the DBI::DBD module, along with the print error, the auto commit, and the rollback of transactions (that pertain to the active database handle).
You can call new() with different descriptors, hence allowing you to connect to multiple data sources from the same program.
HookError( $filename )
Given a valid instance of a DBI::BabyConnect object, this method hooks the STDERR filehandle to a filename. The writing of information to STDERR is then directed to the specified file. This is useful in situations where you want to debug CGI programs that use the DBI::BabyConnect or for developers who want to debug the module itself. DBI error messages will also be redirected to the handle open by the method HookError().
HookTracing( $filename [,tracelevel] )
Given a valid instance of a DBI::BabyConnect object, this method hooks a filehandle to a filename, and sets the trace flag of the module to true. The logging of information is then directed to the specified file.
Optionally, if you pass a tracelevel as the second argument, then the DBI::trace is enabled with that level. Select a level of 0 for no DBI::trace, 1 for minimal information, 2 for more information, etc. For instance, if tracelevel is set to 3 then a select statement (such as fetchQdaAA()) will log extensive information to the file, writing the result to the file. Setting the tracelevel to 1 will always reveal the query statements passed to DBI.
In a production environment, it is strongly recommended that you do not specify any tracelevel by setting tracelevel to 0 or by not calling this method HookTracing() at all.
BABYCONNECT Environment Variable
The module DBI::BabyConnect looks for the environment variable BABYCONNECT to locate its configuration directory. The configuration directory holds the database descriptors file (databases.pl), database configuration files (*.conf files), a global configuration file (globalconf.pl), and skeletons for SQL tables.
A typical configuration tree is shown below:
configuration/
|-- SQL
| `-- TABLES
| |-- TEST_BABYCONNECT.mysql
| |-- TEST_TABLE.mysql
| `-- TEST_TABLE.ora
`-- dbconf
|-- DATAWAREHOUSE_MAY2007.conf
|-- DATAWAREHOUSE_JUNE2007.conf
|-- databases.pl
`-- globalconf.plThe globalconf.pl file contains global configuration parameters that affect all connections to the data sources. The globalconf.pl file is explained in the section "Database Global Configuration File".
The databases.pl file contains a set of database descriptors each of which describes the connection to a data source. The databases.pl file is explained in the section "Database Descriptors File".
Skeleton tables are located in ./configuration/SQL/TABLES/, these tables are used by DBI::BabyConnect::recreateTable method to drop and recreate database tables.
Setting the environment variable can be achieved by exporting the environment variable. For instance if your configuration directory is in /opt/DBI-BabyConnect: export BABYCONNECT=/opt/DBI-BabyConnect/configuration
In a Perl script or a Perl module, you can programmatically set the environment variable in the BEGIN block:
BEGIN{ $ENV{BABYCONNECT}='/opt/DBI-BabyConnect/configuration'; }If you are using persitent DBI::BabyConnect objects by loading the Apache::BabyConnect module in Apache MD2, then you need to setup the variable prior to loading the module; the simplest way is to use the Apache configuration directive PerlSetEnv:
PerlSetEnv BABYCONNECT /opt/DBI-BabyConnect/configurationRefer to Apache::BabyConnect for more information about using DBI::BabyConnect persistence with Apache MD2.
Database Global Configuration File
The globalconf.pl contains several settable parameters that are global to the DBI::BabyConnect object. The following is a list of these parameters:
"ON_FAILED_DBIEXECUTE_ROLLBACK_AND_EXIT"
"DBSETTING_FORCE_SINGLESPACE_FOR_EMPTY_STRING"
CALLER_DISCONNECT
The CALLER_DISCONNECT enforces a check up on whether the caller has disconnected or not from DBI before DBI::BabyConnect::DESTROY method is called. If you want to depend on DBI::BabyConnect to disconnect automatically upon the object destruction then set this to 0. Typically, you do not need to call disconnect on a live DBI::BabyConnect object, because such an object is always connected with the same DBI handle for the duration of the object.
Set CALLER_DISCONNECT to 1 if you want to explicitly call DBI::BabyConnect::disconnect on a live DBI::BabyConnect object so that you disconnect the obejct from DBI yourself. Whenever you call DBI::BabyConnect::disconnect or whenever the DBI::BabyConnect object is destroyed it will check whether you have explicitly disconnected or not, and print to STDERR the state of your DBI::BabyConnect. It will also check if you are trying to disconnect on an already disconnected DBI::BabyConnect object. Such information is useful to keep you in control of the DBI handle.
For simplicity, set CALLER_DISCONNECT=0, to delegate the disconnection from DBI to DBI::BabyConnect object.
ON_FAILED_DBIEXECUTE_ROLLBACK_AND_EXIT
You may not need to set ON_FAILED_DBIEXECUTE_ROLLBACK_AND_EXIT to 1 to rollback if you call exit() from within your program (since exit() will eventually call DBI::BabyConnect::DESTROY), or if you end the class or program that uses DBI::BabyConnect (as the DESTROY is the last to be called even in Apache::BabyConnect) In either case, whenever DESTROY is called, if the autorollback is 1 and autocommit is 0 and the DBI execute has returned with failure, then the rollback is in effect.
The caller can always catch and check the return value of a DBI::BabyConnect method to see if it has failed a DBI execute. Typically DBI::BabyConnect methods return undef whenever a DBI execute fails and therefore the caller can check the return value and decide on whether to call the DBI::BabyConnect object method rollback himself or not, therefore allowing the caller to continue to work with the instance of DBI::BabyConnect object and its open DBI connection. Yet, you can configure the behavior of the DBI::BabyConnect object methods globally and tell the object methods to automatically rollback and exit on failure.
This option is settable and will work only if AutoRollback is in effect for the DBI, because DBI::BabyConnect objects delegate all rollbacks to the DBI itself. DBI rollback is in effect if and only if: RaiseError is 0 (it should be off because otherwise the DBI would have exited earlier due to the error) AutoCommit is 0 (DBI will have no effect on rollback is AutoCommit is set to 1) DBI::BabyConnect will keep track of the success or failure of DBI execute(), hence deciding on what to do on failure. # DBI will not exit if the conditions on the rollback are not met, but it will continue without effectively rolling back. # For these DBI::BabyConnect objects that have been instantiated by loading the DBI::BabyConnect with PERSISTENT_OBJECT_ENABLED set to 1 use DBI::BabyConnect 1, 1 this option will do a rollback but the exit() is redirected to Apache::exit() as it is documented by mod_perl, in which case only the perl script will exit at this point. Refer to perl/testbaby_rollback.pl If for any reason the HTTP child is terminated, or the CORE::exit() is called, or CORE::die() is called, or anything that will terminate the program and call the DESTROY of a DBI::BabyConnect instance, then this DESTROY will still check to see if a rollback conditions are met to do an effective rollback; this is different than the behavior of other application that do persistence using Apache, as the mechanism of rollback is carried externally of Apache handlers and is being dispatched within the DBI::BabyConnect object itself.
DBSETTING_FORCE_SINGLESPACE_FOR_EMPTY_STRING
When inserting new data, a scalar that refer to an empty string "" will normally keep the default value of the attribute in the database, i.e. NULL. You can set DBSETTING_FORCE_SINGLESPACE_FOR_EMPTY_STRING=1 to force the writing of a single space instead of keeping the default NULL.
ENABLE_STATISTICS_ON_DO
When ENABLE_STATISTICS_ON_DO is set to 1, a DBI::BabyConnect object maintains a table to hold statistics about the do()'s requested by identifying each entry with the query string being passed to the do() method. The programmer can then call get_do_stat() to get the object that hold the statistics. Do not enable this unless you need to collect statistics, for instance in data warehousing environment the queries to do() are limited in format and are time consuming, so you may desire to collect statistics about these do()'s queries.
ENABLE_STATISTICS_ON_SPC
When ENABLE_STATISTICS_ON_SPC is set to 1, a DBI::BabyConnect object maintains a table to hold statistics about the spc()'s requested by identifying each entry with the stored procedure name passed to the spc() method. The programmer can then call get_spc_stat() to get the object that hold the statistics. Do not enable this unless you need to collect statistics, for instance in data warehousing environment the stored procedure names passed spc() are limited in number and are time consuming, so you may desire to collect statistics about these spc()'s stored procedures.
Database Descriptors File
The databases.pl file holds a set of database descriptors. The database descriptor is an object whose attributes describe a specific connection to a data source, that is to what database to connect, how to connect, and to handle the connection programmatically in case of failure.
BABYDB_001 =>
{
Driver => 'Mysql',
Server=>'',
UserName=>'admin',
Password=>'adminxyz',
# Mysql defines a database name, CAREFUL it may be case sensitive!
DataName=>'BABYDB',
PrintError=>1,
RaiseError=>1,
AutoRollback => 1,
AutoCommit=>1,
LongTruncOk=>1,
LongReadLen => 900000,
}A descriptor specifies the driver name, the database name, and how to authenticate to connect to the database. DBI::BabyConnect allows you to have multiple descriptors each of which can be used by a DBI::BabyConnect object instance to connect to the data source.
Because it is possible to have multiple descriptors, and you can instantiate multiple DBI::BabyConnect objects, then it is possible to connect to several data sources from a single program. For example, it is possible to connect concurrently from the same program to MySQL database located on a server A, to another MySQL database located on server B, to an Oracle database located on server C, and so on.
The database handle attributes
For each of the active database connection, there are six attributes that are defined:
The first two attributes, LongTruncOk and LongReadLen, are defined for the duration of the active database connection. These two attributes cannot be altered after instantiating an initial connection.
The first four attributes, RaiseError, PrintError, AutoCommit, and AutoRollback, are boolean attributes and can be modified during the run time of a DBI::BabyConnect object. To change or check any of these attributes, the class provides setter and getter methods.
For an instance of a DBI::BabyConnect object, the flag attributes can be altered during run time. Altering the flag attributes allow you to control the behavior of an active database connection before and during each query (i.e. using a do(), spc(), getQdaAA(), getTdaAA(), etc).
When the attribute AutoRollback is set to true, the module will handle the rollback of a transaction on failure; this assumes that the AutoCommit has been set to false. If the AutoCommit has been set to true, and a database transaction fails than the AutoRollback has no effect, and the DBD::DBI will return a string rollback ineffective with AutoCommit enabled. Note also that you need to have "ON_FAILED_DBIEXECUTE_ROLLBACK_AND_EXIT" set to 1.
Connection Attribute Functions
This class contains several functions to retrieve, store, or set the attributes of the DBI::BabyConnect object.
getActiveDescriptor( )
getActiveDescriptor() returns the information about the current DBI::BabyConnect object that is initialized with the specified descriptor.
getActiveDescriptor() takes an optional argument, a hash reference, the method returns the information in that hash reference.
If no argument is passed then the method returns a string of information describing the DBI::BabyConnect object.
You can gather the DBI::BabyConnect object itself by passing a hash reference, then dereferencing it. For example:
$bbconn-> getActiveDescriptor($h);
my $bbconn2 = $$h{Connection};
# now $bbconn and $bbconn2 are the same
my $bbconn3 = $bbconn-> connection;
# now $bbconn, $bbconn2, and $bbconn3 are all the same
# you can get the DBI::db handle used by the DBI::BabyConnect
my $dbh = $$h{DBIhandle};Usually you do not need to use the method getActiveDescriptor(). This method is provided to experiment with multi-threaded DBI::BabyConnect objects.
saveLags( )
Given a DBI::BabyConnect object, this method save the attribute flags: PrintError, RaiseError, AutoCommit, and AutoRollback, to a temporary object.
restoreLags( )
Given a DBI::BabyConnect object, this method restore the attribute flags: PrintError, RaiseError, AutoCommit, and AutoRollback, from the temporary object.
resetLags( )
Given a DBI::BabyConnect object, this method reset the attribute flags: PrintError, RaiseError, AutoCommit, and AutoRollback, to their original values as they have been set at object initialization. These are the values of the "database descriptor" used when creating the DBI::BabyConnect object.
connection( )
Given a DBI::BabyConnect object, this method returns the DBI::db handle to the data source to which the object is connected.
dbname( )
Given a DBI::BabyConnect object, this method returns the name of the data source to which the object is connected.
dbserver( )
Given a DBI::BabyConnect object, this method returns the server name where the data source is located.
dbdriver( )
Given a DBI::BabyConnect object, this method returns the driver name being used by the object to connect to the data source.
dbusername( )
Given a DBI::BabyConnect object, this method returns the username used to authenticate the connection to the data source.
printerror( )
Given a DBI::BabyConnect object, this method returns the state of the PrintError attribute flag as it is being set to the active connection of the object.
If you pass an argument (0 or 1) to this method, then the method acts as a setter, setting the flag to that value.
If PrintError is set to true (1) then the DBI will print warning and error to STDERR.
Initially, when a DBI::BabyConnect object is created (using the new() method), this flag is set to the value read from the database descriptor.
The current state of the flag can also be revealed by printing the information string returned by get_handle_flags()
raiseerror( )
Given a DBI::BabyConnect object, this method returns the state of the RaiseError attribute flag as it is being set to the active connection of the object.
If you pass an argument (0 or 1) to this method, then the method acts as a setter, setting the flag to that value.
If RaiseError is set to true (1) then the connection will break if the DBD::DBI encounter an error, that is because DBD::DBI will raise the error and exit.
Initially, when a DBI::BabyConnect object is created (using the new() method), this flag is set to the value read from the database descriptor.
The current state of the flag can also be revealed by printing the information string returned by get_handle_flags()
autorollback( )
Given a DBI::BabyConnect object, this method returns the state of the AutoRollback attribute flag as it is being set to the active connection of the object.
If you pass an argument (0 or 1) to this method, then the method acts as a setter, setting the flag to that value.
If AutoRollback is set to true (1) then if a DBI execute fails within a transaction, DBI::BabyConnect rollback.
Initially, when a DBI::BabyConnect object is created (using the new() method), this flag is set to the value read from the database descriptor.
Note, that the attribute AutoRollback is not one of the predefined attributes used by the DBI module, and its behavior is defined internally to the class DBI::BabyConnect. The AutoRollback flag has no effect if set to true and AutoCommit flag (settable with autocommit()) is set to true. A rollback is not possible if AutoCommit is set to true.
The current state of the flag can also be revealed by printing the information string returned by get_handle_flags()
autocommit( )
Given a DBI::BabyConnect object, this method returns the state of the AutoCommit attribute flag as it is being set to the active connection of the object.
If you pass an argument (0 or 1) to this method, then the method acts as a setter, setting the flag to that value.
If AutoCommit is set to true (1) then all transactions are being committed to the database. If AutoCommit is set to true (1) then it is not possible to rollback, and calling the rollback() will have no effect.
Initially, when a DBI::BabyConnect object is created (using the new() method), this flag is set to the value read from the database descriptor.
The current state of the flag can also be revealed by printing the information string returned by get_handle_flags()
longtruncok( )
Given a DBI::BabyConnect object, this method returns the state of the LongTruncOk attribute flag as it is being set to the active connection of the object.
longreadlen( )
Given a DBI::BabyConnect object, this method returns the value of the LongReadLen attribute as it is being set to the active connection of the object.
storeLags( )
Store temporary the attributes of the connection.
restoreLags( )
Restore the attributes of the connection as they have been stored using the storeLags.
resetLags( )
Reset the attributes of the connection as they have been initially set by the initial database descriptor.
Class Methods
Once a new DBI::BabyConnect instance is created successfully, then the instance has a established a successfull database connection to a data source, and the new() class method will return a blessed object reference holding a database connection handle which is established with the DBI, and storing internally within the class object the initial database attributes. We will refer to the instance object returned by DBI::BabyConnect simply with the BBCO.
For each DBI::BabyConnect object that has been instantiated with the new() method of the DBI::BabyConnect module, the module provides the following methods:
recreateTable( $table_template, $table_name )
Read a table template and create a table named $table_name. If the table name exist then it will be dropped then recreated.
getTcount( $table, $column, $where )
getTcount() works on a single table. Given a table name, get the count on the columns where the $where condition is satisfied.
sqlbnd( )
sqlbnd() executes a SQL whose elements are specified by order and by type.
sqlbnd( $sql, $o_bnd, $o_typ )$sql is the SQL to be executed by the method
$o_bnd is a pseudo hash with the first element a hash reference that specify the order in which the elements will appear, and the following elements specify the ...
$o_typ is a hash reference that specify mapping each data element to their SQL type. If you are using MySQL, you can set $o_typ to undef, since the MySQL DBD driver knows how to handle the type. If you are using a different database than MySQL, such as Oracle, then you need to specify the proper SQL type mapping for the elements. For instance, when inserting a BLOB into Oracle, the SQL type for the BLOB element is 113.
Consult your driver manual for the SQL types of the driver you are using. Recall that a DBI::BabyConnet object is initially created with the driver that is specified by the database descriptor (see "Database Descriptors File").
do( $query )
On success:
return the number of rows affected
On failure:
return undef on failure if raiseerror=0 and autorollback=0
will die (calling destroy) and will explicit-rollback and will not return if raiseerror=0 and autorollback=1
will die (calling destroy) and will not return if raiseerror=1 and autorollback=0spc( $o, $stproc )
Calls the stored procedure $stproc whose parameters are prepared from the pseudo-hash passed in $o.
Calls the stored procedure $stproc. The binding parameters is derived from the object $o, which is a pseudo-hash, such that the array of the pseudo-hash itself maintains the order of the elements to be passed to the stored procedure, and these elements that are initially undefinied (at entry) will be set with the returned values of the stored procedure (when returning). Returns 1 on success and 0 on failure. The pseudo-hash contains the data values returned by the stored procedure.
spc method, takes a pseudo-hash as a first argument, and the fully specified name of a stored procedure name as the second argument. The method will setup the bindings of the parameters before executing the stored procedure; if the value passed to a parameter is undef, then the method will do a bind_param_inout, otherwise it will simply bind it as bind_param. On return, the method will set undefined parameters of the pseudo-hash to the known values returned from the stored procedure.
Currently, this method will call die() if it fails to execute the SQL of the stored procedure.
fetchQdaO( $qry, [,$recref] [,\@list] [,@bindparams] )
1- the SQL query
2- an optional record hash reference whose attributes will be set to the ones of the fetched record.
If you do not specify a hash reference, then a new hash reference is created within this method
to hold the result to be returned to the caller.
On DBI error, this method will return undef.
3- optional array reference to list the fields that you specified in the query. The listed elements
must be ordered the same way as they are listed in the query or you will end up with unpredictable
results. Although you will be constrained by following the order of the fields as they
appear in the query, this option allows a more efficient memory usage when
retrieving fields that consume large chunk of memory (i.e. BLOB) because it does not do mutiple
memory allocation or copy by value when fetching the fields, rather it assign the references
of the fetched data to appropriate fields of the records.
4- an optional list of binding params
use it when records are unique, since it returns a single (first encountered) record
record result is in $hh or \%REC
return 1 on success, 0 if no record is found, -1 if DBI errorfetchQdaAA( $qry [,$aaref] [,$href] [,@bindparams] )
Given an active database connection, this method takes a query string as an argument to fetch database and return it an a 2D array. The method uses the DBI prepare() method, and binds any parameters if provided in the method argument, then DBI execute() the query, and finally fetch the data by iterating through the DBI cursor fetchrow_arrayref.
The $extra_href is optional and is a reference to a struc that holds two attributes: max_rows and INCLUDE_HEADER.
fetchQdaAA() is given a SQL query to return data into an array of array, that is into a 2D array.
fetchQdaAA() takes four parameters in that order: 1- the SQL query 2- an optional array reference to hold the returned fetched records 3- an optional hash reference to specify the following INCLUDE_HEADER, MAX_ROWS (default to 1, everything) 4- an optional list of binding params
my $qry = qq{SELECT * FROM FR_XDRTABLE1 WHERE ID < ? AND FLD1 = ? };
my $rows = $dbhandle-> fetchQdaAA($qry, {INCLUDE_HEADER=>1,MAX_ROWS=>10});
my $rows = $dbhandle-> fetchQdaAA($qry,14,'u4_1');
# define an array ref, fill it in and expand it
my $rows=[]; # important to mark it as an array ref before calling below
$dbhandle-> fetchQdaAA($qry,$rows,{INCLUDE_HEADER=>1,MAX_ROWS=>1},14,'u4_1');fetchTdaAA( $table, $selection, $where [,$aaref] [,@bindparams] )
fetchTdaAA() method
fetchTdaAA('FR_XDRTABLE1', ' * ' , " id < ? AND FLD1 = ? ",54,'u4_1')
1- table name
2- what to select that follows the SELECT keyword
3- condition that follows the WHERE keyword
4- optional array reference that is extended with the new elements being selected. If no array reference
is passed, then a new array is created within this method to hold the result. The method returns
a reference to the array that holds the final results; otherwise, it returns undef in case there is no result.
5- binding parametersfetchTdaAO( $table, $selection, $where [,$ahref] [,$href] [,@bindparams] )
The method fetchTdaAO() retrieves object records of data using fetchrow_hashref
fetchTdaAO takes the following arguments:
1- the table name 2- what to select from the table, that is what will follow the SELECT keyword. This parameter type will determine the type of the array reference being returned by this method as shown below:
Selection Return
------------------------------------------ -----------------
a literal: "ID,UID,TMD0,FLD1,CHANGEDATE_T" Array of Objects
a wildcard * literal : " * " Array of Objects
a hash ref: {...} Array of Objects
an array: ('ID','UID','TMD0') Array of Array (preserving the order)3- condition that follows the WHERE keyword 4- An optional array reference set by the caller, allowing to expand an already allocated array with the new records being selected. If no array reference is passed, then a new array is created within this method to hold the result. The method returns a reference to the array that holds the final results; otherwise, it returns undef in case there is no result. 5- binding parameters
Closing Functions
After you have executed a SQL transaction with a DBI::BabyConnect object, usually DBI requires that you end the transaction by committing if it passes, by rolling back or raising error if it fails, by calling finish on the DBI::db handle, and by disconnecting the handle.
Usually you do not need to call any of the functions aformentioned because DBI::BabyConnect will do that transparently for you. You use DBI::BabyConnect so that you can work with an object whose connection is persisted to a data source, and the object will do all clean up upon object destruction.
The following functions are provided so that if you chose to porte an application that uses DBI directly, you can easily make use of DBI::BabyConnect without making extensive changes to the application.
commit( )
Call commit on the handle open by DBI::BabyConnect object. Provided to ease portability of programs using DBI directly.
rollback( )
rollback() delegates the rollback to DBI::rollback method, except that the localization of variable will take place prior to calling DBI::rollback. Usually, you do not need to call the rollback explicitly, as it is being called from other methods (i.e. DBI::BabyConnect::do() or DBI::BabyConnect::sqlbnd(), etc.) whenever a DBI exeucte() fails and the rollback conditions are met. Refer to DBI::BabyConnect::do() and ON_FAILED_DBIEXECUTE_ROLLBACK_AND_EXIT settable variable for more information on how this method is being invoked.
You can always call this method explicitly if you wish to handle the rollback from within your program.
finish( )
Call finish on the cursor held by DBI::BabyConnect object. Provided to ease portability of programs using DBI directly.
disconnect( )
Call the disconnect explicitly on a DBI::BabyConnect object, hence delegating the disconnection to DBI disconnect. You do not need to disconnet during the life time of a DBI::BabyConnect object, however, if you do so, then you need to reconnect() by calling DBI::BabyConnect::reconnect() if you want to keep on using the same DBI::BabyConnect object.
disconnect() will call DBI disconnect on the DBI::BabyConnect object. Usually you need to disconnect the DBI::BabyConnect object from the data source once you are done working with the object. Yet, you can rely on DBI::BabyConnect to do the disconnection upon exit or object destruction, by setting CALLER_DISCONNECT to 0. Refer to "CALLER_DISCONNECT".
Error Functions
dbierror
Returns the $DBI::err as returned by the DBD::DBI for the active dbi handler.
dbiconfess
Not implemented! (next release)
Statistical Functions
DBI::BabyConnect can collect statistics about the cumulative run time, and the system time consumed by DBI::BabyConnect objects (while accessing the data sources).
The DBI::BabyConnect::getStatCC returns statistics about all DBI::BabyConnect objects whenever using DBI::BabyConnect with connection caching and persistence.
There following three statistical functions collect statistics per DBI::BabyConnect object: get_do_stats, get_spc_stats, get_running_time
getStatCC
getStatCC() returns the statistics collected on the open DBI handles owned by the DBI::BabyConnect objects. The caching of the handles will only work whenever you instantiate the DBI::BabyConnect by enabling ENABLE_CACHING and PERSISTENT_OBJECT_ENABLED For example: use DBI::BabyConnect 1,1; will load the DBI::BabyConnect and set ENABLE_CACHING and PERSISTENT_OBJECT_ENABLED to true.
use DBI::BabyConnect (1,1) is typically called whenever using Apache::BabyConnect or whenever loading the module from a Perl script that is run under mod_perl.
This method getStatCC() takes one optional argument: - if you do not pass any argument, then this method will return a string containing the statistics collected on all open handles - if you pass a hash reference as the first argument then the statistics table is copied to this hash reference and the method will also return the reference to that hash - if you pass anything else (as a string), then the method will return a hash reference containing the statistics collected on the cached descriptor that matches that string.
get_running_time
get_running_time() returns a string containing time related information about the DBI::BabyConnect object. The string returned has the following format: cumulative-system-time / added-system-time / total-run-time
All three times are expressed in seconds and 1/100 second. cumulative-system-time represents the system+user time used by the DBI::BabyConnect object added-system-time represents the system+user time slices added per each DBI method call, and they hould add up to be close to cumulative-system-time total-run-time represents the time since the DBI::BabyConnect object was instantiated
get_do_stats
This method get_do_stat() takes one optional argument: - if you do not pass any argument, then this method will return a string containing the statistics collected - if you pass a hash reference as the first argument then the do()'s statistics table is copied to this hash reference and the method will also return the reference to that hash - if you pass anything else (as a string), then the method will return a hash reference containing the statistics collected on the do() query that match that string.
get_do_stat() returns the statistics collected on the do() method. You should have enabled to collect the statistics by seting ENABLE_STATISTICS_ON_DO to 1, otherwise the statictics table is empty. Before setting ENABLE_STATISTICS_ON_DO to 1, just know what you are doing otherwise you will imply a huge penalty on the DBI::BabyConnect object by acquiring an unecessary data structure to hold the statistics of all do()'s statement. Refer to the section "ENABLE_STATISTICS_ON_DO".
I added the ENABLE_STATISTICS_ON_DO for some system integrators working in data warehouse, where the do() robots are usually repetitive for the same set of queries and are time consuming. If your do() query is taking too long, and your do() queries are limited in number, and you want to know how many time the same query is being called (and how much system time it is consuming) then enable ENABLE_STATISTICS_ON_DO, and use the method get_do_stat() to get the statistics of all your do()'s that have served by a DBI::BabyConnect object.
get_spc_stats
Similar to "get_do_stats but statistics are collected on Stored Procedures whenever you call spc().
Logging and Tracing
This module provides a tie to a filehandle so that information can be logged during run time of the module. In addition, the filehandle can be shared with the DBI::trace() allowing to redirect the trace output to that file.
You can initialize the hook after getting the database connection by simply calling HookTracing() in which case the tracing is automatically enabled and run time information is printed to the log file. Refer to "HookTracing( $filename [,tracelevel] )".
You can redirect all STDERR output to a file by calling HookError(). Refer to "HookError( $filename )".
The hook can be ignored, and therefore no information will be logged. This is useful in a production environment after the DBI::BabyConnect objects have been tested, you can simply comment out the hook.
SUPPORT
Support for this module is provided via the <bbconn@pugboat.com> email list. A mailing list will soon be provided at babyconnect@pugboat.com.
AUTHOR
Bassem W. Jamaleddine, <bassem@pugboat.com>
MAINTAINER
PUGboat (Processors User Group), <bbconn@pugboat.com>
COPYRIGHT AND LICENSE
Copyright (C) 2001-2007 by Bassem W. Jamaleddine, 2007 by the Processors User Group (PUGboat.COM). All rights reserved. This program is free software; you can redistribute it and/or modify it under the same terms as Perl itself, either Perl version 5.8.8 or, at your option, any later version of Perl 5 you may have available.
SEE ALSO
Persisting DBI::BabyConnect objects with Apache::BabyConnect
This module is being used by Varisphere Processing Server powering the web site www.youprocess.com