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 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, vti@cpan.org.

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.

cpanm SQL::Bind

perl -MCPAN -e shell
install SQL::Bind