NAME
DBIx::Squirrel::Placeholders - find the placeholders in a statement, and match values to them
SYNOPSIS
use DBIx::Squirrel::Placeholders qw(study_sql trim_sql);
my($positions, $normalised, $trimmed)
= study_sql('SELECT * FROM artist WHERE id = :id');
# ({1 => ':id'}, 'SELECT * FROM artist WHERE id = ?', ...)
my($positions, $normalised, $trimmed, $lists, $pieces)
= study_sql('SELECT * FROM artist WHERE id IN (...:ids)');
# (..., {1 => 1}, ['SELECT * FROM artist WHERE id IN (', ')'])
DESCRIPTION
The functions DBIx::Squirrel::db and DBIx::Squirrel::st use to find the placeholders in a statement, rewrite them as ? for the DBI, and match the values given to them. They're for DBIx::Squirrel itself. None is exported by default.
Several take a statement's map of its placeholders' numbers or names to their positions, from 1, such as {id => [1, 3], name => [2]}. It's called $binds below. Others take what a statement handle keeps about its statement, called $state, which holds that map as Binds.
FUNCTIONS
study_sql
($positions, $normalised, $trimmed) = study_sql($statement);
($positions, $normalised, $trimmed) = study_sql($statement, \%options);
($positions, $normalised, $trimmed, $lists, $pieces) = study_sql($statement);
Returns three values:
a hash reference mapping each placeholder's position, from 1, to the placeholder as written (
?,?1,:1,$1,:nameor$name);the statement with each placeholder rewritten as
?;the statement as "trim_sql" returns it.
It returns nothing if the statement is blank.
A placeholder with ... before it, such as ...:ids, takes a list. The ... isn't part of the placeholder as mapped. A statement with any such placeholders returns two more values. The first is a hash reference whose keys are the positions taking a list. The second is an array reference of the pieces of the statement around its placeholders, one more than there are placeholders, for "expand_lists".
A statement must use one style of placeholder throughout: ?, ?1, :1, $1, :name or $name, with ... or without it. It croaks if the statement uses more than one.
Placeholders are looked for only in the SQL itself. They aren't looked for in quoted strings or identifiers, comments or PostgreSQL's dollar-quoted bodies. Nor are casts, such as x::int, or identifiers containing $, such as v$session, placeholders. A ?, : or $ with a backslash before it isn't a placeholder either. Both are left unchanged, for DBD::Pg, which removes the backslash.
%options can hold:
- ekorn_dialect
-
The SQL dialect the statement is written in, named as its DBI driver is, as in
$dbh->{Driver}{Name}. FormysqlandMariaDB, a backslash escapes a character in a string,"..."is a string, and#starts a comment. For any other dialect, or none, a backslash is an ordinary character in a string, except in PostgreSQL'sE'...'strings. - pg_placeholder_dollaronly
-
With
ekorn_dialectPg, only$1-style placeholders are found. The statement is returned unchanged, for DBD::Pg to read. It croaks if one of them takes a list, as the statement isn't rewritten. - pg_placeholder_nocolons
-
With
ekorn_dialectPg, the:1and:namestyles aren't placeholders.
Anything else in %options is ignored, so the attributes passed to prepare can be given unchanged.
trim_sql
$trimmed = trim_sql($statement);
Returns the statement without the blank space around it, or an empty string if it was all blank or undef. Comments are kept.
key_of
$key = key_of($placeholder);
$key = key_of('?', $position);
Returns a placeholder's number or name, as a statement's map of its placeholders keys it: without its sigil, and a number as a number. So :id and id give id, and $02 gives 2. A bare ? is keyed by its position, given with it.
is_numbered
$bool = is_numbered($binds);
Returns true if a statement's placeholders are numbered, not named.
values_taken
$count = values_taken($binds);
Returns how many values a statement takes in order. That's its highest number, or 0 if its placeholders are named.
values_for
%values = values_for($binds, @bind_values);
Returns each of a statement's placeholders' numbers or names, with the value given for it. Values for numbered placeholders are given in order. Values for named ones are given by name, as pairs or in a hash reference. It croaks unless there's a value for each, and no other. See "bind" in DBIx::Squirrel::st.
expand_lists
($statement, $binds, $lengths) = expand_lists($state, \%values);
Returns a statement with list placeholders, with each list's ? written once for each of its values. It's given two things:
what a statement handle keeps about the statement: its
Placeholders, thePieces"study_sql" returns, andLists, a map of each list placeholder's number or name to the placeholder as written, for messages;the values for its placeholders, as "values_for" returns them.
It also returns the map of each number or name to its positions in the statement returned, and the number of values in each list.
It croaks unless each list is a reference to an ARRAY of at least one plain value. A list can't be undef or a value on its own, nor hold an unblessed reference.
place_values
%placed = place_values($state, %values);
Returns each position of a statement's placeholders, from 1, with the value for it. It's given what the statement handle keeps about the statement, and the values for its placeholders' numbers or names. A list placeholder's positions take the values in its list in turn, wherever it's used. The list must have as many values as the statement was prepared for, in Lengths.
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.