" for read only" clause appended for safety when UseDeclareFetch=1 breakssome sql statemen

This is a multipart message in MIME format.
--=_alternative 005F978985257403_=
Content-Type: text/plain; charset="US-ASCII"

I've found the change made in version psqlodbc 8.2.0501 and later
(reference:
http://archives.postgresql.org/pgsql-committers/2007-10/msg0 0420.php)

4. Be more careful about 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/psqlodb c/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.
--=_alternative 005F978985257403_=
Content-Type: text/html; charset="US-ASCII"



I've found the change made in version
psqlodbc 8.2.0501 and later (reference: http://archives.postgresql.org/pgsql-committers/2007-10/msg0 0420.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/psqlodb c/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.
--=_alternative 005F978985257403_=--

Re: " for read only" clause appended for safety when UseDeclareFetch=1breaks some sql stat

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/msg0 0420.php)
>
> 4. Be more careful about 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/psqlodb c/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


--
Sent via pgsql-odbc mailing list (pgsql-odbc@postgresql.org)
To make changes to your subscription:
http://mail.postgresql.org/mj/mj_wwwusr?domain=postgresql.or g&extra=pgsql-odbc

Re: " for read only" clause appended for safety when UseDeclareFetch=1breaks some sql stat

This is a multipart message in MIME format.
--=_alternative 0048B57185257404_=
Content-Type: text/plain; charset="US-ASCII"

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.
--=_alternative 0048B57185257404_=
Content-Type: text/html; charset="US-ASCII"



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.
--=_alternative 0048B57185257404_=--

Re: " for read only" clause appended for safety when UseDeclareFetch=1breaks some sql stat

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

--
Sent via pgsql-odbc mailing list (pgsql-odbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-odbc

Re: " for read only" clause appended for safety when UseDeclareFetch=1breaks some sql stat

This is a multipart message in MIME format.
--=_alternative 006CE95C85257405_=
Content-Type: text/plain; charset="US-ASCII"

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.
--=_alternative 006CE95C85257405_=
Content-Type: text/html; charset="US-ASCII"



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.
--=_alternative 006CE95C85257405_=--

Re: Re: " for read only" clause appended for safety when UseDeclareFetch=1breaks some sql

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.

--
Sent via pgsql-odbc mailing list (pgsql-odbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-odbc

Re: Re: " for read only" clause appended for safety when UseDeclareFetch=1breaks some sql

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.
>


--
Sent via pgsql-odbc mailing list (pgsql-odbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-odbc

Re: Re: " for read only" clause appended for safety when UseDeclareFetch=1breaks some sql

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


--
Sent via pgsql-odbc mailing list (pgsql-odbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-odbc