Execute a stored query

Return: MaxId from table or $nDefault if there are no records.

Static functions

NAME

Database::Wrapper - A truly unified perl database API, with a query cache

DESCRIPTION

Database::Wrapper papers over the cracks in the various DBD implementations. Connection, schema queries and data queries have a unified interface.

Synopsis

  use Database::Wrapper qw(:RETURN_TYPES :CONNECTION_TYPES);

  my $rhConnection =
    {
    ConnectionType => CONNECTION_TYPE_MySQL,
    DatabaseName   => "MyDatabase",
    User           => "JRH",
    Password       => "PWD",
    Attribs        => {RaiseError => 0, PrintError => 0, AutoCommit => 0},
    };
  my $dbw = Database::Wrapper->new($rhConnection);
  die("Couldn't connect, $Database::Wrapper::ConnectionError")
    if(not defined $dbw);

        my $sqry = "SELECT * FROM foos;";
        $dbw->PrepareQuery("GetFoos", $sqry, RETURNTYPE_ARRAYREFOFHASHREFS);
        my $ra = $dbw->ExecuteQuery("GetFoos", []);

The API

Connection

new(): Connects to a database via DBI and DBD.

Parameters

$class

The name of the class or an instance of a sub-class.

$rhConnection

A hash containing the following items (obligatory marked '+', possible values given after ' => ', default indicated by '*'):

        +ConnectionType => (CONNECTION_TYPE_MySQL|CONNECTION_TYPE_ODBC|CONNECTION_TYPE_Postgres)
        +DatabaseName
        +User
        +Password
        Attribs                         => {RaiseError => (1*|0), PrintError => (1|0*), AutoCommit => (1*|0)}

Returns:

The newly created instance, or undef on failure.

If new() fails, the global variable '$ConnectionError' is set with an error message.

Example:

  use Database::Wrapper qw(:RETURN_TYPES :CONNECTION_TYPES);

  my $rhConnection =
    {
    ConnectionType => CONNECTION_TYPE_MySQL,
    DatabaseName   => "MyDatabase",
    User           => "JRH",
    Password       => "PWD",
    Attribs        => {RaiseError => 0, PrintError => 0, AutoCommit => 0},
    };
  my $dbw = Database::Wrapper->new($rhConnection);
  die("Couldn't connect, $Database::Wrapper::ConnectionError")
    if(not defined $dbw);

Disconnection

Database handles are disconnected when they go out of scope, or are set to the undefined value.

  $dbw = undef;

Query Caching

PrepareQuery(): Prepares a cached query.

Example:

        my $sqry = "SELECT * FROM foos;";
        $dbw->PrepareQuery("GetFoos", $sqry, RETURNTYPE_ARRAYREFOFHASHREFS);

Parameters: $sName The user's label for the prepared query. It is a good idea to use a format like "Package::Function" for these to avoid clashes. $nReturnType MUST be one of the values defined by the 'RETURNTYPE_*' constants:

RETURNTYPE_NONE: Return nothing, whether the query succeeds or fails.
RETURNTYPE_SUCCESS: Return non-zero if the query succeeds.
RETURNTYPE_VALUE: Return the first field of the first record.
RETURNTYPE_ALLVALUES: Return an array of the first field of all records.
RETURNTYPE_HASHREF: Return the first record as a hash ref.
RETURNTYPE_ARRAYREF: Return the first record as an array ref.
RETURNTYPE_ARRAYREFOFHASHREFS: Return all records as hash refs.
RETURNTYPE_ARRAYREFOFARRAYREFS: Return all records as array refs.

Return: True - The query is ready to use. False - Something is amiss. Call 'GetLastError()' to know more.

This function does NOT throw an error if the query has already been defined. This wrapper is designed EXACTLY for this purpose.

A function can prepare it's queries WITHOUT having to worry if it has already done so. The user should call 'QueryExists()' if he/she wants to know if a query has been prepared.

Furthermore, through careful use of the 'namespace' naming system (see above), clashes should not happen.

$WarnDifferentPrepare

If the global '$WarnDifferentPrepare' is set to non-zero, PrepareQuery() will emit a warning if it is called with an existing query name, but with a different query. Such a call is likely to indicate a programming error.

QueryExists()
ExecuteQuery()

Example:

        my $sqry = "SELECT * FROM foos;";
        $dbw->PrepareQuery("GetFoos", $sqry, RETURNTYPE_ARRAYREFOFHASHREFS);
        my $ra = $dbw->ExecuteQuery("GetFoos", []);

Non-cached Queries

ExecuteTemporaryQuery(): Prepare and execute a temporary query

Example

  my $ra = $dbw->ExecuteTemporaryQuery("SELECT * FROM foo;", [], RETURNTYPE_ARRAYREFOFHASHREFS);

RunSql($self, $sql [, $reSplit])

$sql: A string containing multiple SQL statements.
$reSplit: Optional parameter. Sets the regular expression to use to split the SQL block into separate queries. Default is /;[\r\n]+/.

Utility Methods

GetDatabaseNames()

Can be called in the following ways:

1. (Deprecated) Static function This function is not easy to remember and is MySQL specific. my $raDatabases = Database::Wrapper::GetDatabaseNames($nConnectionType, {user => XXX, password => YYY[, host => ZZZ][, port => PPP]});

2. Static function This function takes a hash parameter which is the same as Database::Wrapper::new(). my $raDatabases = Database::Wrapper::GetDatabaseNames($rhConnection);

3. Class method The function can also be called on an instance of Database::Wrapper. my $raDatabases = $dbw->GetDatabaseNames();

The way that the 'data_sources' function works varies wildly:

MySQL

For MySQL, you MUST pass a user and password for a user with "SHOW TABLES" privileges.

'$rhAttribs' is undocumented by DBI.pm, but seems to be: $rhAttribs = { user => , password => , [host => ,] [port => ,] }

Postgres

DBD::Pg connects to the 'template1' database, so the supplied user information must be for a user with access privileges to that database.

DatabaseExists()

See GetDatabaseNames() for parameters.

GetTableNames()

GetMaxId()

Errors

GetLastError()

Public Functions

GetDriverName(): The driver name is the string that follows 'DBI' in the connection string.

COPYRIGHT

This library is free software; you can redistribute it and/or modify it under the same terms as Perl itself.