1SQL::Parser(3) User Contributed Perl Documentation SQL::Parser(3)
2
6 SQL::Parser -- validate and parse SQL strings
7
9 use SQL::Parser; # CREATE A PARSER OBJECT
10 my $parser = SQL::Parser->new();
11 $parser->feature( $class, $name, $value ); # SET OR FIND STATUS OF
13 my $has_feature = $parser->feature( $class, $name ); # A PARSER FEATURE
14 $parser->dialect( $dialect_name ); # SET OR FIND STATUS OF
16 my $current_dialect = $parser->dialect; # A PARSER DIALECT
17
19 SQL::Parser is part of the SQL::Statement distribution and, most
20 interaction with the parser should be done through SQL::Statement. The
21 methods shown above create and modify a parser object. To use the
22 parser object to parse SQL and to examine the resulting structure, you
23 should use SQL::Statement.
24 Important Note: Previously SQL::Parser had its own hash-based interface
26 for parsing, but that is now deprecated and will eventually be phased
27 out in favor of the object-oriented parsing interface of
28 SQL::Statement. If you are unable to transition some features to the
29 new interface or have concerns about the phase out, please contact me.
30 See "The Parse Structure" for details of the now-deprecated hash method
31 if you still need them.
32
34 new()
35 Create a new parser object
36 use SQL::Parser;
38 my $parser = SQL::Parser->new();
39 The new() method creates a SQL::Parser object which can then be used to
41 parse and validate the syntax of SQL strings. It takes two optional
42 parameters - 1) the name of the SQL dialect that will define the syntax
43 rules for the parser and 2) a reference to a hash which can contain
44 additional attributes of the parser. If no dialect is specified,
45 'AnyData' is the default.
46 use SQL::Parser;
48 my $parser = SQL::Parser->new( $dialect_name, \%attrs );
49 The dialect_name parameter is a string containing any valid dialect
51 such as 'ANSI', 'AnyData', or 'CSV'. See the section on the dialect()
52 method below for details.
53 The "attrs" parameter is a reference to a hash that can contain error
55 settings for the PrintError and RaiseError attributes.
56 An example:
58 use SQL::Parser;
60 my $parser = SQL::Parser->new('AnyData', {RaiseError=>1} );
61 This creates a new parser that uses the grammar rules
63 contained in the .../SQL/Dialects/AnyData.pm file and which
64 sets the RaiseError attribute to true.
65 dialect()
67 $parser->dialect( $dialect_name ); # load a dialect configuration file
68 my $dialect = $parser->dialect; # get the name of the current dialect
69 For example:
71 $parser->dialect('AnyData'); # loads the AnyData config file
73 print $parser->dialect; # prints 'AnyData'
74 The $dialect_name parameter may be the name of any dialect
76 configuration file on your system. Use the $parser->list('dialects')
77 method to see a list of available dialects. At a minimum it will
78 include "ANSI", "CSV", and "AnyData". For backwards compatibility
79 'Ansi' is accepted as a synonym for 'ANSI', otherwise the names are
80 case sensitive.
81 Loading a new dialect configuration file erases all current parser
83 features and resets them to those defined in the configuration file.
84 feature()
86 Features define the rules to be used by a specific parser instance.
87 They are divided into the following classes:
88 * valid_commands
90 * valid_options
91 * valid_comparison_operators
92 * valid_data_types
93 * reserved_words
94 Within each class a feature name is either enabled or disabled. For
96 example, under "valid_data_types" the name "BLOB" may be either
97 disabled or enabled. If it is not enabled (either by being
98 specifically disabled, or simply by not being specified at all) then
99 any SQL string using "BLOB" as a data type will throw a syntax error
100 "Invalid data type: 'BLOB'".
101 The feature() method allows you to enable, disable, or check the status
103 of any feature.
104 $parser->feature( $class, $name, 1 ); # enable a feature
106 $parser->feature( $class, $name, 0 ); # disable a feature
108 my $feature = $parser->feature( $class, $name ); # return status of a feature
110 For example:
112 $parser->feature('reserved_words','FOO',1); # make 'FOO' a reserved word
114 $parser->feature('valid_data_types','BLOB',0); # disallow 'BLOB' as a
116 # data type
117 # determine if the LIKE
119 # operator is supported
120 my $LIKE = $parser->feature('valid_comparison_operators','LIKE');
121 See the section below on "Backwards Compatibility" for use of the
123 feature() method with SQL::Statement 0.1x style parameters.
124
126 The SQL::Statement distribution can be used to either just parse SQL
127 statements or to execute them against actual data. A broader set of
128 syntax is supported in the parser than in the executor. For example
129 the parser allows you to specify column constraints like PRIMARY KEY.
130 Currently, these are ignored by the execution engine. Likewise syntax
131 such as RESTRICT and CASCADE on DROP statements or LOCAL GLOBAL
132 TEMPORARY tables in CREATE are supported by the parser but ignored by
133 the executor.
134 To see the list of Supported SQL syntax formerly kept in this pod, see
136 SQL::Statement.
137
139 In the event you need to either extend or modify SQL::Parser's default
140 behavior, the following methods may be overridden:
141 "$self-">"get_btwn($string)"
143 Processes the BETWEEN...AND... predicates; default converts to 2
144 range predicates.
145 "$self-">"get_in($string)"
147 Process the IN (...list...) predicates; default converts to a
148 series of OR'd '=' predicate, or AND'd '<>' predicates for NOT IN.
149 "$self-">"transform_syntax($string)"
151 Abstract method; default simply returns the original string.
152 Called after repl_btwn() and repl_in(), but before any further
153 predicate processing is applied. Possible uses include converting
154 other predicate syntax not recognized by SQL::Parser into user-
155 defined functions.
156
158 This section outlines the now-deprecated hash interface to the parsed
159 structure. It is included for backwards compatibility only. You
160 should use the SQL::Statement object interface to the structure
161 instead. See SQL::Statement.
162 Parse Structures
164 Here are some further examples of the data structures returned by the
166 structure() method after a call to parse(). Only specific details are
167 shown for each SQL instance, not the entire structure.
168 parse()
170 Once a SQL::Parser object has been created with the new() method, the
172 parse() method can be used to parse any number of SQL strings. It
173 takes a single required parameter -- a string containing a SQL command.
174 The SQL string may optionally be terminated by a semicolon. The
175 parse() method returns a true value if the parse is successful and a
176 false value if the parse finds SQL syntax errors.
177 Examples:
179 1) my $success = $parser->parse('SELECT * FROM foo');
181 2) my $sql = 'SELECT * FROM foo';
183 my $success = $parser->parse( $sql );
184 3) my $success = $parser->parse(qq!
186 SELECT id,phrase
187 FROM foo
188 WHERE id < 7
189 AND phrase <> 'bar'
190 ORDER BY phrase;
191 !);
192 4) my $success = $parser->parse('SELECT * FRoOM foo ');
194 In examples #1,#2, and #3, the value of $success will be true because
196 the strings passed to the parse() method are valid SQL strings.
197 In example #4, however, the value of $success will be false because the
199 string contains a SQL syntax error ('FRoOM' instead of 'FROM').
200 In addition to checking the return value of parse() with a variable
202 like $success, you may use the PrintError and RaiseError attributes as
203 you would in a DBI script:
204 * If PrintError is true, then SQL syntax errors will be sent as
206 warnings to STDERR (i.e. to the screen or to a file if STDERR
207 has been redirected). This is set to true by default which
208 means that unless you specifically turn it off, all errors
209 will be reported.
210 * If RaiseError is true, then SQL syntax errors will cause the
212 script to die, (i.e. the script will terminate unless wrapped
213 in an eval). This is set to false by default which means
214 that unless you specifically turn it on, scripts will
215 continue to operate even if there are SQL syntax errors.
216 Basically, you should leave PrintError on or else you will not be
218 warned when an error occurs. If you are simply validating a series of
219 strings, you will want to leave RaiseError off so that the script can
220 check all strings regardless of whether some of them contain SQL
221 errors. However, if you are going to try to execute the SQL or need to
222 depend that it is correct, you should set RaiseError on so that the
223 program will only continue to operate if all SQL strings use correct
224 syntax.
225 IMPORTANT NOTE #1: The parse() method only checks syntax, it does NOT
227 verify if the objects listed actually exist. For example, given the
228 string "SELECT model FROM cars", the parse() method will report that
229 the string contains valid SQL but that will not tell you whether there
230 actually is a table called "cars" or whether that table contains a
231 column called 'model'. Those kinds of verifications are performed by
232 the SQL::Statement module, not by SQL::Parser by itself.
233 IMPORTANT NOTE #2: The parse() method uses rules as defined by the
235 selected dialect configuration file and the feature() method. This
236 means that a statement that is valid in one dialect may not be valid in
237 another. For example the 'CSV' and 'AnyData' dialects define 'BLOB' as
238 a valid data type but the 'ANSI' dialect does not. Therefore the
239 statement 'CREATE TABLE foo (picture BLOB)' would be valid in the first
240 two dialects but would produce a syntax error in the 'ANSI' dialect.
241 structure()
243 After a SQL::Parser object has been created and the parse() method used
245 to parse a SQL string, the structure() method returns the data
246 structure of that string. This data structure may be passed on to
247 other modules (e.g. SQL::Statement) or it may be printed out using, for
248 example, the Data::Dumper module.
249 The data structure contains all of the information in the SQL string as
251 parsed into its various components. To take a simple example:
252 $parser->parse('SELECT make,model FROM cars');
254 use Data::Dumper;
255 print Dumper $parser->structure;
256 Would produce:
258 $VAR1 = {
260 'column_defs' => [
261 { 'type' => 'column',
262 'value' => 'make', },
263 { 'type' => 'column',
264 'value' => 'model', },
265 ],
266 'command' => 'SELECT',
267 'table_names' => [
268 'cars'
269 ]
270 };
271 'SELECT make,model, FROM cars'
274 command => 'SELECT',
276 table_names => [ 'cars' ],
277 column_names => [ 'make', 'model' ],
278 'CREATE TABLE cars ( id INTEGER, model VARCHAR(40) )'
280 column_defs => {
282 id => { data_type => INTEGER },
283 model => { data_type => VARCHAR(40) },
284 },
285 'SELECT DISTINCT make FROM cars'
287 set_quantifier => 'DISTINCT',
289 'SELECT MAX (model) FROM cars'
291 set_function => {
293 name => 'MAX',
294 arg => 'models',
295 },
296 'SELECT * FROM cars LIMIT 5,10'
298 limit_clause => {
300 offset => 5,
301 limit => 10,
302 },
303 'SELECT * FROM vars ORDER BY make, model DESC'
305 sort_spec_list => [
307 { make => 'ASC' },
308 { model => 'DESC' },
309 ],
310 "INSERT INTO cars VALUES ( 7, 'Chevy', 'Impala' )"
312 values => [ 7, 'Chevy', 'Impala' ],
314
316 You can find documentation for this module with the perldoc command.
317 perldoc SQL::Parser
319 perldoc SQL::Statement
320 You can also look for information at:
322 · RT: CPAN's request tracker
324 <http://rt.cpan.org/NoAuth/Bugs.html?Dist=SQL-Statement>
326 · AnnoCPAN: Annotated CPAN documentation
328 <http://annocpan.org/dist/SQL-Statement>
330 · CPAN Ratings
332 <http://cpanratings.perl.org/s/SQL-Statement>
334 · Search CPAN
336 <http://search.cpan.org/dist/SQL-Statement/>
338 Where can I go for help?
340 For questions about installation or usage, please ask on the
341 dbi-users@perl.org mailing list or post a question on PerlMonks
342 (<http://www.perlmonks.org/>, where Jeff is known as jZed). Jens does
343 not visit PerlMonks on a regular basis.
344 If you have a bug report, a patch or a suggestion, please open a new
346 report ticket at CPAN (but please check previous reports first in case
347 your issue has already been addressed). You can mail any of the module
348 maintainers, but you are more assured of an answer by posting to the
349 dbi-users list or reporting the issue in RT.
350 Report tickets should contain a detailed description of the bug or
352 enhancement request and at least an easily verifiable way of
353 reproducing the issue or fix. Patches are always welcome, too.
354 Where can I go for help with a concrete version?
356 Bugs and feature requests are accepted against the latest version only.
357 To get patches for earlier versions, you need to get an agreement with
358 a developer of your choice - who may or not report the the issue and a
359 suggested fix upstream (depends on the license you have chosen).
360 Business support and maintenance
362 For business support you can contact Jens via his CPAN email address
363 rehsackATcpan.org. Please keep in mind that business support is neither
364 available for free nor are you eligible to receive any support based on
365 the license distributed with this package.
366
368 This module is
369 copyright (c) 2001,2005 by Jeff Zucker and
371 copyright (c) 2007-2020 by Jens Rehsack.
372 All rights reserved.
374 The module may be freely distributed under the same terms as Perl
376 itself using either the "GPL License" or the "Artistic License" as
377 specified in the Perl README file.
378 Jeff can be reached at: jzuckerATcpan.org Jens can be reached at:
380 rehsackATcpan.org or via dbi-devATperl.org
381perl v5.32.0 2020-10-22 SQL::Parser(3)