NAME

DBIx::NamedQuery - Utilities for decoupling of Perl code and SQL statements

SYNOPSIS

  use DBIx::NamedQuery qw(EXTEND_DBI);

  DBIx::NamedQuery::load_named_queries(*DATA);
  DBIx::NamedQuery::load_named_queries_from_file('customers.sql');
  
  $DBI_statement = $DBI_database_handle->prepare_named_query('invoice');
  
  $DBI_statement = $DBI_database_handle->execute_named_query(
                        'customer_address', $customer_id
                        );

DESCRIPTION

DBIx::NamedQuery decouples the logic of Perl program and SQL queries. Perl program references only symbolic names (labels) of queries. The SQL source can be a separate file or embedded in the program under DATA section.

To reduce the amount of coding, the library can (on demand via import flag EXTEND_DBI) extend the interface of DBI library, namely the methods of database object.

QUERY SOURCE FORMAT

The format of SQL source is suitable for editing in database administration tools, such as TOAD. The label is (from the SQL point of view) just a comment.

  --[invoice]
  SELECT * FROM invoice WHERE invoice_id=?
  
  --[customer_address]
  SELECT cust_name, cust_street, cust_street_no, cust_city
  FROM customers
  WHERE cust_id = ?

STANDARD FUNCTIONS

load_named_queries (HANDLE)

Loads a set of named queries from open filehandle. Returns number of loaded queries or undef in case of error.

load_named_queries_from_file (FILENAME)

Loads a set of named queries from a file. Returns number of loaded queries or undef in case of error.

get_named_query (LABEL)

Returns a SQL query associated with a given label. If there is no such label, returns undef.

set_named_query (LABEL1 => SQL1, ...)

Allows to add/replace one or more named queries in the current set.

DBI EXTENSION (DATABASE HANDLE METHODS)

$DB->execute_named_query (LABEL [, BIND_VALUES])

Prepares and executes SQL query associated with the label. Placeholders in SQL are bound with remaining parameters. Returns DBI statement handle or undef in case of error.

$DB->select_row_from_named_query (LABEL [, BIND_VALUES])

Executes (most likely SELECT) SQL statement identified by the label and returns the first row of data as an array reference. In case of error, undef is returned instead.

$DB->prepare_named_query (LABEL [, PREPARE_OPTIONS])

Prepares SQL statement identified by the label. Prepare options are passed to standard DBI method $DB->prepare() as additional parameters.

SEE ALSO

DBI

AUTHOR

Boleslav Bobcik, <boleslav.bobcik@ys.cz>

COPYRIGHT AND LICENSE

Copyright (C) 2006 by Boleslav Bobcik

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