FAQ
I've just spent a while sorting out a program which uses DBD-CSV and
which broke when moved to a new machine with updated versions of most Perl
modules, reporting

SQL ERROR: Bad predicate: '^1^'!

The problem appears to be that the current version of SQL-Statement
(1.09) can't parse

SELECT col FROM tbl WHERE ((c1 = 1 OR c1 = 2) AND c2 = 1)

while the earlier version that I was using (0.1021) could. Both are happy
with

SELECT col FROM tbl WHERE (c1 = 1 OR c1 = 2) AND c2 = 1

I've no idea if the former is actually legal and/or supposed to be
supported by SQL-Statement but I thought it worth mentioning. The program
below (mildly adapted from the POD example) demonstrates the problem.

--cut--
#!/usr/bin/perl

use strict;
use warnings;

use SQL::Statement;

print "Using version $SQL::Statement::VERSION\n\n";

# Create a parser
my($parser) = SQL::Parser->new('Ansi');

# Parse an SQL statement
$@ = '';
my ($stmt) = eval {
SQL::Statement->new
("SELECT col FROM tbl WHERE ((c1 = 1 OR c1 = 2) AND c2 = 1)",$parser);
};
if ($@) {
die "Cannot parse statement: $@";
}
--cut--

Jon.

--
Jon Warbrick
Web/News Development, Computing Service, University of Cambridge

Search Discussions

  • Dean Arnold at Feb 21, 2005 at 4:31 pm

    Jon Warbrick wrote:
    I've just spent a while sorting out a program which uses DBD-CSV and
    which broke when moved to a new machine with updated versions of most
    Perl modules, reporting

    SQL ERROR: Bad predicate: '^1^'!

    The problem appears to be that the current version of SQL-Statement
    (1.09) can't parse

    SELECT col FROM tbl WHERE ((c1 = 1 OR c1 = 2) AND c2 = 1)

    while the earlier version that I was using (0.1021) could. Both are
    happy with

    SELECT col FROM tbl WHERE (c1 = 1 OR c1 = 2) AND c2 = 1

    I've no idea if the former is actually legal and/or supposed to be
    supported by SQL-Statement but I thought it worth mentioning. The
    program below (mildly adapted from the POD example) demonstrates the
    problem.
    How timely...

    Jeff Z. and I are working on a SQL-Statement update that should
    fix your paren problems, plus provide a few more new features,
    so stay tuned.

    Regards,
    Dean Arnold
    Presicient Corp.
  • Jeff Zucker at Feb 21, 2005 at 4:42 pm

    Dean Arnold wrote:

    How timely...

    Jeff Z. and I are working on a SQL-Statement update that should
    fix your paren problems, plus provide a few more new features,
    so stay tuned.

    And speaking of which, let me publicly thank Dean for pitching in on
    SQL::Parser and Robert Rothenberg and Dan Wright for recent patches and
    to many others for previous suggestions and reports.

    There should be a version available for testing in the next week or two
    and a release hopefully soon thereafter.

    --
    Jeff
  • Jeff Zucker at Feb 21, 2005 at 4:35 pm

    Jon Warbrick wrote:

    The problem appears to be that the current version of SQL-Statement
    (1.09) can't parse

    SELECT col FROM tbl WHERE ((c1 = 1 OR c1 = 2) AND c2 = 1)
    Yes, the module has some problems with certain kinds of parenthetical
    statements, I'm working on it. In particular, it doesn't like parens on
    the outside of statements (though they're certainly legal).
    while the earlier version that I was using (0.1021) could.

    The 0.x series of SQL::Statement currently handles parentheses better
    than the 1.x series. OTOH, the 1.x series handles functions, joins,
    aliases, and many other features the 0.x series did not. In the long
    run, I'll be converting the parsing to Parse::Yapp which will eliminate
    the parentheses problems. In the shorter term, I'll try to patch some
    leaks and recommend that you simplify parentheses where possible.

    Thanks for the report.

    --
    Jeff
  • Jeff Zucker at Feb 21, 2005 at 7:21 pm
    [Copied to dbi-dev, but please reply to dbi-users]

    Since the cat is out of the bag on upcoming SQL::Statement changes,
    here's a preview. Comments on the proposed syntax will be much appreciated.

    The major additions will be column name aliases (thanks Robert
    Rothenberg), improved parsing (thanks Dean Arnold), and expanded support
    for functions including support for user-defined functions. Here's the
    syntax I am thinking of, please comment.

    $dbh->do("CREATE FUNCTION foo");
    # pre-declares function foo, a perl subroutine in current package

    $dbh->do("CREATE FUNCTION foo AS Bar::baz");
    # pre-declares function foo, using baz, a subroutine in package Bar

    $dbh->do("LOAD Qux::Quimble");
    # pre-declares all functions in package Qux::Quimble named
    SQL_FUNCTION_x
    where x is all upper case letters or underscore

    $dbh->do("DROP FUNCTION foo")
    # unloads a function - e.g. if you want the parser to fail when it finds
    a function not supported by your dialect

    Functions, once loaded can be used almost anywhere that a value, column,
    or table can be used in SQL (assuming that the function returns the
    appropriate values(s) for its context). This means that functions can
    work as sub queries, predicates and procedures in addition to working as
    functions.

    $sth = $dbh->prepare("SELECT MyFunc(args)");
    $sth = $dbh->prepare("SELECT * FROM MyFunc(args) WHERE ...");
    $sth = $dbh->prepare("SELECT * FROM TableZ WHERE MyFunc(args) AND ...");
    $sth = $dbh->prepare("SELECT * FROM TableZ WHERE colX < MyFunc(args) AND
    ...");

    --
    Jeff
  • Michael A Chase tech at Feb 21, 2005 at 10:51 pm

    On 02/21/2005 02:23 PM, Jeff Zucker said:

    $dbh->do("CREATE FUNCTION foo");
    # pre-declares function foo, a perl subroutine in current package

    $dbh->do("CREATE FUNCTION foo AS Bar::baz");
    # pre-declares function foo, using baz, a subroutine in package Bar
    Since AS usually introduces an alias, this might make more sense as:

    $dbh -> do( "CREATE FUNCTION Bar::baz AS foo" );
    # predeclares function foo, using baz, a subroutine in package Bar

    --
    Mac :})
    ** I usually forward private questions to the appropriate mail list. **
    Ask Smarter: http://www.catb.org/~esr/faqs/smart-questions.html
    Give a hobbit a fish and he eats fish for a day.
    Give a hobbit a ring and he eats fish for an age.
  • Tim Bunce at Feb 22, 2005 at 9:38 am

    On Mon, Feb 21, 2005 at 02:52:03PM -0500, Michael A Chase tech wrote:
    On 02/21/2005 02:23 PM, Jeff Zucker said:
    $dbh->do("CREATE FUNCTION foo");
    # pre-declares function foo, a perl subroutine in current package

    $dbh->do("CREATE FUNCTION foo AS Bar::baz");
    # pre-declares function foo, using baz, a subroutine in package Bar
    Since AS usually introduces an alias, this might make more sense as:

    $dbh -> do( "CREATE FUNCTION Bar::baz AS foo" );
    # predeclares function foo, using baz, a subroutine in package Bar
    Actually the best fit with the SQL 1999 standard would be
    (in increasing verbosity of optional items):

    CREATE FUNCTION foo EXTERNAL
    CREATE FUNCTION foo EXTERNAL NAME "Bar::baz"
    CREATE FUNCTION foo LANGUAGE Perl EXTERNAL NAME "Bar::baz"

    Tim.

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
groupdbi-users @
categoriesperl
postedFeb 21, '05 at 10:02a
activeFeb 22, '05 at 9:38a
posts7
users5
websitedbi.perl.org

People

Translate

site design / logo © 2022 Grokbase