Thread: error: unterminated dollar-quoted string with 8.2-505.jdbc3.jar?

After digging through the archives and looking at the changelog for
8.2-505 and 8.2-504, it seems that $$ should work, but I'm still
getting errors.

Here's the error message:
Caused by: org.postgresql.util.PSQLException: ERROR: unterminated
dollar-quoted string at or near "$BODY$ BEGIN DELETE FROM ..."

Here's the procedure I'm trying to create (slightly altered for NDA reasons)

CREATE FUNCTION schema.table( del_id INTEGER )
    RETURNS BOOLEAN AS
$BODY$
BEGIN
    DELETE FROM schema.table
        WHERE id = del_id;
    RETURN TRUE;
END
$BODY$
LANGUAGE plpgsql;

Any particular reason I shouldn't be able to execute that with the
latest version (8.2-505) of the driver? I had thought $ quotes were
fixed
(from the changelog: )
"Add support for parsing dollar quotes and comments. (jurka) Thanks to
Michael Paesold".

Any help/suggestions appreciated.

Thanks,

Bucky

On Fri, 4 May 2007, B. Jordan wrote:

> After digging through the archives and looking at the changelog for
> 8.2-505 and 8.2-504, it seems that $$ should work, but I'm still
> getting errors.
>
> Here's the error message:
> Caused by: org.postgresql.util.PSQLException: ERROR: unterminated
> dollar-quoted string at or near "$BODY$ BEGIN DELETE FROM ..."
>
> Any particular reason I shouldn't be able to execute that with the
> latest version (8.2-505) of the driver?

Are you sure you don't have multiple versions of the driver around and
it's selecting an older version?  The error above is exactly what I'd
expect to see with a pre-8.2 driver.  You could also enable statement
logging on the server to see exactly what the driver is sending to it.

Kris Jurka

Kris,

Thanks for the quick reply- I meant to include in my original email
that I tried it via the following mechanisms:
- maven's sql executor plugin
- eclipse quantumDb plugin

and yes I did verify that they are using the correct version of the
driver (since Maven is notorious for causing confusion with
dependencies).

Turns out, both of the above tools parse statements based on
semi-colons (for purposes of "autocommit"- to run each statement
individually), which won't work for stored procedures/functions.

I wrote a little driver program to test it out by sending in the
entire text of my script file in as one statement, and it worked fine
with both the 8.2-505 and 8.2-504 drivers.

Sorry for the bother, but I figured I'd post my findings just in case
anyone else runs into a similar issue.

Thanks,

Bucky

On 5/4/07, Kris Jurka <books@ejurka.com> wrote:
>
>
> On Fri, 4 May 2007, B. Jordan wrote:
>
> > After digging through the archives and looking at the changelog for
> > 8.2-505 and 8.2-504, it seems that $$ should work, but I'm still
> > getting errors.
> >
> > Here's the error message:
> > Caused by: org.postgresql.util.PSQLException: ERROR: unterminated
> > dollar-quoted string at or near "$BODY$ BEGIN DELETE FROM ..."
> >
> > Any particular reason I shouldn't be able to execute that with the
> > latest version (8.2-505) of the driver?
>
> Are you sure you don't have multiple versions of the driver around and
> it's selecting an older version?  The error above is exactly what I'd
> expect to see with a pre-8.2 driver.  You could also enable statement
> logging on the server to see exactly what the driver is sending to it.
>
> Kris Jurka
>
>