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

Thanks for the replies Tom and Maciek,

I wasn't able to get the DriverManager.getConnection() approach to
work (still worked in the unit test, but not the running webapp), but
reverting to the old 'non-dollar' quoting approach worked.

Thanks

Collin

On Thu, Mar 25, 2010 at 2:02 PM, Maciek Sakrejda <msakrejda(at)truviso(dot)com> wrote:
> 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
>>
>