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 inter‐
20 action 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 SQL::State‐
28 ment. If you are unable to transition some features to the new inter‐
29 face or have concerns about the phase out, please contact Jeff. See
30 "The Parse Structure" for details of the now-deprecated hash method if
31 you still need them.
32
34 new()
35 Create a new parser object
37 use SQL::Parser;
39 my $parser = SQL::Parser->new();
40 The new() method creates a SQL::Parser object which can then be used to
42 parse and validate the syntax of SQL strings. It takes two optional
43 parameters - 1) the name of the SQL dialect that will define the syntax
44 rules for the parser and 2) a reference to a hash which can contain
45 additional attributes of the parser. If no dialect is specified, 'Any‐
46 Data' is the default.
47 use SQL::Parser;
49 my $parser = SQL::Parser->new( $dialect_name, \%attrs );
50 The dialect_name parameter is a string containing any valid dialect
52 such as 'ANSI', 'AnyData', or 'CSV'. See the section on the dialect()
53 method below for details.
54 The attribute parameter is a reference to a hash that can contain error
56 settings for the PrintError and RaiseError attributes.
57 An example:
59 use SQL::Parser;
61 my $parser = SQL::Parser->new('AnyData', {RaiseError=>1} );
62 This creates a new parser that uses the grammar rules
64 contained in the .../SQL/Dialects/AnyData.pm file and which
65 sets the RaiseError attribute to true.
66 dialect()
68 $parser->dialect( $dialect_name ); # load a dialect configuration file
70 my $dialect = $parser->dialect; # get the name of the current dialect
71 For example:
73 $parser->dialect('AnyData'); # loads the AnyData config file
75 print $parser->dialect; # prints 'AnyData'
76 The $dialect_name parameter may be the name of any dialect configura‐
78 tion file on your system. Use the $parser->list('dialects') method to
79 see a list of available dialects. At a minimum it will include "ANSI",
80 "CSV", and "AnyData". For backwards compatiblity 'Ansi' is accepted as
81 a synonym for 'ANSI', otherwise the names are case sensitive.
82 Loading a new dialect configuration file erases all current parser fea‐
84 tures and resets them to those defined in the configuration file.
85 feature()
87 Features define the rules to be used by a specific parser instance.
89 They are divided into the following classes:
90 * valid_commands
92 * valid_options
93 * valid_comparison_operators
94 * valid_data_types
95 * reserved_words
96 Within each class a feature name is either enabled or disabled. For
98 example, under "valid_data_types" the name "BLOB" may be either dis‐
99 abled or enabled. If it is not eneabled (either by being specifically
100 disabled, or simply by not being specified at all) then any SQL string
101 using "BLOB" as a data type will throw a syntax error "Invalid data
102 type: 'BLOB'".
103 The feature() method allows you to enable, disable, or check the status
105 of any feature.
106 $parser->feature( $class, $name, 1 ); # enable a feature
108 $parser->feature( $class, $name, 0 ); # disable a feature
110 my $feature = $parser->feature( $class, $name ); # show status of a feature
112 For example:
114 $parser->feature('reserved_words','FOO',1); # make 'FOO' a reserved word
116 $parser->feature('valid_data_types','BLOB',0); # disallow 'BLOB' as a
118 # data type
119 # determine if the LIKE
121 # operator is supported
122 my $LIKE = $parser->feature('valid_operators','LIKE');
123 See the section below on "Backwards Compatibility" for use of the fea‐
125 ture() method with SQL::Statement 0.1x style parameters.
126
128 The SQL::Statement distribution can be used to either just parse SQL
129 statements or to execute them against actual data. A broader set of
130 syntax is supported in the parser than in the executor. For example
131 the parser allows you to specify column constraints like PRIMARY KEY.
132 Currently, these are ignored by the execution engine. Likewise syntax
133 such as RESTRICT and CASCADE on DROP statements or LOCAL GLOBAL TEMPPO‐
134 RARY tables in CREATE are supported by the parser but ignored by the
135 executor.
136 To see the list of Supported SQL syntax formerly kept in this pod, see
138 SQL::Statement.
139
141 In the event you need to either extend or modify SQL::Parser's default
142 behavior, the following methods may be overriden to modify the behav‐
143 ior:
144 "$self-">"get_btwn($string)"
146 Processes the BETWEEN...AND... predicates; default converts to 2
147 range predicates.
148 "$self-">"get_in($string)"
150 Process the IN (...list...) predicates; default converts to a
151 series of OR'd '=' predicate, or AND'd '<>' predicates for NOT IN.
152 "$self-">"transform_syntax($string)"
154 Abstract method; default simply returns the original string.
155 Called after get_btwn() and get_in(), but before any further predi‐
156 cate processing is applied. Possible uses include converting other
157 predicate syntax not recognized by SQL::Parser into user-defined
158 functions.
159
161 This section outlines the now-deprecated hash interface to the parsed
162 structure. It is included for backwards compatability only. You
163 should use the SQL::Statement object interface to the structure
164 instead. See SQL::Statement.
165 Parse Structures
167 Here are some further examples of the data structures returned by the
169 structure() method after a call to parse(). Only specific details are
170 shown for each SQL instance, not the entire struture.
171 parse()
173 Once a SQL::Parser object has been created with the new() method, the
175 parse() method can be used to parse any number of SQL strings. It
176 takes a single required parameter -- a string containing a SQL command.
177 The SQL string may optionally be terminated by a semicolon. The
178 parse() method returns a true value if the parse is successful and a
179 false value if the parse finds SQL syntax errors.
180 Examples:
182 1) my $success = $parser->parse('SELECT * FROM foo');
184 2) my $sql = 'SELECT * FROM foo';
186 my $success = $parser->parse( $sql );
187 3) my $success = $parser->parse(qq!
189 SELECT id,phrase
190 FROM foo
191 WHERE id < 7
192 AND phrase <> 'bar'
193 ORDER BY phrase;
194 !);
195 4) my $success = $parser->parse('SELECT * FRoOM foo ');
197 In examples #1,#2, and #3, the value of $success will be true because
199 the strings passed to the parse() method are valid SQL strings.
200 In example #4, however, the value of $success will be false because the
202 string contains a SQL syntax error ('FRoOM' instead of 'FROM').
203 In addition to checking the return value of parse() with a variable
205 like $success, you may use the PrintError and RaiseError attributes as
206 you would in a DBI script:
207 * If PrintError is true, then SQL syntax errors will be sent as
209 warnings to STDERR (i.e. to the screen or to a file if STDERR
210 has been redirected). This is set to true by default which
211 means that unless you specifically turn it off, all errors
212 will be reported.
213 * If RaiseError is true, then SQL syntax errors will cause the
215 script to die, (i.e. the script will terminate unless wrapped
216 in an eval). This is set to false by default which means
217 that unless you specifically turn it on, scripts will
218 continue to operate even if there are SQL syntax errors.
219 Basically, you should leave PrintError on or else you will not be
221 warned when an error occurs. If you are simply validating a series of
222 strings, you will want to leave RaiseError off so that the script can
223 check all strings regardless of whether some of them contain SQL
224 errors. However, if you are going to try to execute the SQL or need to
225 depend that it is correct, you should set RaiseError on so that the
226 program will only continue to operate if all SQL strings use correct
227 syntax.
228 IMPORTANT NOTE #1: The parse() method only checks syntax, it does NOT
230 verify if the objects listed actually exist. For example, given the
231 string "SELECT model FROM cars", the parse() method will report that
232 the string contains valid SQL but that will not tell you whether there
233 actually is a table called "cars" or whether that table contains a col‐
234 umn called 'model'. Those kinds of verifications can be performed by
235 the SQL::Statement module, not by SQL::Parser by itself.
236 IMPORTANT NOTE #2: The parse() method uses rules as defined by the
238 selected dialect configuration file and the feature() method. This
239 means that a statement that is valid in one dialect may not be valid in
240 another. For example the 'CSV' and 'AnyData' dialects define 'BLOB' as
241 a valid data type but the 'ANSI' dialect does not. Therefore the
242 statement 'CREATE TABLE foo (picture BLOB)' would be valid in the first
243 two dialects but would produce a syntax error in the 'ANSI' dialect.
244 structure()
246 After a SQL::Parser object has been created and the parse() method used
248 to parse a SQL string, the structure() method returns the data struc‐
249 ture of that string. This data structure may be passed on to other
250 modules (e.g. SQL::Statement) or it may be printed out using, for exam‐
251 ple, the Data::Dumper module.
252 The data structure contains all of the information in the SQL string as
254 parsed into its various components. To take a simple example:
255 $parser->parse('SELECT make,model FROM cars');
257 use Data::Dumper;
258 print Dumper $parser->structure;
259 Would produce:
261 $VAR1 = {
263 'column_names' => [
264 'make',
265 'model'
266 ],
267 'command' => 'SELECT',
268 'table_names' => [
269 'cars'
270 ]
271 };
272 '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 This module is copyright (c) 2001,2005 by Jeff Zucker.
317 All rights reserved.
318 The module may be freely distributed under the same terms as
320 Perl itself using either the "GPL License" or the "Artistic
321 License" as specified in the Perl README file.
322 Jeff can be reached at: jzuckerATcpan.org
324perl v5.8.8 2005-04-18 SQL::Parser(3)