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. It's 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. See "prepare" in DBIx::Squirrel::db. 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]);

Given to a statement whose one value is a list, a single array reference is the list, not the values in it. So [1, 2, 3] is a list of three values. [[1, 2, 3]] croaks, as a list holds plain values.

The statement is prepared for the values it's given, so its handle is for lists of those lengths. Executed or bound again, each list must 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. Returns the statement handle, or nothing if the DBI couldn't bind a value. How values are matched to placeholders depends on the statement's style of placeholder:

?

Values are bound in order, as with the DBI.

?1, :1 and $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) gives SELECT 20 + 10, 10.

:name and $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:

  • If its 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. A numbered placeholder is given by number, and a named one by name, with or without its sigil. 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 in it 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 already has a value bound. The value can come from "bind", "bind_param" or an earlier execute, as the DBI keeps them. The DBI would leave the placeholder unbound, which most drivers read as NULL.

The DBI doesn't finish a statement still active from an earlier execute before executing it again. Nor does execute, unless the ekorn_finish_active attribute is true. Pass it to "connect" in DBIx::Squirrel to set it for every statement of a database handle, or to "prepare" in DBIx::Squirrel::db for one statement.

iterate

$it = $sth->iterate(@bind_values, @stages);

Returns a DBIx::Squirrel::Iterator over the statement's results. The statement is executed with @bind_values, in any form "execute" takes, when the first result is asked for. Each row is run through @stages, the code references or pipelines at the end of the arguments.

results

$results = $sth->results(@bind_values, @stages);

As "iterate", returning a DBIx::Squirrel::Results, 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 the caller must deal with an empty list.

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. To prepare it again, execute the statement through DBIx::Squirrel::db's methods, or reset its iterator.

SEE ALSO

DBIx::Squirrel, DBI

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.