Thread: psqlodbc and SQLFetch after SQLTables gives SQL_NO_DATA_FOUND
Dear list, I posted some time ago about a problem with retrieving table in MSQuery on MacOSX using postgres 3.7.1 and psqlodbc-7.2.5 with iODBC (iODBC-SDK-3.5.3). At the time I found, by snooping the TCP stream, that the SQLTables function, that must be used by MSQuery to retrieve the table list, did indeed return the tables, but for some reason MSQuery was unable to get this data. Now I have enabled tracing and found where the failure is. Apparently MSQ uses SQLFetch just after SQLTables to get the table list, this SQLFetch fails by returning SQL_NO_DATA_FOUND. Here I have two snippets from the traces of MSQ retrieving tables first using OpenLink Postgres Lite driver (that succeeds) and then using psqlodbc (that fails): 1) openlink driver: iODBC[Microsoft Query] ThID:A0000DEC ENTER SQLTables iODBC[Microsoft Query] ThID:A0000DEC EXIT SQLTables with return code 0 (SQL_SUCCESS) Microsoft Query ThID:A0000DEC EXIT SQLTables with return code 0 (SQL_SUCCESS) HSTMT 0x01619a00 CHAR* 00000000 "..." SMALLINT -3536 CHAR* 00000000 "..." SMALLINT -18992 CHAR* 00000000 "..." SMALLINT 15056 CHAR* 0x001c3fd8 "Table" SMALLINT 5 Microsoft Query ThID:A0000DEC ENTER SQLFetch HSTMT 0x01619a00 iODBC[Microsoft Query] ThID:A0000DEC ENTER SQLFetch iODBC[Microsoft Query] ThID:A0000DEC EXIT SQLFetch with return code 0 (SQL_SUCCESS) Microsoft Query ThID:A0000DEC EXIT SQLFetch with return code 0 (SQL_SUCCESS) HSTMT 0x01619a00 Microsoft Query ThID:A0000DEC ENTER SQLNumResultCols 2) psqlodbc driver: iODBC[Microsoft Query] ThID:A0000DEC ENTER SQLTables iODBC[Microsoft Query] ThID:A0000DEC EXIT SQLTables with return code 0 (SQL_SUCCESS) Microsoft Query ThID:A0000DEC EXIT SQLTables with return code 0 (SQL_SUCCESS) HSTMT 0x0162e800 CHAR* 00000000 "..." SMALLINT -3536 CHAR* 00000000 "..." SMALLINT -18992 CHAR* 00000000 "..." SMALLINT 14880 CHAR* 0x001c3fd8 "Table" SMALLINT 5 Microsoft Query ThID:A0000DEC ENTER SQLFetch HSTMT 0x0162e800 iODBC[Microsoft Query] ThID:A0000DEC ENTER SQLFetch iODBC[Microsoft Query] ThID:A0000DEC EXIT SQLFetch with return code 100 (SQL_NO_DATA_FOUND) Microsoft Query ThID:A0000DEC EXIT SQLFetch with return code 100 (SQL_NO_DATA_FOUND) HSTMT 0x0162e800 Note that by retrieving the result set by other methods (as in the odbctest application) one can succeed. Anybody any idea if this is a problem with the psqlodbc driver and how it could be solved. Thanks for your time. Giuliano
Giuliano Gavazzi wrote: > > Dear list, > > I posted some time ago about a problem with retrieving table in > MSQuery on MacOSX using postgres 3.7.1 and psqlodbc-7.2.5 with > iODBC (iODBC-SDK-3.5.3). > > At the time I found, by snooping the TCP stream, that the SQLTables > function, that must be used by MSQuery to retrieve the table list, > did indeed return the tables, but for some reason MSQuery was unable > to get this data. > > Now I have enabled tracing and found where the failure is. Apparently > MSQ uses SQLFetch just after SQLTables to get the table list, this > SQLFetch fails by returning SQL_NO_DATA_FOUND. Here I have two > snippets from the traces of MSQ retrieving tables first using > OpenLink Postgres Lite driver (that succeeds) and then using psqlodbc > (that fails): > > 1) openlink driver: > > iODBC[Microsoft Query] ThID:A0000DEC ENTER SQLTables > > iODBC[Microsoft Query] ThID:A0000DEC EXIT SQLTables with return > code 0 (SQL_SUCCESS) > > Microsoft Query ThID:A0000DEC EXIT SQLTables with return code 0 > (SQL_SUCCESS) > HSTMT 0x01619a00 > CHAR* 00000000 "..." > SMALLINT -3536 > CHAR* 00000000 "..." > SMALLINT -18992 > CHAR* 00000000 "..." > SMALLINT 15056 > CHAR* 0x001c3fd8 "Table" Hmm, if it is "TABLE", it seems to work. OK I would change the driver to be insenitive about it. regards, Hiroshi Inoue http://www.geocities.jp/inocchichichi/psqlodbc/
At 16:02 +0900 2003/02/10, Hiroshi Inoue wrote: [...] > > Now I have enabled tracing and found where the failure is. Apparently >> MSQ uses SQLFetch just after SQLTables to get the table list, this > > SQLFetch fails by returning SQL_NO_DATA_FOUND. Here I have two >> snippets from the traces of MSQ retrieving tables first using >> OpenLink Postgres Lite driver (that succeeds) and then using psqlodbc >> (that fails): >> >> 1) openlink driver: >> >> iODBC[Microsoft Query] ThID:A0000DEC ENTER SQLTables >> >> iODBC[Microsoft Query] ThID:A0000DEC EXIT SQLTables with return >> code 0 (SQL_SUCCESS) >> >> Microsoft Query ThID:A0000DEC EXIT SQLTables with return code 0 >> (SQL_SUCCESS) >> HSTMT 0x01619a00 >> CHAR* 00000000 "..." >> SMALLINT -3536 >> CHAR* 00000000 "..." >> SMALLINT -18992 >> CHAR* 00000000 "..." >> SMALLINT 15056 >> CHAR* 0x001c3fd8 "Table" > >Hmm, if it is "TABLE", it seems to work. >OK I would change the driver to be insenitive about >it. > Hi Hiroshi, sorry but I do not understand. In both cases (the working one and the not working one) it is "Table". What seems to change is the way the client application retrieves the data. I have now enable debug and repeated the test, may I send you the output? Where would I change the source anyway? In info.c? Thanks for helping me solve this. Giuliano
> -----Original Message----- > From: Giuliano Gavazzi [mailto:dev+pgsql@humph.com] > > At 16:02 +0900 2003/02/10, Hiroshi Inoue wrote: > [...] > > > Now I have enabled tracing and found where the failure is. Apparently > >> MSQ uses SQLFetch just after SQLTables to get the table list, this > > > SQLFetch fails by returning SQL_NO_DATA_FOUND. Here I have two > >> snippets from the traces of MSQ retrieving tables first using > >> OpenLink Postgres Lite driver (that succeeds) and then using psqlodbc > >> (that fails): > >> > >> 1) openlink driver: > >> > >> iODBC[Microsoft Query] ThID:A0000DEC ENTER SQLTables > >> > >> iODBC[Microsoft Query] ThID:A0000DEC EXIT SQLTables with return > >> code 0 (SQL_SUCCESS) > >> > >> Microsoft Query ThID:A0000DEC EXIT SQLTables with return code 0 > >> (SQL_SUCCESS) > >> HSTMT 0x01619a00 > >> CHAR* 00000000 "..." > >> SMALLINT -3536 > >> CHAR* 00000000 "..." > >> SMALLINT -18992 > >> CHAR* 00000000 "..." > >> SMALLINT 15056 > >> CHAR* 0x001c3fd8 "Table" > > > >Hmm, if it is "TABLE", it seems to work. > >OK I would change the driver to be insenitive about > >it. > > > > Hi Hiroshi, sorry but I do not understand. In both cases (the working > one and the not working one) it is "Table". What seems to change is > the way the client application retrieves the data. I have now enable > debug and repeated the test, may I send you the output? > > Where would I change the source anyway? In info.c? I've just committed a change to cvs(info.c). Please try it. regards, Hiroshi Inoue
At 0:24 +0900 2003/02/11, Hiroshi Inoue wrote: > > -----Original Message----- > > From: Giuliano Gavazzi [...] > > > > Now I have enabled tracing and found where the failure is. Apparently >> >> MSQ uses SQLFetch just after SQLTables to get the table list, this >> > > SQLFetch fails by returning SQL_NO_DATA_FOUND. Here I have two >> >> snippets from the traces of MSQ retrieving tables first using >> >> OpenLink Postgres Lite driver (that succeeds) and then using psqlodbc >> >> (that fails): >> >> >> >> 1) openlink driver: >> >> >> >> iODBC[Microsoft Query] ThID:A0000DEC ENTER SQLTables >> >> >> >> iODBC[Microsoft Query] ThID:A0000DEC EXIT SQLTables with return >> >> code 0 (SQL_SUCCESS) >> >> >> >> Microsoft Query ThID:A0000DEC EXIT SQLTables with return code 0 >> >> (SQL_SUCCESS) >> >> HSTMT 0x01619a00 >> >> CHAR* 00000000 "..." >> >> SMALLINT -3536 >> >> CHAR* 00000000 "..." >> >> SMALLINT -18992 >> >> CHAR* 00000000 "..." >> >> SMALLINT 15056 >> >> CHAR* 0x001c3fd8 "Table" >> > >> >Hmm, if it is "TABLE", it seems to work. >> >OK I would change the driver to be insenitive about > > >it. [...] >I've just committed a change to cvs(info.c). >Please try it. > >regards, >Hiroshi Inoue tested... Thanks Hiroshi, you are a star! You can add a note in the CVS that this change fixes the interaction with MSQuery on MacOSX. Giuliano