NAME
DBIx::Squirrel::st - DBIx::Squirrel's statement handle class
DESCRIPTION
A statement handle prepared on a DBIx::Squirrel database handle is blessed into this class, a subclass of the DBI's DBI::st, so it does everything a DBI statement handle does. It also inherits "pipe" in DBIx::Squirrel and "tap" in DBIx::Squirrel from DBIx::Squirrel::Pipes.
Lists
A placeholder with ... before it, such as ...?, ...$1 or ...:ids, takes a list, as "prepare" in DBIx::Squirrel::db describes. The list is the value for its placeholder, a reference to an array of plain values, given as any other value is:
$dbh->selectall_arrayref('SELECT * FROM artist WHERE id IN (...:ids)',
ids => [1, 2, 3]);
$dbh->selectall_arrayref('SELECT * FROM artist WHERE name = ? OR id IN (...?)',
'Abba', [1, 2, 3]);
To a statement taking one value, a list, a single array reference is the list, not the values in it, so [1, 2, 3] is a list of three values, and [[1, 2, 3]] croaks, as a list holds plain values.
The statement is prepared for the values it's given, and its handle for lists of those lengths. Executed or bound again, its lists must each have as many values, or it croaks; an iterator's "reset" in DBIx::Squirrel::Iterator prepares the statement again, on a new handle, when a list has a different number. "bind_param" binds each value in a list to a position of its own.
METHODS
bind
$sth = $sth->bind(@bind_values);
$sth = $sth->bind(\@bind_values);
$sth = $sth->bind(%values);
$sth = $sth->bind(\%values);
Binds values to the statement's placeholders, as "execute" does, without executing it, and returns the statement handle, or nothing if the DBI couldn't bind a value. How the values are matched to placeholders depends on the statement's style of placeholder:
?-
Values are bound in order, as with the DBI.
?1,:1and$1-
The first value is bound to every use of placeholder 1, the second to every use of 2, and so on, wherever they are in the statement. There must be as many values as the highest number. In
SELECT $2 + $1, $1,(10, 20)givesSELECT 20 + 10, 10. :nameand$name-
Values are given by name, as pairs or in a hash reference, and each is bound to every use of its name. The names can be given with or without the sigil. There must be a value for each name, and no other:
$sth->bind(id => 1, name => 'AC/DC'); $sth->bind({':id' => 1, ':name' => 'AC/DC'});
A single array reference stands for the values in it, so $sth->bind([1, 2]) is $sth->bind(1, 2). There are two exceptions, each for a statement taking one value. To one whose value is a list, as described in "Lists", the array reference is the list. DBD::Pg binds an array reference as a PostgreSQL array, so, with DBD::Pg, an array reference given to a statement taking one value, such as SELECT * FROM artist WHERE id = ANY(?), is that value, as it would be with the DBI. Through another driver, such as DBD::ODBC, it's unwrapped as usual, as the driver can't bind arrays.
bind_param
$rv = $sth->bind_param($placeholder, $value);
$rv = $sth->bind_param($placeholder, $value, \%attr);
$rv = $sth->bind_param($placeholder, $value, $bind_type);
Binds a value, as the DBI's bind_param does, and returns what it returns. The placeholder can be given by number, for numbered placeholders, or by name, with or without its sigil, for named ones, and the value is bound to every use of it in the statement. A statement with named placeholders can also be bound by position, as with the DBI. The value for a list placeholder is its list, each value of which is bound to a position of its own, with the same \%attr or $bind_type.
execute
$rv = $sth->execute;
$rv = $sth->execute(@bind_values);
$rv = $sth->execute(\@bind_values);
$rv = $sth->execute(%values);
$rv = $sth->execute(\%values);
Executes the statement, as the DBI's execute does, and returns what it returns. Values given are bound first, as "bind" binds them.
A statement with numbered or named placeholders, executed without values, croaks unless each placeholder has a value bound already, by "bind", "bind_param" or an earlier execute, as the DBI keeps them. The DBI would leave it unbound, which most drivers read as NULL.
The DBI doesn't finish a statement still active from an earlier execute before executing it again, and nor does execute, unless the ekorn_finish_active attribute is true. It can be set for every statement of a database handle, passed to "connect" in DBIx::Squirrel, or for one statement, passed to "prepare" in DBIx::Squirrel::db.
iterate
$it = $sth->iterate(@bind_values, @stages);
Returns a DBIx::Squirrel::Iterator, an iterator over the statement's results, executed with @bind_values, in any form "execute" takes, and run through @stages, the code references at the end of the arguments. The statement is executed when the first result is asked for.
results
$results = $sth->results(@bind_values, @stages);
Returns a DBIx::Squirrel::Results, as "iterate" does, whose rows are objects with a method for each column.
DIAGNOSTICS
- Expected %d bind values, not %d
-
A statement with numbered placeholders was given more or fewer values than its highest number.
- Expected names and values in pairs
-
A statement with named placeholders was given an odd number of values.
- Expected a placeholder of the statement (%s)
-
A value was given for a placeholder the statement doesn't have.
- Expected a value for each placeholder (none for %s)
-
A statement with named placeholders wasn't given a value for each name, or a statement with numbered or named placeholders was executed without values when some had none bound.
- Expected a reference to an ARRAY for a list (%s)
-
The value given for a list placeholder, named with its
..., wasn't a list: it was a plain value,undef, or a reference to something other than an array. - Expected at least one value in a list (%s)
-
The list given for a list placeholder was empty.
IN ()isn't SQL, so an empty list is for the caller to deal with. - Expected plain values in a list (%s)
-
A list held a reference, such as an array reference inside the one given, as in
[[1, 2]]to a statement whose one value is a list. - Expected %d values in a list, as prepared, not %d (%s)
-
A statement handle prepared for a list of one length was executed or bound with a list of another. Execute the statement through DBIx::Squirrel::db's methods, or reset its iterator, to prepare it again.
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.