Re: ERROR with "Update ... where Current of" - Mailing list pgsql-odbc
From | BGoebel |
---|---|
Subject | Re: ERROR with "Update ... where Current of" |
Date | |
Msg-id | 1309876019178-4553441.post@n5.nabble.com Whole thread Raw |
In response to | Re: ERROR with "Update ... where Current of" (BGoebel <b.goebel@prisma-computer.de>) |
List | pgsql-odbc |
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.
pgsql-odbc by date: