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