Thread: Fetch absolute returns OK when fetching a nonexistent row
Hi All,
Once again i need your help.
The attached example shows an error using a simple ODBC SELECT + FETCH.
Fetching the (non existing) 100th row results in 0 (=ok) instead of 100(no
data).
The error seems to be depend on the FETCH parameter of the connection
string:
When using ...;Fetch=3 in the connection string i get the right resultcode
100.
A previous FETCH NEXT seems to help too, but i think we can not rely on
this.
any help would be appreciated
best regards
Tested with pg 9.1 / odbcDriver 9.00.0310 / Delphi7.0
-----------------------------------------------
PROCEDURE FetchAbsoluteTest;
Var aRes:Integer;
hStmtSelect,hstmtUpdate,fEnvHandle,fConnectHandle:SQLHandle;
szName:ShortString;
cbName:SQLInteger;
aScroll,aSQLSmallInt:SQLSmallInt;
aConnectString:String;
aRow:Cardinal;
Begin
(*
tabledefinition and data used in this case:
drop table if exists customers;
create table customers(nr integer, name varchar(100));
insert into customers(nr, name) VALUES(1, 'Mayers');
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=1';
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);
// 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'),
SQL_NTS);
// fetch will read the column "name"
aRes:= SQLBindCol(hstmtSelect, 1, SQL_C_CHAR, @szName[1], 50, cbName);
//fetching absolute a nonexisting row
aRes := SQLFetchScroll(hStmtSelect, SQL_FETCH_ABSOLUTE, 100);
Assert(aRes = 100);
END;
--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Fetch-absolute-returns-OK-when-fetching-a-nonexistent-row-tp4971382p4971382.html
Sent from the PostgreSQL - odbc mailing list archive at Nabble.com.
Hi, (2011/11/08 0:11), BGoebel wrote: > Hi All, > > Once again i need your help. > > The attached example shows an error using a simple ODBC SELECT + FETCH. > Fetching the (non existing) 100th row results in 0 (=ok) instead of 100(no > data). > > The error seems to be depend on the FETCH parameter of the connection > string: > When using ...;Fetch=3 in the connection string i get the right resultcode > 100. > A previous FETCH NEXT seems to help too, but i think we can not rely on > this. > > any help would be appreciated Could you please try the drivers on testing for 9.0.0311 at http://www.ne.jp/asahi/inocchichichi/entrance/psqlodbc/ ? regards, Inoue, Hiroshi > best regards > > > Tested with pg 9.1 / odbcDriver 9.00.0310 / Delphi7.0 > ----------------------------------------------- > > PROCEDURE FetchAbsoluteTest; > > Var aRes:Integer; > hStmtSelect,hstmtUpdate,fEnvHandle,fConnectHandle:SQLHandle; > szName:ShortString; > cbName:SQLInteger; > aScroll,aSQLSmallInt:SQLSmallInt; > aConnectString:String; > aRow:Cardinal; > Begin > > (* > tabledefinition and data used in this case: > > drop table if exists customers; > create table customers(nr integer, name varchar(100)); > insert into customers(nr, name) VALUES(1, 'Mayers'); > 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=1'; > > 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); > > // 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'), > SQL_NTS); > // fetch will read the column "name" > aRes:= SQLBindCol(hstmtSelect, 1, SQL_C_CHAR, @szName[1], 50, cbName); > > //fetching absolute a nonexisting row > aRes := SQLFetchScroll(hStmtSelect, SQL_FETCH_ABSOLUTE, 100); > Assert(aRes = 100); > END;
Hi Hiroshi, thanks a lot for the Info! The error seems to be fixed. We have tested the new version of the driver. Unfortunately this error still exists: http://postgresql.1045698.n5.nabble.com/Error-Retrieving-Catalog-Info-tt4598955.html Also i have found a new (?) issue with Cursor Update / SQLEndTran / Fetch Last. I will have a closer look on that and open a new topic. regards BGoebel -- View this message in context: http://postgresql.1045698.n5.nabble.com/Fetch-absolute-returns-OK-when-fetching-a-nonexistent-row-tp4971382p4978053.html Sent from the PostgreSQL - odbc mailing list archive at Nabble.com.