Cursor for a positioned update: "cursor <name> does not exist" error

From: Sam Varshavchik <mrsam(at)courier-mta(dot)com>
To: pgsql-odbc(at)postgresql(dot)org
Subject: Cursor for a positioned update: "cursor <name> does not exist" error
Date: 2013-05-22 22:11:57
Message-ID: cone.1369260717.1874.14504.1000@monster.email-scan.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-odbc

I'm using postgresql-odbc-09.01.0200 with postgresl-9.2.4

According to the documention for SQLSetCursorName
(http://msdn.microsoft.com/en-us/library/windows/desktop/ms711707%28v=vs.85%29.aspx)
I don't really need to use it, and ODBC will use a default name.

With unixODBC and a Postgresql connection handle, SQLGetInfo() shows
SQL_CA1_POS_UPDATE capability for SQL_STATIC_CURSOR_ATTRIBUTES1, and
SQL_KEYSET_CURSOR_ATTRIBUTES1.

I tried using both a static and a keyset cursor, by setting
SQL_ATTR_CURSOR_TYPE accordingly, on a new statement handle; then executing
a "SELECT [..] FOR UPDATE", fetching multiple rows, using SQLSetPos to
position on a given row, using SQLGetCursorName to retrieve the cursor's
name, then attempting to execute an "UPDATE [..] WHERE CURRENT OF
<cursor_name>" on another statement handle.

I'm basically following the script given here:
http://msdn.microsoft.com/en-us/library/windows/desktop/ms709389%28v=vs.85%29.aspx

The original SELECT is a simple select on a table with a primary key,
nothing fancy. The call to SQLGetCursorName returns a cursor name string
like "SQL_CUR0x20c9480", but the subsequent UPDATE fails with a "cursor
"sql_cur0x8de420" does not exist" error. If I try an explicit
SQLSetCursorName before preparing the original SELECT, the subsequent
SQLGetCursorName returns the cursor name I specify, but the UPDATE still
fails with the error.

When I strace the process, I don't see anything that suggests that a cursor
gets really opened, I just see the plain original, unadorned, SELECT, being
sent to the server:

sendto(4, "Q\0\0\0BSELECT intkey, strval FROM temptbl ORDER BY intkey FOR
UPDATE\0", 67, MSG_NOSIGNAL, NULL, 0) = 67

This is immediately followed by:

sendto(4, "Q\0\0\0LUPDATE temptbl SET strval='2modified' WHERE CURRENT OF
SQL_CUR0x1d2a210\0", 77, MSG_NOSIGNAL, NULL, 0) = 77

And the response to that is the error:

recvfrom(4, "E\0\0\0_SERROR\0C34000\0Mcursor \"sql_cur0x1d2a210\" does not
exist\0FexecCurrent.c\0L67\0RexecCurrentOf\0\0Z\0\0\0\5I", 4096, MSG_NOSIGNAL, NULL, NULL) = 102

With a MySQL connection instead, the same, simple test code appears to work
as advertised.

P.S. The positioned update example on MSDN references gives a "FOR UPDATE OF
<column name>" clause, which seems to be an MS-ism, and Postgres doesn't
like it even though the way the example is given would lead one to belive
that it's DB-neutral SQL. Postgres accepts just a "FOR UPDATE" on a SELECT,
except that something isn't working for me, and the cursor does not get
created.

Responses

Browse pgsql-odbc by date

  From Date Subject
Next Message Dev Kumkar 2013-05-23 01:06:05 Re: [ODBC] ODBC constructs
Previous Message Dev Kumkar 2013-05-22 21:38:06 Re: [ODBC] ODBC constructs