Thread: 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.