SQL::Bind - SQL flexible placeholders
use SQL::Bind qw(sql); # Scalars my ($sql, @bind) = sql 'SELECT foo FROM bar WHERE id=:id AND status=:status', id => 1, status => 'active'; # Arrays my ($sql, @bind) = sql 'SELECT foo FROM bar WHERE id IN (:id)', id => [1, 2, 3]; # Hashes my ($sql, @bind) = sql 'UPDATE bar SET :columns', columns => {foo => 'bar'}; # Raw values (!) my ($sql, @bind) = sql 'INSERT INTO bar (:keys!) VALUES (:values)', keys => [qw/foo/], values => [qw/bar/]; # Recursive binding (*) my ($sql, @bind) = sql 'SELECT foo FROM bar WHERE :recursive_query*', recursive_query => 'name = :name', name => 'hello';
SQL::Bind simplifies SQL queries maintenance by introducing placeholders. The behavior of the replacement depends on the type of the value. Scalars, Arrays and Hashes are supported.
Configuration
$PlaceholderPrefix
Placeholder prefix (: by default) can be changed by setting the $PlaceholderPrefix global variable:
:
local $SQL::Bind::PlaceholderPrefix = '@'; my ($sql, @bind) = sql 'SELECT foo FROM bar WHERE id=@id', id => 1;
Placeholders
A placeholders is an alphanumeric sequence that is prefixed with : (by default) and can end with ! for raw values or * for recursive binding. Some examples:
!
*
:name :status :CamelCase :Value_123 :ThisWillBeInsertedAsIs! :recursive*
Scalar values
Every value is replaced with a ?.
?
my ($sql, @bind) = sql 'SELECT foo FROM bar WHERE id=:id AND status=:status', id => 1, status => 'active'; # SELECT foo FROM bar WHERE id=? AND status=? # [1, 'active']
Array values
Arrays are replaced with a sequence of ?, ?, ....
?, ?, ...
my ($sql, @bind) = sql 'SELECT foo FROM bar WHERE id IN (:id)', id => [1, 2, 3]; # SELECT foo FROM bar WHERE id IN (?, ?, ?) # [1, 2, 3]
Hash values
Hahes are replaced with a sequence of key1=?, key2=?, ....
key1=?, key2=?, ...
my ($sql, @bind) = sql 'UPDATE bar SET :columns', columns => {foo => 'bar'}; # UPDATE bar SET foo=? # ['bar']
Raw values
Sometimes raw values are needed be it another identifier, or a list of columns (e.g. INSERT, UPDATE). For this case a placeholder should be suffixed with a !.
INSERT, UPDATE
my ($sql, @bind) = sql 'INSERT INTO bar (:keys!) VALUES (:values)', keys => [qw/foo/], values => [qw/bar/]; # INSERT INTO bar (foo) VALUES (?) # ['bar']
Recursive binding
Recursive binding allows you to recursively parse already replaced values. This helps building complex subqueries.
my ($sql, @bind) = sql 'SELECT foo FROM bar WHERE :recursive_query*', recursive_query => 'name = :name', name => 'hello'; # 'SELECT foo FROM bar WHERE name = ? # ['hello']
http://github.com/vti/sql-bind
Viacheslav Tykhanovskyi, vti@cpan.org.
vti@cpan.org
Copyright (C) 2020, Viacheslav Tykhanovskyi
This program is free software, you can redistribute it and/or modify it under the terms of the Artistic License version 2.0.
To install SQL::Bind, copy and paste the appropriate command in to your terminal.
cpanm
cpanm SQL::Bind
CPAN shell
perl -MCPAN -e shell install SQL::Bind
For more information on module installation, please visit the detailed CPAN module installation guide.