Re: Problems with semicolon trying to create a trigger function via jdbc
От | Collin Peters |
---|---|
Тема | Re: Problems with semicolon trying to create a trigger function via jdbc |
Дата | |
Msg-id | 7a8b7ba31003251443v74fe6df1mc412e0c03ba4cf3c@mail.gmail.com обсуждение исходный текст |
Ответ на | Re: Problems with semicolon trying to create a trigger function via jdbc (Maciek Sakrejda <msakrejda@truviso.com>) |
Ответы |
Re: Problems with semicolon trying to create a trigger
function via jdbc
Re: Problems with semicolon trying to create a trigger function via jdbc |
Список | pgsql-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@truviso.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@truviso.com > www.truviso.com > > > > On Thu, Mar 25, 2010 at 1:19 PM, Collin Peters > <cpeters@intouchtechnology.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@postgresql.org) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-jdbc >> >
В списке pgsql-jdbc по дате отправления:
Предыдущее
От: Maciek SakrejdaДата:
Сообщение: Re: Problems with semicolon trying to create a trigger function via jdbc
Следующее
От: Kris JurkaДата:
Сообщение: Re: Problems with semicolon trying to create a trigger function via jdbc