Thread: " 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.
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.

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