1SQL::Statement(3) User Contributed Perl Documentation SQL::Statement(3)
2
6 SQL::Statement - SQL parsing and processing engine
7
9 # ... depends on what you want to do, see below
10
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 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 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
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 perl Makefile.PL
39 make
40 make test
41 make install
42 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
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 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 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 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 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 What SQL Syntax is supported?
78 SQL::Statement supports a small but powerful subset of SQL commands.
79 See SQL::Statement::Syntax.
80 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
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
93 The following methods can or must be overridden by derived classes.
94 capability
96 $has_capability = $h->capability('capability_name');
97 Returns a true value if the specified capability is available.
99 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 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 Arguments given to open_table call:
109 $data
111 The database memo parameter. See "execute".
112 $table
114 The name of the table to open as parsed from SQL statement.
115 $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 $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 The following methods are required to use SQL::Statement in a DBD (for
126 example).
127 new
129 Instantiates a new SQL::Statement object.
130 Arguments:
132 $sql
134 The SQL statement for later actions.
135 $parser
137 An instance of a SQL::Parser object or flags for it's
138 instantiation. If omitted, default flags are used.
139 When the basic initialization is completed, "$self->prepare($sql,
141 $parser)" is invoked.
142 prepare
144 Prepares SQL::Statement to execute a SQL statement.
145 Arguments:
147 $sql
149 The SQL statement to parse and prepare.
150 $parser
152 Instance of a SQL::Parser object to parse the provided SQL
153 statement.
154 execute
156 Executes a prepared statement.
157 Arguments:
159 $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 $params
166 Bound params via DBI ...
167 errstr
169 Gives the error string of the last error, if any.
170 fetch_row
172 Fetches the next row from the result data set (implies removing the
173 fetched row from the result data set).
174 fetch_rows
176 Fetches all (remaining) rows from the result data set.
177
179 You can find documentation for this module with the perldoc command.
180 perldoc SQL::Statement
182 You can also look for information at:
184 · RT: CPAN's request tracker
186 <http://rt.cpan.org/NoAuth/Bugs.html?Dist=SQL-Statement>
188 · AnnoCPAN: Annotated CPAN documentation
190 <http://annocpan.org/dist/SQL-Statement>
192 · CPAN Ratings
194 <http://cpanratings.perl.org/s/SQL-Statement>
196 · CPAN Search
198 <http://search.cpan.org/dist/SQL-Statement/>
200 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 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 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 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 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
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 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 Thanks to all!
245 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
250 · Currently we treat NULL and '' as the same in AnyData/CSV mode -
251 eventually fix.
252 · No nested C-style comments allowed as SQL99 says.
254 · There are some issues regarding combining outer joins with where
256 clauses.
257 · Aggregate functions cannot be used in where clause.
259 · Some SQL commands/features are not supported (most of them cannot
261 by design), as "LOCK TABLE", using indices, sub-selects etc.
262 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 · 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 For SQL::Statement 1.xx it's not planned to add new XS parts.
278 · Wildcards are expanded to lower cased identifiers. This might
280 confuse some people, but it was easier to implement.
281 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 $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 See "FetchHashKeyName" in DBI for more information.
294 · 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 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 Copyright (c) 2001,2005 by Jeff Zucker: jzuckerATcpan.org Copyright (c)
311 2007-2017 by Jens Rehsack: rehsackATcpan.org
312 Portions Copyright (C) 1998 by Jochen Wiedmann: jwiedATcpan.org
314 All rights reserved.
316 You may distribute this module under the terms of either the GNU
318 General Public License or the Artistic License, as specified in the
319 Perl README file.
320perl v5.30.0 2019-07-26 SQL::Statement(3)