Greetings all,
A long time ago, Hiroshi Inoue asked an interesting question about the
declare/fetch option and its default setting of FALSE:
http://archives.postgresql.org/pgsql-interfaces/1999-01/msg00189.php
To which Byron Nikolaidis replied in
http://archives.postgresql.org/pgsql-interfaces/1999-01/msg00194.php
why it was left at false.
Well, I'm pumping today datasets from 7.3.3 using the 7.03.0100 ODBC driver
which contain empty, near empty and really large tables, and I'm seeing very
strange things.
If I don't do anything, I don't have the declare/fetch mechanism in place.
I issue all my requests by following the allocate statement, bind columns,
prepare, execute and then SQLFetch() while SQLFetch doesn't return
SQL_NO_DATA. Things run fine for empty and small tables, and even for
largish tables (until I reach memory implementation limits).
If I set the connection attribute SQL_ATTR_ODBC_CURSORS to
SQL_CUR_USE_IF_NEEDED, and then set the statement's SQL_ATTR_CURSOR_TYPE to
SQL_CURSOR_STATIC, then nothing really different happens. No declare/fetch
either, terrible memory consumption, etc. Just for kicks, I also tried
SQL_CURSOR_FORWARD_ONLY, but no change.
Regardless of what I do with the attributes, if I tick the Declare/Fetch
checkbox in the ODBC data source settings, I get the declare/fetch
behaviour. However, that means that I'm at the mercy of a change of options
in the control panel. Besides, by depending on this (as Byron pointed out in
1999 if that statement is not obsolete today), I'm wedding myself to
SQL_ATTR_CURSOR_SCROLLABLE == SQL_NONSCROLLABLE (it's not really a problem
today, but I don't like to close doors).
But the real problem is the following: for some select queries, I'm not sure
I understood when exactly, I don't get what I expect to: the first SQLFetch
will return an SQL_ERROR + HY010 instead of SQL_NO_DATA or SQL_SUCCESS.
This seems to happen on both empty and non-empty result sets, and it seems
that in both cases the engine successfully performed the query and returned
the results.
My questions:
1. is the HY010 error code for the first SQLFetch() on empty SELECTs,
only in the declare/fetch case, a normal and expected behaviour?
What puzzles me is that some requests ON THE SAME TABLE return
SQL_ERROR+HY010, some return SQL_NO_DATA. Though, in both cases, the same
code path is used, and indeed the HSTMT follows the exact same life cycle!
I don't understand what happens, the only difference I see is that apparently
the failing requests can't follow a straight sequential plan, while the
succeeding ones apparently can (being vanilla SELECTs with no funky clauses
behind). I don't see how that can possibly make a difference, as according
to the communication log, the back-end is perfectly happy with whatever the
ODBC driver throws at it (see appendices A and B).
2. is there a way to avoid ticking the declare/fetch box, yet
getting its effects? Specifically, I can't assume I know what kind of
database engine I'll be coupled with until I identify the database back-end
associated with the ODBC data source I'm using. So obviously, it's a bit
difficult to provide psqlODBC-specific connection string options. Is there a
way to turn on the declare/fetch behaviour on an active connection?
3. is there a way to request the declare/fetch behaviour on a
per-cursor way, without hitting back the problem in point 1? It seemed to me
that some of the statement attributes may make sense as declare/fetch
discriminators (especially for a non-scrollable cursor or a forward-scroll
only cursor), but I guess this isn't implemented today.
Thanks in advance.
-- Cyrille
Appendix A: extracts from a DbgView log file (slightly edited and commented):
------------------------------------------
00000009 15:34:20 [1436] Detected PostgreSQL ODBC driver
00000010 15:34:20 [1436] SQLAllocHandle(STMT) -->012E2048, 0
00000011 15:34:20 [1436] SQLSetStmtAttr(012E2048,STATIC)-->0
00000012 15:34:20 [1436] SQLPrepare(012E2048)-->0 [SELECT lkb.xge FROM lkb
WHERE ((lkb.dgb = 'ek') AND (lkb.fgb = 'ik') AND (lkb.hgb = 'mk_kh')) ORDER
BY lkb.dgb,lkb.fgb,lkb.hgb]
00000013 15:34:20 [1436] bound columns
00000014 15:34:20 [1436] SQLExecute(012E2048)-->0
00000015 15:34:20 [1436] skipping getResultSize, returning 0
00000016 15:34:20 [1436] SQLFetch(012E2048)-->-1
00000017 15:34:20 [1436] SQL Error in Fetch/1: HY010 [Microsoft][ODBC
Driver Manager] Function sequence error
00000018 15:34:20 [1436] bad Fetch/1 was for sql=SELECT lkb.xge FROM lkb
WHERE ((lkb.dgb = 'ek') AND (lkb.fgb = 'ik') AND (lkb.hgb = 'mk_kh')) ORDER
BY lkb.dgb,lkb.fgb,lkb.hgb
00000019 15:34:20 [1436] closeCursor/SQLFreeHandle(012E2048)-->0
00000020 15:34:20 [1436] SQLFreeHandle(012E2048)-->0
00000021 15:34:20 [1436] SQLAllocHandle(STMT) -->012E2048, 0
00000022 15:34:20 [1436] SQLSetStmtAttr(012E2048,STATIC)-->0
00000023 15:34:20 [1436] SQLPrepare(012E2048)-->0 [SELECT lkb.xge FROM lkb
WHERE ((lkb.dgb = 'ek') AND (lkb.fgb = 'ik') AND (lkb.hgb = 'kh_rij')) ORDER
BY lkb.dgb,lkb.fgb,lkb.hgb]
00000024 15:34:20 [1436] bound columns
00000025 15:34:20 [1436] SQLExecute(012E2048)-->0
00000026 15:34:20 [1436] skipping getResultSize, returning 0
00000027 15:34:20 [1436] SQLFetch(012E2048)-->-1
00000028 15:34:20 [1436] SQL Error in Fetch/1: HY010 [Microsoft][ODBC
Driver Manager] Function sequence error
00000029 15:34:20 [1436] bad Fetch/1 was for sql=SELECT lkb.xge FROM lkb
WHERE ((lkb.dgb = 'ek') AND (lkb.fgb = 'ik') AND (lkb.hgb = 'kh_rij')) ORDER
BY lkb.dgb,lkb.fgb,lkb.hgb
00000030 15:34:20 [1436] closeCursor/SQLFreeHandle(012E2048)-->0
00000031 15:34:20 [1436] SQLFreeHandle(012E2048)-->0
*** one can see the problem I have, above ***
00000041 15:34:23 [1436] SQLAllocHandle(STMT) -->012E37B0, 0
00000042 15:34:23 [1436] SQLSetStmtAttr(012E37B0,STATIC)-->0
00000043 15:34:23 [1436] SQLPrepare(012E37B0)-->0 [SELECT
vac.yzb,vac.aac,vac.cac,vac.eac FROM vac ORDER BY vac.aac]
00000044 15:34:23 [1436] bound columns
00000045 15:34:23 [1436] SQLExecute(012E37B0)-->0
00000046 15:34:23 [1436] skipping getResultSize, returning 0
00000047 15:34:23 [1436] SQLFetch(012E37B0)-->-1
00000048 15:34:23 [1436] SQL Error in Fetch/1: HY010 [Microsoft][ODBC
Driver Manager] Function sequence error
00000049 15:34:23 [1436] bad Fetch/1 was for sql=SELECT
vac.yzb,vac.aac,vac.cac,vac.eac FROM vac ORDER BY vac.aac
00000050 15:34:23 [1436] SQLAllocHandle(STMT) -->012E4AE0, 0
00000051 15:34:23 [1436] SQLSetStmtAttr(012E4AE0,STATIC)-->0
00000052 15:34:23 [1436] SQLPrepare(012E4AE0)-->0 [SELECT qob.xge FROM qob
WHERE (qob.azc = 'to') ORDER BY qob.azc]
00000053 15:34:23 [1436] bound columns
00000054 15:34:23 [1436] SQLExecute(012E4AE0)-->0
00000055 15:34:23 [1436] skipping getResultSize, returning 0
00000056 15:34:23 [1436] SQLFetch(012E4AE0)-->-1
00000057 15:34:23 [1436] SQL Error in Fetch/1: HY010 [Microsoft][ODBC
Driver Manager] Function sequence error
00000058 15:34:23 [1436] bad Fetch/1 was for sql=SELECT qob.xge FROM qob
WHERE (qob.azc = 'to') ORDER BY qob.azc
*** it's possible to see that it happens regardless of whether I
have a WHERE clause or not.
00000059 15:34:23 [1436] begin saveNoTimeTable ds
00000060 15:34:23 [1436] SQLAllocHandle(STMT) -->012E5F30, 0
00000061 15:34:23 [1436] SQLSetStmtAttr(012E5F30,STATIC)-->0
00000062 15:34:23 [1436] SQLPrepare(012E5F30)-->0 [SELECT
ds.tre,ds.iic,ds.rq,ds.yzb,ds.vq,ds.xq,ds.zq,ds.br,ds.dr,ds.fr,ds.hr,ds.jr
FROM ds]
00000063 15:34:23 [1436] bound columns
00000064 15:34:23 [1436] about to execute NoTime SELECT
ds.tre,ds.iic,ds.rq,ds.yzb,ds.vq,ds.xq,ds.zq,ds.br,ds.dr,ds.fr,ds.hr,ds.jr
FROM ds
00000065 15:34:23 [1436] SQLExecute(012E5F30)-->0
00000066 15:34:23 [1436] getting results (1)
00000067 15:34:23 [1436] SQLFetch(012E5F30)-->0
00000068 15:34:23 [1436] SQLFetch(012E5F30)-->0
00000069 15:34:23 [1436] SQLFetch(012E5F30)-->0
00000070 15:34:23 [1436] SQLFetch(012E5F30)-->0
00000071 15:34:23 [1436] SQLFetch(012E5F30)-->100
00000072 15:34:23 [1436] absorbing results (1), 4 rows
00000073 15:34:23 [1436] storing results
00000074 15:34:23 [1436] sending progress feedback
00000075 15:34:23 [1436] saveTableFooter ds
00000076 15:34:23 [1436] closeCursor/SQLFreeHandle(012E5F30)-->0
00000077 15:34:23 [1436] SQLFreeHandle(012E5F30)-->0
*** This was the first successful table. It contained a few records.
00000078 15:34:23 [1436] begin saveTimeTable kxs
00000079 15:34:23 [1436] now saving table kxs with time
00000080 15:34:23 [1436] SQLAllocHandle(STMT) -->012E6338, 0
00000081 15:34:23 [1436] SQLSetStmtAttr(012E6338,STATIC)-->0
00000082 15:34:23 [1436] SQLPrepare(012E6338)-->0 [SELECT
kxs.hv,kxs.jv,kxs.lv,kxs.nv,kxs.pv,kxs.rv,kxs.tv,kxs.vv,kxs.xv,kxs.zv,kxs.bw,kxs.molsw,kxs.molvw,kxs.hw,kxs.jw,kxs.uw3sw,kxs.uw3vw,kxs.uw2sw,kxs.uw2vw,kxs.zw2dh,kxs.zw3dh,kxs.dx0,kxs.dx1,kxs.dxt,kxs.hx0,kxs.jx,sf
00000083 15:34:23 [1436] bound columns
00000084 15:34:23 [1436] about to execute Time SELECT
kxs.hv,kxs.jv,kxs.lv,kxs.nv,kxs.pv,kxs.rv,kxs.tv,kxs.vv,kxs.xv,kxs.zv,kxs.bw,kxs.molsw,kxs.molvw,kxs.hw,kxs.jw,kxs.uw3sw,kxs.uw3vw,kxs.uw2sw,kxs.uw2vw,kxs.zw2dh,kxs.zw3dh,kxs.dx0,kxs.dx1,kxs.dxt,kxs.hx0,kxs.jx,kx
00000085 15:34:23 [1436] SQLExecute(012E6338)-->0
00000086 15:34:23 [1436] getting results (1)
00000087 15:34:23 [1436] SQLFetch(012E6338)-->0
00000088 15:34:23 [1436] SQLFetch(012E6338)-->0
00000089 15:34:23 [1436] SQLFetch(012E6338)-->0
00000090 15:34:23 [1436] SQLFetch(012E6338)-->0
00000091 15:34:23 [1436] SQLFetch(012E6338)-->0
00000092 15:34:23 [1436] SQLFetch(012E6338)-->0
00000093 15:34:23 [1436] SQLFetch(012E6338)-->0
00000094 15:34:23 [1436] SQLFetch(012E6338)-->0
00000095 15:34:23 [1436] SQLFetch(012E6338)-->0
00000096 15:34:23 [1436] SQLFetch(012E6338)-->0
00000097 15:34:23 [1436] SQLFetch(012E6338)-->0
00000098 15:34:23 [1436] SQLFetch(012E6338)-->0
00000099 15:34:23 [1436] SQLFetch(012E6338)-->0
00000100 15:34:23 [1436] SQLFetch(012E6338)-->0
00000101 15:34:23 [1436] SQLFetch(012E6338)-->0
00000102 15:34:23 [1436] SQLFetch(012E6338)-->100
00000103 15:34:23 [1436] absorbing results (1), 15 rows
00000104 15:34:23 [1436] storing results
00000105 15:34:23 [1436] sending progress feedback
00000106 15:34:23 [1436] saveTableFooter kxs
00000107 15:34:23 [1436] closeCursor/SQLFreeHandle(012E6338)-->0
00000108 15:34:23 [1436] SQLFreeHandle(012E6338)-->0
00000109 15:34:23 [1436] begin saveNoTimeTable jab
00000110 15:34:23 [1436] SQLAllocHandle(STMT) -->012E3498, 0
00000111 15:34:23 [1436] SQLSetStmtAttr(012E3498,STATIC)-->0
00000112 15:34:23 [1436] SQLPrepare(012E3498)-->0 [SELECT
jab.rbd,jab.rz,jab.tz FROM jab]
00000113 15:34:23 [1436] bound columns
00000114 15:34:23 [1436] about to execute NoTime SELECT
jab.rbd,jab.rz,jab.tz FROM jab
00000115 15:34:23 [1436] SQLExecute(012E3498)-->0
00000116 15:34:23 [1436] getting results (1)
00000117 15:34:23 [1436] SQLFetch(012E3498)-->100
00000118 15:34:23 [1436] absorbing results (1), 0 rows
00000119 15:34:23 [1436] storing results
00000120 15:34:23 [1436] sending progress feedback
00000121 15:34:23 [1436] saveTableFooter jab
00000122 15:34:23 [1436] closeCursor/SQLFreeHandle(012E3498)-->0
00000123 15:34:23 [1436] SQLFreeHandle(012E3498)-->0
*** This table was successful too, and yet it was empty.
*** And now it becomes very strange:
00000203 15:34:23 [1436] begin saveNoTimeTable lkb
00000204 15:34:23 [1436] SQLAllocHandle(STMT) -->012E5F30, 0
00000205 15:34:23 [1436] SQLSetStmtAttr(012E5F30,STATIC)-->0
00000206 15:34:23 [1436] SQLPrepare(012E5F30)-->0 [SELECT
lkb.dgb,lkb.fgb,lkb.hgb,lkb.xge FROM lkb]
00000207 15:34:23 [1436] bound columns
00000208 15:34:23 [1436] about to execute NoTime SELECT
lkb.dgb,lkb.fgb,lkb.hgb,lkb.xge FROM lkb
00000209 15:34:23 [1436] SQLExecute(012E5F30)-->0
00000210 15:34:23 [1436] getting results (1)
00000211 15:34:23 [1436] SQLFetch(012E5F30)-->0
00000212 15:34:23 [1436] SQLFetch(012E5F30)-->0
00000213 15:34:23 [1436] SQLFetch(012E5F30)-->0
00000214 15:34:23 [1436] SQLFetch(012E5F30)-->0
00000215 15:34:23 [1436] SQLFetch(012E5F30)-->0
00000216 15:34:23 [1436] SQLFetch(012E5F30)-->0
00000217 15:34:23 [1436] SQLFetch(012E5F30)-->0
00000218 15:34:23 [1436] SQLFetch(012E5F30)-->0
00000219 15:34:23 [1436] SQLFetch(012E5F30)-->0
00000220 15:34:23 [1436] SQLFetch(012E5F30)-->0
00000221 15:34:23 [1436] SQLFetch(012E5F30)-->0
00000222 15:34:23 [1436] SQLFetch(012E5F30)-->0
00000223 15:34:23 [1436] SQLFetch(012E5F30)-->0
00000224 15:34:23 [1436] SQLFetch(012E5F30)-->0
00000225 15:34:23 [1436] SQLFetch(012E5F30)-->0
00000226 15:34:23 [1436] SQLFetch(012E5F30)-->0
00000227 15:34:23 [1436] SQLFetch(012E5F30)-->0
00000228 15:34:23 [1436] SQLFetch(012E5F30)-->0
00000229 15:34:23 [1436] SQLFetch(012E5F30)-->0
00000230 15:34:23 [1436] SQLFetch(012E5F30)-->0
00000231 15:34:23 [1436] SQLFetch(012E5F30)-->0
00000232 15:34:23 [1436] SQLFetch(012E5F30)-->0
00000233 15:34:23 [1436] SQLFetch(012E5F30)-->0
00000234 15:34:23 [1436] SQLFetch(012E5F30)-->0
00000235 15:34:23 [1436] SQLFetch(012E5F30)-->0
00000236 15:34:23 [1436] SQLFetch(012E5F30)-->0
00000237 15:34:23 [1436] SQLFetch(012E5F30)-->0
00000238 15:34:23 [1436] SQLFetch(012E5F30)-->0
00000239 15:34:23 [1436] SQLFetch(012E5F30)-->0
00000240 15:34:23 [1436] SQLFetch(012E5F30)-->0
00000241 15:34:23 [1436] SQLFetch(012E5F30)-->0
00000242 15:34:23 [1436] SQLFetch(012E5F30)-->0
00000243 15:34:23 [1436] SQLFetch(012E5F30)-->0
00000244 15:34:23 [1436] SQLFetch(012E5F30)-->0
00000245 15:34:23 [1436] SQLFetch(012E5F30)-->0
00000246 15:34:23 [1436] SQLFetch(012E5F30)-->0
00000247 15:34:23 [1436] SQLFetch(012E5F30)-->0
00000248 15:34:23 [1436] SQLFetch(012E5F30)-->0
00000249 15:34:23 [1436] SQLFetch(012E5F30)-->0
00000250 15:34:23 [1436] SQLFetch(012E5F30)-->0
00000251 15:34:23 [1436] SQLFetch(012E5F30)-->0
00000252 15:34:23 [1436] SQLFetch(012E5F30)-->0
00000253 15:34:23 [1436] SQLFetch(012E5F30)-->0
00000254 15:34:23 [1436] SQLFetch(012E5F30)-->0
00000255 15:34:23 [1436] SQLFetch(012E5F30)-->0
00000256 15:34:23 [1436] SQLFetch(012E5F30)-->0
00000257 15:34:23 [1436] SQLFetch(012E5F30)-->0
00000258 15:34:23 [1436] SQLFetch(012E5F30)-->0
00000259 15:34:23 [1436] SQLFetch(012E5F30)-->0
00000260 15:34:23 [1436] SQLFetch(012E5F30)-->0
00000261 15:34:23 [1436] SQLFetch(012E5F30)-->0
00000262 15:34:23 [1436] SQLFetch(012E5F30)-->0
00000263 15:34:23 [1436] SQLFetch(012E5F30)-->0
00000264 15:34:23 [1436] SQLFetch(012E5F30)-->0
00000265 15:34:23 [1436] SQLFetch(012E5F30)-->0
00000266 15:34:23 [1436] SQLFetch(012E5F30)-->0
00000267 15:34:23 [1436] SQLFetch(012E5F30)-->0
00000268 15:34:23 [1436] SQLFetch(012E5F30)-->0
00000269 15:34:23 [1436] SQLFetch(012E5F30)-->0
00000270 15:34:23 [1436] SQLFetch(012E5F30)-->0
00000271 15:34:23 [1436] SQLFetch(012E5F30)-->0
00000272 15:34:23 [1436] SQLFetch(012E5F30)-->0
00000273 15:34:23 [1436] SQLFetch(012E5F30)-->0
00000274 15:34:23 [1436] SQLFetch(012E5F30)-->0
00000275 15:34:23 [1436] SQLFetch(012E5F30)-->0
00000276 15:34:23 [1436] SQLFetch(012E5F30)-->0
00000277 15:34:23 [1436] SQLFetch(012E5F30)-->0
00000278 15:34:23 [1436] SQLFetch(012E5F30)-->0
00000279 15:34:23 [1436] SQLFetch(012E5F30)-->0
00000280 15:34:23 [1436] SQLFetch(012E5F30)-->0
00000281 15:34:23 [1436] SQLFetch(012E5F30)-->0
00000282 15:34:23 [1436] SQLFetch(012E5F30)-->0
00000283 15:34:23 [1436] SQLFetch(012E5F30)-->0
00000284 15:34:23 [1436] SQLFetch(012E5F30)-->0
00000285 15:34:23 [1436] SQLFetch(012E5F30)-->0
00000286 15:34:23 [1436] SQLFetch(012E5F30)-->0
00000287 15:34:23 [1436] SQLFetch(012E5F30)-->0
00000288 15:34:23 [1436] SQLFetch(012E5F30)-->0
00000289 15:34:23 [1436] SQLFetch(012E5F30)-->0
00000290 15:34:23 [1436] SQLFetch(012E5F30)-->0
00000291 15:34:23 [1436] SQLFetch(012E5F30)-->0
00000292 15:34:23 [1436] SQLFetch(012E5F30)-->0
00000293 15:34:23 [1436] SQLFetch(012E5F30)-->0
00000294 15:34:23 [1436] SQLFetch(012E5F30)-->0
00000295 15:34:23 [1436] SQLFetch(012E5F30)-->0
00000296 15:34:23 [1436] SQLFetch(012E5F30)-->0
00000297 15:34:23 [1436] SQLFetch(012E5F30)-->0
00000298 15:34:23 [1436] SQLFetch(012E5F30)-->0
00000299 15:34:23 [1436] SQLFetch(012E5F30)-->0
00000300 15:34:23 [1436] SQLFetch(012E5F30)-->0
00000301 15:34:23 [1436] SQLFetch(012E5F30)-->100
00000302 15:34:23 [1436] absorbing results (1), 90 rows
00000303 15:34:23 [1436] storing results
00000304 15:34:23 [1436] sending progress feedback
00000305 15:34:23 [1436] saveTableFooter lkb
00000306 15:34:23 [1436] closeCursor/SQLFreeHandle(012E5F30)-->0
00000307 15:34:23 [1436] SQLFreeHandle(012E5F30)-->0
*** Yes, lkb is the first table attempted above. Yes, the one which
had the first an HY010 on it. Yes, this time the request succeeded.
*** same thing happens for the vac table:
00000438 15:34:24 [1436] begin saveNoTimeTable vac
00000439 15:34:24 [1436] SQLAllocHandle(STMT) -->012E5F30, 0
00000440 15:34:24 [1436] SQLSetStmtAttr(012E5F30,STATIC)-->0
00000441 15:34:24 [1436] SQLPrepare(012E5F30)-->0 [SELECT
vac.yzb,vac.aac,vac.cac,vac.eac FROM vac]
00000442 15:34:24 [1436] bound columns
00000443 15:34:24 [1436] about to execute NoTime SELECT
vac.yzb,vac.aac,vac.cac,vac.eac FROM vac
00000444 15:34:24 [1436] SQLExecute(012E5F30)-->0
00000445 15:34:24 [1436] getting results (1)
00000446 15:34:24 [1436] SQLFetch(012E5F30)-->0
00000447 15:34:24 [1436] SQLFetch(012E5F30)-->100
00000448 15:34:24 [1436] absorbing results (1), 1 rows
00000449 15:34:24 [1436] storing results
00000450 15:34:24 [1436] sending progress feedback
00000451 15:34:24 [1436] saveTableFooter vac
00000452 15:34:24 [1436] closeCursor/SQLFreeHandle(012E5F30)-->0
00000453 15:34:24 [1436] SQLFreeHandle(012E5F30)-->0
Appendix B
----------
Extracts from the psqlodbc_1436.log file (using the same censorship filter):
conn = 22495288, PGAPI_Connect(DSN='LemoineGDB', UID='LemoineUser',
PWD='xxxxx')
Global Options: Version='07.03.0100', fetch=100, socket=4096,
unknown_sizes=0, max_varchar_size=254, max_longvarchar_size=8190
disable_optimizer=0, ksqo=1, unique_index=1,
use_declarefetch=1
text_as_longvarchar=1, unknowns_as_longvarchar=0,
bools_as_char=1 NAMEDATALEN=64
extra_systable_prefixes='dd_;', conn_settings=''
conn_encoding='OTHER'
conn=22495288, query=' '
conn=22495288, query='BEGIN'
conn=22495288, query='declare SQL_CUR01572900 cursor for select version()'
conn=22495288, query='fetch 100 in SQL_CUR01572900'
[ fetched 1 rows ]
[ PostgreSQL version string = 'PostgreSQL 7.3.3 on i686-pc-cygwin,
compiled by GCC gcc (GCC) 3.2 20020927 (prerelease)' ]
[ PostgreSQL version number = '7.3' ]
conn=22495288, query='close SQL_CUR01572900'
conn=22495288, query='COMMIT'
conn=22495288, query='set DateStyle to 'ISO''
conn=22495288, query='declare SQL_CUR01572900 cursor for select oid from
pg_type where typname='lo''
conn=22495288, query='fetch 100 in SQL_CUR01572900'
[ fetched 0 rows ]
conn=22495288, query='close SQL_CUR01572900'
conn=22495288, query='COMMIT'
conn=22495288, query='select pg_client_encoding()'
[ fetched 1 rows ]
[ Client encoding = 'UNICODE' (gse = 6) ]
conn=22495288, query='declare SQL_CUR0157A0C0 cursor for select relname,
nspname, relkind from pg_catalog.pg_class, pg_catalog.pg_namespace where
relkind in ('r', 'v') and relname !~ '^pg_|^dd_' and pg_namespace.oid =
relnamespace order by nspname, relname'
conn=22495288, query='fetch 100 in SQL_CUR0157A0C0'
[ fetched 43 rows ]
conn=22495288, query='close SQL_CUR0157A0C0'
conn=22495288, query='select current_schema()'
[ fetched 1 rows ]
conn=22495288, query='declare SQL_CUR0157A0C0 cursor for select u.nspname,
c.relname, a.attname, a.atttypid, t.typname, a.attnum, a.attlen,
a.atttypmod, a.attnotnull, c.relhasrules, c.relkind from
pg_catalog.pg_namespace u, pg_catalog.pg_class c, pg_catalog.pg_attribute a,
pg_catalog.pg_type t where u.oid = c.relnamespace and (not a.attisdropped)
and c.oid= a.attrelid and a.atttypid = t.oid and (a.attnum > 0) and
c.relname like 'lkb' and u.nspname like 'public' order by u.nspname,
c.relname, attnum'
conn=22495288, query='fetch 100 in SQL_CUR0157A0C0'
[ fetched 4 rows ]
PGAPI_Columns: table='lkb',field_name='dgb',type=1043,name='varchar'
PGAPI_Columns: table='lkb',field_name='fgb',type=1043,name='varchar'
PGAPI_Columns: table='lkb',field_name='hgb',type=1043,name='varchar'
PGAPI_Columns: table='lkb',field_name='xge',type=1043,name='varchar'
conn=22495288, query='close SQL_CUR0157A0C0'
conn=22495288, query='declare SQL_CUR01572900 cursor for SELECT lkb.xge FROM
lkb WHERE ((lkb.dgb = 'ek') AND (lkb.fgb = 'ik') AND (lkb.hgb = 'mk_kh'))
ORDER BY lkb.dgb,lkb.fgb,lkb.hgb'
conn=22495288, query='fetch 100 in SQL_CUR01572900'
[ fetched 1 rows ]
conn=22495288, query='COMMIT'
conn=22495288, query='declare SQL_CUR01572900 cursor for SELECT lkb.xge FROM
lkb WHERE ((lkb.dgb = 'ek') AND (lkb.fgb = 'ik') AND (lkb.hgb = 'kh_rij'))
ORDER BY lkb.dgb,lkb.fgb,lkb.hgb'
conn=22495288, query='fetch 100 in SQL_CUR01572900'
[ fetched 0 rows ]
conn=22495288, query='COMMIT'
conn=22495288, query='declare SQL_CUR01572900 cursor for SELECT lkb.xge FROM
lkb WHERE ((lkb.dgb = 'ek') AND (lkb.fgb = 'ik') AND (lkb.hgb = 'mk_nk'))
ORDER BY lkb.dgb,lkb.fgb,lkb.hgb'
conn=22495288, query='fetch 100 in SQL_CUR01572900'
[ fetched 1 rows ]
conn=22495288, query='COMMIT'
*** I don't see a need to continue; it seems that as far as the back-end
is concerned, there is no problem issuing and fetching the results for the
lkb table (what we see here is the first few queries which returned HY010's)
Appendix C:
-----------
The first query as seen from the debug log (it's another run, but with the
same results):
[1796][[SQLAllocHandle]][1796]PGAPI_AllocStmt: entering...
[1796]**** PGAPI_AllocStmt: hdbc = 22495288, stmt = 22489384
[1796]CC_add_statement: self=22495288, stmt=22489384
[1796][[SQLGetStmtAttr]] Handle=22489384 10010
[1796]PGAPI_GetStmtAttr Handle=22489384 10010
[1796][[SQLGetStmtAttr]] Handle=22489384 10011
[1796]PGAPI_GetStmtAttr Handle=22489384 10011
[1796][[SQLGetStmtAttr]] Handle=22489384 10012
[1796]PGAPI_GetStmtAttr Handle=22489384 10012
[1796][[SQLGetStmtAttr]] Handle=22489384 10013
[1796]PGAPI_GetStmtAttr Handle=22489384 10013
[1796][[SQLSetStmtAttr]] Handle=22489384 6,0
[1796]PGAPI_SetStmtAttr Handle=22489384 6,0
[1796]PGAPI_SetStmtOption: entering...
[1796]SetStmtOption(): SQL_CURSOR_TYPE = 0 [1796]-> 0
[1796][SQLPrepare][1796]PGAPI_Prepare: entering...
[1796]**** PGAPI_Prepare: STMT_ALLOCATED, copy
[1796][SQLBindCol][1796]PGAPI_BindCol: entering...
[1796]**** PGAPI_BindCol: stmt = 22489384, icol = 1
[1796]**** : fCType=1 rgb=12a5278 valusMax=256 pcb=12a5060
[1796]extend_column_bindings: entering ... self=22489464,
bindings_allocated=0, num_columns=1
[1796]exit extend_column_bindings
[1796] bound buffer[0] = 19550840
[1796][SQLExecute][1796]PGAPI_Execute: entering...
[1796]PGAPI_Execute: clear errors...
[1796]recycle statement: self= 22489384
[1796]APD_free_params: ENTER, self=22489516
[1796]IPD_free_params: ENTER, self=22489544
[1796]IPD_free_params: EXIT
[1796]Exec_with_parameters_resolved: copying statement params:
trans_status=2, len=197, stmt='SELECT lkb.xge FROM lkb WHERE ((lkb.dgb =
'ek') AND (lkb.fgb = 'ik') AND (lkb.hgb = 'mk_kh')) ORDER BY
lkb.dgb,lkb.fgb,lkb.hgb'
[1796] stmt_with_params = 'declare SQL_CUR01572928 cursor for SELECT
lkb.xge FROM lkb WHERE ((lkb.dgb = 'ek') AND (lkb.fgb = 'ik') AND (lkb.hgb =
'mk_kh')) ORDER BY lkb.dgb,lkb.fgb,lkb.hgb'
[1796] Sending SELECT statement on stmt=22489384,
cursor_name='SQL_CUR01572928'
[1796]send_query(): conn=22495288, query='declare SQL_CUR01572928 cursor for
SELECT lkb.xge FROM lkb WHERE ((lkb.dgb = 'ek') AND (lkb.fgb = 'ik') AND
(lkb.hgb = 'mk_kh')) ORDER BY lkb.dgb,lkb.fgb,lkb.hgb'
[1796]send_query: done sending query
[1796]in QR_Constructor
[1796]exit QR_Constructor
[1796]read 17, global_socket_buffersize=4096
[1796]send_query: got id = 'C'
[1796]send_query: ok - 'C' - DECLARE CURSOR
[1796]send_query: setting cmdbuffer = 'DECLARE CURSOR'
[1796]send_query: returning res = 22519784
[1796]send_query: got id = 'Z'
[1796]QResult: in DESTRUCTOR
[1796]QResult: free memory in, fcount=0
[1796]QResult: free memory out
[1796]QResult: exit DESTRUCTOR
[1796]send_query(): conn=22495288, query='fetch 100 in SQL_CUR01572928'
[1796]send_query: done sending query
[1796]in QR_Constructor
[1796]exit QR_Constructor
[1796]read 85, global_socket_buffersize=4096
[1796]send_query: got id = 'P'
[1796]send_query: got id = 'T'
[1796]QR_fetch_tuples: cursor = 'SQL_CUR01572928', self->cursor=0
[1796]num_fields = 1
[1796]READING ATTTYPMOD
[1796]CI_read_fields: fieldname='xge', adtid=1043, adtsize=-1, atttypmod=-1
[1796]QR_fetch_tuples: past CI_read_fields: num_fields = 1
[1796]MALLOC: tuple_size = 100, size = 800
[1796]next_tuple: inTuples = true, falling through: fcount = 101,
fetch_count = 101
[1796]qresult: len=30, buffer='Thu, 7 Aug 2003 14:09:16 +0200'
[1796]end of tuple list -- setting inUse to false: this = 22519784
[1796]_next_tuple: 'C' fetch_total = 1 & this_fetch = 1
[1796]send_query: got id = 'Z'
[1796] done sending the query:
[1796]extend_column_bindings: entering ... self=22489464,
bindings_allocated=1, num_columns=1
[1796]exit extend_column_bindings
[1796][[SQLEndTran]][1796]entering PGAPI_Transact: hdbc=22495288, henv=0
[1796]PGAPI_Transact: sending on conn 22495288 'COMMIT'
[1796]send_query(): conn=22495288, query='COMMIT'
[1796]send_query: done sending query
[1796]in QR_Constructor
[1796]exit QR_Constructor
[1796]read 9, global_socket_buffersize=4096
[1796]send_query: got id = 'C'
[1796]send_query: ok - 'C' - COMMIT
[1796]send_query: setting cmdbuffer = 'COMMIT'
[1796]send_query: returning res = 22526168
[1796]send_query: got id = 'Z'
[1796]QResult: in DESTRUCTOR
[1796]QResult: free memory in, fcount=0
[1796]QResult: free memory out
[1796]QResult: exit DESTRUCTOR
[1796][SQLCancel][1796]PGAPI_Cancel: entering...
[1796]PGAPI_FreeStmt: entering...hstmt=22489384, fOption=0
[1796]recycle statement: self= 22489384
[1796]QResult: in DESTRUCTOR
[1796]QResult: free memory in, fcount=1
[1796]row = 0, num_fields = 1
[1796]free [lf=0] 22489344
[1796]QResult: free memory out
[1796]QResult: exit DESTRUCTOR
[1796]APD_free_params: ENTER, self=22489516
[1796]IPD_free_params: ENTER, self=22489544
[1796]IPD_free_params: EXIT
[1796]PGAPI_Cancel: PGAPI_FreeStmt returned 0
[1796][SQLFreeStmt][1796]PGAPI_FreeStmt: entering...hstmt=22489384,
fOption=0
[1796]recycle statement: self= 22489384
[1796]APD_free_params: ENTER, self=22489516
[1796]IPD_free_params: ENTER, self=22489544
[1796]IPD_free_params: EXIT
[1796][[SQLFreeHandle]][1796]PGAPI_FreeStmt: entering...hstmt=22489384,
fOption=1
[1796]SC_Destructor: self=22489384, self->result=0, self->hdbc=22495288
[1796]reset_a_column_binding: entering ... self=22489464,
bindings_allocated=1, icol=1
[1796]APD_free_params: ENTER, self=22489516
[1796]IPD_free_params: ENTER, self=22489544
[1796]IPD_free_params: EXIT
[1796]SC_Destructor: EXIT
Appendix D:
-----------
The same as Appendix A, but with declarefetch=0:
00000009 16:35:38 [820] Detected PostgreSQL ODBC driver
00000010 16:35:38 [820] SQLAllocHandle(STMT) -->012E2048, 0
00000011 16:35:38 [820] SQLSetStmtAttr(012E2048,STATIC)-->0
00000012 16:35:38 [820] SQLPrepare(012E2048)-->0 [SELECT lkb.xge FROM lkb
WHERE ((lkb.dgb = 'ek') AND (lkb.fgb = 'ik') AND (lkb.hgb = 'mk_kh')) ORDER
BY lkb.dgb,lkb.fgb,lkb.hgb]
00000013 16:35:38 [820] bound columns
00000014 16:35:38 [820] SQLExecute(012E2048)-->0
00000015 16:35:38 [820] skipping getResultSize, returning 0
00000016 16:35:38 [820] SQLFetch(012E2048)-->0
00000017 16:35:38 [820] closeCursor/SQLFreeHandle(012E2048)-->0
00000018 16:35:38 [820] SQLFreeHandle(012E2048)-->0
00000019 16:35:38 [820] SQLAllocHandle(STMT) -->012E2048, 0
00000020 16:35:38 [820] SQLSetStmtAttr(012E2048,STATIC)-->0
00000021 16:35:38 [820] SQLPrepare(012E2048)-->0 [SELECT lkb.xge FROM lkb
WHERE ((lkb.dgb = 'ek') AND (lkb.fgb = 'ik') AND (lkb.hgb = 'kh_rij')) ORDER
BY lkb.dgb,lkb.fgb,lkb.hgb]
00000022 16:35:38 [820] bound columns
00000023 16:35:38 [820] SQLExecute(012E2048)-->0
00000024 16:35:38 [820] skipping getResultSize, returning 0
00000025 16:35:38 [820] SQLFetch(012E2048)-->100
00000026 16:35:38 [820] closeCursor/SQLFreeHandle(012E2048)-->0
00000027 16:35:38 [820] SQLFreeHandle(012E2048)-->0
00000028 16:35:38 [820] SQLAllocHandle(STMT) -->012E2048, 0
00000029 16:35:38 [820] SQLSetStmtAttr(012E2048,STATIC)-->0
00000030 16:35:38 [820] SQLPrepare(012E2048)-->0 [SELECT lkb.xge FROM lkb
WHERE ((lkb.dgb = 'ek') AND (lkb.fgb = 'ik') AND (lkb.hgb = 'mk_nk')) ORDER
BY lkb.dgb,lkb.fgb,lkb.hgb]
00000031 16:35:38 [820] bound columns
00000032 16:35:38 [820] SQLExecute(012E2048)-->0
00000033 16:35:38 [820] skipping getResultSize, returning 0
00000034 16:35:38 [820] SQLFetch(012E2048)-->0
00000035 16:35:43 [820] SQLAllocHandle(STMT) -->012E2480, 0
00000036 16:35:43 [820] SQLSetStmtAttr(012E2480,STATIC)-->0
00000037 16:35:43 [820] SQLPrepare(012E2480)-->0 [SELECT
vac.yzb,vac.aac,vac.cac,vac.eac FROM vac ORDER BY vac.aac]
00000038 16:35:43 [820] bound columns
00000039 16:35:43 [820] SQLExecute(012E2480)-->0
00000040 16:35:43 [820] skipping getResultSize, returning 0
00000041 16:35:43 [820] SQLFetch(012E2480)-->0
00000042 16:35:43 [820] SQLFetch(012E2480)-->100
00000043 16:35:43 [820] SQLAllocHandle(STMT) -->012E28B0, 0
00000044 16:35:43 [820] SQLSetStmtAttr(012E28B0,STATIC)-->0
00000045 16:35:43 [820] SQLPrepare(012E28B0)-->0 [SELECT qob.xge FROM qob
WHERE (qob.azc = 'to') ORDER BY qob.azc]
*** etc. basically, all is fine as long as I can guarantee that I
use tiny tables only (this is not the case)
--
