Thread: Problems with semicolon trying to create a trigger function via jdbc
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
Collin Peters <cpeters@intouchtechnology.com> writes: > 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. Not really an expert, but there are some versions of the jdbc driver that don't understand dollar-quoted strings --- try using more conventional quoting and see if it works. regards, tom lane
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 >
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 >> >
On Thu, 25 Mar 2010, Collin Peters wrote: > 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. > I'd bet you have two different driver versions deployed to your webapp and you're getting an old version which doesn't understand dollar quotes. Try System.out.println(Class.forName("org.postgresql.Driver").getResource("/org/postgresql/Driver.class")); Kris Jurka
Hmm. I'm curious as to what your web app is doing differently. If you're getting the connection directly from the DriverManager, I don't believe it would be proxied (although perhaps I'm wrong here--maybe some other driver is registering as a proxy for the jdbc:postgresql subprotocol?). Can you try to find out the actual type of the Connection you get? I think if it is a PGConnection, this implies an issue with the driver, and if that is the case, it'd be nice to figure it out and fix it. Are there differences in your JUnit environment and webapp environment? Jdbc driver versions? JRE versions? Anything else that's different? --- 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 2:43 PM, Collin Peters <cpeters@intouchtechnology.com> wrote: > 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 >>> >> >
On Thu, Mar 25, 2010 at 3:31 PM, Kris Jurka <books@ejurka.com> wrote: > > > On Thu, 25 Mar 2010, Collin Peters wrote: > >> 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. >> > > I'd bet you have two different driver versions deployed to your webapp and > you're getting an old version which doesn't understand dollar quotes. > > Try > System.out.println(Class.forName("org.postgresql.Driver").getResource("/org/postgresql/Driver.class")); Hmmm... good idea but the driver versions are the same jar:file:/home/collin/.m2/repository/postgresql/postgresql/8.3-603.jdbc3/postgresql-8.3-603.jdbc3.jar!/org/postgresql/Driver.class jar:file:/home/collin/Code/intouch/trunk2/java/intouch-webapp/target/intouch-webapp/WEB-INF/lib/postgresql-8.3-603.jdbc3.jar!/org/postgresql/Driver.class > > Kris Jurka > >
Collin Peters wrote: > 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. > [...] > > 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) I tried your statement with postgresql-8.3-604.jdbc3.jar and postgresql-8.4-701.jdbc4.jar, and it worked on both. Must be something weird... Yours, Laurenz Albe