Provided by: libtemplate-plugin-dbi-perl_2.65-2.1_all bug

NAME

       Template::Plugin::DBI - Template interface to the DBI module

SYNOPSIS

       Making an implicit database connection:

           # ...using positional arguments
           [% USE DBI('dbi:driver:dbname', 'user', 'pass') %]

           # ...using named parameters
           [% USE DBI( database = 'dbi:driver:dbname',
                       username = 'user',
                       password = 'pass' )
           %]

           # ...using short named parameters (4 lzy ppl and bad typsits)
           [% USE DBI( db   = 'driver:dbname',
                       user = 'user',
                       pass = 'pass' )
           %]

           # ...or an existing DBI database handle
           [% USE DBI( dbh = my_dbh_ref ) %]

       Making explicit database connections:

           [% USE DBI %]

           [% DBI.connect(db, user, pass) %]
              ...

           [% DBI.connect(new_db, new_user, new_pass) %]
              ...

           [% DBI.disconnect %]      # final disconnect is optional

       Making an automagical database connection using DBI_DSN environment variable:

           [% USE DBI %]

       Making database queries:

           # single step query
           [% FOREACH user = DBI.query('SELECT * FROM users') %]
              [% user.uid %] blah blah [% user.name %] etc. etc.
           [% END %]

           # two stage prepare/execute
           [% query = DBI.prepare('SELECT * FROM users WHERE uid = ?') %]

           [% FOREACH user = query.execute('sam') %]
              ...
           [% END %]

           [% FOREACH user = query.execute('abw') %]
              ...
           [% END %]

       Making non-SELECT statements:

           [% IF DBI.do("DELETE FROM users WHERE uid = '$uid'") %]
              The user '[% uid %]' was successfully deleted.
           [% END %]

       Using named DBI connections:

           [% USE one = DBI(...) %]
           [% USE two = DBI(...) %]

           [% FOREACH item = one.query("SELECT ...etc...") %]
              ...
           [% END %]

           [% FOREACH item = two.query("SELECT ...etc...") %]
              ...
           [% END %]

       Tieing to a database table (via Tie::DBI):

           [% people = DBI.tie('users', 'uid') %]

           [% me = people.abw %]   # => SELECT * FROM users WHERE uid='abw'

           I am [% me.name %]

           # clobber option allows table updates (see Tie::DBI)
           [% people = DBI.tie('users', 'uid', clobber=1) %]

           [% people.abw.name = 'not a number' %]

           I am [% people.abw.name %]   # I am a free man!

DESCRIPTION

       This Template Toolkit plugin module provides an interface to the Perl DBI/DBD modules,
       allowing you to integrate SQL 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 SQL in your templates.

       A DBI plugin object can be created as follows:

           [% USE DBI %]

       This creates an uninitialised DBI object.  You can then open a connection to a database
       using the connect() method.

           [% DBI.connect('dbi:driver:dbname', 'user', 'pass') %]

       The DBI connection can be opened when the plugin is created by passing arguments to the
       constructor, called from the USE directive.

           [% USE DBI('dbi:driver:dbname', 'user', 'pass') %]

       You can also use named parameters to provide the data source connection string, user name
       and password.

           [% USE DBI(database => 'dbi:driver:dbname',
                      username => 'user',
                      password => 'pass')  %]

       For backwards compatability with previous versions of this plugin, you can also spell
       'database' as 'data_source'.

           [% USE DBI(data_source => 'dbi:driver:dbname',
                      username    => 'user',
                      password    => 'pass')  %]

       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.

           [% USE DBI(db   => 'driver:dbname',
                      user => 'user',
                      pass => 'pass')  %]

       Any additional DBI attributes can be specified as named parameters.  The 'PrintError'
       attribute defaults to 0 unless explicitly set true.

           [% USE DBI(db, user, pass, ChopBlanks=1) %]

       An alternate variable name can be provided for the plugin as per regular Template Toolkit
       syntax:

           [% USE mydb = DBI('dbi:driver:dbname', 'user', 'pass') %]

           [% FOREACH item = mydb.query('SELECT * FROM users') %]
              ...
           [% END %]

       You can also specify the DBI plugin name in lower case if you prefer:

           [% USE dbi(dsn, user, pass) %]

           [% FOREACH item = dbi.query('SELECT * FROM users') %]
              ...
           [% END %]

       The disconnect() 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 connect() at any time to open a connection to another
       database.  The previous connection will be closed automatically.

       Internally, the DBI connect_cached() method is used instead of the connect() 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:

           SetEnv DBI_DSN "dbi:mysql:dbname;host=dbhost;
                                     user=uname;password=pword"

       (NOTE: 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).

       You can then use the DBI plugin without any parameters or the need to explicitly call
       connect().

       Once you've loaded a DBI 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:

           [% FOREACH user = DBI.query('SELECT * FROM users') %]
              [% user.uid %] blah blah [% user.name %] etc. etc.
           [% END %]

       The query() 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.

           [% query = DBI.prepare('SELECT * FROM users WHERE uid = ?') %]

           [% FOREACH user = query.execute('sam') %]
              ...
           [% END %]

           [% FOREACH user = query.execute('abw') %]
              ...
           [% END %]

       The query() and execute() methods return an iterator object which manages the result set
       returned.  You can save a reference to the iterator and access methods like size() to
       determine the number of rows returned by a query.

           [% users = DBI.query('SELECT * FROM users') %]
           [% users.size %] records returned

       or even

           [% DBI.query('SELECT * FROM users').size %]

       When used within a FOREACH loop, the iterator is always aliased to the special "loop"
       variable.  This makes it possible to do things like this:

           [% FOREACH user = DBI.query('SELECT * FROM users') %]
              [% loop.count %]/[% loop.size %]: [% user.name %]
           [% END %]

       to generate a result set of the form:

           1/3: Jerry Garcia
           2/3: Kurt Cobain
           3/3: Freddie Mercury

       See Template::Iterator for further details on iterators and the methods that they
       implement.

       The DBI plugin also provides the do() method to execute non-SELECT statements like this:

           [% IF DBI.do("DELETE FROM users WHERE uid = '$uid'") %]
              The user '[% uid %]' was successfully deleted.
           [% END %]

       The plugin also allows you to create a tie to a table in the database using the Tie::DBI
       module.  Simply call the tie() method, passing the name of the table and the primary key
       as arguments.

           [% people = DBI.tie('person', 'uid') %]

       You can then access records in the database table as if they were entries in the 'people'
       hash.

           My name is [% people.abw.name %]

       IMPORTANT NOTE: the XS Stash (Template::Stash::XS) does not currently support access to
       tied hashes.  If you are using the XS stash and having problems then you should try
       enabling the regular stash instead.  You can do this by setting $Template::Config::STASH
       to 'Template::Stash' before instantiating the Template object.

OBJECT METHODS

   connect($database, $username, $password)
       Establishes a database connection.  This method accepts both positional and named
       parameter syntax.  e.g.

           [% DBI.connect( 'dbi:driver:dbname', 'timmy', 'sk8D00Dz' ) %]

           [% DBI.connect( database = 'dbi:driver:dbname'
                           username = 'timmy'
                           password = 'sk8D00Dz' ) %]

       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.

       If you already have a database handle then you can instruct the plugin to reuse it by
       passing it as the 'dbh' parameter.

           [% DBI.connect( dbh = my_dbh_ref ) %]

   query($sql,[\%args],[@bind_values])
       This method submits an SQL query to the database and creates an iterator object to return
       the results.  This may be used directly in a FOREACH directive as shown below.  Data is
       automatically fetched a row at a time from the query result set as required for memory
       efficiency.

           [% FOREACH user = DBI.query('SELECT * FROM users') %]
              Each [% user.field %] can be printed here
           [% END %]

   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 execute() method can subsequently be
       called.

           [% query = DBI.prepare('SELECT * FROM users WHERE id = ?') %]

   execute(@bind_values)
       Execute a previously prepared query.  This method should be called on the query object
       returned by the prepare() method.  Returns an iterator object which can be used directly
       in a FOREACH directive.

           [% query = DBI.prepare('SELECT * FROM users WHERE manager = ?') %]

           [% FOREACH minion = query.execute('abw') %]
              [% minion.name %]
           [% END %]

           [% FOREACH minion = query.execute('sam') %]
              [% minion.name %]
           [% END %]

   do($sql,[\%attr,[@bind_values]])
       The do() method executes a sql statement from which no records are returned.  It will
       return true if the statement was successful

           [% IF DBI.do("DELETE FROM users WHERE uid = 'sam'") %]
              The user was successfully deleted.
           [% END %]

   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.

           [% people = DBI.tie('users', 'uid') %]

       Or if you prefer, you can use the 'table' and 'key' named parameters.

           [% people = DBI.tie(table='users', key='uid') %]

       In this example, the Tie::DBI module will convert the accesses into the 'people' hash into
       SQL queries of the form:

           SELECT * FROM users WHERE uid=?

       For example:

           [% me = people.abw %]

       The record returned can then be accessed just like a normal hash.

           I am [% me.name %]

       You can also do things like this to iterate through all the records in a table.

           [% FOREACH uid = people.keys.sort;
                   person = people.$uid
           %]
               * [% person.id %] : [% person.name %]
           [% END %]

       With the 'clobber' (or 'CLOBBER') option set you can update the record and have those
       changes automatically permeated back into the database.

           [% people = DBI.tie('users', 'uid', clobber=1) %]

           [% people.abw.name = 'not a number' %]

           I am [% people.abw.name %]  # I am a free man!

       And you can also add new records.

           [% people.newguy = {
                  name = 'Nobby Newguy'
                  ...other fields...
              }
           %]

       See Tie::DBI for further information on the 'CLOBBER' option.

   quote($value, $type)
       Calls the quote() method on the underlying DBI handle to quote the value specified in the
       appropriate manner for its type.

   dbh
         [% DBI.dbh() %]

       Return the database handle currently in use by the plugin.

   disconnect
         [% DBI.disconnect() %]

       Disconnects the current database.

   fetch
         [% DBI.fetch('Name') %]

       Fetches an attribute from the connected database handle.  Throws an exception when not
       connected.

   store
         [% DBI.store('RowCacheSize',0) %]

       Stores an attribute in the connected database handle.  Throws an exception when not
       connected.

ITERATOR METHODS

   get_all
         [% allhash = result.get_all() %]

       Returns all (remaining) rows as a hash.

   get_all_list
         [% allhash = result.get_all_list() %]

       Returns all (remaining) rows as a list.

   rows
       Returns the number of affected rows of the executed statement.

   get_colnames
       Returns the lowercase column names ($sth attribute "->{NAME_lc}").

   get_COLnames
       Returns the uppercase column names ($sth attribute "->{NAME_uc}").

   fetch
       Fetches an attribute from the statement handle.

         [% result.fetch('FetchHashKeyName') %]

   store
       Stores an attribute to the statement handle.

         [% result.store('FetchHashKeyName','NAME_lc') %]

AUTHORS

       The DBI 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.

COPYRIGHT

         Copyright (C) 1999-2006 Simon Matthews, Andy Wardley.  All Rights Reserved.
         Copyright (C) 2010 Jens Rehsack.  All Rights Reserved

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

SEE ALSO

       Template Template::Plugins, DBI, Tie::DBI