.\" Automatically generated by Pod::Man 4.14 (Pod::Simple 3.40)
.\"
.\" Standard preamble:
.\" ========================================================================
.de Sp \" Vertical space (when we can't use .PP)
.if t .sp .5v
.if n .sp
..
.de Vb \" Begin verbatim text
.ft CW
.nf
.ne \\$1
..
.de Ve \" End verbatim text
.ft R
.fi
..
.\" Set up some character translations and predefined strings.  \*(-- will
.\" give an unbreakable dash, \*(PI will give pi, \*(L" will give a left
.\" double quote, and \*(R" will give a right double quote.  \*(C+ will
.\" give a nicer C++.  Capital omega is used to do unbreakable dashes and
.\" therefore won't be available.  \*(C` and \*(C' expand to `' in nroff,
.\" nothing in troff, for use with C<>.
.tr \(*W-
.ds C+ C\v'-.1v'\h'-1p'\s-2+\h'-1p'+\s0\v'.1v'\h'-1p'
.ie n \{\
.    ds -- \(*W-
.    ds PI pi
.    if (\n(.H=4u)&(1m=24u) .ds -- \(*W\h'-12u'\(*W\h'-12u'-\" diablo 10 pitch
.    if (\n(.H=4u)&(1m=20u) .ds -- \(*W\h'-12u'\(*W\h'-8u'-\"  diablo 12 pitch
.    ds L" ""
.    ds R" ""
.    ds C` ""
.    ds C' ""
'br\}
.el\{\
.    ds -- \|\(em\|
.    ds PI \(*p
.    ds L" ``
.    ds R" ''
.    ds C`
.    ds C'
'br\}
.\"
.\" Escape single quotes in literal strings from groff's Unicode transform.
.ie \n(.g .ds Aq \(aq
.el       .ds Aq '
.\"
.\" If the F register is >0, we'll generate index entries on stderr for
.\" titles (.TH), headers (.SH), subsections (.SS), items (.Ip), and index
.\" entries marked with X<> in POD.  Of course, you'll have to process the
.\" output yourself in some meaningful fashion.
.\"
.\" Avoid warning from groff about undefined register 'F'.
.de IX
..
.nr rF 0
.if \n(.g .if rF .nr rF 1
.if (\n(rF:(\n(.g==0)) \{\
.    if \nF \{\
.        de IX
.        tm Index:\\$1\t\\n%\t"\\$2"
..
.        if !\nF==2 \{\
.            nr % 0
.            nr F 2
.        \}
.    \}
.\}
.rr rF
.\" ========================================================================
.\"
.IX Title "Template::Plugin::DBI 3pm"
.TH Template::Plugin::DBI 3pm "2021-01-01" "perl v5.32.0" "User Contributed Perl Documentation"
.\" For nroff, turn off justification.  Always turn off hyphenation; it makes
.\" way too many mistakes in technical documents.
.if n .ad l
.nh
.SH "NAME"
Template::Plugin::DBI \- Template interface to the DBI module
.SH "SYNOPSIS"
.IX Header "SYNOPSIS"
Making an implicit database connection:
.PP
.Vb 2
\&    # ...using positional arguments
\&    [% USE DBI(\*(Aqdbi:driver:dbname\*(Aq, \*(Aquser\*(Aq, \*(Aqpass\*(Aq) %]
\&
\&    # ...using named parameters
\&    [% USE DBI( database = \*(Aqdbi:driver:dbname\*(Aq,
\&                username = \*(Aquser\*(Aq, 
\&                password = \*(Aqpass\*(Aq )
\&    %]
\&
\&    # ...using short named parameters (4 lzy ppl and bad typsits)
\&    [% USE DBI( db   = \*(Aqdriver:dbname\*(Aq,
\&                user = \*(Aquser\*(Aq, 
\&                pass = \*(Aqpass\*(Aq )
\&    %]
\&
\&    # ...or an existing DBI database handle
\&    [% USE DBI( dbh = my_dbh_ref ) %]
.Ve
.PP
Making explicit database connections:
.PP
.Vb 1
\&    [% USE DBI %]
\&
\&    [% DBI.connect(db, user, pass) %]
\&       ...
\&
\&    [% DBI.connect(new_db, new_user, new_pass) %]
\&       ...
\&
\&    [% DBI.disconnect %]      # final disconnect is optional
.Ve
.PP
Making an automagical database connection using \s-1DBI_DSN\s0 environment variable:
.PP
.Vb 1
\&    [% USE DBI %]
.Ve
.PP
Making database queries:
.PP
.Vb 4
\&    # single step query
\&    [% FOREACH user = DBI.query(\*(AqSELECT * FROM users\*(Aq) %]
\&       [% user.uid %] blah blah [% user.name %] etc. etc.
\&    [% END %]
\&
\&    # two stage prepare/execute
\&    [% query = DBI.prepare(\*(AqSELECT * FROM users WHERE uid = ?\*(Aq) %]
\&
\&    [% FOREACH user = query.execute(\*(Aqsam\*(Aq) %]
\&       ...
\&    [% END %]
\&
\&    [% FOREACH user = query.execute(\*(Aqabw\*(Aq) %]
\&       ...
\&    [% END %]
.Ve
.PP
Making non-SELECT statements:
.PP
.Vb 3
\&    [% IF DBI.do("DELETE FROM users WHERE uid = \*(Aq$uid\*(Aq") %]
\&       The user \*(Aq[% uid %]\*(Aq was successfully deleted.
\&    [% END %]
.Ve
.PP
Using named \s-1DBI\s0 connections:
.PP
.Vb 2
\&    [% USE one = DBI(...) %]
\&    [% USE two = DBI(...) %]
\&
\&    [% FOREACH item = one.query("SELECT ...etc...") %]
\&       ...
\&    [% END %]
\&
\&    [% FOREACH item = two.query("SELECT ...etc...") %]
\&       ...
\&    [% END %]
.Ve
.PP
Tieing to a database table (via Tie::DBI):
.PP
.Vb 1
\&    [% people = DBI.tie(\*(Aqusers\*(Aq, \*(Aquid\*(Aq) %]
\&
\&    [% me = people.abw %]   # => SELECT * FROM users WHERE uid=\*(Aqabw\*(Aq
\&
\&    I am [% me.name %]
\&
\&    # clobber option allows table updates (see Tie::DBI)
\&    [% people = DBI.tie(\*(Aqusers\*(Aq, \*(Aquid\*(Aq, clobber=1) %]
\&
\&    [% people.abw.name = \*(Aqnot a number\*(Aq %]
\&
\&    I am [% people.abw.name %]   # I am a free man!
.Ve
.SH "DESCRIPTION"
.IX Header "DESCRIPTION"
This Template Toolkit plugin module provides an interface to the Perl
\&\s-1DBI/DBD\s0 modules, allowing you to integrate \s-1SQL\s0 queries into your
template documents.  It also provides an interface via the Tie::DBI
module (if installed on your system) so that you can access database
records without having to embed any \s-1SQL\s0 in your templates.
.PP
A \s-1DBI\s0 plugin object can be created as follows:
.PP
.Vb 1
\&    [% USE DBI %]
.Ve
.PP
This creates an uninitialised \s-1DBI\s0 object.  You can then open a connection
to a database using the \fBconnect()\fR method.
.PP
.Vb 1
\&    [% DBI.connect(\*(Aqdbi:driver:dbname\*(Aq, \*(Aquser\*(Aq, \*(Aqpass\*(Aq) %]
.Ve
.PP
The \s-1DBI\s0 connection can be opened when the plugin is created by passing
arguments to the constructor, called from the \s-1USE\s0 directive.
.PP
.Vb 1
\&    [% USE DBI(\*(Aqdbi:driver:dbname\*(Aq, \*(Aquser\*(Aq, \*(Aqpass\*(Aq) %]
.Ve
.PP
You can also use named parameters to provide the data source connection 
string, user name and password.
.PP
.Vb 3
\&    [% USE DBI(database => \*(Aqdbi:driver:dbname\*(Aq,
\&               username => \*(Aquser\*(Aq,
\&               password => \*(Aqpass\*(Aq)  %]
.Ve
.PP
For backwards compatability with previous versions of this plugin, you can
also spell 'database' as 'data_source'.
.PP
.Vb 3
\&    [% USE DBI(data_source => \*(Aqdbi:driver:dbname\*(Aq,
\&               username    => \*(Aquser\*(Aq,
\&               password    => \*(Aqpass\*(Aq)  %]
.Ve
.PP
Lazy Template hackers may prefer to use 'db', 'dsn' or 'connect' as a
shorthand form of the 'database' parameter, and 'user' and 'pass' as
shorthand forms of 'username' and 'password', respectively.  You can
also drop the 'dbi:' prefix from the database connect string because
the plugin will add it on for you automagically.
.PP
.Vb 3
\&    [% USE DBI(db   => \*(Aqdriver:dbname\*(Aq,
\&               user => \*(Aquser\*(Aq,
\&               pass => \*(Aqpass\*(Aq)  %]
.Ve
.PP
Any additional \s-1DBI\s0 attributes can be specified as named parameters.
The 'PrintError' attribute defaults to 0 unless explicitly set true.
.PP
.Vb 1
\&    [% USE DBI(db, user, pass, ChopBlanks=1) %]
.Ve
.PP
An alternate variable name can be provided for the plugin as per regular
Template Toolkit syntax:
.PP
.Vb 1
\&    [% USE mydb = DBI(\*(Aqdbi:driver:dbname\*(Aq, \*(Aquser\*(Aq, \*(Aqpass\*(Aq) %]
\&
\&    [% FOREACH item = mydb.query(\*(AqSELECT * FROM users\*(Aq) %]
\&       ...
\&    [% END %]
.Ve
.PP
You can also specify the \s-1DBI\s0 plugin name in lower case if you prefer:
.PP
.Vb 1
\&    [% USE dbi(dsn, user, pass) %]
\&
\&    [% FOREACH item = dbi.query(\*(AqSELECT * FROM users\*(Aq) %]
\&       ...
\&    [% END %]
.Ve
.PP
The \fBdisconnect()\fR method can be called to explicitly disconnect the
current database, but this generally shouldn't be necessary as it is
called automatically when the plugin goes out of scope.  You can call
\&\fBconnect()\fR at any time to open a connection to another database.  The
previous connection will be closed automatically.
.PP
Internally, the \s-1DBI\s0 \fBconnect_cached()\fR method is used instead of the
\&\fBconnect()\fR method.  This allows for connection caching in a server
environment, such as when the Template Toolkit is used from an Apache
mod_perl handler.  In such a case, simply enable the mod_env module
and put in a line such as:
.PP
.Vb 2
\&    SetEnv DBI_DSN "dbi:mysql:dbname;host=dbhost; 
\&                              user=uname;password=pword"
.Ve
.PP
(\s-1NOTE:\s0 the string shown here is split across 2 lines for the sake of
reasonable page formatting, but you should specify it all as one long
string with no spaces or newlines).
.PP
You can then use the \s-1DBI\s0 plugin without any parameters or the need
to explicitly call \fBconnect()\fR.
.PP
Once you've loaded a \s-1DBI\s0 plugin and opened a database connection using 
one of the techniques shown above, you can then make queries on the database
using the familiar dotted notation:
.PP
.Vb 3
\&    [% FOREACH user = DBI.query(\*(AqSELECT * FROM users\*(Aq) %]
\&       [% user.uid %] blah blah [% user.name %] etc. etc.
\&    [% END %]
.Ve
.PP
The \fBquery()\fR method prepares a query and executes it all in one go.
If you want to repeat a query with different parameters then you 
can use a separate prepare/execute cycle.
.PP
.Vb 1
\&    [% query = DBI.prepare(\*(AqSELECT * FROM users WHERE uid = ?\*(Aq) %]
\&
\&    [% FOREACH user = query.execute(\*(Aqsam\*(Aq) %]
\&       ...
\&    [% END %]
\&
\&    [% FOREACH user = query.execute(\*(Aqabw\*(Aq) %]
\&       ...
\&    [% END %]
.Ve
.PP
The \fBquery()\fR and \fBexecute()\fR methods return an iterator object which
manages the result set returned.  You can save a reference to the
iterator and access methods like \fBsize()\fR to determine the number of
rows returned by a query.
.PP
.Vb 2
\&    [% users = DBI.query(\*(AqSELECT * FROM users\*(Aq) %]
\&    [% users.size %] records returned
.Ve
.PP
or even
.PP
.Vb 1
\&    [% DBI.query(\*(AqSELECT * FROM users\*(Aq).size %]
.Ve
.PP
When used within a \s-1FOREACH\s0 loop, the iterator is always aliased to the 
special \f(CW\*(C`loop\*(C'\fR variable.  This makes it possible to do things like this:
.PP
.Vb 3
\&    [% FOREACH user = DBI.query(\*(AqSELECT * FROM users\*(Aq) %]
\&       [% loop.count %]/[% loop.size %]: [% user.name %]
\&    [% END %]
.Ve
.PP
to generate a result set of the form:
.PP
.Vb 3
\&    1/3: Jerry Garcia
\&    2/3: Kurt Cobain
\&    3/3: Freddie Mercury
.Ve
.PP
See Template::Iterator for further details on iterators and the
methods that they implement.
.PP
The \s-1DBI\s0 plugin also provides the \fBdo()\fR method to execute non-SELECT
statements like this:
.PP
.Vb 3
\&    [% IF DBI.do("DELETE FROM users WHERE uid = \*(Aq$uid\*(Aq") %]
\&       The user \*(Aq[% uid %]\*(Aq was successfully deleted.
\&    [% END %]
.Ve
.PP
The plugin also allows you to create a tie to a table in the database
using the Tie::DBI module.  Simply call the \fBtie()\fR method, passing the
name of the table and the primary key as arguments.
.PP
.Vb 1
\&    [% people = DBI.tie(\*(Aqperson\*(Aq, \*(Aquid\*(Aq) %]
.Ve
.PP
You can then access records in the database table as if they were
entries in the 'people' hash.
.PP
.Vb 1
\&    My name is [% people.abw.name %]
.Ve
.PP
\&\s-1IMPORTANT NOTE:\s0 the \s-1XS\s0 Stash (Template::Stash::XS) does not currently
support access to tied hashes.  If you are using the \s-1XS\s0 stash and having
problems then you should try enabling the regular stash instead.  You 
can do this by setting \f(CW$Template::Config::STASH\fR to 'Template::Stash' 
before instantiating the Template object.
.SH "OBJECT METHODS"
.IX Header "OBJECT METHODS"
.ie n .SS "connect($database, $username, $password)"
.el .SS "connect($database, \f(CW$username\fP, \f(CW$password\fP)"
.IX Subsection "connect($database, $username, $password)"
Establishes a database connection.  This method accepts both positional 
and named parameter syntax.  e.g.
.PP
.Vb 1
\&    [% DBI.connect( \*(Aqdbi:driver:dbname\*(Aq, \*(Aqtimmy\*(Aq, \*(Aqsk8D00Dz\*(Aq ) %]
\&
\&    [% DBI.connect( database = \*(Aqdbi:driver:dbname\*(Aq
\&                    username = \*(Aqtimmy\*(Aq 
\&                    password = \*(Aqsk8D00Dz\*(Aq ) %]
.Ve
.PP
The connect method allows you to connect to a data source explicitly.
It can also be used to reconnect an exisiting object to a different
data source.
.PP
If you already have a database handle then you can instruct the plugin
to reuse it by passing it as the 'dbh' parameter.
.PP
.Vb 1
\&    [% DBI.connect( dbh = my_dbh_ref ) %]
.Ve
.SS "query($sql,[\e%args],[@bind_values])"
.IX Subsection "query($sql,[%args],[@bind_values])"
This method submits an \s-1SQL\s0 query to the database and creates an iterator 
object to return the results.  This may be used directly in a \s-1FOREACH\s0 
directive as shown below.  Data is automatically fetched a row at a time
from the query result set as required for memory efficiency.
.PP
.Vb 3
\&    [% FOREACH user = DBI.query(\*(AqSELECT * FROM users\*(Aq) %]
\&       Each [% user.field %] can be printed here
\&    [% END %]
.Ve
.SS "prepare($sql,[\e%args])"
.IX Subsection "prepare($sql,[%args])"
Prepare a query for later execution.  This returns a compiled query
object (of the Template::Plugin::DBI::Query class) on which the
\&\fBexecute()\fR method can subsequently be called.
.PP
.Vb 1
\&    [% query = DBI.prepare(\*(AqSELECT * FROM users WHERE id = ?\*(Aq) %]
.Ve
.SS "execute(@bind_values)"
.IX Subsection "execute(@bind_values)"
Execute a previously prepared query.  This method should be called on
the query object returned by the \fBprepare()\fR method.  Returns an
iterator object which can be used directly in a \s-1FOREACH\s0 directive.
.PP
.Vb 1
\&    [% query = DBI.prepare(\*(AqSELECT * FROM users WHERE manager = ?\*(Aq) %]
\&
\&    [% FOREACH minion = query.execute(\*(Aqabw\*(Aq) %]
\&       [% minion.name %]
\&    [% END %]
\&
\&    [% FOREACH minion = query.execute(\*(Aqsam\*(Aq) %]
\&       [% minion.name %]
\&    [% END %]
.Ve
.SS "do($sql,[\e%attr,[@bind_values]])"
.IX Subsection "do($sql,[%attr,[@bind_values]])"
The \fBdo()\fR method executes a sql statement from which no records are
returned.  It will return true if the statement was successful
.PP
.Vb 3
\&    [% IF DBI.do("DELETE FROM users WHERE uid = \*(Aqsam\*(Aq") %]
\&       The user was successfully deleted.
\&    [% END %]
.Ve
.ie n .SS "tie($table, $key, \e%args)"
.el .SS "tie($table, \f(CW$key\fP, \e%args)"
.IX Subsection "tie($table, $key, %args)"
Returns a reference to a hash array tied to a table in the database,
implemented using the Tie::DBI module.  You should pass the name of
the table and the key field as arguments.
.PP
.Vb 1
\&    [% people = DBI.tie(\*(Aqusers\*(Aq, \*(Aquid\*(Aq) %]
.Ve
.PP
Or if you prefer, you can use the 'table' and 'key' named parameters.
.PP
.Vb 1
\&    [% people = DBI.tie(table=\*(Aqusers\*(Aq, key=\*(Aquid\*(Aq) %]
.Ve
.PP
In this example, the Tie::DBI module will convert the accesses into
the 'people' hash into \s-1SQL\s0 queries of the form:
.PP
.Vb 1
\&    SELECT * FROM users WHERE uid=?
.Ve
.PP
For example:
.PP
.Vb 1
\&    [% me = people.abw %]
.Ve
.PP
The record returned can then be accessed just like a normal hash.
.PP
.Vb 1
\&    I am [% me.name %]
.Ve
.PP
You can also do things like this to iterate through all the records
in a table.
.PP
.Vb 5
\&    [% FOREACH uid = people.keys.sort;
\&            person = people.$uid 
\&    %] 
\&        * [% person.id %] : [% person.name %]
\&    [% END %]
.Ve
.PP
With the 'clobber' (or '\s-1CLOBBER\s0') option set you can update the record
and have those changes automatically permeated back into the database.
.PP
.Vb 1
\&    [% people = DBI.tie(\*(Aqusers\*(Aq, \*(Aquid\*(Aq, clobber=1) %]
\&
\&    [% people.abw.name = \*(Aqnot a number\*(Aq %]
\&
\&    I am [% people.abw.name %]  # I am a free man!
.Ve
.PP
And you can also add new records.
.PP
.Vb 5
\&    [% people.newguy = {
\&           name = \*(AqNobby Newguy\*(Aq
\&           ...other fields...
\&       }
\&    %]
.Ve
.PP
See Tie::DBI for further information on the '\s-1CLOBBER\s0' option.
.ie n .SS "quote($value, $type)"
.el .SS "quote($value, \f(CW$type\fP)"
.IX Subsection "quote($value, $type)"
Calls the \fBquote()\fR method on the underlying \s-1DBI\s0 handle to quote the value
specified in the appropriate manner for its type.
.SS "dbh"
.IX Subsection "dbh"
.Vb 1
\&  [% DBI.dbh() %]
.Ve
.PP
Return the database handle currently in use by the plugin.
.SS "disconnect"
.IX Subsection "disconnect"
.Vb 1
\&  [% DBI.disconnect() %]
.Ve
.PP
Disconnects the current database.
.SS "fetch"
.IX Subsection "fetch"
.Vb 1
\&  [% DBI.fetch(\*(AqName\*(Aq) %]
.Ve
.PP
Fetches an attribute from the connected database handle.
Throws an exception when not connected.
.SS "store"
.IX Subsection "store"
.Vb 1
\&  [% DBI.store(\*(AqRowCacheSize\*(Aq,0) %]
.Ve
.PP
Stores an attribute in the connected database handle.
Throws an exception when not connected.
.SH "ITERATOR METHODS"
.IX Header "ITERATOR METHODS"
.SS "get_all"
.IX Subsection "get_all"
.Vb 1
\&  [% allhash = result.get_all() %]
.Ve
.PP
Returns all (remaining) rows as a hash.
.SS "get_all_list"
.IX Subsection "get_all_list"
.Vb 1
\&  [% allhash = result.get_all_list() %]
.Ve
.PP
Returns all (remaining) rows as a list.
.SS "rows"
.IX Subsection "rows"
Returns the number of affected rows of the executed statement.
.SS "get_colnames"
.IX Subsection "get_colnames"
Returns the lowercase column names (\f(CW$sth\fR attribute \f(CW\*(C`\->{NAME_lc}\*(C'\fR).
.SS "get_COLnames"
.IX Subsection "get_COLnames"
Returns the uppercase column names (\f(CW$sth\fR attribute \f(CW\*(C`\->{NAME_uc}\*(C'\fR).
.SS "fetch"
.IX Subsection "fetch"
Fetches an attribute from the statement handle.
.PP
.Vb 1
\&  [% result.fetch(\*(AqFetchHashKeyName\*(Aq) %]
.Ve
.SS "store"
.IX Subsection "store"
Stores an attribute to the statement handle.
.PP
.Vb 1
\&  [% result.store(\*(AqFetchHashKeyName\*(Aq,\*(AqNAME_lc\*(Aq) %]
.Ve
.SH "AUTHORS"
.IX Header "AUTHORS"
The \s-1DBI\s0 plugin was originally written by Simon A Matthews, and
distributed as a separate module.  It was integrated into the Template
Toolkit distribution for version 2.00 and includes contributions from
Andy Wardley, Craig Barratt, Dave Hodgkinson and Rafael Kitover. Andy
Wardley extracted it back into a separate distribution in May 2006.
After that, in 2010 Jens Rehsack maintains this distribution.
.SH "COPYRIGHT"
.IX Header "COPYRIGHT"
.Vb 2
\&  Copyright (C) 1999\-2006 Simon Matthews, Andy Wardley.  All Rights Reserved.
\&  Copyright (C) 2010 Jens Rehsack.  All Rights Reserved
.Ve
.PP
This module is free software; you can redistribute it and/or
modify it under the same terms as Perl itself.
.SH "SEE ALSO"
.IX Header "SEE ALSO"
Template Template::Plugins, \s-1DBI\s0, Tie::DBI