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.