FETCH LAST is returning "no data" after a Cursor Update

Hi All,

this is a bug we have found when testing the unreleased psqlodbc30a.dll
9.00.0311.

The attached example shows an error using a  SELECT + CursorUpdate + FETCH
LAST.
After a SELECT we are updating the column name on the first row via
SQLSetPos and SQLEndtran.
Executing a Fetch LAST will returning 100/No Data

Sending a COMMIT via ExecuteSQL seems to work, but i do not know if that is
a really a reliable solution.

regards

BGoebel



Tested with pg 9.1 / psqlodbc30a.dll 9.00.0311 / Delphi7.0
-----------------------------------------------
     Used SQL Data/Definition

     drop table if exists customers;
     create table customers(nr integer, name varchar(100));
     insert into customers(nr, name) VALUES(1, 'Mayer');
     insert into customers(nr, name) VALUES(2, 'Miller');
     insert into customers(nr, name) VALUES(3, 'Smith');

-----------------------------------------------
 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;usedeclarefetch=1;fetch=50';

 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);
   aRes:= SQLAllocHandle(SQL_HANDLE_STMT, fConnectHandle, hStmtUpdate);

  //  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_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_NEXT, 0);


  // 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 );

  // Make changes visible to other users --> commit
  //  aRes:=SQLExecDirect(hstmtUpdate, 'commit', SQL_NTS); //<-- next
SQLFetchScroll will work
  aRes := SQLEndTran(SQL_HANDLE_DBC, fConnectHandle, SQL_COMMIT); //<-- next
SQLFetchScroll will return 100

  aRes := SQLFetchScroll(hStmtSelect,
                               SQL_FETCH_LAST,
                                0);
  Assert(aRes = 0);


  aRow:=100;
  aRes := SQLFetchScroll(hStmtSelect,
                                SQL_FETCH_ABSOLUTE,
                                arow);
  Assert(aRes = 0);

--
View this message in context:
http://postgresql.1045698.n5.nabble.com/FETCH-LAST-is-returning-no-data-after-a-Cursor-Update-tp4978166p4978166.html
Sent from the PostgreSQL - odbc mailing list archive at Nabble.com.