Thread: ODBC does not handle WITH clause
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
It seems the ODBC driver does not deal well with a WITH clause in a
statement:
8<----------------------------------
SQL> select id from generate_series(1,2) as t(id)
+------------+
| id |
+------------+
| 1 |
| 2 |
+------------+
SQLRowCount returns -1
2 rows fetched
SQL> select w.id from (select id from generate_series(1,2) as t(id)) as w
+------------+
| id |
+------------+
| 1 |
| 2 |
+------------+
SQLRowCount returns -1
2 rows fetched
SQL> with w as (select id from generate_series(1,2) as t(id)) select
w.id from w
SQLRowCount returns 0
SQL> select id from generate_series(1,2) as t(id)
[ISQL]ERROR: Could not SQLExecute
8<----------------------------------
The last statement fails (according to the logs) because:
ERROR: cursor "SQL_CUR0x17c4bd0" already exists
At that point the only recovery is ABORT.
8<----------------------------------
SQL> abort
SQLRowCount returns 0
SQL> select id from generate_series(1,2) as t(id)
+------------+
| id |
+------------+
| 1 |
| 2 |
+------------+
SQLRowCount returns -1
2 rows fetched
8<----------------------------------
This is with the latest odbc package on Linux Mint 14, but I have seen
it on a client machine based on current Red Hat as well. Is this a
known issue?
Thanks,
Joe
- --
Joe Conway
credativ LLC: http://www.credativ.us
Linux, PostgreSQL, and general Open Source
Training, Service, Consulting, & 24x7 Support
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.11 (GNU/Linux)
Comment: Using GnuPG with Thunderbird - http://www.enigmail.net/
iQIcBAEBAgAGBQJRm/T1AAoJEDfy90M199hl5ZgQAJ1IIyHggS5IGf+HqRqVizl1
GUqrPP0gK5SzMHWvPacVRZyGyZZogjy7zW6w69I6zlQtxZ++EZCzBe5uSCwVU+Bb
uQ7TsR/RBJoe0ftSDBiID52p3MJp/hbhhNJgi8Pky2GyntvEYuBe4YIFqAocfgts
MSVmU19zujpL1AKXsjUW9Viql+7bk9AT/Qz8RZARtLJ0XbwUuSHWiVphVzY5PuBt
DdjOZ8IzQken2WckvSkRkgkmeked18/3/TqzhApaWJAnOpak6Xl2n5ITre5lnnIa
IMQq6e2tu9UlKPrlNA9Y6gjirDgUqxas9uflqHfWrI0ks/uE8q6oIZ/ZPamnT8/e
kaoIVs6u5pt/zKcIRuKR05oOzdt/ZWzM+8cH+YlrcmNYROikoN4m2K96PHN/MgFb
FUJqAX0mFyrAKObNhN1giqWTpwVsSKkK82jVGGH2CfTNcdPdJlmMx3xvDUk5vMZe
Ocl0EGBV3rK7kdjn1/xcEASve0/WzS/LKVx4uKiqyBJ8we0xrcYXJ4IBZcu2BzC0
c+bqxJ+nDHDEU1dvPcwUZyh4s61OVKU0fbvgxTONizi90DMlf+cTkP4tnrjS5fai
DMxlojL7n6FzCt4oNNsufEqooG3dhcc3Mih0PKVltUhvIcA30LpDGPkl0R376fWV
9r1Et3Z2o/AIrD9LdQ48
=acL+
-----END PGP SIGNATURE-----
Hi Jack, (2013/05/22 7:28), Joe Conway wrote: > -----BEGIN PGP SIGNED MESSAGE----- > Hash: SHA1 > > It seems the ODBC driver does not deal well with a WITH clause in a > statement: > > 8<---------------------------------- > SQL> select id from generate_series(1,2) as t(id) > +------------+ > | id | > +------------+ > | 1 | > | 2 | > +------------+ > SQLRowCount returns -1 > 2 rows fetched . . > SQL> with w as (select id from generate_series(1,2) as t(id)) select > w.id from w > SQLRowCount returns 0 > SQL> select id from generate_series(1,2) as t(id) > [ISQL]ERROR: Could not SQLExecute Thanks for the report. > 8<---------------------------------- > > The last statement fails (according to the logs) because: > ERROR: cursor "SQL_CUR0x17c4bd0" already exists > > At that point the only recovery is ABORT. > > 8<---------------------------------- > > This is with the latest odbc package on Linux Mint 14, but I have seen > it on a client machine based on current Red Hat as well. Is this a > known issue? AFAIK no. Psqlodbc drivers take care of with clause just a little. As for the ERROR message, possibly I found at least one of the cause. Could you test the change when I make a patch? regards, Hiroshi INoue
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 On 05/23/2013 08:15 AM, Hiroshi Inoue wrote: > Psqlodbc drivers take care of with clause just a little. As for the > ERROR message, possibly I found at least one of the cause. Could > you test the change when I make a patch? Sure, absolutely. Joe - -- Joe Conway credativ LLC: http://www.credativ.us Linux, PostgreSQL, and general Open Source Training, Service, Consulting, & 24x7 Support -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.12 (GNU/Linux) Comment: Using GnuPG with undefined - http://www.enigmail.net/ iQIcBAEBAgAGBQJRnlk6AAoJEDfy90M199hl8m8P/12H2hjmHKNc5PuZ7Fvwvrrr xBW8KZE41CitD32dK0+UpaGa3mceTiZsy35zKS/gYAk6uiN6dSa3nkKc7Vj0OO/f ctngY5jbM40eqpRY5FmOJP7FsNhFZ6Q/MXuooTi8gJmyHU1oLuo9AE+G0GyUkvEz sYa8nNVFc6hWvGrX8sETSefeaQPJlZDVOGUFhlQkuBpukyMfRznX9hEDLUxUT7uv F+9ARmGxZl89ehwD6kVk8Yct50oCfvaw3gDdegeT0GbXDpgkbBlw8iYnuor0oUtn uao2brOTsP/tw2Rkr44RgloP2n5uR7yxtkEu2dkWM4XUmdC1khav+YOK2vpXSeHF 4m8N+fv9hR8Ip0EA71qqnOS2GORhHiT2Tw1K7G1rn1t174n+9BBIxt9a61ryUlEt ZOa66kWYYg4gtRgORqh8CA5N2+kzM1oQ6aZlCzju4YTG+6YPBubsKO87EbzzqSRE jnAkTS1hIDmeeYDUfiDTEn/BGsavGsgGb62hjQm3Db2TWi6e6ATHTfREmM5Fb9pN AUaiajTc9jYW1JAUDdgkoga4WIqpFLywVjfyumutr5RALd6v/nYMc5AbnzD5ZUyM xBqUKiDDGxtJSK4HGIZUa0xpi0FtaNeg/mX9N1PjdvYh+z5Ia6P4CQKNSup6fSRf KabCgY1cH6mw5eU+tN4J =ALtq -----END PGP SIGNATURE-----
Hi Joe, (2013/05/24 3:00), Joe Conway wrote: > -----BEGIN PGP SIGNED MESSAGE----- > Hash: SHA1 > > On 05/23/2013 08:15 AM, Hiroshi Inoue wrote: >> Psqlodbc drivers take care of with clause just a little. As for the >> ERROR message, possibly I found at least one of the cause. Could >> you test the change when I make a patch? > > Sure, absolutely. After further investigation I found that more works are needed to support WITH CTE statements properly. We are preparing for the next release. Is it OK to apply a stopgap patch to fix the ERROR message? The patch is a one line patch to the current source git but the patch to 9.1.0200 will be very big. regards, Hiroshi Inoue
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 On 05/24/2013 05:04 PM, Hiroshi Inoue wrote: > After further investigation I found that more works are needed to > support WITH CTE statements properly. > > We are preparing for the next release. Good to hear :-) > Is it OK to apply a stopgap patch to fix the ERROR message? The > patch is a one line patch to the current source git but Maybe, but I think we should also document that CTE statements are not supported at the moment. > the patch to 9.1.0200 will be very big. OK. I'll be happy to help with testing once you are ready. Joe - -- Joe Conway credativ LLC: http://www.credativ.us Linux, PostgreSQL, and general Open Source Training, Service, Consulting, & 24x7 Support -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.12 (GNU/Linux) Comment: Using GnuPG with undefined - http://www.enigmail.net/ iQIcBAEBAgAGBQJRoLG+AAoJEDfy90M199hlQiMQAI0sf7UKzDJTub/SMkcNdSAi khaFaWO9mQdNky15dZtKXk22L7EMAld2gp7L11PiicbSRZ2hGafo+X4r8hB/fT3P 5FNOQ2fhWraTnwb/i92ZEJHCIOuka8JJs0LxL4PYR1mA1kCnmdlGpHlRwMIhC0HM owC9oOGfYhEnvs1cJ+56ONuObD+Fv0HiKaaN+mxPu2niF41pkkZKBIQGlACgcTLA 0x2epAl2rjwYSbCTZvncQIpG43jg7YjHQr9NJUvFMndh78xepJfy8TkcEjxNyiyM amA5u51GJts9TzhseB3vkRn7NqkbVSUGQtYcnN8QDekaNUrMOFtkY9eSRDn/2FsJ nnwfj8rvThA5a4viaJsq08JuLOjfbwKzXuvJ33rsri/MFqg8B+FYcPKKgU/18dik y137vvD0mTiBdM6GuF8Y2nZJenQkzDc2sMplIcLfnE7f93jEpylv/VTfnIB+ngia pgTSa6gUFUm8YZvwnha/y66PPtteTtyLDVDwI7Zj1sYes0kfkTJ98qufVnFSDI7z Mwi4viEGUbsDl44NKTFS4cQjzJbqBZNHAjRNp9hE5/KB51blfq6BW/wC+S4g+5eF SxSJQR5CjnnTU8yEKJr0dYvFD3q5DWTNRWD9ikkSaem3EuYlK8LH9LC4hw5hwDoI A/xOWysupzhLh0k4WNAc =B+Wp -----END PGP SIGNATURE-----