Обсуждение: Parsing problem when launching SQL files with Ant (through JDBC driver)

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
    '
;


[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'
;

Any idea?

Thanks a lot in advance!

Olivier


--
Downloading signature ... 99%
*CRC FAILED*
signature aborted

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