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.