NAME
DBIx::Squirrel::db - DBIx::Squirrel's database handle class
DESCRIPTION
A database handle from "connect" in DBIx::Squirrel is blessed into this class, a subclass of the DBI's DBI::db, so it does everything a DBI database handle does. It also inherits "pipe" in DBIx::Squirrel and "tap" in DBIx::Squirrel from DBIx::Squirrel::Pipes.
METHODS
prepare
$sth = $dbh->prepare($statement);
$sth = $dbh->prepare($statement, \%attr);
Prepares a statement, as the DBI's prepare does, and returns a DBIx::Squirrel::st. "do" and the DBI's select methods, such as selectrow_array, prepare a statement this way too.
The statement can use placeholders in any of these styles, which are rewritten as ? before the DBI sees them:
SELECT * FROM artist WHERE id = ?
SELECT * FROM artist WHERE id = ?1
SELECT * FROM artist WHERE id = :1
SELECT * FROM artist WHERE id = $1
SELECT * FROM artist WHERE id = :id
A statement uses one style of placeholder throughout; prepare croaks if it finds more than one. The same placeholder can appear more than once.
A numbered placeholder is bound once, wherever it appears, and a named one by name, as "bind" in DBIx::Squirrel::st describes. Numbers start from 1.
Placeholders are looked for only in the SQL itself. Anything that looks like one inside a quoted string or identifier ('10:30', "a:b", `c?`), a comment (-- :x, /* $1 */) or a PostgreSQL dollar-quoted body ($$ ... $$, $tag$ ... $tag$) is left as it is, and so is a cast, such as x::int, or an identifier containing $, such as v$session.
Strings are read by the rules of the database handle's driver. With DBD::mysql and DBD::MariaDB, a backslash escapes a character in a string ('it\'s'), "..." is a string, and # starts a comment. With other drivers, a backslash is an ordinary character in a string ('C:\'), except in PostgreSQL's E'...' strings. MySQL's NO_BACKSLASH_ESCAPES mode isn't noticed.
A database reached through another driver, such as DBD::ODBC or DBD::Proxy, is read by its own rules if it says what it is, as "connect" in DBIx::Squirrel describes, and by the standard rules if it doesn't. To read a statement as another dialect, name that dialect's driver in the ekorn_dialect attribute:
$dbh->prepare($statement, {ekorn_dialect => 'mysql'});
Attributes starting ekorn_ are DBIx::Squirrel's own, and aren't passed on to the DBI. Passed to "connect" in DBIx::Squirrel, they're the defaults for every statement the handle prepares; passed to prepare, they override those for one statement.
To use a ?, : or $ that isn't a placeholder, such as one of PostgreSQL's ?, ?| and ?& operators, put a backslash before it, as DBD::Pg allows. The backslash and the character after it are passed on as they are, and DBD::Pg removes the backslash:
$dbh->prepare(q{SELECT * FROM doc WHERE data \? 'key' AND id = :id});
DBD::Pg's own placeholder options are followed too, whether set on the database handle or passed to prepare. With pg_placeholder_dollaronly, only $1-style placeholders are recognised, and the statement is passed on as written, for DBD::Pg to read. With pg_placeholder_nocolons, the :1 and :name styles aren't placeholders, so array slices such as data[:2] are left alone.
The statement can be a string, an array reference of lines, which are joined with spaces, a code reference returning either, or a statement handle, whose statement is prepared again. Blank space around the statement is removed; comments are passed on to the database.
Unlike the DBI's prepare, it always croaks if the statement can't be prepared, whatever RaiseError is set to, with the message the DBI raises under RaiseError, such as DBD::SQLite::db prepare failed: near "SELEC": syntax error.
prepare_cached
$sth = $dbh->prepare_cached($statement);
$sth = $dbh->prepare_cached($statement, \%attr);
$sth = $dbh->prepare_cached($statement, \%attr, $if_active);
As "prepare", using the DBI's cache of statement handles. Its errors name prepare_cached, as the DBI's do.
A cached handle that an iterator is part way through isn't handed out, or finished, as the DBI would finish an active one, which would cut the iterator's results short. A new handle is made instead, and cached in its place, unless $if_active is 2, which asks for the cached handle as it is. Otherwise, $if_active means what it does to the DBI.
do
$rv = $dbh->do($statement);
$rv = $dbh->do($statement, \%attr, @bind_values);
$rv = $dbh->do($statement, undef, @bind_values);
$rv = $dbh->do($statement, @bind_values);
Runs a statement, and returns what the DBI's do returns: the number of rows affected, 0E0 for none, or undef on an error without RaiseError. Unlike the DBI's, the attributes may be left out: the first argument after the statement is taken as the attributes only if it's a hash reference or undef. An error names do, as the DBI's does: DBD::SQLite::db do failed: ....
A statement with ? placeholders, or none, goes to the driver's own do, as with plain DBI, so the driver's row counts and options apply, such as running several statements with DBD::SQLite's sqlite_allow_multiple_statements. A statement with numbered or named placeholders is prepared, as "prepare" does, and executed, with the values bound as "bind" in DBIx::Squirrel::st binds them; some drivers, such as DBD::SQLite, then count the rows of an UPDATE ... RETURNING only as they're fetched, giving 0E0.
Like the DBI's, do returns no rows, even from a SELECT or a RETURNING clause. Use "prepare" and execute, or the select methods, for those.
iterate
$it = $dbh->iterate($statement, \%attr, @bind_values, @stages);
$it = $dbh->iterate($statement, @bind_values, @stages);
Prepares a statement, as "prepare" does, and returns a DBIx::Squirrel::Iterator, an iterator over its results, as "iterate" in DBIx::Squirrel::st does. As with "do", the attributes may be left out.
results
$results = $dbh->results($statement, \%attr, @bind_values, @stages);
$results = $dbh->results($statement, @bind_values, @stages);
As "iterate", returning a DBIx::Squirrel::Results, whose rows are objects with a method for each column.
The select methods
@row_ary = $dbh->selectrow_array($statement, \%attr, @bind_values);
@row_ary = $dbh->selectrow_array($statement, @bind_values);
$ary_ref = $dbh->selectrow_arrayref($statement, \%attr, @bind_values);
$hash_ref = $dbh->selectrow_hashref($statement, \%attr, @bind_values);
$ary_ref = $dbh->selectall_arrayref($statement, \%attr, @bind_values);
$hash_ref = $dbh->selectall_hashref($statement, $key_field, \%attr,
@bind_values);
$ary_ref = $dbh->selectcol_arrayref($statement, \%attr, @bind_values);
- selectrow_array
- selectrow_arrayref
- selectrow_hashref
- selectall_arrayref
- selectall_hashref
- selectcol_arrayref
Each works as the DBI's, preparing the statement as "prepare" does, unless it's already a statement handle, and binding the values as "bind" in DBIx::Squirrel::st binds them. The DBI's selectall_array goes through selectall_arrayref, so it does too. As with "do", the attributes may be left out: the first argument after the statement, or after selectall_hashref's key field, is taken as the attributes only if it's a hash reference or undef. Unlike "prepare", they don't croak if the statement can't be prepared: without RaiseError, each returns what the DBI's returns, as do does.
DIAGNOSTICS
- Expected one placeholder style, not several (%s)
-
The statement used more than one style of placeholder, such as
?and:name. The styles found are listed, each as an example:?,?1,:1,$1,:nameor$name. - Expected placeholders numbered from 1 (%s)
-
The statement had a numbered placeholder such as
$0. - Expected a statement
-
The statement was blank, or was a reference other than those "prepare" takes.
- Expected a reference to a HASH or ARRAY
-
The first argument after the statement in "do", "iterate", "results" or a select method was a reference, but not to a hash or an array.
SEE ALSO
AUTHOR
Iain Campbell <cpanic@cpan.org>
LICENSE
Copyright (c) 2024-2026 by Iain Campbell.
This is free software; you can redistribute it and/or modify it under the same terms as the Perl 5 programming language system itself.