Help-Site Computer Manuals
Software
Hardware
Programming
Networking
  Algorithms & Data Structures   Programming Languages   Revision Control
  Protocols
  Cameras   Computers   Displays   Keyboards & Mice   Motherboards   Networking   Printers & Scanners   Storage
  Windows   Linux & Unix   Mac

DBI::BabyConnect
creates an object that holds a DBI connection to a database

DBI::BabyConnect - creates an object that holds a DBI connection to a database


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');

  my $sql = qq{

       INSERT INTO TABLE1

       (DATASTRING,DATANUM,IMAGE,RECORDDATE_T)

       VALUES

       (?,?,?,SYSDATE())

     };

  $bbconn-> sqlbnd($sql,$dataStr,1000,$imgGif);


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 object

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


GETTING STARTED

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.


USAGE

This class has the following methods:

new


  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.

HookError


  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


 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

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

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:


  PerlSetEnv BABYCONNECT /opt/DBI-BabyConnect-0.93/configuration

Refer 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:

CALLER_DISCONNECT

ON_FAILED_DBIEXECUTE_ROLLBACK_AND_EXIT

DBSETTING_FORCE_SINGLESPACE_FOR_EMPTY_STRING

ENABLE_STATISTICS_ON_DO

ENABLE_STATISTICS_ON_SPC

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

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

DBSETTING_FORCE_SINGLESPACE_FOR_EMPTY_STRING

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.

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.

Database Handle Attributes

For each of the active database connection, there are six attributes that are defined:

  1. RaiseError
  2. PrintError
  3. AutoCommit
  4. AutoRollback
  5. LongTruncOk
  6. LongReadLen

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. See Database Descriptors File.

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. 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()

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. 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()

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. Refer to Database Descriptors File.

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. 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()

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.


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


  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


  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


  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


  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

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


  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


  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


  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


 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:


    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


  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


  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.


Closing Functions

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.

commit

Call commit() on the handle open by DBI::BabyConnect object. This method is provided to ease portability of programs using DBI directly.

rollback

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.

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=0. Refer to CALLER_DISCONNECT.


Error Functions

dbierror

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.


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 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 the Apache::BabyConnect manpage.

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 the Apache::BabyConnect manpage, 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

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

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.

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 been invoked by a DBI::BabyConnect object.

get_spc_stats

Similar to get_do_stats but statistics are collected on Stored Procedures whenever you call spc().


Database Status and Schema

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


  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.

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

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.

snapTableDescription


  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.

snapTableMetadata


  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.

strucTableMetadata


  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.


Formatter Functions

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


  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() 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.

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


  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


  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.

See eg/fetchTdaAO.pl for an example.

textFormattedAO


  textFormattedAO( $AO ,$attributesList  ,attributesRenaming )

textFormattedAO() is similar to datalinesFormattedAO() but it returns a string containing the formatted data.

See eg/fetchTdaAO.pl for an example.


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.

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

Support for this module is provided via the <bbconn@pugboat.com> email. 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

Programminig
Wy
Wy
yW
Wy
Programming
Wy
Wy
Wy
Wy