Re: 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(at)postgresql(dot)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 circulated or used for any purpose other than for what is stated. If you have received this message erroneously, please notify the originator immediately. If you are not the intended recipient, you are notified that you are strictly prohibited from using, copying, altering, or disclosing the contents of this message and are called upon to delete the same from your system. Any contradictory action by you may invite penal consequences. Vihaan Networks Limited accepts no responsibility for loss or damage arising from the use of the information transmitted by this email including but not limited to damage from virus."
The information contained in this e-mail is private & confidential and may also be legally privileged. If you are not the intended recipient, please notify us, preferably by e-mail, and do not read, copy or disclose the contents of this message to anyone.