Обсуждение: 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.
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);
Hi Hiroshi, the bug is fixed. Thanks a lot! regards BGoebel -- View this message in context: http://postgresql.1045698.n5.nabble.com/FETCH-LAST-is-returning-no-data-after-a-Cursor-Update-tp4978166p4980614.html Sent from the PostgreSQL - odbc mailing list archive at Nabble.com.