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=postgres;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-where-Current-of-tp4499184p4553441.html
Sent from the PostgreSQL - odbc mailing list archive at Nabble.com.