Postgres Pro
Downloads
Home   >   mailing lists

Thread: Exception using dollar-quoted string

Exception using dollar-quoted string

From
"Fichtenelch"
Date:
Hi,

when I'm trying to execute the following command with a java.sql.Statement
an error occurs:

CREATE OR REPLACE FUNCTION test(text) RETURNS void AS $$ DECLARE tmp text;
BEGIN tmp := 'test'; END; $$ LANGUAGE plpgsql;

org.postgresql.util.PSQLException: ERROR: unterminated dollar-quoted string
at or near "$$ DECLARE tmp text"
    at
org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorI
mpl.java:1512)
    at
org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.ja
va:1297)
    at
org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:188)
    at
org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.j
ava:437)
    at
org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2St
atement.java:339)
    at
org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.j
ava:331)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
    at java.lang.reflect.Method.invoke(Unknown Source)
    at
org.postgresql.ds.common.PooledConnectionImpl$StatementHandler.invoke(Pooled
ConnectionImpl.java:467)
    at $Proxy2.execute(Unknown Source)

The command: CREATE OR REPLACE FUNCTION test(text) RETURNS void AS ' DECLARE
tmp text; BEGIN tmp := "test"; END; ' LANGUAGE plpgsql;

works fine, also creating the function with php (phppgadmin). I have the
latest version of the jdbc driver.
Is this a bug, or my fault?

Thanks,
Fichtenelch

Re: Exception using dollar-quoted string

From
Tom Lane
Date:
"Fichtenelch" <hillebra@inf.fu-berlin.de> writes:
> when I'm trying to execute the following command with a java.sql.Statement
> an error occurs:

> CREATE OR REPLACE FUNCTION test(text) RETURNS void AS $$ DECLARE tmp text;
> BEGIN tmp := 'test'; END; $$ LANGUAGE plpgsql;

I'm pretty sure the jdbc driver doesn't understand dollar-quoting yet.
(Patches to fix this would be gratefully accepted no doubt...)

            regards, tom lane

Re: Exception using dollar-quoted string

From
Kris Jurka
Date:

On Fri, 24 Mar 2006, Fichtenelch wrote:

> works fine, also creating the function with php (phppgadmin). I have the
> latest version of the jdbc driver.
> Is this a bug, or my fault?
>

This is a known bug in the JDBC driver.  It does not support dollar
quotes.

Kris Jurka

Re: Exception using dollar-quoted string

From
"Fichtenelch"
Date:
And how are sql commands like

INSERT INTO table VALUES ($t$some text$t$)

(or UPDATE ...)

passed to the database? In that case dollar quotes are working
definitely.

Fichtenelch

> -----Original Message-----
> From: pgsql-jdbc-owner@postgresql.org [mailto:pgsql-jdbc-
> owner@postgresql.org] On Behalf Of Kris Jurka
> Sent: Friday, March 24, 2006 7:29 PM
> To: Fichtenelch
> Cc: pgsql-jdbc@postgresql.org
> Subject: Re: [JDBC] Exception using dollar-quoted string
>
>
>
> On Fri, 24 Mar 2006, Fichtenelch wrote:
>
> > works fine, also creating the function with php (phppgadmin). I have the
> > latest version of the jdbc driver.
> > Is this a bug, or my fault?
> >
>
> This is a known bug in the JDBC driver.  It does not support dollar
> quotes.
>
> Kris Jurka
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend

Re: Exception using dollar-quoted string

From
Kris Jurka
Date:

On Fri, 24 Mar 2006, Fichtenelch wrote:

> And how are sql commands like
>
> INSERT INTO table VALUES ($t$some text$t$)
>
> passed to the database? In that case dollar quotes are working
> definitely.
>

The JDBC driver executes multiple queries within the same string
(Statement.execute("SELECT 1; SELECT 2")) by splitting them on the
semicolon and executing them separately.  So the difference is that your
plpgsql function body has semicolons while your simple example does not.

Kris Jurka