1DBI::SQL::Nano(3) User Contributed Perl Documentation DBI::SQL::Nano(3)
2
6 DBI::SQL::Nano - a very tiny SQL engine
7
9 BEGIN { $ENV{DBI_SQL_NANO}=1 } # forces use of Nano rather than SQL::Statement
10 use DBI::SQL::Nano;
11 use Data::Dumper;
12 my $stmt = DBI::SQL::Nano::Statement->new(
13 "SELECT bar,baz FROM foo WHERE qux = 1"
14 ) or die "Couldn't parse";
15 print Dumper $stmt;
16
18 DBI::SQL::Nano is meant as a *very* minimal SQL engine for use in situ‐
19 ations where SQL::Statement is not available. In most situations you
20 are better off installing SQL::Statement although DBI::SQL::Nano may be
21 faster for some very simple tasks.
22 DBI::SQL::Nano, like SQL::Statement is primarily intended to provide a
24 SQL engine for use with some pure perl DBDs including DBD::DBM,
25 DBD::CSV, DBD::AnyData, and DBD::Excel. It isn't of much use in and of
26 itself. You can dump out the structure of a parsed SQL statement, but
27 that's about it.
28
30 Setting the DBI_SQL_NANO flag
31 By default, when a DBD uses DBI::SQL::Nano, the module will look to see
33 if SQL::Statement is installed. If it is, SQL::Statement objects are
34 used. If SQL::Statement is not available, DBI::SQL::Nano objects are
35 used.
36 In some cases, you may wish to use DBI::SQL::Nano objects even if
38 SQL::Statement is available. To force usage of DBI::SQL::Nano objects
39 regardless of the availability of SQL::Statement, set the environment
40 variable DBI_SQL_NANO to 1.
41 You can set the environment variable in your shell prior to running
43 your script (with SET or EXPORT or whatever), or else you can set it in
44 your script by putting this at the top of the script:
45 BEGIN { $ENV{DBI_SQL_NANO} = 1 }
47 Supported SQL syntax
49 Here's a pseudo-BNF. Square brackets [] indicate optional items;
51 Angle brackets <> indicate items defined elsewhere in the BNF.
52 statement ::=
54 DROP TABLE [IF EXISTS] <table_name>
55 ⎪ CREATE TABLE <table_name> <col_def_list>
56 ⎪ INSERT INTO <table_name> [<insert_col_list>] VALUES <val_list>
57 ⎪ DELETE FROM <table_name> [<where_clause>]
58 ⎪ UPDATE <table_name> SET <set_clause> <where_clause>
59 ⎪ SELECT <select_col_list> FROM <table_name> [<where_clause>]
60 [<order_clause>]
61 the optional IF EXISTS clause ::=
63 * similar to MySQL - prevents errors when trying to drop
64 a table that doesn't exist
65 identifiers ::=
67 * table and column names should be valid SQL identifiers
68 * especially avoid using spaces and commas in identifiers
69 * note: there is no error checking for invalid names, some
70 will be accepted, others will cause parse failures
71 table_name ::=
73 * only one table (no multiple table operations)
74 * see identifier for valid table names
75 col_def_list ::=
77 * a parens delimited, comma-separated list of column names
78 * see identifier for valid column names
79 * column types and column constraints may be included but are ignored
80 e.g. these are all the same:
81 (id,phrase)
82 (id INT, phrase VARCHAR(40))
83 (id INT PRIMARY KEY, phrase VARCHAR(40) NOT NULL)
84 * you are *strongly* advised to put in column types even though
85 they are ignored ... it increases portability
86 insert_col_list ::=
88 * a parens delimited, comma-separated list of column names
89 * as in standard SQL, this is optional
90 select_col_list ::=
92 * a comma-separated list of column names
93 * or an asterisk denoting all columns
94 val_list ::=
96 * a parens delimited, comma-separated list of values which can be:
97 * placeholders (an unquoted question mark)
98 * numbers (unquoted numbers)
99 * column names (unquoted strings)
100 * nulls (unquoted word NULL)
101 * strings (delimited with single quote marks);
102 * note: leading and trailing percent mark (%) and underscore (_)
103 can be used as wildcards in quoted strings for use with
104 the LIKE and CLIKE operators
105 * note: escaped single quote marks within strings are not
106 supported, neither are embedded commas, use placeholders instead
107 set_clause ::=
109 * a comma-separated list of column = value pairs
110 * see val_list for acceptable value formats
111 where_clause ::=
113 * a single "column/value <op> column/value" predicate, optionally
114 preceded by "NOT"
115 * note: multiple predicates combined with ORs or ANDs are not supported
116 * see val_list for acceptable value formats
117 * op may be one of:
118 < > >= <= = <> LIKE CLIKE IS
119 * CLIKE is a case insensitive LIKE
120 order_clause ::= column_name [ASC⎪DESC]
122 * a single column optional ORDER BY clause is supported
123 * as in standard SQL, if neither ASC (ascending) nor
124 DESC (descending) is specified, ASC becomes the default
125
127 Tim Bunce provided the original idea for this module, helped me out of
128 the tangled trap of namespace, and provided help and advice all along
129 the way. Although I wrote it from the ground up, it is based on Jochen
130 Weidmann's orignal design of SQL::Statement, so much of the credit for
131 the API goes to him.
132
134 This module is written and maintained by
135 Jeff Zucker < jzucker AT cpan.org >
137 Copyright (C) 2004 by Jeff Zucker, all rights reserved.
139 You may freely distribute and/or modify this module under the terms of
141 either the GNU General Public License (GPL) or the Artistic License, as
142 specified in the Perl README file.
143perl v5.8.8 2006-02-07 DBI::SQL::Nano(3)