ERROR with "Update ... where Current of"

Hi,

after a lot of studying i couldn't find out what's going wrong with my
positioned update. Whatever i have tried: SQLGetDiagRec reports: Error:
"Cursor C1 does not exists"

Here is my Code (a simplified example from MS):
....
SQLRETURN = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmtSelect);
SQLRETURN = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmtUpdate);
SQLRETURN = SQLSetCursorName(hstmtSelect, "C1", SQL_NTS);

SQLRETURN = SQLExecDirect(hstmtSelect,
"SELECT NAME, PHONE FROM CUSTOMERS",SQL_NTS);
/* in the example, a SQLBindCol followed here ... */
SQLRETURN = SQLFetch(hstmtSelect);
/* positioned update : set first customers phonenumber to empty */
SQLRETURN = SQLExecDirect(hstmtUpdate,
"UPDATE EMPLOYEE SET PHONE=\"\" WHERE CURRENT OF C1",SQL_NTS);

....SQLRETURN is set to -1, SQLGetDiagRec reports Error: "Cursor >>C1<< does
not exist"
....
psqlodbc30a.dll ist directly linked.

Here is what i have tried:
- Reading, exploring, reading, ...
- Select ... for Update
- connection Parameter UpdatableCursors=1;
- connection Parameter Autocommit off
- connection Parameter UseDeclareFetch=1
- Select ... for Update
- Select ... with hold
- older odbc Driver 8.04
- explict BEGIN


Any help would be greatly appreciated!




--
View this message in context: http://postgresql.1045698.n5.nabble.com/ERROR-with-Update-wh ere-Current-of-tp4499184p4499184.html
Sent from the PostgreSQL - odbc mailing list archive at Nabble.com.

--
Sent via pgsql-odbc mailing list (pgsql-odbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-odbc

Re: ERROR with "Update ... where Current of"

Hi,

Unfortunately the psqlodbc driver doesn't support POSITIONED UPDATE.

regards,
Hiroshi Inoue

(2011/06/18 0:51), BGoebel wrote:
> Hi,
>
> after a lot of studying i couldn't find out what's going wrong with my
> positioned update. Whatever i have tried: SQLGetDiagRec reports: Error:
> "Cursor C1 does not exists"
>
> Here is my Code (a simplified example from MS):
> ...
> SQLRETURN = SQLAllocHandle(SQL_HANDLE_STMT, hdbc,&hstmtSelect);
> SQLRETURN = SQLAllocHandle(SQL_HANDLE_STMT, hdbc,&hstmtUpdate);
> SQLRETURN = SQLSetCursorName(hstmtSelect, "C1", SQL_NTS);
>
> SQLRETURN = SQLExecDirect(hstmtSelect,
> "SELECT NAME, PHONE FROM CUSTOMERS",SQL_NTS);
> /* in the example, a SQLBindCol followed here ... */
> SQLRETURN = SQLFetch(hstmtSelect);
> /* positioned update : set first customers phonenumber to empty */
> SQLRETURN = SQLExecDirect(hstmtUpdate,
> "UPDATE EMPLOYEE SET PHONE=\"\" WHERE CURRENT OF C1",SQL_NTS);
>
> ...SQLRETURN is set to -1, SQLGetDiagRec reports Error: "Cursor>>C1<< does
> not exist"
> ...
> psqlodbc30a.dll ist directly linked.
>
> Here is what i have tried:
> - Reading, exploring, reading, ...
> - Select ... for Update
> - connection Parameter UpdatableCursors=1;
> - connection Parameter Autocommit off
> - connection Parameter UseDeclareFetch=1
> - Select ... for Update
> - Select ... with hold
> - older odbc Driver 8.04
> - explict BEGIN
>
>
> Any help would be greatly appreciated!
>
>
>
>
> --
> View this message in context: http://postgresql.1045698.n5.nabble.com/ERROR-with-Update-wh ere-Current-of-tp4499184p4499184.html
> Sent from the PostgreSQL - odbc mailing list archive at Nabble.com.

--
Sent via pgsql-odbc mailing list (pgsql-odbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-odbc

Re: ERROR with "Update ... where Current of"

Hi,

thank you a lot for your message.

.... a more appropriate error message by the ODBC-Driver would have been very
nice.

I will try to implement an update via SQLSetPos with SQL_UPDATE using a
keyset_driven Cursor as it is described at
http://msdn.microsoft.com/en-us/library/ms715392(v=vs.85).as px.

Does any docu exists, what is (not) implemented by the ODBC driver?


regards




--
View this message in context: http://postgresql.1045698.n5.nabble.com/ERROR-with-Update-wh ere-Current-of-tp4499184p4510135.html
Sent from the PostgreSQL - odbc mailing list archive at Nabble.com.

--
Sent via pgsql-odbc mailing list (pgsql-odbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-odbc

Re: ERROR with "Update ... where Current of"

This is the solution what i have found for my postioned update problem
It is implemented in Pascal/Delphi. To keep it short(er) i ommitted some
code (Error handling, setting String-length for returned data, type Info.
OK, "Update ... where Current of" would have been more elegant, but the
mean thing is: it works.

Thanks goes to Mr. Inoue for the hint, that SQLSetStmtAttr(...
SQL_CONCUR_ROWVER ...) is needed.


----------------------- snip ---------------


Var aRes:Integer;
hStmtSelect,hstmtUpdate,fEnvHandle,fConnectHandle:SQLHandle;
szName:ShortString;
cbName:SQLInteger;
aScroll,aSQLSmallInt:SQLSmallInt;
aConnectString:String;
aRow:Cardinal;

// -UpdatableCursors=1/0 makes no difference
// -using SQL_CURSOR_Static : Update works. DB2 Says NO.

Begin

fEnvHandle := 0;
fConnectHandle := 0;

aRes := SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, fEnvHandle);
aRes := SQLAllocHandle(SQL_HANDLE_DBC, fEnvHandle, fConnectHandle);

aSqlSmallint := 0;
aConnectString :=
'Driver={PostgreSQL};Server=127.0.0.1;Port=5432;Database=pos tgres;Uid=postgres;Pwd=mypwd;UpdatableCursors=1;';

aRes := SQLDriverConnect(fConnectHandle,
GetDesktopWindow,
@aConnectString[1],
length(aConnectString),
nil,
0,
aSqlSmallint,
0);
//switchin AUTOCOMMIT off
aRes := SQLSetConnectAttr(fConnectHandle,
SQL_ATTR_AUTOCOMMIT,
pointer(SQL_AUTOCOMMIT_OFF),
sizeof(SQL_AUTOCOMMIT_OFF));
aRes:= SQLAllocHandle(SQL_HANDLE_STMT, fConnectHandle, hStmtSelect);

// Cursor : KeySetDriven + SQL_CONCUR_ROWVER(=updatable)
aRes:= sqlSetStmtAttr(hStmtSelect,
SQL_ATTR_CONCURRENCY,
pointer(SQL_CONCUR_ROWVER),
sizeof(SQLSmallint));
aRes:= sqlSetStmtAttr(hStmtSelect,
SQL_ATTR_CURSOR_TYPE,
//pointer(SQL_CURSOR_STATIC), // would work
for updates with PostGreSQL. DB2: non updatable
pointer(SQL_CURSOR_KEYSET_DRIVEN),
sizeof(SQLSmallint));
// Select ...
aRes := SQLExecDirect(hstmtSelect,
pchar('SELECT name FROM customers order by nr'),
SQL_NTS);
// fetch will read name
aRes:= SQLBindCol(hstmtSelect, 1, SQL_C_CHAR, @szName[1], 50, cbName);

// fetching/reading the first row
aRow := 1;
aRes := SQLFetchScroll(hStmtSelect,
SQL_FETCH_ABSOLUTE,
arow);
// Changing data. Doing so, every time i call this snippet, the value will
be changed
szName[0]:=Chr(cbName);
IF szName[1]<'a'
THEN
szName := 'anyname'
ELSE
szName := 'ANYNAME';
cbName:=Length(szName);
// UPDATE data
aRes := SQLSetPos(hstmtSelect,
1,
SQL_UPDATE,
SQL_LOCK_UNLOCK );
// We are fetching the second row...
aRow:=2;
aRes := SQLFetchScroll(hStmtSelect,
SQL_FETCH_ABSOLUTE,
arow);
// ... and the first (changed) row again
// PostgreSQL: updated data in first row is shown, according to the ODBC
KeySetDriven property.
aRow:=1;
aRes := SQLFetchScroll(hStmtSelect,
SQL_FETCH_ABSOLUTE,
arow);
// Make changes visible to other users
// PostGreSQL: as expected: other users do not see the updates, when i do
not COMMIT them
aRes:=SQLExecDirect(hstmtUpdate,
'commit',
SQL_NTS);
// Does Fetch works without a new SELECT ?
// DB2: "...Ordinarily, all cursors close at the end of a transaction...",
there is an " with hold" Option.
// PostGreSQL : yes
aRow:=1;
aRes := SQLFetchScroll(hStmtSelect,
SQL_FETCH_ABSOLUTE,
arow);
end;

--
View this message in context: http://postgresql.1045698.n5.nabble.com/ERROR-with-Update-wh ere-Current-of-tp4499184p4553441.html
Sent from the PostgreSQL - odbc mailing list archive at Nabble.com.

--
Sent via pgsql-odbc mailing list (pgsql-odbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-odbc