Re: Fetch absolute returns OK when fetching a nonexistent row

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;