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

Copyright (C) 2003-2005 by Joe Yates, All Rights Reserved.

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

cpanm Database::Wrapper

perl -MCPAN -e shell
install Database::Wrapper