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;