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 ;