Alan, take a look at this link, perhaps it'll help:
http://www.centerfieldtechnology.com/asktheexpert_viewquestions.asp#Q51
Elvis
Celebrating 10-Years of SQL Performance Excellence
-----Original Message-----
Subject: Dynamic Cursor.
I have a stored procedure now written as a RGP/ILE service program that
I need to be able to scroll through the result set more than once and I
am running into problems.
I issue the following declaration to declare the cursor
Exec Sql Declare C1 InSensitive Scroll Cursor With Return To
Client For
Select VarChar(a.FITEM,15,037) As ItemNumber,
iDate(a.SSTDT) As StartDate,
iDate(a.ODUDT) As DueDate,
VarChar(a.ORDNO,7,037) As OrderNumber,
VarChar(c.OPSEQ,4,037) As OperationsSequence,
VarChar(c.OPDSC,20,037) As
OperationsDescription,
VarChar(b.ITDSC,30,037) As ItemDescription,
VarChar(c.WKCTR,5,037) As WorkCenter,
VarChar(c.OPSTC,2,037) As OperationStatus,
a.DOSCH As DaysOffSchedule,
VarChar(a.JOBNO,12,037) As Notes,
VarChar(:dsSystem.CurrentUserProfile,10,037)
As CurrentUser
From MOMAST a
Inner Join ITMRVA b
On b.STID = a.FITWH And
b.ITNBR = a.FITEM
Inner Join MOROUT c
On c.ORDNO = a.ORDNO And
c.WKCTR <> '0000'
Where a.FITEM = :InItemNumber And
a.OSTAT < '45'
Order By a.FITWH,
iDate(a.ODUDT),
iDate(a.SSTDT),
a.ORDNO,
c.OPSEQ
For Read Only;
and issue the open return to client.
The VB programmer opens the result set through VB and ADO and reads
through the result set. No problem there.
The problem starts when he tries to reposition to the first record again
which should be reasonable and he gets an error.
From looking at the error, it appears what is happening is that the
cursor is being closed when he gets to the end of the result set which
doesn't make a lot of sense to me if it is a scrollable cursor. I should
be able to scroll back and forth through the result as much as I want.
Anybody got any experience with this? Is there some option that keeps
the cursor open?
midrange.com
