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

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