Skip site navigation (1)Skip section navigation (2)

FreeBSD Manual Pages


home | help
Template::Plugin::DBI(User Contributed Perl DocumentatTemplate::Plugin::DBI(3)

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

       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

	   [% USE DBI %]

       Making database queries:

	   # single step query
	   [% FOREACH user = DBI.query('SELECT * FROM users') %]
	      [% user.uid %] blah blah [% %] 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"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	[% %]

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

	   [% =	'not a number' %]

	   I am	[% %]	# I am a free man!

       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;

       (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 [% %] 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

	   [% FOREACH user = DBI.query('SELECT * FROM users') %]
	      [% loop.count %]/[% loop.size %]:	[% %]
	   [% 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"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 [% %]

       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.

   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 ) %]

       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 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 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') %]
	      [% %]
	   [% END %]

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

       The do()	method executes	a sql statement	from which no records are
       returned.  It will return true if the statement was successful

	   [% IF"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	[% %]

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

	   [% FOREACH uid = people.keys.sort;
		   person = people.$uid
	       * [% %] : [% %]
	   [% 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) %]

	   [% =	'not a number' %]

	   I am	[% %]  # 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.

	 [% DBI.dbh() %]

       Return the database handle currently in use by the plugin.

	 [% DBI.disconnect() %]

       Disconnects the current database.

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

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

	 [%'RowCacheSize',0)	%]

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

	 [% allhash = result.get_all() %]

       Returns all (remaining) rows as a hash.

	 [% allhash = result.get_all_list() %]

       Returns all (remaining) rows as a list.

       Returns the number of affected rows of the executed statement.

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

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

       Fetches an attribute from the statement handle.

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

       Stores an attribute to the statement handle.

	 [%'FetchHashKeyName','NAME_lc') %]

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

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

perl v5.32.1			  2010-07-26	      Template::Plugin::DBI(3)


Want to link to this manual page? Use this URL:

home | help