Обсуждение: ODBC + Dynamic cursors
Hi, I have written a C++ ODBC wrapper that right now runs on Oracle,Access,SQL Server, Sybase. I am right now investigating PostgreSQL which we want to use instead of SQL Server. PostgreSQL Version: 7.33 (Cygwin/Windows XP) using ipc-daemon ODBC Version: 7.03.01.10 OS: Windows XP Pro, SP1 Synopsis: I basically open a connection to a db, create a statement,use a dynamic cursor via SQLSetConnectAttr (SQL_ATTR_CURSOR_TYPE, SQL_CURSOR_DYNAMIC), execute the statement , bind some fields and want to fetch data using SQLFetchScroll (m_hstmt, SQL_FETCH_ABSOLUTE, lPosition). The SQLFetchScroll statement fails with the following message: 'HY106 - the fetch type for PGAPI_ExtendedFetch isn't allowed for ForwardOnly cursor.' Which of course irritates me because I explicitely use SQL_CURSOR_DYNAMIC. I attached the standard windows SQL.log with some mixins of the code I called. It is not the complete log and it is not the PostgreSQL-Driver specific log, which is far too verbose for me. I have enabled the Dynamic cursor checkbox in the driver. Any suggestions? Another question: are positionable updates and deletes (which means SQLSetPos) possible with PostgreSQL at present (as mentioned in http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/ht m/odch12pr_2.asp) ? Thanks for your help Amanjit Gill Software Developer Medical and Bioinformatics UFK (Gynecology and Obstetrics) University Clinic Charite Berlin --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.504 / Virus Database: 302 - Release Date: 24.07.2003
Вложения
Hi Amanjit, As far as I know, PostgreSQL ODBC doesn't support SQL_CURSOR_DYNAMIC so it is defaulting to SQL_CURSOR_FORWARD_ONLY and that's why you're seeing the error. Please try SQL_CURSOR_KEYSET_DRIVEN and I think you'll get the behavior you want. PostgreSQL ODBC does support SQLSetPos but you need to make sure to check the "Updatable cursors" checkbox in the driver setup. ======================== Janet Borschowa Software Engineer, Database Products Rogue Wave Software (541) 753-1931 FAX: (541) 757-4630 mailto:borschow@roguewave.com http://www.roguewave.com -----Original Message----- From: Amanjit Gill [mailto:amanjit.gill@gmx.de] Sent: Wednesday, July 30, 2003 1:06 PM To: pgsql-odbc@postgresql.org Subject: [ODBC] ODBC + Dynamic cursors Hi, I have written a C++ ODBC wrapper that right now runs on Oracle,Access,SQL Server, Sybase. I am right now investigating PostgreSQL which we want to use instead of SQL Server. PostgreSQL Version: 7.33 (Cygwin/Windows XP) using ipc-daemon ODBC Version: 7.03.01.10 OS: Windows XP Pro, SP1 Synopsis: I basically open a connection to a db, create a statement,use a dynamic cursor via SQLSetConnectAttr (SQL_ATTR_CURSOR_TYPE, SQL_CURSOR_DYNAMIC), execute the statement , bind some fields and want to fetch data using SQLFetchScroll (m_hstmt, SQL_FETCH_ABSOLUTE, lPosition). The SQLFetchScroll statement fails with the following message: 'HY106 - the fetch type for PGAPI_ExtendedFetch isn't allowed for ForwardOnly cursor.' Which of course irritates me because I explicitely use SQL_CURSOR_DYNAMIC. I attached the standard windows SQL.log with some mixins of the code I called. It is not the complete log and it is not the PostgreSQL-Driver specific log, which is far too verbose for me. I have enabled the Dynamic cursor checkbox in the driver. Any suggestions? Another question: are positionable updates and deletes (which means SQLSetPos) possible with PostgreSQL at present (as mentioned in http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/ht m/odch12pr_2.asp) ? Thanks for your help Amanjit Gill Software Developer Medical and Bioinformatics UFK (Gynecology and Obstetrics) University Clinic Charite Berlin --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.504 / Virus Database: 302 - Release Date: 24.07.2003
Amanjit Gill wrote: > > Hi, I have written a C++ ODBC wrapper that right now runs on > Oracle,Access,SQL Server, Sybase. > I am right now investigating PostgreSQL which we want to use instead of > SQL Server. > > PostgreSQL Version: 7.33 (Cygwin/Windows XP) using ipc-daemon > ODBC Version: 7.03.01.10 > OS: Windows XP Pro, SP1 > > Synopsis: > I basically open a connection to a db, create a statement,use a dynamic > cursor > via SQLSetConnectAttr (SQL_ATTR_CURSOR_TYPE, SQL_CURSOR_DYNAMIC), Please set the SQL_ATTR_CURSOR_TYPE attribute for statement handles not for connection handles. Also note that psqlodbc driver doesn't support dynamic cursors. Please use keyset-driven or static cursors instead. [snip] > Another question: are positionable updates and deletes (which means > SQLSetPos) possible with > PostgreSQL at present (as mentioned in > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/ht > m/odch12pr_2.asp) ? No. What the driver supports is to update/delete/insert/.. a row (rows) in a rowset using SQLSetPos(.., .., SQL_UPDATE/DELETE/ADD, .. Positioned update/delete means updat/deleting a row in a rowset by executing a query such as UPDATE/DELETE ... WHERE CURRENT OF .... Please note that the examples in the page you specified are updat/deleting a row using SQLExecute. regards, Hiroshi Inoue http://www.geocities.jp/inocchichichi/psqlodbc/
Dear Mr. Inoue, - Keyset driven cursors did the trick - Thank you for pointing out to set the attribute at the statement level - SQLSetPos() functionality basically does what I want to. Regards, Amanjit Gill Software Developer Medical and Bioinformatics UFK (Gynecology and Obstetrics) University Clinic Charite Berlin -----Ursprüngliche Nachricht----- Von: pgsql-odbc-owner@postgresql.org [mailto:pgsql-odbc-owner@postgresql.org] Im Auftrag von Hiroshi Inoue Gesendet: Donnerstag, 31. Juli 2003 02:01 An: Amanjit Gill Cc: pgsql-odbc@postgresql.org Betreff: Re: [ODBC] ODBC + Dynamic cursors Amanjit Gill wrote: > > Hi, I have written a C++ ODBC wrapper that right now runs on > Oracle,Access,SQL Server, Sybase. I am right now investigating > PostgreSQL which we want to use instead of SQL Server. > > PostgreSQL Version: 7.33 (Cygwin/Windows XP) using ipc-daemon > ODBC Version: 7.03.01.10 > OS: Windows XP Pro, SP1 > > Synopsis: > I basically open a connection to a db, create a statement,use a > dynamic cursor via SQLSetConnectAttr (SQL_ATTR_CURSOR_TYPE, > SQL_CURSOR_DYNAMIC), Please set the SQL_ATTR_CURSOR_TYPE attribute for statement handles not for connection handles. Also note that psqlodbc driver doesn't support dynamic cursors. Please use keyset-driven or static cursors instead. [snip] > Another question: are positionable updates and deletes (which means > SQLSetPos) possible with > PostgreSQL at present (as mentioned in > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/ > ht > m/odch12pr_2.asp) ? No. What the driver supports is to update/delete/insert/.. a row (rows) in a rowset using SQLSetPos(.., .., SQL_UPDATE/DELETE/ADD, .. Positioned update/delete means updat/deleting a row in a rowset by executing a query such as UPDATE/DELETE ... WHERE CURRENT OF .... Please note that the examples in the page you specified are updat/deleting a row using SQLExecute. regards, Hiroshi Inoue http://www.geocities.jp/inocchichichi/psqlodbc/ ---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives? http://archives.postgresql.org --- Incoming mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.504 / Virus Database: 302 - Release Date: 24.07.2003 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.504 / Virus Database: 302 - Release Date: 24.07.2003