Quick Navigator

Search Site

Unix VPS
A - Starter
B - Basic
C - Preferred
D - Commercial
MPS - Dedicated
Previous VPSs
* Sign Up! *

Contact Us
Online Help
Domain Status
Man Pages

Virtual Servers

Topology Map

Server Agreement
Year 2038

USA Flag



Man Pages

Manual Reference Pages  -  SQL::ABSTRACT::MORE (3)

.ds Aq ’


SQL::Abstract::More - extension of SQL::Abstract with more constructs and more flexible API



Generates SQL from Perl datastructures. This is a subclass of SQL::Abstract, fully compatible with the parent class, but with some additions :
o additional SQL constructs like -union, -group_by, join, etc.
o methods take arguments as named parameters instead of positional parameters, so that various SQL fragments are more easily identified
o values passed to select, insert or update can directly incorporate information about datatypes, in the form of arrayrefs of shape [{dbd_attrs => \%type}, $value]
This module was designed for the specific needs of DBIx::DataModel, but is published as a standalone distribution, because it may possibly be useful for other needs.


  my $sqla = SQL::Abstract::More->new();
  my ($sql, @bind);

  # ex1: named parameters, select DISTINCT, ORDER BY, LIMIT/OFFSET
  ($sql, @bind) = $sqla->select(
   -columns  => [-distinct => qw/col1 col2/],
   -from     => Foo,
   -where    => {bar => {">" => 123}},
   -order_by => [qw/col1 -col2 +col3/],  # BY col1, col2 DESC, col3 ASC
   -limit    => 100,
   -offset   => 300,

  # ex2: column aliasing, join
  ($sql, @bind) = $sqla->select(
    -columns => [         qw/Foo.col_A|a           Bar.col_B|b /],
    -from    => [-join => qw/Foo           fk=pk   Bar         /],

  # ex3: INTERSECT (or similar syntax for UNION)
  ($sql, @bind) = $sqla->select(
    -columns => [qw/col1 col2/],
    -from    => Foo,
    -where   => {col1 => 123},
    -intersect => [ -columns => [qw/col3 col4/],
                    -from    => Bar,
                    -where   => {col3 => 456},

  # ex4: passing datatype specifications
  ($sql, @bind) = $sqla->select(
   -from     => Foo,
   -where    => {bar => [{dbd_attrs => {ora_type => ORA_XMLTYPE}}, $xml]},
  my $sth = $dbh->prepare($sql);
  $sqla->bind_params($sth, @bind);

  # merging several criteria
  my $merged = $sqla->merge_conditions($cond_A, $cond_B, ...);
  ($sql, @bind) = $sqla->select(..., -where => $merged, ..);

  # insert / update / delete
  ($sql, @bind) = $sqla->insert(
    -into   => $table,
    -values => {col => $val, ...},
  ($sql, @bind) = $sqla->update(
    -table => $table,
    -set   => {col => $val, ...},
    -where => \%conditions,
  ($sql, @bind) = $sqla->delete (
    -from  => $table
    -where => \%conditions,



  my $sqla = SQL::Abstract::More->new(%options);

where %options may contain any of the options for the parent class (see new in SQL::Abstract), plus the following :
table_alias A sprintf format description for generating table aliasing clauses. The default is %s AS %s. Can also be supplied as a method coderef (see Overriding methods).
column_alias A sprintf format description for generating column aliasing clauses. The default is %s AS %s. Can also be supplied as a method coderef.
limit_offset Name of a limit-offset dialect, which can be one of LimitOffset, LimitXY, LimitYX or RowNum; see SQL::Abstract::Limit for an explation of those dialects. Here, unlike the SQL::Abstract::Limit implementation, limit and offset values are treated as regular values, with placeholders ’?’ in the SQL; values are postponed to the @bind list.

The argument can also be a coderef (see below Overriding methods). That coderef takes $self, $limit, $offset as arguments, and should return ($sql, @bind). If $sql contains %s, it is treated as a sprintf format string, where the original SQL is injected into %s.

join_syntax A hashref where keys are abreviations for join operators to be used in the join method, and values are associated SQL clauses with placeholders in sprintf format. The default is described below under the join method.
join_assoc_right A boolean telling if multiple joins should be associative on the right or on the left. Default is false (i.e. left-associative).
max_members_IN An integer specifying the maximum number of members in a IN clause. If the number of given members is greater than this maximum, SQL::Abstract::More will automatically split it into separate clauses connected by ’OR’ (or connected by ’ANDif used with the -not_in operator).

  my $sqla = SQL::Abstract::More->new(max_members_IN => 3);
  ($sql, @bind) = $sqla->select(
   -from     => Foo,
   -where    => {foo => {-in     => [1 .. 5]}},
                 bar => {-not_in => [6 .. 10]}},
  # .. WHERE (     (foo IN (?,?,?) OR foo IN (?, ?))
  #            AND (bar NOT IN (?,?,?) AND bar NOT IN (?, ?)) )

sql_dialect This is actually a meta-argument : it injects a collection of regular arguments, tuned for a specific SQL dialect. Dialects implemented so far are :
MsAccess For Microsoft Access. Overrides the join syntax to be right-associative.
BasisJDBC For Livelink Collection Server (formerly Basis), accessed through a JDBC driver. Overrides the column_alias syntax. Sets max_members_IN to 255.
MySQL_old For old versions of MySQL. Overrides the limit_offset syntax. Recent versions of MySQL do not need that because they now implement the regular LIMIT ? OFFSET ? ANSI syntax.
Oracle For Oracle. Overrides the limit_offset to use the RowNum dialect (beware, this injects an additional column rownum__index into your resultset). Also sets max_members_IN to 999.
Overriding methods

Several arguments to new() can be references to method implementations instead of plain scalars : this allows you to completely redefine a behaviour without the need to subclass. Just supply a regular method body as a code reference : for example, if you need another implementation for LIMIT-OFFSET, you could write

  my $sqla = SQL::Abstract::More->new(
    limit_offset => sub {
      my ($self, $limit, $offset) = @_;
      defined $limit or die "NO LIMIT!"; #:-)
      $offset ||= 0;
      my $last = $offset + $limit;
      return ("ROWS ? TO ?", $offset, $last); # ($sql, @bind)



  # positional parameters, directly passed to the parent class
  ($sql, @bind) = $sqla->select($table, $columns, $where, $order);

  # named parameters, handled in this class
  ($sql, @bind) = $sqla->select(
    -columns  => \@columns,
      # OR: -columns => [-distinct => @columns],
    -from     => $table || \@joined_tables,
    -where    => \%where,
    -union    => [ %select_subargs ], # OR -intersect, -minus, etc
    -order_by => \@order,
    -group_by => \@group_by,
    -having   => \%having_criteria,
    -limit => $limit, -offset => $offset,
      # OR: -page_size => $size, -page_index => $index,
    -for      => $purpose,

  my $details = $sqla->select(..., want_details => 1);
  # keys in %$details: sql, bind, aliased_tables, aliased_columns

If called with positional parameters, as in SQL::Abstract, select() just forwards the call to the parent class. Otherwise, if called with named parameters, as in the example above, some additional SQL processing is performed.

The following named arguments can be specified :
-columns => \@columns \@columns is a reference to an array of SQL column specifications (i.e. column names, * or table.*, functions, etc.).

A ’|’ in a column is translated into a column aliasing clause: this is convenient when using perl qw/.../ operator for columns, as in

  -columns => [ qw/table1.longColumn|t1lc table2.longColumn|t2lc/ ]

SQL column aliasing is then generated through the column_alias method.

Initial items in @columns that start with a minus sign are shifted from the array, i.e. they are not considered as column names, but are re-injected later into the SQL (without the minus sign), just after the SELECT keyword. This is especially useful for

  $sqla->select(..., -columns => [-DISTINCT => @columns], ...);

However, it may also be useful for other purposes, like vendor-specific SQL variants :

   # MySQL features
  ->select(..., -columns => [-STRAIGHT_JOIN    => @columns], ...);
  ->select(..., -columns => [-SQL_SMALL_RESULT => @columns], ...);

   # Oracle hint
  ->select(..., -columns => ["-/*+ FIRST_ROWS (100) */" => @columns], ...);

The argument to -columns can also be a string instead of an arrayref, like for example "c1 AS foobar, MAX(c2) AS m_c2, COUNT(c3) AS n_c3"; however this is mainly for backwards compatibility. The recommended way is to use the arrayref notation as explained above :

  -columns => [ qw/  c1|foobar   MAX(c2)|m_c2   COUNT(c3)|n_c3  / ]

If omitted, -columns takes ’*’ as default argument.

-from => $table || \@joined_tables
-where => $criteria Like in SQL::Abstract, $criteria can be a plain SQL string like "col1 IN (3, 5, 7, 11) OR col2 IS NOT NULL"; but in most cases, it will rather be a reference to a hash or array of conditions that will be translated into SQL clauses, like for example {col1 => val1, col2 => val2}. The structure of that hash or array can be nested to express complex boolean combinations of criteria; see WHERE CLAUSES in SQL::Abstract for a detailed description.

When using hashrefs or arrayrefs, leaf values can be bind values with types; see the BIND VALUES WITH TYPES section below.

-union => [ %select_subargs ]
-union_all => [ %select_subargs ]
-intersect => [ %select_subargs ]
-except => [ %select_subargs ]
-minus => [ %select_subargs ] generates a compound query using set operators such as UNION, INTERSECT, etc. The argument %select_subargs contains a nested set of parameters like for the main select (i.e. -columns, -from, -where, etc.); however, arguments -columns and -from can be omitted, in which case they will be copied from the main select(). Several levels of set operators can be nested.
-group_by => ‘‘string’’ or -group_by => \@array adds a GROUP BY clause in the SQL statement. Grouping columns are specified either by a plain string or by an array of strings.
-having => ‘‘string’’ or -having => \%criteria adds a HAVING clause in the SQL statement (only makes sense together with a GROUP BY clause). This is like a -where clause, except that the criteria are applied after grouping has occured.
-order_by => \@order \@order is a reference to a list of columns for sorting. Columns can be prefixed by ’+’ or ’-’ for indicating sorting directions, so for example -orderBy => [qw/-col1 +col2 -col3/] will generate the SQL clause ORDER BY col1 DESC, col2 ASC, col3 DESC.

Column names asc and desc are treated as exceptions to this rule, in order to preserve compatibility with SQL::Abstract. So -orderBy => [-desc => colA] yields ORDER BY colA DESC and not ORDER BY desc DEC, colA. Any other syntax supported by SQL::Abstract is also supported here; see ORDER BY CLAUSES in SQL::Abstract for examples.

The whole -order_by parameter can also be a plain SQL string like "col1 DESC, col3, col2 DESC".

-page_size => $page_size specifies how many rows will be retrieved per page of data. Default is unlimited (or more precisely the maximum value of a short integer on your system). When specified, this parameter automatically implies -limit.
-page_index => $page_index specifies the page number (starting at 1). Default is 1. When specified, this parameter automatically implies -offset.
-limit => $limit limit to the number of rows that will be retrieved. Automatically implied by -page_size.
-offset => $offset Automatically implied by -page_index. Defaults to 0.
-for => $clause specifies an additional clause to be added at the end of the SQL statement, like -for => READ ONLY or -for => UPDATE.
-want_details => 1 If true, the return value will be a hashref instead of the usual ($sql, @bind). The hashref contains the following keys :
sql generated SQL
bind bind values
aliased_tables a hashref of {table_alias => table_name} encountered while parsing the -from parameter.
aliased_columns a hashref of {column_alias => column_name} encountered while parsing the -columns parameter.


  # positional parameters, directly passed to the parent class
  ($sql, @bind) = $sqla->insert($table, \@values || \%fieldvals, \%options);

  # named parameters, handled in this class
  ($sql, @bind) = $sqla->insert(
    -into      => $table,
    -values    => {col => $val, ...},
    -returning => $return_structure,

Like for select, values assigned to columns can have associated SQL types; see BIND VALUES WITH TYPES.

Named parameters to the insert() method are just syntactic sugar for better readability of the client’s code. Parameters -into and -values are passed verbatim to the parent method. Parameter -returning is optional and only supported by some database vendors (see insert in SQL::Abstract); if the $return_structure is
o a scalar or an arrayref, it is passed directly to the parent method
o a hashref, it is interpreted as a SQL clause RETURNING .. INTO .., as required in particular by Oracle. Hash keys are field names, and hash values are references to variables that will receive the results. Then it is the client code’s responsability to use bind_param_inout in DBD::Oracle for binding the variables and retrieving the results, but the bind_params method in the present module is there for help. Example:

  ($sql, @bind) = $sqla->insert(
    -into      => $table,
    -values    => {col => $val, ...},
    -returning => {key_col => \my $generated_key},

  my $sth = $dbh->prepare($sql);
  $sqla->bind_params($sth, @bind);
  print "The new key is $generated_key";


  # positional parameters, directly passed to the parent class
  ($sql, @bind) = $sqla->update($table, \%fieldvals, \%where);

  # named parameters, handled in this class
  ($sql, @bind) = $sqla->update(
    -table => $table,
    -set   => {col => $val, ...},
    -where => \%conditions,

This works in the same spirit as the insert method above. Named parameters to the update() method are just syntactic sugar for better readability of the client’s code; they are passed verbatim to the parent method.


  # positional parameters, directly passed to the parent class
  ($sql, @bind) = $sqla->delete($table, \%where);

  # named parameters, handled in this class
  ($sql, @bind) = $sqla->delete (
    -from  => $table
    -where => \%conditions,

Named parameters to the delete() method are just syntactic sugar for better readability of the client’s code; they are passed verbatim to the parent method.


  my $sql = $sqla->table_alias($table_name, $alias);

Returns the SQL fragment for aliasing a table. If $alias is empty, just returns $table_name.


Like table_alias, but for column aliasing.


  ($sql, @bind) = $sqla->limit_offset($limit, $offset);

Generates ($sql, @bind) for a LIMIT-OFFSET clause.


  ($sql, @bind) = $sqla->join(
    <table0> <join_1> <table_1> ... <join_n> <table_n>

Generates ($sql, @bind) for a JOIN clause, taking as input a collection of joined tables with their join conditions. The following example gives an idea of the available syntax :

  ($sql, @bind) = $sqla->join(qw[
     Table1|t1       ab=cd         Table2|t2
                 <=>{ef>gh,ij<kl}  Table3
                  =>{}     Table4

This will generate

  Table1 AS t1 INNER JOIN Table2 AS t2 ON
               INNER JOIN Table3       ON t2.ef>
                                      AND t2.ij<Table3.kl
                LEFT JOIN Table4       ON

More precisely, the arguments to join() should be a list containing an odd number of elements, where the odd positions are table specifications and the even positions are join specifications.

Table specifications

A table specification for join is a string containing the table name, possibly followed by a vertical bar and an alias name. For example Table1 or Table1|t1 are valid table specifications.

These are converted into internal hashrefs with keys sql, bind, name, aliased_tables, like this :

    sql            => "Table1 AS t1"
    bind           => [],
    name           => "t1"
    aliased_tables => {"t1" => "Table1"}

Such hashrefs can be passed directly as arguments, instead of the simple string representation.

Join specifications

A join specification is a string containing an optional join operator, possibly followed by a pair of curly braces or square brackets containing the join conditions.

Default builtin join operators are <=>, =>, <=, ==, corresponding to the following SQL JOIN clauses :

  <=> => %s INNER JOIN %s ON %s,
   => => %s LEFT OUTER JOIN %s ON %s,
  <=  => %s RIGHT JOIN %s ON %s,
  ==  => %s NATURAL JOIN %s,

This operator table can be overridden through the join_syntax parameter of the new method.

The join conditions is a comma-separated list of binary column comparisons, like for example


Table names may be explicitly given using dot notation, or may be implicit, in which case they will be filled automatically from the names of operands on the left-hand side and right-hand side of the join.

In accordance with SQL::Abstract common conventions, if the list of comparisons is within curly braces, it will become an AND; if it is within square brackets, it will become an OR.

Join specifications expressed as strings are converted into internal hashrefs with keys operator and condition, like this :

    operator  => <=>,
    condition => { %1$s.ab => {= => {-ident => %2$}},
                   %1$s.ef => {= => {-ident =>}}},

The operator is a key into the join_syntax table; the associated value is a sprinf format string, with placeholders for the left and right operands, and the join condition. The condition is a structure suitable for being passed as argument to where in SQL::Abstract. Places where the names of left/right tables (or their aliases) are expected should be expressed as sprintf placeholders, i.e. respectively %1$s and %2$s. In most cases the right-hand side of the condition should <B>notB> belong to the @bind list, so this is why we need to use the -ident operator from SQL::Abstract.

Hashrefs for join specifications as shown above can be passed directly as arguments, instead of the simple string representation.


  my $conditions = $sqla->merge_conditions($cond_A, $cond_B, ...);

This utility method takes a list of "where" conditions and merges all of them in a single hashref. For example merging

  ( {a => 12, b => {">" => 34}},
    {b => {"<" => 56}, c => 78} )


  {a => 12, b => [-and => {">" => 34}, {"<" => 56}], c => 78});


  $sqla->bind_params($sth, @bind);

For each $value in @bind:
o if the value is a scalarref, call

  $sth->bind_param_inout($index, $value, $INOUT_MAX_LEN)

(see bind_param_inout in DBI). $INOUT_MAX_LEN defaults to 99, which should be good enough for most uses; should you need another value, you can change it by setting

  local $SQL::Abstract::More::INOUT_MAX_LEN = $other_value;

o if the value is an arrayref that matches is_bind_value_with_type, then call the method and arguments returned by is_bind_value_with_type.
o for all other cases, call

  $sth->bind_param($index, $value);

This method is useful either as a convenience for Oracle statements of shape "INSERT ... RETURNING ... INTO ..." (see insert method above), or as a way to indicate specific datatypes to the database driver.

==head2 is_bind_value_with_type

  my ($method, @args) = $sqla->is_bind_value_with_type($value);

If $value is a ref to a pair [\%args, $orig_value] :
o if %args is of shape {dbd_attrs => \%sql_type}, then return (bind_param, $orig_value, \%sql_type).
o if %args is of shape {sqlt_size => $num}, then return (bind_param_inout, $orig_value, $num).
Otherwise, return ().


At places where SQL::Abstract would expect a plain value, SQL::Abstract::More also accepts a pair, i.e. an arrayref of 2 elements, where the first element is a type specification, and the second element is the value. This is convenient when the DBD driver needs additional information about the values used in the statement.

The usual type specification is a hashref {dbd_attrs => \%type}, where \%type is passed directly as third argument to bind_param in DBI, and therefore is specific to the DBD driver.

Another form of type specification is {sqlt_size => $num}, where $num will be passed as buffer size to bind_param_inout in DBI.

Here are some examples

  ($sql, @bind) = $sqla->insert(
   -into   => Foo,
   -values => {bar => [{dbd_attrs => {ora_type => ORA_XMLTYPE}}]},
  ($sql, @bind) = $sqla->select(
   -from  => Foo,
   -where => {d_begin => {">" => [{dbd_attrs => {ora_type => ORA_DATE}},

When using this feature, the @bind array will contain references that cannot be passed directly to DBI methods; so you should use bind_params from the present module to perform the appropriate bindings before executing the statement.


Future versions may include some of these features :
o support for WITH initial clauses, and WITH RECURSIVE.
o suport for Oracle-specific syntax for recursive queries (START_WITH, PRIOR, CONNECT_BY NOCYCLE, CONNECT SIBLINGS, etc.)
o support for INSERT variants


o support for MySQL LOCK_IN_SHARE_MODE
o new constructor option

  ->new(..., select_implicitly_for => $string, ...)

This would provide a default values for the -for parameter.


Laurent Dami, <laurent.dami at>


Please report any bugs or feature requests to bug-sql-abstract-more at, or through the web interface at <>. I will be notified, and then you’ll automatically be notified of progress on your bug as I make changes.


You can find documentation for this module with the perldoc command.

    perldoc SQL::Abstract::More

You can also look for information at:
RT: CPAN’s request tracker <>
AnnoCPAN: Annotated CPAN documentation <>
CPAN Ratings <>
MetaCPAN <>


Copyright 2011, 2012 Laurent Dami.

This program is free software; you can redistribute it and/or modify it under the terms of either: the GNU General Public License as published by the Free Software Foundation; or the Artistic License.

See for more information.

Search for    or go to Top of page |  Section 3 |  Main Index

perl v5.20.3 SQL::ABSTRACT::MORE (3) 2013-04-18

Powered by GSP Visit the GSP FreeBSD Man Page Interface.
Output converted with manServer 1.07.