DBIWrapper - Perl extension for generic DBI database access.
use DBIWrapper;
my $db = DBIWrapper->new(dbType => "Pg",
dbName => "test_db",
dbHost => "localhost",
dbUser => "nobody",
dbPasswd => "",
dbPort => "5432",
predefinedDSN => "",
printError => 1,
raiseError => 1,
autoCommit => 0);
if ($db->error())
{
die $db->errorMessage();
}
my $sth = $db->read("SELECT * FROM test_tb");
my $result = $db->write(sql => "INSERT INTO test_tb (name, value) VALUES (?, ?)",
plug => [ $name, $value ]);
# this used DBI's substitution features to plugin the name and value.
$db->close(); # close down the database connection. Any read()'s # or write()'s will no longer be valid with this object until a new() is # issued again.
DBIWrapper is the generic database Object for accessing the DBI database interface. It provides the lowest level of functionality needed by any program wanting to access databases via the DBI. Currently, DBIWrapper is only aware of Pg (PostgreSQL), mysql (MySQL), Sybase and ODBC DBD modules and how to work with them correctly.
Support for transactions on MySQL is now checked for and if found to be available, the AutoCommit flag is turned off so that transactions will be used.
The substitution array (if used) will cause each ##?1##, ##?2##, etc. string in the sql string to be substituted for the corresponding value in the substitution array. It must start at ?1. It is up to the user to pass in the correct number of elements for both the plug and substitution arrays. The plug array is used to pass in the values for DBI to replace in the sql string of ? which is standard DBI notation.
The getDataArray(), getDataArrayHeader(), getDataHash(),
getDataHashHeader(), readXML(), readHTML() methods all properly handle
multiple result sets being returned from Sybase. This could be the
result of multiple select statements or a compute clause. In the
case of the Header() methods, the header row is based on the first
returned select statement, which may not be correct for the following
statements or compute blocks.
NOTE: bool = 1(true), 0(false)
Creates a new instance of the DBIWrapper object and opens a connection to the specified database. If predefinedDSN is specified then it is used instead of the dbName, dbHost, dbPort values. This is mainly to support ODBC easier. If setDateStyle is 1 (default) and dbType = Pg, then the datestyle for PostgreSQL is set to US (MM/DD/YYYY). logLevel defaults to 0. There are 4 levels 0, 1, 2 and 3 which log the following items when an error occurs: 0) Nothing is output 1) dbType, dbHost, dbName, printError, raiseError, autoCommit, setDateStyle, supportsTransactions, transactionType 2) all of 1 plus dbUser, dbPort, predefinedDSN 3) all of 2 plus dbPasswd
Sybase specific: server allows you to specify the database server to connect to by name and must be defined in your interfaces file. interfaces allows you to specify the Sybase interfaces file needed to properly connect to the Sybase database.
If you do not specify server and interfaces, then dbHost and dbPort will be used.
isValid()Returns 1 if the DBI object is valid, else 0 if invalid.
close()Closes the connection to the database.
error(errorString)This method will set the error condition if an argument is specified.
The current error state is returned, regardless of if we are setting an error or not.
A \n is appended to the errorString so you don't have to provide it. errorString is prefixed with the caller's full method name followed by the errorPhrase string.
You can either specify the errorString value by name:
$self->error(errorString => "This is an error!");
or by value:
$self->error("This is an error!");
If you specify multiple arguments (in pass by value mode), then we check to see if the first argument contains %'s that are not \ escaped and are not %%. If this is the case, then the incoming arguments will be passed through sprintf() for formatting, else we just join them with a space ' ' and append them to the current errorString.
To see if an error happened:
if ($self->error) { die "Error: " . $self->errorMessage; }
setError(errorString)DEPRECATED: see error()
optional: errorString returns: nothing Sets error = 1 and errorString = string passed in. The errorString is prefixed with the caller's full method name followed by the errorPhrase string.
You can either call as setError(errorString => $string) or setError($string)
If you do not specify anything, we blow an error telling you to specify errorString.
\n is appended to the contents of the errorString passed in.
prefixError(errorString)optional: errorString returns: nothing Sets error = 1 and prefixes errorString with string passed in. The errorString is prefixed with the caller's full method name followed by the errorPhrase string.
You can either specify the errorString value by name:
$self->prefixError(errorString => "This is an error!");
or by value:
$self->prefixError("This is an error!");
If you specify multiple arguments (in pass by value mode), then we check to see if the first argument contains %'s that are not \ escaped and are not %%. If this is the case, then the incoming arguments will be passed through sprintf() for formatting, else we just join them with a space ' ' and append them to the current errorString.
If you don't specify anything then
If you have a previous error, we prefix the caller info to
that error message.
didErrorOccur(void)DEPRECATED: see error()
Returns the value of error.
errorMessage(void)Returns the value of errorString.
errorStr(void)Returns the value of errorString.
Alternative to errorMessage().
resetError(void)Resets the error condition flag and string.
commit()causes the database to commit the current transaction. Only works if AutoCommit is set to 0 and the database supports Transactions.
rollback()causes the database to rollback the current transaction. Only works if AutoCommit is set to 0 and the database supports Transactions.
(This function should only be called for SELECT statements). executes the specified sql statement passing in any values in plug to the execute method after doing any substitutions that are in substitute. The resulting sql data is passed back to the user as a reference for them to do with as they please.
See read() for argument info.
case defaults to 0 (lowercase).
See read() for argument info.
The first row of the array is an array containing the column names in the order returned by the database. The column names are case preserved if case = 1, or lowercased if case = 0.
NOTE: If 0 rows were returned, we still return an array with 1 row in it, which is the header row.
case defaults to 0 (lowercase).
See read() for argument info.
The first row of the array is an array containing the column names in the order returned by the database. The column names respect the case flag.
NOTE: If 0 rows were returned, we still return an array with 1 row in it, which is the header row.
case defaults to 0 (lowercase).
See read() for argument info.
requires: sql optional: plug, substitute, columns = 0 returns: valid XML document describing the data selected from the database. Calls read() to actually validate the data and execute the SELECT statement. The resulting XML document will either have an error condition set (if read() signaled an error occured) or will be the result of traversing the sth object returned from read().
If columns = 0, then all info will be returned in the <row> tag as attributes where the column name = column value. Ex. <row name="test" value="testing" other="something else"/> When the column names were name, value and other.
If columns = 1, then all info will be returned in <column>
tags which are children of the <row> tag. A column tag has
attributes name and value. name = column name and value =
column value.
Ex.
<row>
<column name="name" value="test"/>
<column name="value" value="testing"/>
</row>
The XML format is as follows:
<?xml version="1.0" encoding="ISO-8859-1"?>
<resultset version="1.1">
<select sql="" plug=""/>
<status result="" error=""/>
<rows numRows="" columns="0|1">
<row/>
</rows>
</resultset>
If the XML document is an error document, then: <status result="Error" error="Error message"/> else <status result="Ok" error=""/>
In <select> tag, sql = The sql SELECT string, plug = the string made when joining all the plug array entries together and comma seperating them. The entries are single quoted. Ex. plug="''" would represent no plug entries used. plug="'x', 'y'" would mean that 2 entries were passed in: x, y.
In <rows> numRows will be equal to the number of rows being returned or 0 if an error had occured.
The <row> tag will depend on the value of columns.
encodeEntities(string)requires: string - string to encode optional: returns: string that has been encoded. summary: replaces all special characters with their XML entity equivalent. " => "
fixupAttributes(string)Converts (, ), -, \, /, =, >, <, & to _ Deletes ', ``, \n
requires: sql optional: plug, substitute, tableClass, alternateRows, displayNumRows returns: valid HTML <table> describing the data selected from the database. Calls read() to actually validate the data and execute the SELECT statement. The resulting HTML <table> will either have the error displayed (if read() signaled an error occured) or will be the result of traversing the sth object returned from read().
If an error occured, then the generated tr and td will have class="sqlError" assigned to them so you can change the way the sql Error row is displayed. The error output will also be wrapped in a <span class="sqlError"></span> so you can change the display behaviour.
tableClass defines the class to assign to this table so it knows how to display itself. Defaults to "". This allows you to have different readHTML generated tables on the same page and let them look different (border, width, cellspacing, cellpadding, etc.).
alternateRows (boolean) lets the caller indicate they want to possibly have different row characteristics on every other row. Defaults to 1.
displayNumRows (boolean) lets the caller indicate they want a <p> above the generated table that tells the user how many rows were returned. Defaults to 1. The generated paragraph has class="sqlNumRows" assigned to it so the caller can affect the characteristics of the output and the NumRows statement is wrapped in a <span class="sqlNumRows"></span>.
The table header will be made up from the returned columns in the sql statement. Each <th> will have the css class="column_name" defined so that the callers style can have th.column_name defined to dictate how the <th> is to be displayed. The <tr> for the table header will have class="sqlHeader" assigned to it.
Each <tr> will have class="sqlRow" assigned, unless alternateRows is enabled, which then causes the even rows to have class="sqlRow" and the odd rows to have class="sqlRow2" assigned. Each <td> will have the css class="column_name" defined so the callers style can have td.column_name defined to dictate how the <td> is to be displayed. The contents of the <td> entry will be wrapped in <span class="column_name"></span> to allow even more display control.
(This function should be called for any sql statement other than SELECT). executes the specified sql statement passing in any values in plug to the execute method after doing any substitutions that are in substitute.
Returns the number of rows affected.
If the sql to execute is an INSERT statement, then the oid or insertid (Postgresql or MySQL) values will be stored in the oid value in this object, for later access by getID().
getID(``table.column'')write() method and
that is was able to update the oid value.
This method requires a string value specifying the table.column that is the ID field for the INSERT statement that just executed, if you are using a PostgreSQL backend.
If using MySQL, you do not need to specify the table.column value, but your table must have an AUTO_INCREMENT field defined.
If using Sybase, you do not need to specify the table.column value, but you may not get a valid ID back if you used ? substitution or the INSERT was in a stored procedure. See the DBD::Sybase man-page for more information.
If the database type is unsupported or an error happened, a value of 0 will be returned.
debugMessage()Returns the string that contains all the info that is to be logged at the current logLevel level. If logLevel is not 0, 1, 2 or 3 then a default of 3 is used.
getLogLevel()returns the current logLevel value.
sets the logLevel value. If the value is not specified then it defaults to logLevel 1.
Returns 1 on Success, 0 on Error.
We validate that the logLevel is 1, 2 or 3.
boolToDBI(string)Takes the string and returns a 1 for 1|t|true, returns a 0 for anything else.
This method basically will output a true or false value that any database should recognize based upon the input string.
dbiToBool(string)Takes the 1 or 0 from the DBI and returns true or false.
All data fields are accessible by specifying the object and variable as follows: Ex. $value = $obj->variable;
Any methods where it is possible to specify just a single argument and still have it be valid, you can now specify the argument without having to name it first.
Ex: calling read() without using the substitute or plug
options can be done as $dbi->read("SELECT * from test");
Methods updated to support this: setError, read, readXML, write, setLogLevel
James A. Pattie, james at pcxperience dot com
perl(1), DBI(3), DBIWrapper::XMLParser(3), DBIWrapper::ResultSet(3)