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.