DESCRIPTION

DBIx::Interpolate wraps DBI and inherits from SQL::Interpolate. It does nothing more than bring SQL::Interpolate behavior into DBI. The DBIx::Interpolate interface is very close to that of DBI. All DBI-derived methods look and behave identically or analogously to their DBI counterparts. They differ mainly in that certain methods, such as do and select.*, expect an interpolation list as input:

DBIx::Interpolate also supports statement handle sets. A statement handle set is an abstraction of a statement handle and represents an entire set of statement handles for a given class of SQL queries. This abstraction is used because a single interpolation list may interpolate into any number of SQL queries (depending on variable input), so multiple statement handles may need to be managed and cached. DBIx::Interpolate also provides a way to handle this caching transparently.

INTERFACE

The parameters for most DBIx::Interpolate methods are internally passed to dbi_interp(), which is a thin wrapper around sql_interp. dbi_interp() accepts a few additional types of parameters and typically returns a parameter list suitable for DBI, typically ($statement, \%attr, @bind_values). Therefore, the previous example is equivalent to

dbi_interp

dbi_interp() is a wrapper function around sql_interp(). It serves as an adapter that returns also the \%attr value (and sometimes $key_field value) so that the result can be passed directly to the DBI functions.

In addition to the parameters accepted by SQL::Interpolate::sql_interp, @interp_list may contain the macros returned by attr and key_field functions. dbi_interp() will convert these DBI-specific objects into additional return values expected by certain DBI methods. For example, selectall_hashref accepts an additional $key_field parameter:

key_field

$keyobj = key_field($key_field);

Creates and returns an SQL::Interpolate::Key macro object, which if processed by dbi_interp will cause dbi_interp to return an extra $key_field value in the result so that it is suitable for passing into $dbh->fetchrow_hashref and related methods.

Creates a new statement handle set ($stx of type SQL::Interpolate::STX) associated with $dbx. There are no parameters.

A statement handle set represents a set of statement handles for a class of queries. Up to one statement handle is considered active. Other operations performed on the statement handle set are passed to the active statement handle so that the statement handle set often looks and feels like a regular statement handle.

Statement handle set (STX) methods

These methods are for statement handle set objects.

new

$stx = SQL::Interpolate::STX->new($dbx);

Creates a new statement handle set. Typically this is not called directly but rather is invoked through prepare().

max_sths

$max_sths = $stx->max_sths(); # get
$stx->max_sths($max_sths); # set

Gets or sets the maximum number of statement handles to cache in the statement handle set. The default and minimum value is 1.

sth

$sth = $stx->sth();

Gets the current active statement handle (e.g. the only that was just executed). Returns undef on none.

sths

$sths = $stx->sths();

Return a hashref of contained statement handles (map: $sql -> $sth).

execute

$rv = $stx->execute(@list);

Executes the query in the given interpolation list against a statement handle. If no statement matching statement handle exists, a new one is prepared. The used statement handle is made the active statement handle. Return on error behavior is similar to DBI's execute.

@list is an interpolation list (suitable for passing to dbi_interp).

fetch...

$ary_ref = $stx->fetchrow_arrayref();

Various fetch.* methods analogous to those in DBIx::Interpolate are available. The fetch will be performed against the active statement handle in the set.

DEPENDENCIES

This module depends on SQL::Interpolate and DBI.

ADDITIONAL EXAMPLES

These are more advanced examples.

Preparing and reusing statement handles

# preparing and reusing statement handles
my $stx = $dbx->prepare();
# note: $stx represents a set of statement handles ($sth) for a class
# of queries.
$stx->max_sths(3);
for my $colors (@colorlists) {
$stx->execute("SELECT * FROM table WHERE color IN", $colors);
# note: this will transparently prepare a new $sth whenever
# one compatible with the given query is not cached.
my $ary_ref = $stx->fetchall_arrayref();
}

The statement handle set transparently prepare statement handles if ever and whenever the underlying SQL string (and number of bind values) changes. The size of the statement handle cache (3) may be configured to optimize performance on given data sets. Compare this simpler and more flexible code to the example in SQL::Interpolate.

DESIGN NOTES

Philosophy and requirements

DBIx::Interpolate is designed to look an feel like DBI even when the DBI interface is not entirely user friendly (e.g. the (fetch|select)(all|row)?_(array|hash)(ref)? and do methods). Still, the approach lowers the learning code and could simplify the process of converting existing DBI code over to SQL::Interpolate.

The use of statement handle sets (STX) is not strictly necessary but is rather designed to mimic DBI's statement handles more than anything else. The DBX object itself contains a statement handle set, which can be used for non-prepared calls such as to selectall_.*() methods (i.e. cache statement handles like in DBIx::Simple's keep_statements).