Обсуждение: " for read only" clause appended for safety when UseDeclareFetch=1 breaks some sql statements with trailing semicolons
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@dNiOgSiPtAaMlav.com
How: Remove Capital Letters from above for a valid email address
Why: Standard Disclaimer fits nicely here.
Hi Jack, Jack Wilson wrote: > > 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. Oops my fault. Unfortunately I'm very busy and please wait for a while. regards, Hiroshi Inoue
Hiroshi,
No problem. I understand perfectly.
Thanks for looking at it!
...jack
--
********************************************
Who: L Jack Wilson
Where: ljwilson@dNiOgSiPtAaMlav.com
How: Remove Capital Letters from above for a valid email address
Why: Standard Disclaimer fits nicely here.
Jack Wilson wrote: > > Hiroshi, > > No problem. I understand perfectly. I can't test my change with 8.3 servers by myself now. Could you try the driver testing for 8.3.0101 at http://www.geocities.jp/inocchichichi/psqlodbc/index.html ? regards, Hiroshi Inoue
Hiroshi,
I get slightly different error now:
Error: 42601 ERROR: syntax error at or near "a";
Here is the sql statement with the trailing semicolon I'm testing with--substitute a valid table name for the word 'my_table' below:
SELECT pg_attribute.attname, pg_attribute.attnotnull, pg_attribute.atttypmod, pg_type.typname
FROM pg_attribute
JOIN pg_class ON pg_attribute.attrelid = pg_class.oid
JOIN pg_namespace ON pg_namespace.oid = pg_class.relnamespace
JOIN pg_type on pg_type.oid = pg_attribute.atttypid
where pg_namespace.nspname = 'public'
AND pg_class.relname = 'my_table' and pg_class.relkind = 'r' and pg_attribute.attnum > 0
ORDER BY pg_attribute.attnum;
Thanks...jack
--
********************************************
Who: L Jack Wilson
Where: ljwilson@dNiOgSiPtAaMlav.com
How: Remove Capital Letters from above for a valid email address
Why: Standard Disclaimer fits nicely here.
Re: Re: " for read only" clause appended for safety when UseDeclareFetch=1 breaks some sql statements with trailing semicolons
От
alepaes - aldeia digital
Дата:
Hi, Postgresql 8.2.3 have the same problem: <192.168.1.10>ERROR: syntax error at or near "read" at character 487 <192.168.1.10>STATEMENT: declare "SQL_CUR01B5BDE8" cursor for SELECT ct12emp04, ct12numlot, ct12numlan, ct12dtlanc, ct12docume, ct12empcc1, ct12empcc2, ct12empcc3, ct12emptr1, ct12emptr2, ct12emptr3, ct12dtdig, ct12tradu1, ct12ccust1, ct12tradu2, ct12ccust2, ct12tradu3, ct12ccust3, ct12valor, ct12histo1, ct12histo2, ct12histo3, ct12histo4, ct12histo5, ct12histo6, ct12renume, ct12origem FROM CT12T WHERE ct12emp04 = '500' AND ct12numlot = '20080532' AND ct12numlan = '52' FOR UPDATE OF CT12T for read only;fetch 100 in "SQL_CUR01B5BDE8" The check box in page 2 is turned off. Thanks, Alexandre Jack Wilson wrote: > > Hiroshi, > > I get slightly different error now: > > Error: 42601 ERROR: syntax error at or near "a"; > > Here is the sql statement with the trailing semicolon I'm testing > with--substitute a valid table name for the word 'my_table' below: > > SELECT pg_attribute.attname, pg_attribute.attnotnull, > pg_attribute.atttypmod, pg_type.typname > FROM pg_attribute > JOIN pg_class ON pg_attribute.attrelid = pg_class.oid > JOIN pg_namespace ON pg_namespace.oid = pg_class.relnamespace > JOIN pg_type on pg_type.oid = pg_attribute.atttypid > where pg_namespace.nspname = 'public' > AND pg_class.relname = 'my_table' and pg_class.relkind = 'r' and > pg_attribute.attnum > 0 > ORDER BY pg_attribute.attnum; > > Thanks...jack > -- > ******************************************** > Who: L Jack Wilson > Where: ljwilson@dNiOgSiPtAaMlav.com > How: Remove Capital Letters from above for a valid email address > Why: Standard Disclaimer fits nicely here.
Re: Re: " for read only" clause appended for safety when UseDeclareFetch=1 breaks some sql statements with trailing semicolons
От
alepaes - aldeia digital
Дата:
Sorry, I mean: "PGODBC 8.03.0200 have the same problem" alepaes - aldeia digital wrote: > Hi, > > Postgresql 8.2.3 have the same problem: > > <192.168.1.10>ERROR: syntax error at or near "read" at character 487 > <192.168.1.10>STATEMENT: declare "SQL_CUR01B5BDE8" cursor for SELECT > ct12emp04, ct12numlot, ct12numlan, ct12dtlanc, ct12docume, ct12empcc1, > ct12empcc2, ct12empcc3, ct12emptr1, ct12emptr2, ct12emptr3, ct12dtdig, > ct12tradu1, ct12ccust1, ct12tradu2, ct12ccust2, ct12tradu3, > ct12ccust3, ct12valor, ct12histo1, ct12histo2, ct12histo3, ct12histo4, > ct12histo5, ct12histo6, ct12renume, ct12origem FROM CT12T WHERE > ct12emp04 = '500' AND ct12numlot = '20080532' AND ct12numlan = '52' FOR > UPDATE OF CT12T for read only;fetch 100 in "SQL_CUR01B5BDE8" > > The check box in page 2 is turned off. > > Thanks, > > > Alexandre > > > Jack Wilson wrote: >> >> Hiroshi, >> >> I get slightly different error now: >> >> Error: 42601 ERROR: syntax error at or near "a"; >> >> Here is the sql statement with the trailing semicolon I'm testing >> with--substitute a valid table name for the word 'my_table' below: >> >> SELECT pg_attribute.attname, pg_attribute.attnotnull, >> pg_attribute.atttypmod, pg_type.typname FROM pg_attribute JOIN >> pg_class ON pg_attribute.attrelid = pg_class.oid JOIN pg_namespace >> ON pg_namespace.oid = pg_class.relnamespace JOIN pg_type on >> pg_type.oid = pg_attribute.atttypid where pg_namespace.nspname = >> 'public' >> AND pg_class.relname = 'my_table' and pg_class.relkind = 'r' and >> pg_attribute.attnum > 0 ORDER BY pg_attribute.attnum; >> >> Thanks...jack >> -- >> ******************************************** >> Who: L Jack Wilson >> Where: ljwilson@dNiOgSiPtAaMlav.com >> How: Remove Capital Letters from above for a valid email address >> Why: Standard Disclaimer fits nicely here. >
alepaes - aldeia digital wrote: > Sorry, > > I mean: "PGODBC 8.03.0200 have the same problem" > > > alepaes - aldeia digital wrote: >> Hi, >> >> Postgresql 8.2.3 have the same problem: >> >> <192.168.1.10>ERROR: syntax error at or near "read" at character 487 >> <192.168.1.10>STATEMENT: declare "SQL_CUR01B5BDE8" cursor for SELECT >> ct12emp04, ct12numlot, ct12numlan, ct12dtlanc, ct12docume, ct12empcc1, >> ct12empcc2, ct12empcc3, ct12emptr1, ct12emptr2, ct12emptr3, >> ct12dtdig, ct12tradu1, ct12ccust1, ct12tradu2, ct12ccust2, >> ct12tradu3, ct12ccust3, ct12valor, ct12histo1, ct12histo2, >> ct12histo3, ct12histo4, ct12histo5, ct12histo6, ct12renume, >> ct12origem FROM CT12T WHERE ct12emp04 = '500' AND ct12numlot = >> '20080532' AND ct12numlan = '52' FOR UPDATE OF CT12T for read >> only;fetch 100 in "SQL_CUR01B5BDE8" Could you try the driver testing for 8.3.0201 at http://www.geocities.jp/inocchichichi/psqlodbc/index.html ? regards, Hiroshi Inoue