1SQL::Statement(3)     User Contributed Perl Documentation    SQL::Statement(3)
2
3
4

NAME

6       SQL::Statement - SQL parsing and processing engine
7

SYNOPSIS

9         # ... depends on what you want to do, see below
10

DESCRIPTION

12       The SQL::Statement module implements a pure Perl SQL parsing and
13       execution engine. While it by no means implements full ANSI standard,
14       it does support many features including column and table aliases,
15       built-in and user-defined functions, implicit and explicit joins,
16       complex nested search conditions, and other features.
17
18       SQL::Statement is a small embeddable Database Management System (DBMS).
19       This means that it provides all of the services of a simple DBMS except
20       that instead of a persistent storage mechanism, it has two things: 1)
21       an in-memory storage mechanism that allows you to prepare, execute, and
22       fetch from SQL statements using temporary tables and 2) a set of
23       software sockets where any author can plug in any storage mechanism.
24
25       There are three main uses for SQL::Statement. One or another (hopefully
26       not all) may be irrelevant for your needs: 1) to access and manipulate
27       data in CSV, XML, and other formats 2) to build your own DBD for a new
28       data source 3) to parse and examine the structure of SQL statements.
29

INSTALLATION

31       There are no prerequisites for using this as a standalone parser. If
32       you want to access persistent stored data, you either need to write a
33       subclass or use one of the DBI DBD drivers.  You can install this
34       module using CPAN.pm, CPANPLUS.pm, PPM, apt-get, or other packaging
35       tools or you can download the tar.gz file from CPAN and use the
36       standard perl mantra:
37
38         perl Makefile.PL
39         make
40         make test
41         make install
42
43       It works fine on all platforms it has been tested on. On Windows, you
44       can use ppm or with the mantra use nmake, dmake, or make depending on
45       which is available.
46

USAGE

48   How can I use SQL::Statement to access and modify data?
49       SQL::Statement provides the SQL engine for a number of existing DBI
50       drivers including DBD::CSV, DBD::DBM, DBD::AnyData, DBD::Excel,
51       DBD::Amazon, and others.
52
53       These modules provide access to Comma Separated Values, Fixed Length,
54       XML, HTML and many other kinds of text files, to Excel Spreadsheets, to
55       BerkeleyDB and other DBM formats, and to non-traditional data sources
56       like on-the-fly Amazon searches.
57
58       If you are interested in accessing and manipulating persistent data,
59       you may not really want to use SQL::Statement directly, but use DBI
60       along with one of the DBDs mentioned above instead. You will be using
61       SQL::Statement, but under the hood of the DBD. See
62       <http://dbi.perl.org> for help with DBI and see SQL::Statement::Syntax
63       for a description of the SQL syntax that SQL::Statement provides for
64       these modules and see the documentation for whichever DBD you are using
65       for additional details.
66
67   How can I use it to parse and examine the structure of SQL statements?
68       SQL::Statement can be used stand-alone (without a subclass and without
69       DBI) to parse and examine the structure of SQL statements.  See
70       SQL::Statement::Structure for details.
71
72   How can I use it to embed a SQL engine in a DBD or other module?
73       SQL::Statement is designed to be easily embedded in other modules and
74       is especially suited for developing new DBI drivers (DBDs).  See
75       SQL::Statement::Embed.
76
77   What SQL Syntax is supported?
78       SQL::Statement supports a small but powerful subset of SQL commands.
79       See SQL::Statement::Syntax.
80
81   How can I extend the supported SQL syntax?
82       You can modify and extend the SQL syntax either by issuing SQL commands
83       or by subclassing SQL::Statement.  See SQL::Statement::Syntax.
84

How can I participate in ongoing development?

86       SQL::Statement is a large module with many potential future directions.
87       You are invited to help plan, code, test, document, or kibbitz about
88       these directions. If you want to join the development team, or just
89       hear more about the development, write Jeff (<jzuckerATcpan.org>) or
90       Jens (<rehsackATcpan.org>) a note.
91

METHODS

93       The following methods can or must be overridden by derived classes.
94
95   capability
96         $has_capability = $h->capability('capability_name');
97
98       Returns a true value if the specified capability is available.
99
100       Currently no capabilities are defined and this is a placeholder for
101       future use. It is envisioned it will be used like
102       "SQL::Eval::Table::capability".
103
104   open_table
105       The "open_table" method must be overridden by derived classes to
106       provide the capability of opening data tables. This is a necessity.
107
108       Arguments given to open_table call:
109
110       $data
111           The database memo parameter. See "execute".
112
113       $table
114           The name of the table to open as parsed from SQL statement.
115
116       $createMode
117           A flag indicating the mode ("CREATE TABLE ...") the table should be
118           opened with. Set to a true value in create mode.
119
120       $lockMode
121           A flag indicating whether the table should be opened for writing
122           (any other than "SELECT ...").  Set to a true value if the table is
123           to be opened for write access.
124
125       The following methods are required to use SQL::Statement in a DBD (for
126       example).
127
128   new
129       Instantiates a new SQL::Statement object.
130
131       Arguments:
132
133       $sql
134           The SQL statement for later actions.
135
136       $parser
137           An instance of a SQL::Parser object or flags for it's
138           instantiation.  If omitted, default flags are used.
139
140       When the basic initialization is completed, "$self->prepare($sql,
141       $parser)" is invoked.
142
143   prepare
144       Prepares SQL::Statement to execute a SQL statement.
145
146       Arguments:
147
148       $sql
149           The SQL statement to parse and prepare.
150
151       $parser
152           Instance of a SQL::Parser object to parse the provided SQL
153           statement.
154
155   execute
156       Executes a prepared statement.
157
158       Arguments:
159
160       $data
161           Memo field passed through to calls of the instantiated $table
162           objects or "open_table" calls. In "CREATE" with subquery,
163           "$data->{Database}" must be a DBI database handle object.
164
165       $params
166           Bound params via DBI ...
167
168   errstr
169       Gives the error string of the last error, if any.
170
171   fetch_row
172       Fetches the next row from the result data set (implies removing the
173       fetched row from the result data set).
174
175   fetch_rows
176       Fetches all (remaining) rows from the result data set.
177

SUPPORT

179       You can find documentation for this module with the perldoc command.
180
181           perldoc SQL::Statement
182
183       You can also look for information at:
184
185       •   RT: CPAN's request tracker
186
187           <http://rt.cpan.org/NoAuth/Bugs.html?Dist=SQL-Statement>
188
189       •   AnnoCPAN: Annotated CPAN documentation
190
191           <http://annocpan.org/dist/SQL-Statement>
192
193       •   CPAN Ratings
194
195           <http://cpanratings.perl.org/s/SQL-Statement>
196
197       •   CPAN Search
198
199           <http://search.cpan.org/dist/SQL-Statement/>
200
201   Where can I go for help?
202       For questions about installation or usage, please ask on the
203       dbi-users@perl.org mailing list (see http://dbi.perl.org) or post a
204       question on PerlMonks (<http://www.perlmonks.org/>, where Jeff is known
205       as jZed).  Jens does not visit PerlMonks on a regular basis.
206
207       If you have a bug report, a patch or a suggestion, please open a new
208       report ticket at CPAN (but please check previous reports first in case
209       your issue has already been addressed). You can mail any of the module
210       maintainers, but you are more assured of an answer by posting to the
211       dbi-users list or reporting the issue in RT.
212
213       Report tickets should contain a detailed description of the bug or
214       enhancement request and at least an easily verifiable way of
215       reproducing the issue or fix. Patches are always welcome, too.
216
217   Where can I go for help with a concrete version?
218       Bugs and feature requests are accepted against the latest version only.
219       To get patches for earlier versions, you need to get an agreement with
220       a developer of your choice - who may or not report the issue and a
221       suggested fix upstream (depends on the license you have chosen).
222
223   Business support and maintenance
224       For business support you can contact Jens via his CPAN email address
225       rehsackATcpan.org. Please keep in mind that business support is neither
226       available for free nor are you eligible to receive any support based on
227       the license distributed with this package.
228

ACKNOWLEDGEMENTS

230       Jochen Wiedmann created the original module as an XS (C) extension in
231       1998.  Jeff Zucker took over the maintenance in 2001 and rewrote all of
232       the C portions in Perl and began extending the SQL support.  More
233       recently Ilya Sterin provided help with SQL::Parser, Tim Bunce provided
234       both general and specific support, Dan Wright and Dean Arnold have
235       contributed extensively to the code, and dozens of people from around
236       the world have submitted patches, bug reports, and suggestions.
237
238       In 2008 Jens Rehsack took over the maintenance of the extended module
239       from Jeff.  Together with H.Merijn Brand (who has taken DBD::CSV),
240       Detlef Wartke and Volker Schubbert (especially between 1.16 developer
241       versions until 1.22) and all submitters of bug reports via RT a lot of
242       issues have been fixed.
243
244       Thanks to all!
245
246       If you're interested in helping develop SQL::Statement or want to use
247       it with your own modules, feel free to contact Jeff or Jens.
248

BUGS AND LIMITATIONS

250       •   Currently we treat NULL and '' as the same in AnyData/CSV mode -
251           eventually fix.
252
253       •   No nested C-style comments allowed as SQL99 says.
254
255       •   There are some issues regarding combining outer joins with where
256           clauses.
257
258       •   Aggregate functions cannot be used in where clause.
259
260       •   Some SQL commands/features are not supported (most of them cannot
261           by design), as "LOCK TABLE", using indices, sub-selects etc.
262
263           Currently the statement for missing features is: I plan to create a
264           SQL::Statement v2.00 based on a pure Backus-Naur-Form parser and a
265           fully object oriented command pattern based engine implementation.
266           When the time is available, I will do it. Until then bugs will be
267           fixed or other Perl modules under my maintainership will receive my
268           time. Features which can be added without deep design changes might
269           be applied earlier - especially when their addition allows studying
270           effective ways to implement the feature in upcoming 2.00.
271
272       •   Some people report that SQL::Statement is slower since the XS parts
273           were implemented in pure Perl. This might be true, but on the other
274           hand a large number of features have been added including support
275           for ANSI SQL 99.
276
277           For SQL::Statement 1.xx it's not planned to add new XS parts.
278
279       •   Wildcards are expanded to lower cased identifiers. This might
280           confuse some people, but it was easier to implement.
281
282           The warning in DBI to never trust the case of returned column names
283           should be read more often. If you need to rely on identifiers,
284           always use "sth->{NAME_lc}" or "sth->{NAME_uc}" - never rely on
285           "sth->{NAME}":
286
287             $dbh->{FetchHashKeyName} = 'NAME_lc';
288             $sth = $dbh->prepare("SELECT FOO, BAR, ID, NAME, BAZ FROM TABLE");
289             $sth->execute;
290             $hash_ref = $sth->fetchall_hashref('id');
291             print "Name for id 42 is $hash_ref->{42}->{name}\n";
292
293           See "FetchHashKeyName" in DBI for more information.
294
295       •   Unable to use the same table twice with different aliases.
296           Workaround: Temporary tables: "CREATE TEMP TABLE t_foo AS SELECT *
297           FROM foo".  Than both tables can be used independently.
298
299       Patches to fix bugs/limitations (or a grant to do it) would be very
300       welcome. Please note, that any patches must successfully pass all the
301       "SQL::Statement", DBD::File and DBD::CSV tests and must be a general
302       improvement.
303
305       Jochen Wiedmann created the original module as an XS (C) extension in
306       1998.  Jeff Zucker took over the maintenance in 2001 and rewrote all of
307       the C portions in perl and began extending the SQL support. Since 2008,
308       Jens Rehsack is the maintainer.
309
310       Copyright (c) 2001,2005 by Jeff Zucker: jzuckerATcpan.org Copyright (c)
311       2007-2020 by Jens Rehsack: rehsackATcpan.org
312
313       Portions Copyright (C) 1998 by Jochen Wiedmann: jwiedATcpan.org
314
315       All rights reserved.
316

LICENSE

318       You may distribute this module under the terms of either the GNU
319       General Public License or the Artistic License, as specified in the
320       Perl README file.
321
322
323
324perl v5.36.0                      2023-01-20                 SQL::Statement(3)
Impressum