DBI::BabyConnect - creates an object that holds a DBI connection to a database
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'); my $sql = qq{ INSERT INTO TABLE1 (DATASTRING,DATANUM,IMAGE,RECORDDATE_T) VALUES (?,?,?,SYSDATE()) }; $bbconn-> sqlbnd($sql,$dataStr,1000,$imgGif);
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.
DBD::MySQL
DBD::Oracle
DBD::ODBC
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.
DBI
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 object
+-----------------+ |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 creates an object instance to access a data source as being described by a database descriptor.
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 all simultaneous connection. For instance BBCO1 can be using MySQL driver and BBCO2 can be using an Oracle driver. Therefore, an application using DBI::BabyConnect should be able to access many different data sources from the same program.
If your application needs only to read data from the database then you should be able to use DBI::BabyConnect to access the database concurrently by starting several processes with DBI::BabyConnect objects.
If your application need to write to the data source, you can still use DBI::BabyConnect objects to write concurrently, however you need to be known what you are doing.
The DBI::BabyConnect distribution comes with a set of sample programs to assist you in testing your installation. All programs are located in eg/ directory. The file eg/README show a roadmap on how to use the programs. You need to have MySQL installed, and you need to create the database BABYDB.
The distribution also comes with a configuration/ directory. You need to locate the file configuration/dbconf/databases.pl and make the proper moditication to the descriptors so that you can access the databases.
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 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.
new()
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.
fetchQdaAA()
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.
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 |-- databases.pl `-- globalconf.pl
The 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 recreateTable method to drop and recreate database tables.
recreateTable
Setting the environment variable can be achieved by exporting the environment variable. For instance if your configuration directory is in /opt/DBI-BabyConnect-0.93: export BABYCONNECT=/opt/DBI-BabyConnect-0.93/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-0.93/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:
Apache::BabyConnect
PerlSetEnv BABYCONNECT /opt/DBI-BabyConnect-0.93/configuration
Refer to Apache::BabyConnect for more information about using DBI::BabyConnect persistence with Apache MD2.
The globalconf.pl contains several settable parameters that are global to the DBI::BabyConnect object. The following is a list of these parameters:
"CALLER_DISCONNECT"
"ON_FAILED_DBIEXECUTE_ROLLBACK_AND_EXIT"
"DBSETTING_FORCE_SINGLESPACE_FOR_EMPTY_STRING"
"ENABLE_STATISTICS_ON_DO"
"ENABLE_STATISTICS_ON_SPC"
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 "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 in control of the DBI handles.
For simplicity, set CALLER_DISCONNECT=0, to allow automatic disconnection and delegate the disconnection to the DBI::BabyConnect object.
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. See eg/perl/testrollback.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.
When inserting new data, a scalar that refers 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.
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.
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.
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.
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).
do()
spc()
getQdaAA()
getTdaAA()
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.
This class contains several functions to retrieve, store, or set the attributes of the DBI::BabyConnect object.
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.
Given a DBI::BabyConnect object, this method save the attribute flags: PrintError, RaiseError, AutoCommit, and AutoRollback, to a temporary object.
DBI::BabyConnect
Given a DBI::BabyConnect object, this method restore the attribute flags: PrintError, RaiseError, AutoCommit, and AutoRollback, from the temporary object.
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. See "Database Descriptors File".
Given a DBI::BabyConnect object, this method returns the DBI::db handle to the data source to which the object is connected.
Given a DBI::BabyConnect object, this method returns the name of the data source to which the object is connected.
Given a DBI::BabyConnect object, this method returns the server name where the data source is located.
Given a DBI::BabyConnect object, this method returns the driver name being used by the object to connect to the data source.
Given a DBI::BabyConnect object, this method returns the username used to authenticate the connection to the data source.
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. Refer to "Database Descriptors File".
The current state of the flag can also be revealed by printing the information string returned by get_handle_flags()
get_handle_flags()
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 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.
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 AutoRollback is set to true (1) then if a DBI execute fails within a transaction, DBI::BabyConnect rollback.
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.
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 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.
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.
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.
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 exists then drop it and recreate it. See eg/createtables.pl for an example.
Note that the table template is read from one of the skeletons located in the directory $ENV{BABYCONNECT}/SQL/TABLES. The skeleton files are text flat files that contains SQL commands. These files use the tilda ~ as a seperator, and -- starting at the beginning of the line for comments.
recreateTableFromString( $tableStr, $table_name )
recreateTableFromString() is similar to recreateTable(), except that it takes a table template as a string. See eg/recreateTableFromString_mysql.pl and eg/recreateTableFromString_ora.pl.
getTcount( $table, $column, $where )
getTcount() takes a database table name, a specific column name, and return the count of rows where the $where condition is satisfied.
See eg/getTcount.pl for an example.
insertrec( $table, %rec )
insertrec() is a method that simply inserts a record in a database table. The method takes two parameters: a table name, and a hash. The record is passed as a hash, and the attributes specify the values of the data to be inserted. For all data that is to be inserted as characters or binary, use a reference to a SCALAR. See eg/insertrec.pl for an example.
For more constructive SQL inserts, use the method "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 being a hash reference that specify the order in which the elements will appear, and the following ordered elements specify the values of the elements.
$o_typ is a hash reference that maps each data element to its corresponding 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 103.
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=0
spc( $o, $stproc )
Calls the stored procedure $stproc whose parameters are prepared from the pseudo-hash passed in $o.
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. Returns 1 on success and 0 on failure. The pseudo-hash contains the data values returned by the stored procedure.
Currently, this method will call die() if it fails to execute the SQL of the stored procedure.
spc() works with Oracle stored procedure, the following code shows the package that will dequeue messages from a persistent database queue:
package DataManagement::Queue; use DBI::BabyConnect; # this mini sub-package only knows how to to dequeue # from our persisted database queue @ISA=(Queue); sub new { my $type = shift; my $db_descriptor = shift; my $_ORA_PKG = 'PKG_DATA_MANAGEMENT'; my $_QTABLE = 'TASK_QUEUE'; my $_bbconn = DBI::BabyConnect->new($db_descriptor); #$_bbconn->HookTracing(">>/tmp/db.log",1); $_bbconn->printerror(1); $_bbconn->raiseerror(0); $_bbconn->autorollback(1); $_bbconn->autocommit(1); my $this = { _bbconn => DBI::BabyConnect->new($db_descriptor), _ORA_PKG => $_ORA_PKG, _QTABLE => $_QTABLE, }; bless $this, $type; } sub hasNext { my $this = shift; my $o = shift; my $ORA_PKG = $this->{_ORA_PKG}; $this{_bbconn}-> spc($o,"$ORA_PKG.spc_DequeueTask") && return 1; return 0; } sub getNext { my $this = shift; my $o = [ {task_key=>1,task_type=>2,task_arguments=>3}, undef,undef,undef]; return undef unless $this-> hasNext($o); if (defined $$o{tsq_param}) { $this->{task_key}=$$o{task_key}; $this->{task_type}=$$o{task_type}; $this->{task_arguments}=$$o{task_arguments}; } return $o; } 1;
The package DataManagement::Queue use the Oracle stored procedure spc_DequeueTask stored in the package ACME_DATAWAREHOUSE.PKG_DATA_MANAGEMENT.
CREATE OR REPLACE PACKAGE BODY ACME_DATAWAREHOUSE.PKG_DATA_MANAGEMENT PROCEDURE spc_DequeueTask ( task_key_out IN OUT TASK_QUEUE.TASK_KEY%TYPE, task_type_out IN OUT TASK_QUEUE.TASK_TYPE%TYPE, task_arguments_out IN OUT TASK_QUEUE.TASK_ARGUMENTS%TYPE ) AS PRAGMA AUTONOMOUS_TRANSACTION; BEGIN BEGIN SELECT TASK_KEY, TASK_TYPE, TASK_ARGUMENTS INTO task_key_out, task_type_out, task_arguments_out FROM TASK_QUEUE WHERE STATUS_CODE = 'WAITING' AND ROWNUM <= 1 FOR UPDATE; EXCEPTION WHEN NO_DATA_FOUND THEN ROLLBACK; END; UPDATE TASK_QUEUE SET STATUS_CD = 'INPROCESS', DEQUEUED_DATE = SYSDATE WHERE TASK_KEY = task_key_out; COMMIT; END; END PKG_DATA_MANAGEMENT /
The package DataManagement::Queue shows how to use spc(), but it does not include the detailed implementation of the database Queue in Oracle.
fetchQdaO( $qry, ,$recref ,\@list ,@bindparams )
fetchQdaO() fetches a record from the data source as specified by the SQL query, and it returns a single first encountered record in the result. The method returns the hash reference holding the fetched record.
fetchQdaO() takes the following 4 arguments:
1- the SQL query, it can be a simple query or a join.
2- an optional hash reference pointing to the record 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- an 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 the appropriate fields of the records. You need to dereference the data retrieved in the record, See eg/fetchrec1.pl and eg/fetchrec2.pl.
4- an optional list of binding parameters used to replace the place holder ? in the query.
Here is a simple example:
my $rec= $bbconn-> fetchQdaO( "SELECT * FROM TABLE1 WHERE DATASTRING='This is a flower ...' ", ); foreach my $k (keys %$rec) { print "$k -- ${$$rec{$k}}\n"; }
Here is another example:
my $rec= $bbconn-> fetchQdaO( "SELECT DATASTRING, DATANUM,BIN_SREF,RECORDDATE_T FROM TABLE1 WHERE DATASTRING='This is a flower ...' ", ); foreach my $k (keys %$rec) { print "$k -- ${$$rec{$k}}\n"; }
The following example is not productive but it shows the usage of this method:
my %rec; $bbconn-> fetchQdaO( "SELECT a.LOOKUP,b.DATASTRING, b.DATANUM,b.BIN_SREF,a.RECORDDATE_T FROM TABLE1 a, TABLE2 b WHERE a.DATASTRING=? ", \%rec, ['LOOKUP','DATASTRING','DATANUM','BIN_SREF','RECORDDATE_T'], 'This is a flower ...', ); print "${$rec{DATASTRING}}\n"; print "${$rec{RECORDDATE_T}}\n";
fetchQdaAA( $qry ,$aaref ,$href ,@bindparams )
Given a DBI::BabyConnect object, this method takes a query string as an argument to fetch data from the database and return the data in an array of array, that is into 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.
fetchQdaAA() takes four parameters in the following 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 4- an optional list of binding params
The $href is optional and is a reference to a hash that holds two attributes: MAX_ROWS and INCLUDE_HEADER. MAX_ROWS enforces a maximum number of the rows to be fetched, and if you want to fetch everything just do not specify it. INCLUDE_HEADER if set to true then the first row of the returned data is a header that contains the attribute names. To omit the header just specify nothing or set INCLUDE_HEADER to 0. If you want to view the retrieved data, you can use the formatting methods. See "Formatter Functions". However for any of the formatting methods to work properly you need to include the header.
In this example fetchQdaAA returns the $rows:
fetchQdaAA
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');
In this example we pass the $rows to fetchQdaAA:
# define an array ref, fill it in and expand it my $rows=[]; # must specify $rows as an array reference before calling below $dbhandle-> fetchQdaAA($qry,$rows,{INCLUDE_HEADER=>1},14,'u4_1');
See eg/fetchQdaAA.pl for an example.
fetchTdaAA( $table, $selection, $where ,$aaref ,@bindparams )
The method fetchTdaAA() retrieves selected data from the specified database table, where the $where condition apply. You can specify a reference to an array of array to be expanded with the new data rows. The method returns a reference to the array that holds the final results.
fetchTdaAA() method takes the following arguments: 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 parameters
For example to fetch data from the FR_XDRTABLE1 table where ID < 54 AND FLD1='u4_1'
my $xdr = fetchTdaAA('FR_XDRTABLE1', ' * ' , " ID < ? AND FLD1 = ? ",54,'u4_1')
See eg/fetchTdaAA.pl for an example.
fetchTdaAO( $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
See eg/fetchTdaAO.pl for an example.
Because DBI::BabyConnect objects are live objects that are connected to data sources, programmers can invoke methods to execute SQL transactions on the data sources.
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 cursor, and by disconnecting the handle.
However DBI::BabyConnect objects are designed to be persisted and to be pooled within an application. Programmers, do not need to call any of the functions aforementioned 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 port an application that uses DBI directly, you can easily make use of DBI::BabyConnect without making extensive changes to the application.
Call commit() on the handle open by DBI::BabyConnect object. This method is provided to ease portability of programs using DBI directly.
rollback() delegates the rollback to DBI::rollback method, except that the localization of DBI variables will take place prior to calling DBI::rollback. The localization is necessary because DBI::BabyConnect allows you to modify the behavior of rollback during run time, even after you have created a DBI::BabyConnect object.
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.
Call finish() on the cursor held by DBI::BabyConnect object. Provided to ease portability of programs using DBI directly.
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.
reconnect
DBI::BabyConnect::reconnect
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=0. Refer to "CALLER_DISCONNECT".
CALLER_DISCONNECT=0
Returns the $DBI::err as returned by the DBI for the active handle of a DBI::BabyConnect object. If a DBI::BabyConnect method returns an error then you can check for the DBI error by calling dbierror(). For example:
$bbconn-> do($sql) || die $bbconn-> dbierror;
See eg/error_do.pl and eg/error_die.pl.
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 following three statistical functions collect statistics per DBI::BabyConnect object: get_do_stats, get_spc_stats, get_running_time
The DBI::BabyConnect::getStatCC returns statistics about all DBI::BabyConnect objects whenever using DBI::BabyConnect with connection caching and persistence.
DBI::BabyConnect with connection caching and persistence is being used by Apache::BabyConnect.
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.
The 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.
See eg/perl/statcc.pl for an example.
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
htmlStatCC() prints in HTML format the statistics collected on the open DBI handles owned by the DBI::BabyConnect objects. This function is provided so that you can quickly print the statistical table of all DBI::BabyConnect objects that have been cached by a specific process, such as the http server process, or one of its child process.
The printing is in HTML format, therefore you need to use this function from a Perl script that is served under Apache. For an example, see any of the following scripts eg/perl/testbaby.pl, eg/perl/testcache.pl, or eg/perl/onemore.pl.
See "getStatCC" for description of this the cached statistical table of DBI::BabyConnect objects.
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 been invoked by a DBI::BabyConnect object.
Similar to "get_do_stats" but statistics are collected on Stored Procedures whenever you call spc().
DBI::BabyConnect provides several functions that can request meta data and schema information about tables that resides in the data source to which a DBI::BabyConnect is connected. These functions provide statistics about the meta data saved within the database, and about the schema of the database tables. While these functions should be generic and work with any database, currently they support only MySQL, and they have been tested with mysql Ver 14.12 Distrib 5.0.27.
dbschema( $database, $tablelike )
dbschema() retrieves information about tables from MySQL INFORMATION_SCHEMA.TABLES, matching these tables that pertains to the specified $database and whose names are like $tablelike.
dbschema()
Use this method with MySQL to quickly reveal inserts, updates, or any changes on specific tables. This method may not work with any MySQL release, but it has been tested with Ver 14.12 Distrib 5.0.27.
For example, given the database name BABYDB, get the status of all these table names containing TABL in their names. See eg/dbschema.pl for an example.
print $bbconn-> dbschema('BABYDB','TABL');
snapTablesInfo() list all the tables that are defined within the database to which the DBI::BabyConnect object is connected. See eg/tablesinfo.pl for an example.
snapTablesInfo()
snapTableDescription( $table )
snapTableDescription() returns the description of the specified table. However, the table should be defined within the database that the DBI::BabyConnect object is connected to. See eg/tabledescription.pl for an example.
snapTableDescription()
snapTableMetadata( $table )
snapTableMetadata() returns a string describing the meta data of a table. However, the table should be defined within the database that the DBI::BabyConnect object is connected to. See eg/tablemeta.pl for an example.
snapTableMetadata()
strucTableMetadata( $table )
strucTableMetadata() returns a hash reference describing the meta data of a table. However, the table should be defined within the database that the DBI::BabyConnect object is connected to. See eg/tablemeta_struc.pl for an example.
strucTableMetadata()
Four methods are provided within DBI::BabyConnect module to assist the programmer in getting a snapshot of the data retrieved from the database.
textFormattedAO, datalinesFormattedAO, textFormattedAA, and datalinesFormattedAA are typically used to format the data that you have fetched using "fetchQdaAA", "fetchTdaAO", and "fetchTdaAA".
datalinesFormattedAA( $rows ,$attributesList ,attributesRenaming )
datalinesFormattedAA() is a text formatter method that I included in this module to assist you in getting a quick snapshot at what you may have fetched from a database.
datalinesFormattedAA()
datalinesFormattedAA() takes an array reference holding the data as returned by either "fetchQdaAA" or "fetchTdaAA" and returns the data formatted into text format.
datalinesFormattedAA() takes $rows as a first argument, followed optionally by a list of attributes and a hash mapping to rename the attributes.
datalinesFormattedAA() returns a hash reference that contains the data layout in text format. For example, if the data layout is returned in $dataLines, then - the header lines are in: $$dataLines{TITLE_LINE} and $$dataLines{UNDERLINE} - and the formatted data lines are in @{$$dataLines{DATA_LINES}}
If you call datalinesFormattedAA( $rows ) by passing only the $rows, then the method will return the formatted data of all fields found by default in the header (first row).
You can optionally pass as a second argument an array reference that list the attributes to be printed. The list must be of the following format: attribute1, length1, attribute2, length2, ... where each attribute is followed by the desired formatted length.
attribute1, length1, attribute2, length2, ...
The following is an example:
use DBI::BabyConnect; my $bbconn = DBI::BabyConnect->new('BABYDB_001'); $bbconn-> HookError(">>/tmp/error.log"); $bbconn-> HookTracing(">>/tmp/db.log",1); my $qry = qq{SELECT * FROM TABLE2 WHERE ID < ? }; # $rows is an array reference to be filled by fetchQdaAA() my $rows=[]; # fetch data from query, and put data into $rows. Do not exceed 2000 rows # and include the header. if ($bbconn-> fetchQdaAA($qry,$rows,{INCLUDE_HEADER=>1,MAX_ROWS=>2000},15) ) { # we will use the formatting method datalinesFormattedAA() to print the fetched data my $dataLines = $bbconn-> datalinesFormattedAA( $rows, ['ID',6,'DATASTRING',22,'DATANUM',10], {ID=>'Id', DATASTRING=>'Data', DATANUM => 'Data Number'} ); for (my $i=0; $i<@{ $$dataLines{DATA_LINES} }; $i++) { if ($i % 10 == 0) { print $$dataLines{TITLE_LINE}; print $$dataLines{UNDERLINE}; } print ${$$dataLines{DATA_LINES}}[$i]; } } else { print "NONE!!!!!!!!\n"; }
See eg/fetchQdaAA.pl and eg/fetchTdaAA.pl for examples.
textFormattedAA( $AA ,$attributesList ,attributesRenaming )
textFormattedAA() is similar to datalinesFormattedAA() but it returns a string containing the formatted data.
See eg/etchTdaAA.pl for an example.
datalinesFormattedAO( $AO ,$attributesList ,attributesRenaming )
datalinesFormattedAO() is similar to datalinesFormattedAA() but it takes an array of hash as input. It is designed to work with "fetchTdaAO".
textFormattedAO( $AO ,$attributesList ,attributesRenaming )
textFormattedAO() is similar to datalinesFormattedAO() but it returns a string containing the formatted data.
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.
DBI::trace()
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".
You can redirect all STDERR output to a file by calling HookError(). Refer to "HookError".
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 for this module is provided via the <bbconn@pugboat.com> email. A mailing list will soon be provided at babyconnect@pugboat.com.
Bassem W. Jamaleddine, <bassem@pugboat.com>
PUGboat (Processors User Group), <bbconn@pugboat.com>
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.
Persisting DBI::BabyConnect objects with Apache::BabyConnect
This module is being used by Varisphere Processing Server powering the web site www.youprocess.com
To install DBI::BabyConnect, copy and paste the appropriate command in to your terminal.
cpanm
cpanm DBI::BabyConnect
CPAN shell
perl -MCPAN -e shell install DBI::BabyConnect
For more information on module installation, please visit the detailed CPAN module installation guide.