Skip site navigation (1) Skip section navigation (2)

Re: Parsing problem when launching SQL files with Ant (through

From: Oliver Jowett <oliver(at)opencloud(dot)com>
To: Olivier Hubaut <olivier(at)amaze(dot)ulb(dot)ac(dot)be>
Cc: pgsql-jdbc(at)postgresql(dot)org
Subject: Re: Parsing problem when launching SQL files with Ant (through
Date: 2004-05-21 07:52:03
Message-ID: 40ADB523.9010506@opencloud.com (view raw or flat)
Thread:
Lists: pgsql-jdbc
Olivier Hubaut wrote:
> Using
> - Ant 1.6.1
> - PostgreSQL 7.4.2
> - PostgreSQL 7.4.2 JDBC Driver
> 
> We detected that when launching SQL script files with Ant (using the 
> sql  task), a parsing problem occurs when those files contain comments.
> 
> We found a VERY ugly workaround: positionning the semi-colons at the  
> beginnig of the lines instead of the end.
> 
> [snippet]
> -- 
> -- This code does NOT work with this comment
> -- 
> CREATE FUNCTION foo.bar (VARCHAR(256))
>     RETURNS integer
>     LANGUAGE SQL
>     AS '
>         DELETE FROM foo.bar_table WHERE field = $1;
>         SELECT 1
>     '
> ;

By default, Ant's sql task uses ';' as a query delimiter (see the Ant 
manual). Also, Ant doesn't appear to check for string constants when 
breaking up the input file -- all semicolons are treated as delimiters 
regardless of where they occur. So this is being given to the driver as 
two queries:

query 1: CREATE FUNCTION foo.bar (VARCHAR(256)) RETURNS integer LANGUAGE 
SQL AS 'DELETE FROM foo.bar_table WHERE field = $1

query 2: SELECT 1'

and things break horribly.

If you specify a different delimiter via the delimiter attribute of the 
sql task, the above example works fine.

> [snippet]
> -- 
> -- This code works with this comment
> -- 
> CREATE FUNCTION foo.bar (VARCHAR(256))
>     RETURNS integer
>     LANGUAGE SQL
>     AS '
>     ; DELETE FROM foo.bar_table WHERE field = $1
>     ; SELECT 1'
> ;

I don't know why this works (it shouldn't!) -- but for some reason Ant 
submits this as a single query even with delimiter=";".

You can see the exact queries being submitted to the server by setting 
"log_statement = true" in postgresql.conf.

-O

In response to

pgsql-jdbc by date

Next:From: Oliver JowettDate: 2004-05-21 07:54:14
Subject: Re: Timeouts on big queries with JDBC?
Previous:From: Andrea AimeDate: 2004-05-21 06:14:58
Subject: Re: Queries with large ResultSets

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group