Re: Problems with semicolon trying to create a trigger function via jdbc

The parseQuery() method in QueryExecutorImpl breaks up a query if
you're executing more than one statement in a single JDBC query.
However, it seems to take quotes (and comments) into account. I tried
your simple test case (replacing DriverManager.getConnection() for
ds.getConnection()), and it works fine. I have a feeling that the
DataSource is wrapping the Connection (and Statement) in proxies that
also try to break up the individual queries, but that do not take
dollar-quotes into account.
---
Maciek Sakrejda | Software Engineer | Truviso

1065 E. Hillsdale Blvd., Suite 230
Foster City, CA 94404
(650) 242-3500 Main
(650) 242-3501 F
msakrejda(at)truviso(dot)com
www.truviso.com

On Thu, Mar 25, 2010 at 1:19 PM, Collin Peters
<cpeters(at)intouchtechnology(dot)com> wrote:
> Hi all,
>
> I have some framework code that needs to dynamically generate a
> function.  There seems to be a problem where the SQL gets truncated at
> the first semicolon encountered in the function.  I have tried this
> with a very simple function and duplicated it.
>
> The test trigger function is as follows:
> CREATE OR REPLACE FUNCTION test()
>   RETURNS trigger AS
> $BODY$
> DECLARE
>     foo integer;
> BEGIN
>     foo = 4;
>     RAISE NOTICE 'Foo: %', foo;
> END;
> $BODY$
> LANGUAGE 'plpgsql' VOLATILE
> COST 100;
> ALTER FUNCTION test() OWNER TO mcrtdbms;
>
> The simple test code is:
> String sql = "CREATE OR REPLACE FUNCTION
> history.history_insert_trigger()     RETURNS trigger AS   $BODY$
> DECLARE   foo integer;   BEGIN    foo = 4; RAISE NOTICE 'Foo: %', foo;
>      END;   $BODY$     LANGUAGE 'plpgsql' VOLATILE; ";
>         DataSource ds = getDataSource();
>         try
>         {
>             Connection conn = ds.getConnection();
>             conn.setAutoCommit(true);
>             Statement st = conn.createStatement();
>             st.executeUpdate(sql);
>             st.close();
>             conn.close();
>         }
>         catch (SQLException e)
>         {
>             e.printStackTrace();
>         }
>
> When I try to run this via my webapp I get the error:
> org.postgresql.util.PSQLException: ERROR: unterminated dollar-quoted
> string at or near "$BODY$   DECLARE   foo integer"
>     at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:1591)
>     at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1340)
>     at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:192)
> <snip>
>
> So it seems to be truncating the SQL at the first semicolon it
> encounters which, of course, borks the whole thing.  What is even
> stranger in my quest to get this working is that the above code
> actually WORKS when I run it through a JUnit test!!!  I have made no
> progress in trying to figure out what is different between the unit
> test and the running webapp.  At first I though it was my ORM so I
> tried with the straight JDBC code used above and so eliminated that.
> Now I am trying to determine if the Postgres JDBC driver is at fault.
>
> Here is the rest of the details
>  * JDBC version 8.3-605 JDBC 3
>  * Postgres 8.3
>  * JUnit 4
>  * Application is built w/ Spring (but this manual query shouldn't be
> affected by taht)
>  * When the above query is run through the webapp, it is initially
> triggered by a Quartz (scheduling api) trigger (which runs when the
> webapp starts)
>
> Any help at all would be appreciated!!  I am about to give up and
> write a function which will do the job of creating the trigger for
> me!!
>
> Regards,
> Collin
>
> --
> Sent via pgsql-jdbc mailing list (pgsql-jdbc(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-jdbc
>