Thread: ODBC + Dynamic cursors

ODBC + Dynamic cursors

From
"Amanjit Gill"
Date:
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



Attachment

Re: ODBC + Dynamic cursors

From
Janet Borschowa
Date:
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



Re: ODBC + Dynamic cursors

From
Hiroshi Inoue
Date:
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/

Re: ODBC + Dynamic cursors

From
"Amanjit Gill"
Date:
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