Skip site navigation (1) Skip section navigation (2)

Fetch absolute returns OK when fetching a nonexistent row

From: BGoebel <b(dot)goebel(at)prisma-computer(dot)de>
To: pgsql-odbc(at)postgresql(dot)org
Subject: Fetch absolute returns OK when fetching a nonexistent row
Date: 2011-11-07 15:11:33
Message-ID: 1320678693054-4971382.post@n5.nabble.com (view raw or flat)
Thread:
Lists: pgsql-odbc
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.

Responses

pgsql-odbc by date

Next:From: Devrim GÜNDÜZDate: 2011-11-07 20:05:31
Subject: Re: 9.1 driver status
Previous:From: tamanna madaanDate: 2011-11-06 11:33:16
Subject: Re: SQLDriverConnect function call hangs

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group