# NAME

# NAME

SQL::Bind - SQL flexible placeholders

# SYNOPSIS

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';

# DESCRIPTION

[SQL::Bind](https://metacpan.org/pod/SQL%3A%3ABind) 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=?, ...`.

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 `!`.

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']

# DEVELOPMENT

## Repository

http://github.com/vti/sql-bind

# CREDITS

# AUTHOR

Viacheslav Tykhanovskyi, `[email protected]`.

# COPYRIGHT AND LICENSE

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.