Skip site navigation (1) Skip section navigation (2)

" for read only" clause appended for safety when UseDeclareFetch=1 breaks some sql statements with trailing semicolons

From: Jack Wilson <ljwilson(at)digitalav(dot)com>
To: pgsql-odbc(at)postgresql(dot)org
Subject: " for read only" clause appended for safety when UseDeclareFetch=1 breaks some sql statements with trailing semicolons
Date: 2008-03-05 17:24:27
Message-ID: OF0B65CC2A.83351659-ON85257403.005E4A54-85257403.005F978C@digitalav.com (view raw or flat)
Thread:
Lists: pgsql-odbc
I've found the change made in version psqlodbc 8.2.0501 and later 
(reference: 
http://archives.postgresql.org/pgsql-committers/2007-10/msg00420.php)

4. Be more careful about <for locking clause> in UseDeclareFetch mode.
   Add missing? "for read only" clause for read only queries for 8.3
   or later servers for safety.

Here is a cvs link to the actual code change:
http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/psqlodbc/psqlodbc/convert.c.diff?r1=1.163&r2=1.164

This change breaks existing code that sends a single sql statement with a 
trailing semicolon. What happens is the odbc driver sees UseDeclareFetch=1 
and for sql statements appends " for read only" at the end of the 
statement. This is fine except for the situation where the statement has a 
trailing semicolon. In that case the statement ends up being (say) "select 
blah blah; for read only", which gives a syntax error like:

ERROR: syntax error at or near "for"
SQL state: 42601

Setting UseDeclareFetch=0 fixes the problem, or using psqlodbc 8.2.0500 or 
earlier, or using PostgreSQL 8.2.6--any one of these prevents the " for 
read only" from being appended to the end of the sql statement.

The quick fix was to remove the trailing semicolons in my code, but a 
better fix would be to make the parser smart enough to put the " for read 
only" clause in front of an existing semicolon, not after it.

Thanks...jack
-- 
********************************************
Who:    L Jack Wilson
Where:  ljwilson(at)dNiOgSiPtAaMlav(dot)com
How:    Remove Capital Letters from above for a valid email address
Why:    Standard Disclaimer fits nicely here.

Responses

pgsql-odbc by date

Next:From: Hiroshi InoueDate: 2008-03-06 02:24:53
Subject: Re: " for read only" clause appended for safety when UseDeclareFetch=1 breaks some sql statements with trailing semicolons
Previous:From: Hiroshi SaitoDate: 2008-03-05 14:25:31
Subject: Re: Build and other trouble with 08.03.0100 on Linux

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group