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.