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

Hi,

(2011/11/10 1:10), BGoebel wrote:
> 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.

Could you please retry the drivers on testing for 9.0.0311?

regards,
Inoue, Hiroshi

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