Thread: Problem with dollar-quoted CREATE OR REPLACE FUNCTION
Hi All, I am currently using the beta 2 dev 3 binary for 8.0 on WinXP. I am using the pgdev.305.jdbc3.jar and eclipse for Java dev. I have a CREATE OR REPLACE FUNCTION that uses $BODY$ to start and finish the body of the function. When submitting that through jdbc it complains: ERROR: unterminated dollar-quoted string at or near "$BODY$ LANGUAGE 'plpgsql'" If I copy and paste that create function statement and paste it in pgAdmin, it works fine. Any ideas? Tim
Can you show us the code, logs? Dave On Tue, 2004-09-14 at 10:30, Tim Penhey wrote: > Hi All, > > I am currently using the beta 2 dev 3 binary for 8.0 on WinXP. > I am using the pgdev.305.jdbc3.jar and eclipse for Java dev. > > I have a CREATE OR REPLACE FUNCTION that uses $BODY$ to start and finish > the body of the function. > When submitting that through jdbc it complains: > > ERROR: unterminated dollar-quoted string at or near "$BODY$ LANGUAGE > 'plpgsql'" > > If I copy and paste that create function statement and paste it in > pgAdmin, it works fine. > > Any ideas? > > Tim > > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org -- Dave Cramer 519 939 0336 ICQ # 14675561 www.postgresintl.com
Dave Cramer wrote:
>Can you show us the code, logs?
>
>Dave
>
>
CREATE OR REPLACE FUNCTION quote_test(first INTEGER, second INTEGER)
RETURNS INTEGER AS $BODY$
DECLARE
result INTEGER;
BEGIN
result := first + second;
RETURN result;
END;
$BODY$
LANGUAGE 'plpgsql';
This gives the error in eclipse. Didn't mention before but using
MyEclipseIDE plugin for DB access in Eclipse.
Also, just noticed the jdbc directory with the 8.0 beta. Should I be
using that instead of the pgdev one?
It has pg74.214.jdbc1.jar, pg74.214.jdbc2.jar, pg74.214.jdbc2ee.jar,
pg74.214.jdbc3.jar. Would these be better?
Tim
Tim, Ideally you should look in the logs, I suspect that if you escaped the $ with \$ it should work. Dave On Tue, 2004-09-14 at 10:55, Tim Penhey wrote: > Dave Cramer wrote: > > >Can you show us the code, logs? > > > >Dave > > > > > > CREATE OR REPLACE FUNCTION quote_test(first INTEGER, second INTEGER) > RETURNS INTEGER AS $BODY$ > DECLARE > result INTEGER; > BEGIN > result := first + second; > RETURN result; > END; > $BODY$ > LANGUAGE 'plpgsql'; > > > This gives the error in eclipse. Didn't mention before but using > MyEclipseIDE plugin for DB access in Eclipse. > > Also, just noticed the jdbc directory with the 8.0 beta. Should I be > using that instead of the pgdev one? > It has pg74.214.jdbc1.jar, pg74.214.jdbc2.jar, pg74.214.jdbc2ee.jar, > pg74.214.jdbc3.jar. Would these be better? > > Tim > -- Dave Cramer 519 939 0336 ICQ # 14675561 www.postgresintl.com
What kind of machine is it ? /var/log/postgres.log ? Dave On Tue, 2004-09-14 at 13:26, Tim Penhey wrote: > Dave Cramer wrote: > > >Tim, > > > >Ideally you should look in the logs, I suspect that if you escaped the $ > >with \$ it should work. > > > >Dave > > > > > Nope, the only thing that \$ did was give me the error: > > ERROR: syntax error at or near "\" > > Where would I find the logs? > > Tim > > -- Dave Cramer 519 939 0336 ICQ # 14675561 www.postgresintl.com
Dave Cramer wrote: >What kind of machine is it ? > >/var/log/postgres.log ? > > Windows XP. Checked the event log, but nothing of any consequence there. Tim
On Tue, 14 Sep 2004, Tim Penhey wrote: > Hi All, > > I am currently using the beta 2 dev 3 binary for 8.0 on WinXP. > I am using the pgdev.305.jdbc3.jar and eclipse for Java dev. > > I have a CREATE OR REPLACE FUNCTION that uses $BODY$ to start and finish > the body of the function. > When submitting that through jdbc it complains: > > ERROR: unterminated dollar-quoted string at or near "$BODY$ LANGUAGE > 'plpgsql'" > The problem is that the jdbc driver does not have any knowledge of dollar quoting. It is trying to split your statement on semi colons and send each piece separately. That is something like "SELECT 1; SELECT 2" would be sent as two individual queries by the driver. The driver needs to do the splitting to use the V3 extended query protocol. Your options seem to be: - don't use dollar quoting - use the 7.4 driver which doesn't try to split queries - teach the driver about dollar quoting Making the driver aware of dollar quoting is clearly the best (and hardest) option. Kris Jurka
Kris Jurka wrote: > Your options seem to be: > - don't use dollar quoting > - use the 7.4 driver which doesn't try to split queries > - teach the driver about dollar quoting It's a pity the grammar doesn't let you specify the function body as a parameter, then you could just use a PreparedStatement and avoid the quoting problems altogether.. -O
On Wed, 15 Sep 2004, Oliver Jowett wrote: > Kris Jurka wrote: > > > Your options seem to be: > > - don't use dollar quoting > > - use the 7.4 driver which doesn't try to split queries > > - teach the driver about dollar quoting > > It's a pity the grammar doesn't let you specify the function body as a > parameter, then you could just use a PreparedStatement and avoid the > quoting problems altogether.. Yes and no. The problem with that approach is that you have to know what you're doing ahead of time instead of just blindly passing a query string into Statement.execute(). Consider an admin interface (or say psql) written in java, it should be able to take an arbitrary sql string and run it without problem. This is just another case of the driver not correctly lexing/parsing a query. Also note that the current code does not correctly handle single quotes that are escaped with backslashes. Kris Jurka
Kris Jurka wrote: > > On Wed, 15 Sep 2004, Oliver Jowett wrote: > > >>Kris Jurka wrote: >> >> >>>Your options seem to be: >>> - don't use dollar quoting >>> - use the 7.4 driver which doesn't try to split queries >>> - teach the driver about dollar quoting >> >>It's a pity the grammar doesn't let you specify the function body as a >>parameter, then you could just use a PreparedStatement and avoid the >>quoting problems altogether.. > > > Yes and no. The problem with that approach is that you have to know what > you're doing ahead of time instead of just blindly passing a query string > into Statement.execute(). Well, yes. But it'd still be useful. COMMENT ON is another example where the grammar doesn't let you pass data (not needed at parse/plan time) as a parameter. Our app has to do its own escaping of comment text and glue it into the COMMENT ON query. Yuck. > Consider an admin interface (or say psql) > written in java, it should be able to take an arbitrary sql string and run > it without problem. Depending on the exact interface, the client code may need exactly the same parsing logic as the driver, e.g. to determine the end of a multiline query. Part of the problem is that JDBC is not really set up for multiple-statement queries; they're a driver-specific extension that doesn't interact wonderfully with the standard API. > This is just another case of the driver not correctly > lexing/parsing a query. Also note that the current code does not > correctly handle single quotes that are escaped with backslashes. Sure. It looks like double quotes also aren't handled correctly. I think that parsing code got inherited from the pre-v3 version. -O
yes DISCLAIMER This message and any attachment therewith is privileged, confidential and proprietary in nature and the property of the originator.It is intended solely and exclusively for use by the individual to whom it is addressed. It should not be circulatedor used for any purpose other than for what is stated. If you have received this message erroneously, please notifythe originator immediately. If you are not the intended recipient, you are notified that you are strictly prohibitedfrom using, copying, altering, or disclosing the contents of this message and are called upon to delete the samefrom your system. Any contradictory action by you may invite penal consequences. Vihaan Networks Limited accepts no responsibilityfor loss or damage arising from the use of the information transmitted by this email including but not limitedto damage from virus." The information contained in this e-mail is private & confidential and may also be legally privileged. If you are not theintended recipient, please notify us, preferably by e-mail, and do not read, copy or disclose the contents of this messageto anyone.