NAME

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

SYNOPSIS

    [% USE DBI('dbi:driver:database', 'username', 'password') %]

    [% USE DBI(data_source = 'dbi:driver:database',
               username    = 'username', 
               password    = 'password') %]

    [% FOREACH item = DBI.query( 'SELECT rows FROM table' ) %]
       Here's some row data: [% item.field %]
    [% END %]

    [% DBI.prepare('SELECT * FROM user WHERE manager = ?') %]
    [% FOREACH user = DBI.execute('sam') %]
       ...

    [% query = DBI.prepare('SELECT * FROM user WHERE manager = ?') %]
    [% FOREACH user = query.execute('sam') %]
       ...

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

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.

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:database', 'username', 'password') %]

The DBI plugin can be initialised when created by passing parameters to the constructor, called from the USE directive.

    [% USE DBI('dbi:driver:database','username','password') %]

Methods can then be called on the plugin object using the familiar dotted notation. e.g.

    [% FOREACH item = DBI.query( 'SELECT rows FROM table' ) %]
       Here's some row data: [% item.field %]
    [% END %]

See "OBJECT METHODS" below for further details.

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

    [% USE mydb = DBI('dbi:driver:database','username','password') %]

    [% FOREACH item = mydb.query( 'SELECT rows FROM table' ) %]
       Here's some row data: [% item.field %]
    [% END %]

The disconnect() method can be called to explicitly disconnect the current database. This is called automatically when the plugin goes out of scope.

OBJECT METHODS

connect($data_source, $username, $password)

Establishes a database connection. This method accepts both positional and named parameter syntax. e.g.

    [% db = DBI.connect(data_source, username, password) %]
    [% db = DBI.connect(data_source = 'dbi:driver:database'
                        username    = 'foo' 
                        password    = 'bar' ) %]

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.

query($sql)

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 greater efficiency.

    [% FOREACH row = DBI.query('select * from users') %]
       Each [% row.whatever %] can be processed here
    [% END %]

prepare($sql)

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. The compiled query is also cached internally, allowing the execute() method call to also be made on the parent DBI object.

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

execute(@args)

Execute a previously prepared query. This method can be called on the parent DBI object to execute the query most recently compiled via prepare(). It can also be called directly on the query object returned by prepare(). Returns an iterator object which can be used directly in a FOREACH directive.

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

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

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

do($sql)

Do executes a sql statement where there will be no records returned. It will return true if the statement was successful

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

quote($value, $type)

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

disconnect()

Disconnects the current database.

error()

Returns the current error value, if any.

PRE-REQUISITES

Perl 5.005, Template-Toolkit 2.00-beta3, DBI 1.02

SEE ALSO

For general information on the Template Toolkit and DBI modules, see Template and DBI.

AUTHOR

Simon A Matthews, <sam@knowledgepool.com>

COPYRIGHT

Copyright (C) 1999 Simon Matthews. All Rights Reserved

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

cpanm Template::Plugin::DBI

perl -MCPAN -e shell
install Template::Plugin::DBI