Thread: Problem with cursor

Hi all

I posted this to the newsgroup on June 11th, but I got no replies.

Is the mailing list is a better forum for asking questions of this nature?

Below is a script that creates and populates three tables, and then
creates a scroll cursor to retrieve the data. Mostly it works fine,
but in this precise situation it fails, in the following manner.

The "select" returns a cursor with two rows. Results are as follows -

1. move 1 in cursor - ok, skips past first row
2. fetch next from cursor - ok, fetches second row
3. move -2 in cursor - ok, moves to beginning of cursor
4. fetch next from cursor - not ok - should return first row, returns
0 rows (in fact any cursor activity from this point returns 0 rows - all I can do is 'end')

If I remove the "order by" clause, or change it to order by anything
else, it works correctly.

Tested on 7.2.1 and 7.3.2 - results are the same.

Any advice will be much appreciated.

Frank Millman


CREATE TABLE SysUsers ( UserId varchar (999) not null primary key, Password varchar (999), Surname varchar (999) not null, FirstName varchar (999), Administrator char default ' ' check (Administrator in ('Y',' ')), DeleteInd char default ' ' check (DeleteInd in ('Y',' ')) ) ;

INSERT INTO SysUsers VALUES ('Admin','','Administrator','','Y',' ') ;
INSERT INTO SysUsers VALUES ('Frank','','Millman', 'Frank', ' ',' ') ;
INSERT INTO SysUsers VALUES ('Greer','','Millman', 'Greer', ' ',' ') ;

CREATE TABLE SysCompanies ( CompanyId varchar (999) not null primary key, CompanyName varchar (999) not null ) ;

INSERT INTO SysCompanies VALUES ('ccc', 'System Administration') ;
INSERT INTO SysCompanies VALUES ('chagford', 'Chagford cc') ;
INSERT INTO SysCompanies VALUES ('chagtest', 'Chagford test company')
;

CREATE TABLE SysUsersCompanies ( UserId varchar (999) not null references SysUsers, CompanyId varchar (999) not null references SysCompanies, primary key (UserId, CompanyId) ) ;

INSERT INTO SysUsersCompanies VALUES ('Admin', 'ccc') ;
INSERT INTO SysUsersCompanies VALUES ('Admin', 'chagford') ;
INSERT INTO SysUsersCompanies VALUES ('Admin', 'chagtest') ;
INSERT INTO SysUsersCompanies VALUES ('Frank', 'chagford') ;
INSERT INTO SysUsersCompanies VALUES ('Frank', 'chagtest') ;
INSERT INTO SysUsersCompanies VALUES ('Greer', 'chagford') ;
INSERT INTO SysUsersCompanies VALUES ('Greer', 'chagtest') ;

begin ;
declare fm scroll cursor for select a.CompanyId, b.CompanyName from SysUsersCompanies a, SysCompanies b where UserId = 'Greer' and a.CompanyId = b.CompanyId order by a.CompanyId ;
move 1 in fm ;
fetch next from fm ;
move -2 in fm ;
fetch next from fm ;