NAME

DBIx::Interpolate - Interpolate Perl variables into SQL with DBI

SYNOPSIS

  use DBI;
  use DBIx::Interpolate qw(:all);

  # This basic usage is all you really need:
  my $dbx = DBIx::Interpolate->new($dbh);  # Construct object.
  $dbx->stx()->max_sths(10);               # Optionally enable transparent
                                           #   statement handle caching.
  $dbx->selectall_arrayref('               # Perform query.
      SELECT * FROM table WHERE color IN', \@colors, '
      AND y =', \$x, 'OR', {z => 3, w => 2}
  );

  # The above is largely equivalent to filtering the parameter
  # list through the function dbi_interp() before passing it to DBI:
  my $ref = $dbh->selectall_arrayref(dbi_interp '
      SELECT * FROM mytable WHERE color IN', \@colors, '
      AND y =', \$x, 'OR', {z => 3, w => 2}
  );
  # dbi_interp() is a thin wrapper around the function sql_interp()
  # (see the SQL::Interpolate module for details) except its return
  # value is in the form DBI expects--typically ($sql, \%attr, @bind).

  # This module is an abstraction of DBI.  This module uses an
  # abstraction of DBI statement handles called "statement handle
  # sets" (stx), which are each a set of statement handles
  # for a class of queries.
  my $stx = $dbx->prepare();      # create stx
  for my $colors (@colorlists) {  # run some queries
      $stx->execute("SELECT * FROM table WHERE color IN", $colors);
          # execute() transparently prepares a new $sth whenever one
          # compatible with the given query invocation is not cached.
      my $ref = $stx->fetchall_arrayref();
  }

DESCRIPTION

DBIx:Interpolate interpolates Perl variables into SQL statements in a simplified manner and passes the result to DBI. DBIx::Interpolate does nothing more than bring SQL::Interpolate and DBI together (please read the documentation on those two modules for background). The DBIx::Interpolate interface is very close to that of DBI. Many methods behave like their DBI counterparts. The methods differ mainly in that they expect an interpolation list as input:

  $dbx->selectall_arrayref(
      "SELECT * from mytable WHERE height > ", \$x);

rather than the typical ($statement, \%attr, @bind_values) of DBI:

  $dbh->selectall_arrayref(
      "SELECT * from mytable WHERE height > ?", undef, $x);

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

  $dbh->select_arrayref(dbi_interp
      "SELECT * from mytable WHERE height > ", \$x );

which in this particular case is equivalent to

  my ($sql, @bind) = sql_interp
      "SELECT * from mytable WHERE height > ", \$x ;
  $dbh->selectall_arrayref($sql, undef, @bind);

It is a design goal of DBIx::Interpolate to maintaining as much resemblance to DBI as reasonably possible.

`dbi_interp`

  ($sql, $attr, @bind) = dbi_interp(@interp_list);
  ($sql, $key_field, $attr, @bind) = dbi_interp(@interp_list);

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:

  $dbh->selectall_hashref($statement, $key_field, \%attr, @bind_values);

dbi_interp() can generate the $key_field parameter (as well as \%attr) as follows:

  my ($sql, $key_field, $attr, @bind) = dbi_interp
      "SELECT * FROM mytable WHERE x=", \$x,
      key_field("y"), attr(myatt => 1)
  # Sets
  #   ($sql, $key_field, $attr, @bind) =
  #       ("SELECT * FROM mytable WHERE x=?", 'y', {myatt=>1}, $x)

Therefore, one may do

dbi_interp() is typically unnecessary to use directly since it is called internally by the DBI wrapper methods:

  $dbx->selectall_hashref(
      "SELECT * FROM mytable WHERE x=", \$x,
      key_field("y"), attr(myatt => 1));
  # same as
  # $dbh->selectall_hashref(dbi_interp
  #     "SELECT * FROM mytable WHERE x=", \$x,
  #     key_field("y"), attr(myatt => 1));

`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.

  my ($sql, $key, $attr, @bind) =
  my @params = dbi_interp "SELECT * FROM mytable", key_field('itemid');
  $dbh->selectall_hashref(@params);

`attr`

  $attrobj = attr(%attr);

Creates and returns an SQL::Interpolate::Attr macro object, which if processed by dbi_interp() will cause dbi_interp() to add the provided key-value pair to the $attr hashref used by DBI methods.

  my ($sql, $attr, @bind) =
  my @params =
    dbi_interp "SELECT a, b FROM mytable", attr(Columns=>[1,2]);
  $dbh->selectcol_arrayref(@params);

`make_dbi_interp`

  $dbi_interp = make_dbi_interp(@params);          # functional
  $dbi_interp = $interp->make_dbi_interp(@params); # OO

This is similar in make_sql_interp except that is generates a closure around the dbi_interp() function or method rather than sql_interp.

Database object (DBX) methods

An object of type DBIx::Interpolate represents (and wraps) a database handle. Most of its methods are wrappers around corresponding DBI methods.

new (static method)

 my $dbx = DBX::Interpolate->new($db, %params);

Creates a new object and optionally creates or attached a DBI handle.

$db [optional] is either a DBI database handle or an ARRAYREF containing parameters that will be passed to DBI::connect, e.g. [$data_source, $username, $auth, \%attr]. This parameter may be omitted.

Any additional %params are passed onto SQL::Interpolate::new.

connect (static method)

 $dbx = DBIx::Interpolate->connect($data_source, $username, $auth, \%attr);

Connects to a database.

This is identical to DBI::connect except that it returns at DBIx::Interpolate object. An alternate way to connect or attach an existing DBI handle is via the new method.

dbh

 $dbh = $dbx->dbh();

Returns the underlying DBI handle $dbh. The is useful if you need to pass the DBI handle to code that does not use SQL::Interpolate.

 $dbx->dbh()->selectall_arrayref(
     "SELECT * FROM mytable WHERE x = ?", undef, $x);

stx

 $stx = $dbx->stx();

Returns the underlying statement handle set $stx. (These are discussed later.) Each DBIx::Interpolate object contains one statement handle set for use on non-prepared database calls (e.g. selectall_.*() methods).

 $dbx->stx()->max_sths(10);

do

selectall_arrayref

selectall_hashref

selectcol_arrayref

selectrow_array

selectrow_arrayref

selectrow_hashref

These methods are identical to those in DBI except that it takes a parameter list identical to dbi_interp().

 my $res = $dbx->selectall_hashref(
               "SELECT * FROM mytable WHERE x=", \$x);

prepare

 $stx = $dbx->prepare();

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

A statement handle set (stx) 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. Typically, you do not need to call "prepare" directly because DBIx::Interpolate can transparently mangage a statement handle set (see $dbx->stx()->max_sths(10)).

Up to one statement handle in a set 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.

Binding variable types (DBI bind_param)

Compare this much simpler code to the example in SQL::Interpolate.

  $dbx->selectall_arrayref(
      "SELECT * FROM mytable WHERE",
      "x=", \$x, "AND y=", sql_var(\$y, SQL_VARCHAR), "AND z IN",
      sql_var([1, 2], SQL_INTEGER)
  );

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).

  $dbx->stx()->max_sths(2);
  $dbx->do(...) for 1..5;
  $dbx->do(...) for 1..5;

An ideal solution would probably be to integrate SQL::Interpolate into DBIx::Simple rather than directly into DBI.

Proposed enhancements

The following enhancements to SQL::Interpolate have been proposed. The most important suggestions are listed at top, and some suggestions could be rejected.

DBI database handle and statement handle attributes are not currently exposed from the wrapper except via $dbx->dbh()->{...}. Maybe a Tie can be used. e.g. $dbx->{mysql_insert_id}

Support might be added for something analogous to DBI's bind_param_inout.

DBI's bind_param_array is not currently supported. A syntax as follows might be used:

  "INSERT INTO mytable", [[...], [...], ...]

Passing identified variables:

  my $x = {one => 'two'};
  my $stx = $dbx->prepare("SELECT * FROM mytable WHERE", sql_var(\$x);
  $stx->execute_vars();
  ...
  $x->{two} = 'three';
  $stx->execute_vars();
  ...

  my $x = {one => 'two'};
  my $y = {one => 'three', two => 'four'};
  my $stx = $dbx->prepare("SELECT * FROM mytable WHERE", sql_var($x, 'x'));
  $stx->execute_vars();
  ...
  $stx->execute_vars(sql_var($x, 'x'); # or?
  $stx->execute_vars(x => $x); # or?
  ...

Conditional macros: (made possible by late expansion of macros)

  $blue = 1;
  $z = 123;
  $stx = $dbx->prepare(
      "SELECT * FROM mytable WHERE",
      sql_and( sql_if(\$blue,  "color = "blue""),
              sql_if(\$shape, sql("shape =", \$shape)),
              'z=', \$z),
      "LIMIT 10"
  );
  $stx->execute_vars();
  $stx->selectall_arrayref();
  $z = 234;
  $stx->execute_vars();  # note: $sth unchanged
  $stx->selectall_arrayref();
  $blue = 0;
  $stx->execute_vars();  # note: $sth changed
  $stx->selectall_arrayref();

CONTRIBUTORS

David Manura (http://math2.org/david) (author). The existence and original design of this module as a wrapper around DBI was suggested by Jim Cromie.

FEEDBACK

Bug reports and comments on the design are most welcome. See the main SQL::Interpolate module for details.

	Global
`s`	Focus search bar
`?`	Bring up this help dialog

	GitHub
`g` `p`	Go to pull requests
`g` `i`	go to github issues (only if github is preferred repository)

	POD
`g` `a`	Go to author
`g` `c`	Go to changes
`g` `i`	Go to issues
`g` `d`	Go to dist
`g` `r`	Go to repository/SCM
`g` `s`	Go to source
`g` `b`	Go to file browse

	Search terms
module: (e.g. module:Plugin)
distribution: (e.g. distribution:Dancer auth)
author: (e.g. author:SONGMU Redis)
version: (e.g. version:1.00)

NAME

SYNOPSIS

DESCRIPTION

INTERFACE

`dbi_interp`

`key_field`

`attr`

`make_dbi_interp`

Database object (DBX) methods

Statement handle set (STX) methods

DEPENDENCIES

ADDITIONAL EXAMPLES

Binding variable types (DBI bind_param)

DESIGN NOTES

Philosophy and requirements

Proposed enhancements

CONTRIBUTORS

FEEDBACK

LEGAL

SEE ALSO

Other modules in this distribution

NAME

SYNOPSIS

DESCRIPTION

INTERFACE

dbi_interp

key_field

attr

make_dbi_interp

Database object (DBX) methods

Statement handle set (STX) methods

DEPENDENCIES

ADDITIONAL EXAMPLES

Binding variable types (DBI bind_param)

DESIGN NOTES

Philosophy and requirements

Proposed enhancements

CONTRIBUTORS

FEEDBACK

LEGAL

SEE ALSO

Other modules in this distribution

Module Install Instructions

`dbi_interp`

`key_field`

`attr`

`make_dbi_interp`