From: | BGoebel <b(dot)goebel(at)prisma-computer(dot)de> |
---|---|
To: | pgsql-odbc(at)postgresql(dot)org |
Subject: | FETCH LAST is returning "no data" after a Cursor Update |
Date: | 2011-11-09 16:10:55 |
Message-ID: | 1320855055219-4978166.post@n5.nabble.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-odbc |
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.
From | Date | Subject | |
---|---|---|---|
Next Message | Hiroshi Inoue | 2011-11-09 17:57:17 | Re: Locale problem with psqlODBC under Fedora 16 |
Previous Message | BGoebel | 2011-11-09 15:41:04 | Re: Fetch absolute returns OK when fetching a nonexistent row |