WWWdb::DbSession - DbSessions for WWWdb
use WWWdb::DbSession;
$oDbSession = DbSession->new($cDbDriver,
$cDbName,
$cUserName,
$cPassword,
[RaiseError => $],
[PrintError => $],
[AutoCommit => $]);
$oDbSession->SqlDo($cSQL_Statement,
[@lBindValues]);
$lResult = $oDbSession->SqlSelect($cSQL_Statement,
[@lBindValues]);
$iMaxId = $oDbSessionGL->getMaxId($cNameOfIdField, $cTable);
$oDbSessionGL->TransactionLoop($iNrOfLoopsIfTransactionFails,
\&pProcedureToCall,
[@lParamsForProcedure]);
$oDbSessionGL->TransactionLoop($iNrOfLoopsIfTransactionFails,
sub
{
my $pDbHandle = shift;
# db-actions
...
},
[@lParamsForProcedure]);
$oDbSessionGL->DateToDb(scalar(localtime())); DbSession::Pg::DateToDb(undef, scalar(localtime()));
$oDbSessionGL->DateFromDb($cDateField); DbSession::Pg::DateFromDb($cDateField);
$bAutoCommit = $oDbSession->getAutoCommit(); $oDbSession->setAutoCommit($bAutoCommit);
$bPrintError = $oDbSession->getPrintError(); $oDbSession->setPrintError($bPrintError);
$bRaiseError = $oDbSession->getRaiseError(); $oDbSession->setRaiseError($bRaiseError);
$cDataSource = $oDbSession->getDataSource();
$pDbHandle = $oDbSession->getDbHandle();
Db-Session is another layer over the DBI-Module. It gives some easy-to-use functionality for db-access.
When a DbSession-object gets destroyed, the session will be automatically finished.
$oDbSession = DbSession->new($cDatabaseType,
$cDatabaseName,
$cDatabaseUser,
$cDatabasePassword,
RaiseError => $bRaiseError,
PrintError => $bPrintError,
AutoCommit => $bAutoCommit);
Create a new DbSession and connect to the database. The following database-types are supported currently:
- PostgreSQL - MySql - Oracle
The attributes are passed to the internal DBI-Object.
$oDbSession->Error($pHandle,
$cSQL_Statement,
$cDescription);
This function generates an error-message, if a problem occured in the last database statement. You will get the text and the number of the database-error and the statement, which leaded to this error.
Normally this method is only called internally.
$oDbSession->SqlDo($cSQL_Statement,
[@lBindValues]);
SqlDo executes a non-select SQL-statement passed in $cSQL_Statement. If you want to pass Parameters, then you can generate the statement by example via sprintf, but you have to quote them by yourselve then.
The second possibility (which is recommended and much easier) is, to pass the parameters separately. The underlying DBI will care about quoting etc.
Here are two examples which show the difference:
$oDbSession->SqlDo
(sprintf("INSERT INTO customer
(id_customer,
name,
last_change)
VALUES
(%d, '%s', '%s')",
$iIdCustomer,
$cCustomerName,
$cLastChange));
Here you have to build your SQL-Statement by yourself and you need to quote the fields correctly.
$oDbSession->SqlDo
("INSERT INTO customer
(id_customer,
name,
last_change)
VALUES
(?, ?, ?)",
$iIdCustomer,
$cCustomerName,
$cLastChange);
Here, you only have to pass the SQL-statement and the parameters and ... thats it!
If some database-error occurs, the procedure will throw a die-signal with an error-message via the Error-method.
$lResult = $oDbSession->SqlSelect($cSQL_Statement,
[@lBindValues]);
This statement is a special-form of the SqlDo-statement. It prepares a SELECT-statement passed in $cSQL_Statement, executes it and returns the result in a array of arrays.
To process the results the following loops may be helpful:
# loop through all records
foreach $pRecord (@$lResult)
{
# loop through the fields
foreach $cField (@$pRecord)
{
print "$cField, ";
}
print "\n";
}
To access a value direcly you may use:
print "Second Record <@{$lResult->[1]}>\n";
print "Third Record, Second Field <$lResult->[2]->[1]>\n";
If some database-error occurs, the procedure will throw a die-signal with an error-message via the Error-method.
$oDbSessionGL->TransactionLoop($iNrOfLoopsIfTransactionFails,
\&pProcedureToCall,
[@lParamsForProcedure]);
or with a anonymous subroutine
$oDbSessionGL->TransactionLoop($iNrOfLoopsIfTransactionFails,
sub
{
my $pDbHandle = shift;
# db-actions
...
},
[@lParamsForProcedure]);
This procedure disables (temporarily) the AutoComit functionality. Then it
runs the procedure given by \&pProcedureToCall. The first parameter for
this procedure is a pointer to the DbSession-object it belongs to. Then any
other parameters, defined by the user may follow. If that procedure thows a
die or returns undef, the transaction is assumed to be failed. In this case
it will be returned up to $iNrOfLoopsIfTransactionFails times.
If the db-error keeps happening, then this procedure throws a die with the SQL-errors.
$iMaxId = $oDbSessionGL->getMaxId($cNameOfIdField, $cTable);
Especially when you use databases, which do not support auto-increment fields, or if you want to keep your portability, then this will help you to generate id\'s for unique indexes in a simple way.
Passing the name of the unique key-field in the variable
$cNameOfIdField and the database-table in $cTable, this
procedure selects the maximum value for this field, adds one and returns
the value.
Please note that this is only a hack, because if two users use this function at the same time, duplicate values may be generated. A better way would be to have a own table for this index-fields and use them like tickets in secure transactions.
This internal procedure creates a datasource-descriptor for the database and connects to the database.
This procedure is for internal use only.
This internal procedure commits all open transactions and disconnects from the database.
This procedure is for internal use only.
This internal procedure generates the necessary DBI-datasource for the specified database.
This procedure is for internal use only.
$oDbSessionGL->DateToDb(scalar(localtime()));
or the specialized form for a database
DbSession::Pg::DateToDb(undef, scalar(localtime()));
Converts a date, given by the perl-localtime-function (b.e. Mon Mar 6 16:21:21 2000) to a date-format, the database can understand.
The following date-data-types are supported
+------------+-----------+
| Database | Date-type |
+------------+-----------+
| PostgreSQL | datetime |
| Oracle | timestamp |
| MySql | datetime |
+------------+-----------+
$oDbSessionGL->DateFromDb($cDateField);
or the specialized form for a database
DbSession::Pg::DateFromDb($cDateField);
This function is the opposit to DateToDb. It converts a date-field as delivered by the database to the same format, the perl-localtime-function would give back.
$bAutoCommit = $oDbSession->getAutoCommit(); $oDbSession->setAutoCommit($bAutoCommit);
If a commit-statement should be issued automatically after each SQL-statement then AutoCommit must be set to a true value (which it is by default)
$bPrintError = $oDbSession->getPrintError(); $oDbSession->setPrintError($bPrintError);
Reads or sets the DBI::PrintError flag.
$bRaiseError = $oDbSession->getRaiseError(); $oDbSession->setRaiseError($bRaiseError);
Reads or sets the DBI::RaiseError flag.
$cDataSource = $oDbSession->getDataSource();
Returns the name of the DBI::Datasource.
$pDbHandle = $oDbSession->getDbHandle();
Returns the database-handle for the current connection.