Skip to content


Subversion checkout URL

You can clone with
Download ZIP
Perl Shell
branch: master

Fetching latest commit…

Cannot retrieve the latest commit at this time

Failed to load latest commit information.


    SQL::Pico - Prebuilt Raw SQL Statement Builder

        use SQL::Pico ();
        $dbh    = DBI->connect(...);
        $sp     = SQL::Pico->new->dbh($dbh);
        $quoted = $sp->quote($val);             # $dbh->quote($val)
        $quoted = $sp->quote_identifier($key);  # $dbh->quote_identifier($key)

        $select = $sp->bind("SELECT * FROM ?? WHERE id = ?", $table, $id);
        $where  = join(" AND " => $sp->bind("?? = ?", %hash));
        $select = $sp->bind("SELECT * FROM mytbl WHERE ???", $where);

        $keys   = join(", " => $sp->quote_identifier(keys %hash));
        $vals   = join(", " => $sp->quote(values %hash));
        $insert = $sp->bind("INSERT INTO mytbl (???) VALUES (???)", $keys, $vals);

        $sets   = join(", " => $sp->bind("?? = ?", %hash));
        $update = $sp->bind("UPDATE mytbl SET ??? WHERE id = ?", $sets, $id);

        $in     = join(", " => $sp->quote(@list));
        $delete = $sp->bind("DELETE mytbl WHERE id IN (???)", $in);

    This provides a simple but safe way to build raw SQL statements without
    learning any other languages than Perl and SQL. "SQL::Pico" is
    lightweight and doesn't require any non-core modules except for DBI.

    "SQL::Pico"'s "bind()" method generates a SQL statement which
    placeholders are filled with immediate values bound. This makes you free
    from handling bind values and calling "prepare()". See "RECIPES" section

  Why "Prebuilt" SQL?
    Because SQL is the most simplest language to comunicate with RDBMS.

    The most of ORM modules and something *SQL::Builder*-ish modules would
    have required you to understand its complex class structure or dialectal

    This module simply provides just one of new method, "bind()", which
    allows you to build SQL statements using placeholders, as well as
    "quote()" and "quote_identifier()" methods which are wrappers for
    "DBI"'s methods with the same name you already know.

    The most of modern Web applications would not cache prepared $sth
    instances though they would cache a connected $dbh instance. It means
    you don't need to worry about its performance (dis)advantage when you
    don't use bind values at "execute()".

    This creates a "SQL::Pico" instance.

        $sp = SQL::Pico->new;

    This accepts key/value pair(s) as its initial parameters. Only "dbh"
    parameter is available at this module.

        $sp = SQL::Pico->new(dbh => $dbh);

    This is an accessor to specify a "DBI" instance.

        $sp = SQL::Pico->new;

    The setter returns the current "SQL::Pico" instance for you to chain
    method calls.

        $quoted = SQL::Pico->new->dbh($dbh)->quote($val);

    Please note that quoting formats for literals and identifiers depend on
    RDBMS server you connect. For example, a literal string "Don't" would be
    quoted as 'Don''t' in a server, as well as 'Don\'t', "Don't", etc. in

    This supports SQL-92 Standard's quoting format, per default, under
    DBD::NullP driver which is included in "DBI" distribution. You could
    specify a "DBI" instance to make string quoted properly for your RDBMS

    This calls "DBI"'s "quote()" method internally.

        $quoted = $sp->quote($val);             # $dbh->quote($val)

    This doesn't accept literal's data type specified at its second
    argument. The other difference to original is that this accept multiple
    arguments and returns them quoted.

        @list   = $sp->quote(@vals);            # multiple quotes at once

    This calls "DBI"'s "quote_identifier()" method internally.

        $quoted = $sp->quote_identifier($key);  # $dbh->quote_identifier($key)

    Multiple arguments are allowed as well.

        @list   = $sp->quote_identifier(@keys); # multiple quotes at once

  bind(SQL, VALUES...)
    This builds a SQL statement by using placeholders with bind values.

        $sql = $sp->bind("SELECT * FROM ?? WHERE id = ?", $table, $id);

    Note that this returns a SQL statement built with values bound at the
    method prior to "DBI"'s <execute()> method called.

    Three types of placeholders are allowed at the first argument:

    Single character of "?" represents a placeholder for a literal which
    will be escaped by "quote()".

    Double characters of "??" represents a placeholder for an identifier
    which will be escaped by "quote_identifier()".

    Triple characters of "???" represents a placeholder for a raw SQL string
    which will not be escaped.

        $hash   = {"category" => "foo", "price" => "100"};
        @list   = $sp->bind("?? = ?", %$hash);
        $where  = join(" AND ", @list);
        $select = "SELECT * FROM mytable WHERE $where";

        # SELECT * FROM mytable WHERE "category" = 'foo' AND "price" = '100'
        # Note that the order of key/value pairs vary.

    In list context, this returns a list of strings repeatedly built with
    parameters following.

    In addition to the OO style described above, this also supports the
    functional style below by exporting three shortcut functions: "v()",
    "k()" and "sql()" per default.

        use SQL::Pico;                            # functions exported

    This is a shortcut for "quote()" method which quotes a literal, e.g.
    string, number, etc.

        $quoted = v("string");                    # quotes literal
        @quoted = v("foo", "bar", "100");         # multiple literals

    This is a shortcut for "quote_identifier()" method which quotes an
    identifier, e.g. table name, column name, etc.

        $quoted = k("table_name");                # quotes identifier
        @quoted = k("category", "name", "price"); # multiple identifiers

  sql(SQL, VALUES...)
    This is a shortcut for "bind()" method which builds a SQL statement by
    using placeholders with bind values.

        $select = sql("SELECT * FROM ?? WHERE id = ?", $table, $id);

    Call "dbh()" class method to specify the database handle for those
    "v()", "k()" and "sql()" functions above.

        $dbh = DBI->connect(...);

    Note that "dbh()" method is not exported.

    The recipes show which "DBI" methods would follow SQL statements built
    by "sql()" function.

        # fetch a single record as a hahsref
        $sql   = sql("SELECT * FROM mytable WHERE id = ?", $id);
        $hash  = $dbh->selectrow_hashref($sql);
        # fetch multiple records as an arrayref of hashrefs
        $in    = join(", " => v(@list));
        $sql   = sql("SELECT * FROM mytable WHERE id IN (???)", $in);
        $array = $dbh->selectall_arrayref($sql, {Slice=>{}});
        # fetch a list of a single column as an arrayref
        $sql   = sql("SELECT id FROM mytable WHERE price < ?", $price);
        $array = $dbh->selectcol_arrayref($sql);
        # fetch a list of a pair column as an arrayref then a hashref
        $sql   = sql("SELECT id, name FROM mytable WHERE price < ?", $price);
        $array = $dbh->selectall_arrayref($sql);
        $hash  = +{ map { $_->[0] => $_->[1] } @$array };
        # check a record exists as a scalar
        $sql   = sql("SELECT count(*) FROM mytable WHERE id = ?", $id);
        $exist = $dbh->selectrow_array($sql);
        # count total number of records as a scalar
        $sql   = sql("SELECT count(*) FROM mytable WHERE price < ?", $price);
        $count = $dbh->selectrow_array($sql);
        # insert a record with a hashref
        $keys  = join(", " => k(keys %$hash));
        $vals  = join(", " => v(values %$hash));
        $sql   = sql("INSERT INTO mytable (???) VALUES (???)", $keys, $vals);
        $dbh->do($sql) or die "insert failed";
        # update a record with a hashref
        $sets  = join(", " => sql("?? = ?", %$hash));
        $sql   = sql("UPDATE mytable SET ??? WHERE id = ?", $sets, $id);
        $dbh->do($sql) or die "update failed";
        # delete a record
        $sql   = sql("DELETE mytable WHERE id = ?", $id);
        $dbh->do($sql) or die "delete failed";

    Yusuke Kawasaki

    The following copyright notice applies to all the files provided in this
    distribution, including binary files, unless explicitly noted otherwise.

    Copyright 2012 Yusuke Kawasaki

    This library is free software; you can redistribute it and/or modify it
    under the same terms as Perl itself.

    DBI executes SQL statements built by the module.

Something went wrong with that request. Please try again.