NAME
DBIx::Squirrel::db - DBIx::Squirrel's database handle class
DESCRIPTION
A database handle from "connect" in DBIx::Squirrel is blessed into this class. It's 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 Ekorn::Pipeline::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. Each is rewritten as ? before the DBI sees it:
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 is bound by name. Numbers start from 1. "bind" in DBIx::Squirrel::st says more.
A placeholder with ... before it takes a list. The list is written as a ? for each of its values, separated by commas:
SELECT * FROM artist WHERE id IN (...?)
SELECT * FROM artist WHERE id IN (...$1)
SELECT * FROM artist WHERE id IN (...:ids)
Its value is a reference to an array of at least one plain value. The number of values decides the statement the DBI prepares. So a statement taking a list is prepared with its values, by "do", "iterate", "results" or a select method. prepare and prepare_cached croak given one. A placeholder takes a list everywhere it's used, or nowhere. "Lists" in DBIx::Squirrel::st says more.
Placeholders are looked for only in the SQL itself. These are left unchanged:
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$);a cast, such as
x::int;an identifier containing
$, such asv$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. If it doesn't, it's read by the standard rules. "connect" in DBIx::Squirrel says more. 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, put a backslash before it, as DBD::Pg allows. PostgreSQL's ?, ?| and ?& operators are examples. The backslash and the character after it are passed on unchanged, 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. The statement is passed on as written, for DBD::Pg to read, so none can take a list.With
pg_placeholder_nocolons, the:1and:namestyles aren't placeholders, so array slices such asdata[: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;
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. The message is the one 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. Like prepare, it croaks given a statement taking a list.
The DBI finishes a cached handle that's still active before handing it out. If an iterator is part way through the handle, that would cut its results short. So such a handle isn't handed out or finished. A new handle is made instead, and cached in its place. The exception is $if_active of 2, which asks for the cached handle, unchanged. 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. So is a statement taking a list, prepared for the values given. 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 over its results, or, if the last stage is a sink, the sink's result. See "iterate" in DBIx::Squirrel::st. As with "do", the attributes may be left out. A statement taking a list is prepared for the values given.
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);
@rows = $dbh->selectall_array($statement, \%attr, @bind_values);
- selectrow_array
- selectrow_arrayref
- selectrow_hashref
- selectall_arrayref
- selectall_hashref
- selectcol_arrayref
- selectall_array
Each works as the DBI's does. It prepares the statement as "prepare" does, unless it's already a statement handle, and binds the values as "bind" in DBIx::Squirrel::st binds them. A statement taking a list is prepared for the values given. selectall_array gives the rows selectall_arrayref gives, as a list, as the DBI's does.
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.
Given stages after the bind values, each pushes what the DBI's method returns through them, as a pipeline called with values does, and returns what comes out of the last: every value; or, where one value is asked for, a sink's result, if a sink is last, and otherwise how many values came out. selectrow_array pushes the row's values, selectall_array each row, and the others their one reference.
my @names = $dbh->selectall_array('SELECT name FROM artist', pluck);
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 bind values with a statement taking a list (%s)
-
A statement with a list placeholder, such as
...:ids, was given to "prepare" or "prepare_cached". They have no values to prepare it for. Each list placeholder is named, and a?is named by its position, as in...? at 2. - Expected a placeholder to take a list everywhere or nowhere (%s)
-
A placeholder was used with
...in one place and without it in another, as inSELECT :a WHERE x IN (...:a). - Expected no list placeholder with pg_placeholder_dollaronly (%s)
-
With DBD::Pg's
pg_placeholder_dollaronly, the statement goes to DBD::Pg as written, so a$1placeholder can't take a list. - Expected a reference to a HASH or ARRAY
-
The first argument after the statement in "do", "iterate", "results" or a select method was an unblessed reference, but not to a hash or an array. An object there is a bind value, as it is to the DBI.
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.