/ 
DBIx-QueryByName-0.01
River stage zero No dependents
/ DBIx::QueryByName

NAME

DBIx::QueryByName - Execute SQL queries by name

DESCRIPTION

DBIx::QueryByName allows you to decouple SQL code from Perl code by replacing inline SQL queries with method calls.

The idea is to write the code of your SQL queries somewhere else than in your perl code (in a XML file for example) and let DBIx::QueryByName load those SQL declarations and generate methods to execute each query as a usual object method call.

DBIx::QueryByName can manage multiple database connections and is fork safe.

SYNOPSIS

use DBIx::QueryByName;

my $dbh = DBIx::QueryByName->new();

# define 2 database connections
$dbh->connect("db1", "dbi:Pg:dbname=mydb;host=127.0.0.1;port=6666", $username, $password);
$dbh->connect("db2", "dbi:SQLite:/path/to/db/file");

# load some default queries to run against db1
my $queries = <<__ENDQ__;
<queries>
    <query name="add_job" params="id,username,description">INSERT INTO jobs (id, username, description, status) VALUES (?,?,?,0)</query>
    <query name="get_job_count" params="">SELECT COUNT(*) FROM jobs</query>
</queries>
__ENDQ__

$dbh->load(session => 'db1', from_xml => $queries);

# load some default queries to run against db2, from an xml file
$dbh->load(session => 'db1', from_xml_file => $filepath);

# now run some queries:

# insert a few rows in db1.jobs
$dbh->add_job( { id => 12,
                 username => "tom",
                 description => "catch mouse" } );
$dbh->add_job( { id => 13,
                 username => "jerry",
                 description => "run away from cat" } );

# count the number of rows:
my $sth = $dbh->get_job_count();

# then do what you usually do with a statement handler...

SESSION MANAGEMENT AND FORK SAFETY

DBIx::QueryByName opens one database connection for every process that needs to execute a query over a given session (as declared in load()) and for every child process of that process.

A rollback or commit (or any other database method) therefore only affects the connection associated with the running process (defined by its pid $$) and not the connections to the same database openened for the process's children or parents.

LOGGING

DBIx::QueryByName logs via Log::Log4perl if it is available. If Log::Log4perl is available but not configured, you may see warnings poping up. Just configure a default logger in Log::Log4perl to get rid of them.

INTERFACE

$dbh = DBIx::QueryByName->new();

Return an instance of DBIx::QueryByName.

$dbh->connect($session_name, @dbi_connection_params);

Declare how to open (later on) a database connection called $session_name with the provided standard DBI connection parameters. Actually opening the connection is defered until needed, that is until one of query(), quote(), begin_work(), rollback() or commit() is called or any of the named queries loaded for this session.

Example: $dbh->connect('db',"dbi:Pg:dbname=$db;host=$host;port=$port", $username, $password, {pg_enable_utf8 => 1});

$dbh->load(session => $session_name, from_xml_file => $file);

or

$dbh->load(session => $session_name, from_xml => $string);

Load SQL queries from the xml query file $queryfile or the string $string. Afterward, to execute those queries just call the method of the same name on $dbh. This method will automatically execute the corresponding query over the database connection $session_name.

$dbh->load(session => $session_name, from_pg => 1);

NOT IMPLEMENTED YET! Autoload named queries to call all stored procedures declared in a postgres database to whom we can connect using $session_name.

$dbh->rollback($session_name);

Perform a rollback on the session named $session_name and return its result.

$dbh->commit();

Perform a commit on the session named $session_name and return its result.

$dbh->begin_work();

Call the DBI begin_work() method on the session named $session_name and return its result.

$dbh->quote($session_name, $string);

Call DBI's quote() method on $string for the database handler associated with $session_name and return its result.

my $sth = $dbh->query($session_name,$sql);

Call prepare and execute for this SQL. Return the executed statement handler.

XML FILE SYNTAX

When calling load() with from_xml or from_xml_file, the XML string expected must have the following format:

<queries>
    <query name="{query's name}"
           params="{names of the sql's placeholders, as a comma-separated and in order of appearance}">
    {some sql code with placeholders}</query>
    <query ...>...</query>
    <query ...>...</query>
    <query ...>...</query>
    ...
</queries>

Always use placeholders ('?' signs) in your SQL!

SEE ALSO

DBIx::NamedQuery: almost the same but doesn't support named parameters, forks and multiple simultaneous database connections.

AUTHORS

Created by Joel Jacobson <joel AT gluefinance.com>. Maintained by Erwan Lemonnier <erwan AT cpan.org>. With support from Claes Jakobsson <claes AT surfar.nu>

COPYRIGHT AND DISCLAIMER

This module was developed by Glue Finance AB as part of the corporation's software development activities. This module is distributed under the same terms as Perl itself. We encourage you to help us improving this module by sending feedback and bug reports to the maintainer(s).

This module is provided 'as is' and comes with no warranty. Glue Finance AB as well as the author(s) decline any responsibility for the consequences of using all or part of this module.

SVN INFO

$Id: QueryByName.pm 5251 2009-10-15 14:36:18Z erwan $