.\" Automatically generated by Pod::Man 2.27 (Pod::Simple 3.28)
.\"
.\" Standard preamble:
.\" ========================================================================
.de Sp \" Vertical space (when we can't use .PP)
.if t .sp .5v
.if n .sp
..
.de Vb \" Begin verbatim text
.ft CW
.nf
.ne \\$1
..
.de Ve \" End verbatim text
.ft R
.fi
..
.\" Set up some character translations and predefined strings.  \*(-- will
.\" give an unbreakable dash, \*(PI will give pi, \*(L" will give a left
.\" double quote, and \*(R" will give a right double quote.  \*(C+ will
.\" give a nicer C++.  Capital omega is used to do unbreakable dashes and
.\" therefore won't be available.  \*(C` and \*(C' expand to `' in nroff,
.\" nothing in troff, for use with C<>.
.tr \(*W-
.ds C+ C\v'-.1v'\h'-1p'\s-2+\h'-1p'+\s0\v'.1v'\h'-1p'
.ie n \{\
.    ds -- \(*W-
.    ds PI pi
.    if (\n(.H=4u)&(1m=24u) .ds -- \(*W\h'-12u'\(*W\h'-12u'-\" diablo 10 pitch
.    if (\n(.H=4u)&(1m=20u) .ds -- \(*W\h'-12u'\(*W\h'-8u'-\"  diablo 12 pitch
.    ds L" ""
.    ds R" ""
.    ds C` ""
.    ds C' ""
'br\}
.el\{\
.    ds -- \|\(em\|
.    ds PI \(*p
.    ds L" ``
.    ds R" ''
.    ds C`
.    ds C'
'br\}
.\"
.\" Escape single quotes in literal strings from groff's Unicode transform.
.ie \n(.g .ds Aq \(aq
.el       .ds Aq '
.\"
.\" If the F register is turned on, we'll generate index entries on stderr for
.\" titles (.TH), headers (.SH), subsections (.SS), items (.Ip), and index
.\" entries marked with X<> in POD.  Of course, you'll have to process the
.\" output yourself in some meaningful fashion.
.\"
.\" Avoid warning from groff about undefined register 'F'.
.de IX
..
.nr rF 0
.if \n(.g .if rF .nr rF 1
.if (\n(rF:(\n(.g==0)) \{
.    if \nF \{
.        de IX
.        tm Index:\\$1\t\\n%\t"\\$2"
..
.        if !\nF==2 \{
.            nr % 0
.            nr F 2
.        \}
.    \}
.\}
.rr rF
.\" ========================================================================
.\"
.IX Title "SQL::Statement 3"
.TH SQL::Statement 3 "2016-04-11" "perl v5.16.3" "User Contributed Perl Documentation"
.\" For nroff, turn off justification.  Always turn off hyphenation; it makes
.\" way too many mistakes in technical documents.
.if n .ad l
.nh
.SH "NAME"
SQL::Statement \- SQL parsing and processing engine
.SH "SYNOPSIS"
.IX Header "SYNOPSIS"
.Vb 1
\&  # ... depends on what you want to do, see below
.Ve
.SH "DESCRIPTION"
.IX Header "DESCRIPTION"
The SQL::Statement module implements a pure Perl \s-1SQL\s0 parsing and execution
engine. While it by no means implements full \s-1ANSI\s0 standard, it does support
many features including column and table aliases, built-in and user-defined
functions, implicit and explicit joins, complex nested search conditions,
and other features.
.PP
SQL::Statement is a small embeddable Database Management System
(\s-1DBMS\s0). This means that it provides all of the services of a simple
\&\s-1DBMS\s0 except that instead of a persistent storage mechanism, it has two
things: 1) an in-memory storage mechanism that allows you to prepare,
execute, and fetch from \s-1SQL\s0 statements using temporary tables and 2) a
set of software sockets where any author can plug in any storage
mechanism.
.PP
There are three main uses for SQL::Statement. One or another (hopefully not
all) may be irrelevant for your needs: 1) to access and manipulate data in
\&\s-1CSV, XML,\s0 and other formats 2) to build your own \s-1DBD\s0 for a new data source
3) to parse and examine the structure of \s-1SQL\s0 statements.
.SH "INSTALLATION"
.IX Header "INSTALLATION"
There are no prerequisites for using this as a standalone parser. If
you want to access persistent stored data, you either need to write a
subclass or use one of the \s-1DBI DBD\s0 drivers.  You can install this
module using \s-1CPAN\s0.pm, \s-1CPANPLUS\s0.pm, \s-1PPM,\s0 apt-get, or other packaging
tools or you can download the tar.gz file from \s-1CPAN\s0 and use the
standard perl mantra:
.PP
.Vb 4
\&  perl Makefile.PL
\&  make
\&  make test
\&  make install
.Ve
.PP
It works fine on all platforms it has been tested on. On Windows, you
can use ppm or with the mantra use nmake, dmake, or make depending on
which is available.
.SH "USAGE"
.IX Header "USAGE"
.SS "How can I use SQL::Statement to access and modify data?"
.IX Subsection "How can I use SQL::Statement to access and modify data?"
SQL::Statement provides the \s-1SQL\s0 engine for a number of existing \s-1DBI\s0 drivers
including \s-1DBD::CSV\s0, \s-1DBD::DBM\s0, DBD::AnyData, DBD::Excel,
DBD::Amazon, and others.
.PP
These modules provide access to Comma Separated Values, Fixed Length, \s-1XML,
HTML\s0 and many other kinds of text files, to Excel Spreadsheets, to BerkeleyDB
and other \s-1DBM\s0 formats, and to non-traditional data sources like on-the-fly
Amazon searches.
.PP
If you are interested in accessing and manipulating persistent data, you may
not really want to use SQL::Statement directly, but use \s-1DBI\s0 along with
one of the DBDs mentioned above instead. You will be using SQL::Statement, but
under the hood of the \s-1DBD.\s0 See <http://dbi.perl.org> for help with \s-1DBI\s0 and
see SQL::Statement::Syntax for a description of the \s-1SQL\s0 syntax that
SQL::Statement provides for these modules and see the documentation for
whichever \s-1DBD\s0 you are using for additional details.
.SS "How can I use it to parse and examine the structure of \s-1SQL\s0 statements?"
.IX Subsection "How can I use it to parse and examine the structure of SQL statements?"
SQL::Statement can be used stand-alone (without a subclass and without
\&\s-1DBI\s0) to parse and examine the structure of \s-1SQL\s0 statements.  See
SQL::Statement::Structure for details.
.SS "How can I use it to embed a \s-1SQL\s0 engine in a \s-1DBD\s0 or other module?"
.IX Subsection "How can I use it to embed a SQL engine in a DBD or other module?"
SQL::Statement is designed to be easily embedded in other modules and is
especially suited for developing new \s-1DBI\s0 drivers (DBDs).
See SQL::Statement::Embed.
.SS "What \s-1SQL\s0 Syntax is supported?"
.IX Subsection "What SQL Syntax is supported?"
SQL::Statement supports a small but powerful subset of \s-1SQL\s0 commands.
See SQL::Statement::Syntax.
.SS "How can I extend the supported \s-1SQL\s0 syntax?"
.IX Subsection "How can I extend the supported SQL syntax?"
You can modify and extend the \s-1SQL\s0 syntax either by issuing \s-1SQL\s0 commands or
by subclassing SQL::Statement.  See SQL::Statement::Syntax.
.SH "How can I participate in ongoing development?"
.IX Header "How can I participate in ongoing development?"
SQL::Statement is a large module with many potential future directions.
You are invited to help plan, code, test, document, or kibbitz about these
directions. If you want to join the development team, or just hear more
about the development, write Jeff (<jzuckerATcpan.org>) or Jens
(<rehsackATcpan.org>) a note.
.SH "METHODS"
.IX Header "METHODS"
The following methods can or must be overridden by derived classes.
.SS "capability"
.IX Subsection "capability"
.Vb 1
\&  $has_capability = $h\->capability(\*(Aqcapability_name\*(Aq);
.Ve
.PP
Returns a true value if the specified capability is available.
.PP
Currently no capabilities are defined and this is a placeholder for
future use. It is envisioned it will be used like \f(CW\*(C`SQL::Eval::Table::capability\*(C'\fR.
.SS "open_table"
.IX Subsection "open_table"
The \f(CW\*(C`open_table\*(C'\fR method must be overridden by derived classes to provide
the capability of opening data tables. This is a necessity.
.PP
Arguments given to open_table call:
.ie n .IP "$data" 4
.el .IP "\f(CW$data\fR" 4
.IX Item "$data"
The database memo parameter. See \*(L"execute\*(R".
.ie n .IP "$table" 4
.el .IP "\f(CW$table\fR" 4
.IX Item "$table"
The name of the table to open as parsed from \s-1SQL\s0 statement.
.ie n .IP "$createMode" 4
.el .IP "\f(CW$createMode\fR" 4
.IX Item "$createMode"
A flag indicating the mode (\f(CW\*(C`CREATE TABLE ...\*(C'\fR) the table should
be opened with. Set to a true value in create mode.
.ie n .IP "$lockMode" 4
.el .IP "\f(CW$lockMode\fR" 4
.IX Item "$lockMode"
A flag indicating whether the table should be opened for writing (any
other than \f(CW\*(C`SELECT ...\*(C'\fR).  Set to a true value if the table is to
be opened for write access.
.PP
The following methods are required to use SQL::Statement in a \s-1DBD \s0(for
example).
.SS "new"
.IX Subsection "new"
Instantiates a new SQL::Statement object.
.PP
Arguments:
.ie n .IP "$sql" 4
.el .IP "\f(CW$sql\fR" 4
.IX Item "$sql"
The \s-1SQL\s0 statement for later actions.
.ie n .IP "$parser" 4
.el .IP "\f(CW$parser\fR" 4
.IX Item "$parser"
An instance of a SQL::Parser object or flags for it's instantiation.
If omitted, default flags are used.
.PP
When the basic initialization is completed,
\&\f(CW\*(C`$self\->prepare($sql, $parser)\*(C'\fR is invoked.
.SS "prepare"
.IX Subsection "prepare"
Prepares SQL::Statement to execute a \s-1SQL\s0 statement.
.PP
Arguments:
.ie n .IP "$sql" 4
.el .IP "\f(CW$sql\fR" 4
.IX Item "$sql"
The \s-1SQL\s0 statement to parse and prepare.
.ie n .IP "$parser" 4
.el .IP "\f(CW$parser\fR" 4
.IX Item "$parser"
Instance of a SQL::Parser object to parse the provided \s-1SQL\s0 statement.
.SS "execute"
.IX Subsection "execute"
Executes a prepared statement.
.PP
Arguments:
.ie n .IP "$data" 4
.el .IP "\f(CW$data\fR" 4
.IX Item "$data"
Memo field passed through to calls of the instantiated \f(CW$table\fR
objects or \f(CW\*(C`open_table\*(C'\fR calls. In \f(CW\*(C`CREATE\*(C'\fR with subquery,
\&\f(CW\*(C`$data\->{Database}\*(C'\fR must be a \s-1DBI\s0 database handle object.
.ie n .IP "$params" 4
.el .IP "\f(CW$params\fR" 4
.IX Item "$params"
Bound params via \s-1DBI ...\s0
.SS "errstr"
.IX Subsection "errstr"
Gives the error string of the last error, if any.
.SS "fetch_row"
.IX Subsection "fetch_row"
Fetches the next row from the result data set (implies removing the fetched
row from the result data set).
.SS "fetch_rows"
.IX Subsection "fetch_rows"
Fetches all (remaining) rows from the result data set.
.SH "SUPPORT"
.IX Header "SUPPORT"
You can find documentation for this module with the perldoc command.
.PP
.Vb 1
\&    perldoc SQL::Statement
.Ve
.PP
You can also look for information at:
.IP "\(bu" 4
\&\s-1RT: CPAN\s0's request tracker
.Sp
<http://rt.cpan.org/NoAuth/Bugs.html?Dist=SQL\-Statement>
.IP "\(bu" 4
AnnoCPAN: Annotated \s-1CPAN\s0 documentation
.Sp
<http://annocpan.org/dist/SQL\-Statement>
.IP "\(bu" 4
\&\s-1CPAN\s0 Ratings
.Sp
<http://cpanratings.perl.org/s/SQL\-Statement>
.IP "\(bu" 4
\&\s-1CPAN\s0 Search
.Sp
<http://search.cpan.org/dist/SQL\-Statement/>
.SS "Where can I go for help?"
.IX Subsection "Where can I go for help?"
For questions about installation or usage, please ask on the
dbi\-users@perl.org mailing list (see http://dbi.perl.org) or post a
question on PerlMonks (<http://www.perlmonks.org/>, where Jeff is
known as jZed).  Jens does not visit PerlMonks on a regular basis.
.PP
If you have a bug report, a patch or a suggestion, please open a new
report ticket at \s-1CPAN \s0(but please check previous reports first in case
your issue has already been addressed). You can mail any of the module
maintainers, but you are more assured of an answer by posting to
the dbi-users list or reporting the issue in \s-1RT.\s0
.PP
Report tickets should contain a detailed description of the bug or
enhancement request and at least an easily verifiable way of
reproducing the issue or fix. Patches are always welcome, too.
.SS "Where can I go for help with a concrete version?"
.IX Subsection "Where can I go for help with a concrete version?"
Bugs and feature requests are accepted against the latest version
only. To get patches for earlier versions, you need to get an
agreement with a developer of your choice \- who may or not report the
issue and a suggested fix upstream (depends on the license you have
chosen).
.SS "Business support and maintenance"
.IX Subsection "Business support and maintenance"
For business support you can contact Jens via his \s-1CPAN\s0 email
address rehsackATcpan.org. Please keep in mind that business
support is neither available for free nor are you eligible to
receive any support based on the license distributed with this
package.
.SH "ACKNOWLEDGEMENTS"
.IX Header "ACKNOWLEDGEMENTS"
Jochen Wiedmann created the original module as an \s-1XS \s0(C) extension in 1998.
Jeff Zucker took over the maintenance in 2001 and rewrote all of the C
portions in Perl and began extending the \s-1SQL\s0 support.  More recently Ilya
Sterin provided help with SQL::Parser, Tim Bunce provided both general and
specific support, Dan Wright and Dean Arnold have contributed extensively
to the code, and dozens of people from around the world have submitted
patches, bug reports, and suggestions.
.PP
In 2008 Jens Rehsack took over the maintenance of the extended module
from Jeff.  Together with H.Merijn Brand (who has taken \s-1DBD::CSV\s0),
Detlef Wartke and Volker Schubbert (especially between 1.16 developer
versions until 1.22) and all submitters of bug reports via \s-1RT\s0 a lot of
issues have been fixed.
.PP
Thanks to all!
.PP
If you're interested in helping develop SQL::Statement or want to use it
with your own modules, feel free to contact Jeff or Jens.
.SH "BUGS AND LIMITATIONS"
.IX Header "BUGS AND LIMITATIONS"
.IP "\(bu" 4
Currently we treat \s-1NULL\s0 and '' as the same in AnyData/CSV mode \-
eventually fix.
.IP "\(bu" 4
No nested C\-style comments allowed as \s-1SQL99\s0 says.
.IP "\(bu" 4
There are some issues regarding combining outer joins with where
clauses.
.IP "\(bu" 4
Aggregate functions cannot be used in where clause.
.IP "\(bu" 4
Some \s-1SQL\s0 commands/features are not supported (most of them cannot by
design), as \f(CW\*(C`LOCK TABLE\*(C'\fR, using indices, sub-selects etc.
.Sp
Currently the statement for missing features is: I plan to create a
SQL::Statement v2.00 based on a pure Backus-Naur-Form parser and a
fully object oriented command pattern based engine implementation.
When the time is available, I will do it. Until then bugs will be
fixed or other Perl modules under my maintainership will receive my
time. Features which can be added without deep design changes might be
applied earlier \- especially when their addition allows studying
effective ways to implement the feature in upcoming 2.00.
.IP "\(bu" 4
Some people report that SQL::Statement is slower since the \s-1XS\s0 parts
were implemented in pure Perl. This might be true, but on the other
hand a large number of features have been added including support for
\&\s-1ANSI SQL 99.\s0
.Sp
For SQL::Statement 1.xx it's not planned to add new \s-1XS\s0 parts.
.IP "\(bu" 4
Wildcards are expanded to lower cased identifiers. This might confuse
some people, but it was easier to implement.
.Sp
The warning in \s-1DBI\s0 to never trust the case of returned column names
should be read more often. If you need to rely on identifiers, always
use \f(CW\*(C`sth\->{NAME_lc}\*(C'\fR or \f(CW\*(C`sth\->{NAME_uc}\*(C'\fR \- never rely on
\&\f(CW\*(C`sth\->{NAME}\*(C'\fR:
.Sp
.Vb 5
\&  $dbh\->{FetchHashKeyName} = \*(AqNAME_lc\*(Aq;
\&  $sth = $dbh\->prepare("SELECT FOO, BAR, ID, NAME, BAZ FROM TABLE");
\&  $sth\->execute;
\&  $hash_ref = $sth\->fetchall_hashref(\*(Aqid\*(Aq);
\&  print "Name for id 42 is $hash_ref\->{42}\->{name}\en";
.Ve
.Sp
See \*(L"FetchHashKeyName\*(R" in \s-1DBI\s0 for more information.
.IP "\(bu" 4
Unable to use the same table twice with different aliases. \fBWorkaround\fR:
Temporary tables: \f(CW\*(C`CREATE TEMP TABLE t_foo AS SELECT * FROM foo\*(C'\fR.
Than both tables can be used independently.
.PP
Patches to fix bugs/limitations (or a grant to do it) would be
very welcome. Please note, that any patches \fBmust\fR successfully pass
all the \f(CW\*(C`SQL::Statement\*(C'\fR, DBD::File and \s-1DBD::CSV\s0 tests and must
be a general improvement.
.SH "AUTHOR AND COPYRIGHT"
.IX Header "AUTHOR AND COPYRIGHT"
Jochen Wiedmann created the original module as an \s-1XS \s0(C) extension in 1998.
Jeff Zucker took over the maintenance in 2001 and rewrote all of the C
portions in perl and began extending the \s-1SQL\s0 support. Since 2008, Jens
Rehsack is the maintainer.
.PP
Copyright (c) 2001,2005 by Jeff Zucker: jzuckerATcpan.org
Copyright (c) 2007\-2016 by Jens Rehsack: rehsackATcpan.org
.PP
Portions Copyright (C) 1998 by Jochen Wiedmann: jwiedATcpan.org
.PP
All rights reserved.
.PP
You may distribute this module under the terms of either the \s-1GNU\s0
General Public License or the Artistic License, as specified in
the Perl \s-1README\s0 file.