Templa...in::DBI(3pm)

📦
version 2.65 · 2 versions
, in debian bookworm, debian trixie, ubuntu jammy, ubuntu noble

💖 Template::Plugin::DBI 💖

okay so bestie, this is Template::Plugin::DBI(3pm) — straight out of the User Contributed Perl Documentation — and it is literally the glow-up that gives your templates full SQL powers. no cap. 🎀✨

🌈 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 💅

So this Template Toolkit plugin is the interface between your templates and the Perl DBI/DBD modules — which means you can yeet actual SQL queries straight into your template documents and it just works. iconic. 🫶 It also hooks you up via the Tie::DBI module (if it's installed on your system, iykyk) so you can poke at database records without having to embed any SQL in your templates at all. no cap.

You can spin up a DBI plugin object like this:

[% USE DBI %]

That gives you an uninitialised DBI object. then you just open a connection to a database using the connect() method, slay:

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

Want it done immediately? you can open the DBI connection right when the plugin gets created, by passing arguments to the constructor from the USE directive:

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

You can also use named parameters to hand over the data source connection string, user name and password. main character energy:

[% 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. And bestie, 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 too. Heads up tho: the 'PrintError' attribute defaults to 0 unless you explicitly set it true. ⚠️

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

An alternate variable name can be provided for the plugin, exactly like 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 that's more your vibe:

[% 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 honestly this generally shouldn't be necessary, because it gets called automatically when the plugin goes out of scope. 💀 You can call connect() at any time to open a connection to another database, and the previous connection will be closed automatically. so chill.

Internally, the DBI connect_cached() method is used instead of the connect() method. Why? because it allows for connection caching in a server environment, like 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). 😭 don't get caught lacking on this one.

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

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

…and bestie, that's just part 1. there's more coming, don't go anywhere 💜

✨ OBJECT METHODS ✨

💖 connect($database, $username, $password)

Okay bestie, this one opens the door to your database. It works with both positional args and named params, so you can flex however you like. Like, either:

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

or the named-param glow-up:

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

connect lets you hook up to a data source explicitly. And plot twist — you can also use it to reconnect an object that already exists over to a different data source. Glow-up era fr fr. 💅

And if you already have a database handle just sitting there? Don't be wasteful, bestie — tell the plugin to reuse it by passing it as the 'dbh' parameter:

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

🌸 query($sql,[%args],[@bind_values])

This is the one that sends an SQL query off to the database and hands you back an iterator object to carry the results. You can drop it straight into a FOREACH directive like this:

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

And here's the cute part — data gets fetched one row at a time from the result set as needed, for memory efficiency. Little sips, not the whole bottle. 🍓

🎀 prepare($sql,[%args])

Prepares a query for later. This hands back a compiled query object (of the Template::Plugin::DBI::Query class) and you can call execute() on it whenever you're ready. Planning ahead? Iconic.

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

✨ execute(@bind_values)

Runs a query you prepared earlier. Call this on the query object that prepare() gave you. Returns an iterator object that slots right into 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 %]

Look at that — one prepared query, reused, two different managers, zero drama. Periodt.

💜 do($sql,[%attr,[@bind_values]])

do() executes a sql statement that doesn't return any records. It'll come back true if the statement went through okay.

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

Sam is gone. Sad for Sam, great for your database. 😭

🦋 tie($table, $key, %args)

Returns a reference to a hash array tied to a table in the database, implemented with the Tie::DBI module. Hand it the table name and the key field as arguments:

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

Or, if that's more your vibe, use the 'table' and 'key' named parameters:

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

In this example, Tie::DBI converts accesses into the 'people' hash into SQL queries shaped like this:

SELECT * FROM users WHERE uid=?

For example:

[% me = people.abw %]

And the record that comes back can be poked at just like a normal hash:

I am [% me.name %]

You can also do stuff like this to loop through every record in a table:

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

Now — with the 'clobber' (or 'CLOBBER') option set, you can update a record and have those changes automatically permeate back into the database. That's main character energy right there. 👑

[% 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 add brand new records too:

[% people.newguy = {
       name = 'Nobby Newguy'
       ...other fields...
   }
%]
Want more on the 'CLOBBER' option? Go read Tie::DBI, bestie. It's all in there. 🫶

💖 quote($value, $type)

Calls the quote() method on the underlying DBI handle to quote the value you specified, in whatever manner is appropriate for its type. Safety first, always. ✨

🌈 dbh

[% DBI.dbh() %]

Returns the database handle the plugin is currently using. That's your girl right there.

🌸 disconnect

[% DBI.disconnect() %]

Disconnects the current database. Say bye bestie, you're done here. 💅

🎀 fetch

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

Fetches an attribute from the connected database handle. And heads up — it throws an exception when you're not connected. So connect first, okay? 🙈

⭐ store

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

Stores an attribute in the connected database handle. Same deal: throws an exception when not connected. No handle, no service.

🦋 ITERATOR METHODS 🦋

💜 get_all

[% allhash = result.get_all() %]

Returns all the (remaining) rows as a hash. All of them. The whole squad. ✨

🌸 get_all_list

[% allhash = result.get_all_list() %]

Returns all the (remaining) rows as a list. Same energy, list-shaped. 🎀

🔥 rows

Returns the number of affected rows from the executed statement. Receipts! 💯

🧚 get_colnames

Returns the lowercase column names (that's the $sth attribute "->{NAME_lc}"). Lowercase and unbothered.

👑 get_COLnames

Returns the uppercase column names (that's the $sth attribute "->{NAME_uc}"). UPPERCASE AND SCREAMING, respectfully. 📣

🌈 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 was distributed as a separate module back in the day. Then it got integrated into the Template Toolkit distribution for version 2.00, and it includes contributions from Andy Wardley, Craig Barratt, Dave Hodgkinson and Rafael Kitover. Andy Wardley extracted it back out into a separate distribution in May 2006. And after that, in 2010, Jens Rehsack maintains this distribution. A whole lineage of legends fr fr. 🫶

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. Share the love, it's the law (well, the license). 💅

🎀 SEE ALSO

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

And that's a wrap on this page, bestie! 🦋 This beauty ships with perl v5.32.0 and is dated 2021-01-01. Go forth and query responsibly. ✨💖

📦 where this page comes from