Hello,

I've been using DBISAM for quite some time and am not trying to get my
arms around ElevateDB. So here goes.
I'm working on a procedure to create a copy of rows that match the
desired WBSIDList. I am currently testing this in script state. Here's
the problem:
8 rows are returned from the SELECT statement.
Using WHILE in the CURSOR with the FETCH NEXT, does not create
additional rows, the only record that is copied is the one from FETCH FIRST.

I have experimented, thinking that there was an issue with WHILE and NOT
EOF, so I tried loop and experienced the same results. I then decided to
cycle through 5 entries using N to analyze the results. In this case, I
got the 1st and the 5th record, but none in between.

I'm at a loss here and any help will definitely be appreciated.

Best Regards,

David

-----
SCRIPT --(IN ToWBSIDList INT, IN FromWBSIDList INT)
BEGIN
DECLARE CrsrWBS SENSITIVE CURSOR WITH RETURN FOR Stmt;
DECLARE ToWBSIDList INT DEFAULT 26;
DECLARE FromWBSIDList Int Default 1;
DECLARE VActualFinish Int;
DECLARE VActualStart Int;
DECLARE VCaption VarChar(255);
DECLARE VEventType Int;
DECLARE VFinish TimeStamp;
DECLARE VID Int;
DECLARE VLabelColor Int;
DECLARE VLocation VarChar(255);
DECLARE VMessage VarChar(255);
DECLARE VOptions Int;
DECLARE VParentID Int;
DECLARE VRecurrenceIndex Int;
DECLARE VRecurrenceInfo Blob;
DECLARE VReminderDate TimeStamp;
DECLARE VReminderMinutesBeforeStart Int;
DECLARE VReminderResourcesData Blob;
DECLARE VResourceID Int;
DECLARE VStart TimeStamp;
DECLARE VState Int;
DECLARE VTaskCompleteField Int;
DECLARE VTaskIndexField Int;
DECLARE VTaskLinksField Blob;
DECLARE VTaskStatusField Int;
DECLARE VWBSListID Int;
DECLARE VParent Bool;
DECLARE N Int DEFAULT 0;

PREPARE Stmt FROM 'SELECT * FROM WBSTemplates WHERE WBSListID = ?';
OPEN CrsrWBS USING FromWBSIDList;

START TRANSACTION ON TABLES WBSTemplates;
BEGIN

FETCH FIRST FROM CrsrWBS (ActualFinish, ActualStart, Caption, EventType,
Finish, ID, LabelColor, Location, Message,
Options, ParentID, RecurrenceIndex, RecurrenceInfo, ReminderDate,
ReminderMinutesBeforeStart,
ReminderResourcesData, ResourceID, Start, State, TaskCompleteField,
TaskIndexField, TaskLinksField,
TaskStatusField, WBSListID, Parent) INTO VActualFinish, VActualStart,
VCaption, VEventType, VFinish, VID, VLabelColor, VLocation, VMessage,
VOptions, VParentID, VRecurrenceIndex, VRecurrenceInfo, VReminderDate,
VReminderMinutesBeforeStart,
VReminderResourcesData, VResourceID, VStart, VState, VTaskCompleteField,
VTaskIndexField, VTaskLinksField,
VTaskStatusField, VWBSListID, VParent;

WHILE NOT EOF(CrsrWBS) DO
--WHILE (N < 5) DO
--LOOP
INSERT INTO CrsrWBS VALUES (VActualFinish, VActualStart, VCaption,
VEventType, VFinish, VID, VLabelColor, VLocation, VMessage,
VOptions, VParentID, VRecurrenceIndex, VRecurrenceInfo, VReminderDate,
VReminderMinutesBeforeStart,
VReminderResourcesData, VResourceID, VStart, VState, VTaskCompleteField,
VTaskIndexField, VTaskLinksField,
VTaskStatusField, ToWBSIDList, VParent);

--IF NOT EOF(CrsrWBS) THEN
FETCH NEXT FROM CrsrWBS (ActualFinish, ActualStart, Caption, EventType,
Finish, ID, LabelColor, Location, Message,
Options, ParentID, RecurrenceIndex, RecurrenceInfo, ReminderDate,
ReminderMinutesBeforeStart,
ReminderResourcesData, ResourceID, Start, State, TaskCompleteField,
TaskIndexField, TaskLinksField,
TaskStatusField, WBSListID, Parent) INTO VActualFinish, VActualStart,
VCaption, VEventType, VFinish, VID, VLabelColor, VLocation, VMessage,
VOptions, VParentID, VRecurrenceIndex, VRecurrenceInfo, VReminderDate,
VReminderMinutesBeforeStart,
VReminderResourcesData, VResourceID, VStart, VState, VTaskCompleteField,
VTaskIndexField, VTaskLinksField,
VTaskStatusField, VWBSListID, VParent;
--SET N = N + 1;
--ELSE
--LEAVE;
--END IF;
END WHILE;
--END LOOP;
COMMIT;

EXCEPTION
ROLLBACK;
END;
CLOSE CrsrWBS;

END
-----

David,

<< I'm working on a procedure to create a copy of rows that match the
desired WBSIDList. I am currently testing this in script state. Here's the
problem:
8 rows are returned from the SELECT statement.
Using WHILE in the CURSOR with the FETCH NEXT, does not create additional
rows, the only record that is copied is the one from FETCH FIRST. >>

Can you send me the database catalog and table files that you're using
(support@elevatesoft.com) ?  Your code looks okay, so something else that
isn't so obvious must be the problem.

Thanks,

--
Tim Young
Elevate Software
www.elevatesoft.com

David

Inserting a record into a live cursor will move the record pointer to that
record.  In this case the record is falling outside the scope of the where
clause so the record pointer sits on the last record and eof is true.

I think you need to declare a second sensitive cursor for the insert (eg
SELECT * FROM WBSTemplates).

John

John,

<< Inserting a record into a live cursor will move the record pointer to
that record.  In this case the record is falling outside the scope of the
where clause so the record pointer sits on the last record and eof is
true.>>

Nice catch - I completely missed that he was inserting into the same cursor.

<< I think you need to declare a second sensitive cursor for the insert (eg
SELECT * FROM WBSTemplates). >>

Or just execute an insert directly into the table using:

EXECUTE IMMEDIATE 'INSERT INTO....'

or

PREPARE/EXECUTE..USING with parameters.

--
Tim Young
Elevate Software
www.elevatesoft.com

Thanks for your help John and Tim,

I tried to do an insert immediate when I first started on this, but one
of my input parameters needs to insert into an id column, while the
other is in the where clause.

I took your advice and declared a second cursor and got it working in no
time. As I always hate it when someone says thanks and doesn't share the
solution. Here is the final solution in script form:

-----
SCRIPT --(IN ToWBSIDList INT, IN FromWBSIDList INT)
BEGIN
   DECLARE CrsrWBSOut SENSITIVE CURSOR WITH RETURN FOR Stmt0;
   DECLARE CrsrWBSIn SENSITIVE CURSOR WITHOUT RETURN FOR Stmt1;
   DECLARE ToWBSIDList INT DEFAULT 28;
   DECLARE FromWBSIDList Int Default 1;
   DECLARE VActualFinish Int;
   DECLARE VActualStart Int;
   DECLARE VCaption VarChar(255);
   DECLARE VEventType Int;
   DECLARE VFinish TimeStamp;
   DECLARE VID Int;
   DECLARE VLabelColor Int;
   DECLARE VLocation VarChar(255);
   DECLARE VMessage VarChar(255);
   DECLARE VOptions Int;
   DECLARE VParentID Int;
   DECLARE VRecurrenceIndex Int;
   DECLARE VRecurrenceInfo Blob;
   DECLARE VReminderDate TimeStamp;
   DECLARE VReminderMinutesBeforeStart Int;
   DECLARE VReminderResourcesData Blob;
   DECLARE VResourceID Int;
   DECLARE VStart TimeStamp;
   DECLARE VState Int;
   DECLARE VTaskCompleteField Int;
   DECLARE VTaskIndexField Int;
   DECLARE VTaskLinksField Blob;
   DECLARE VTaskStatusField Int;
   DECLARE VWBSListID Int;
   DECLARE VParent Bool;
   DECLARE N Int DEFAULT 0;

   PREPARE Stmt0 FROM 'SELECT * FROM WBSTemplates WHERE WBSListID = ?';
   PREPARE Stmt1 FROM 'SELECT * FROM WBSTemplates';
   OPEN CrsrWBSOut USING FromWBSIDList;
   OPEN CrsrWBSIn USING FromWBSIDList;
   START TRANSACTION ON TABLES WBSTemplates;
   BEGIN
      FETCH FIRST FROM CrsrWBSOut (ActualFinish, ActualStart, Caption,
EventType, Finish, ID, LabelColor, Location, Message,
      Options, ParentID, RecurrenceIndex, RecurrenceInfo, ReminderDate,
ReminderMinutesBeforeStart,
      ReminderResourcesData, ResourceID, Start, State,
TaskCompleteField, TaskIndexField, TaskLinksField,
      TaskStatusField, WBSListID, Parent) INTO VActualFinish,
VActualStart, VCaption, VEventType, VFinish, VID, VLabelColor,
VLocation, VMessage,
      VOptions, VParentID, VRecurrenceIndex, VRecurrenceInfo,
VReminderDate, VReminderMinutesBeforeStart,
      VReminderResourcesData, VResourceID, VStart, VState,
VTaskCompleteField, VTaskIndexField, VTaskLinksField,
      VTaskStatusField, VWBSListID, VParent;

      WHILE NOT EOF(CrsrWBSOut) DO
         INSERT INTO CrsrWBSIn VALUES (VActualFinish, VActualStart,
VCaption, VEventType, VFinish, VID, VLabelColor, VLocation, VMessage,
         VOptions, VParentID, VRecurrenceIndex, VRecurrenceInfo,
VReminderDate, VReminderMinutesBeforeStart,
         VReminderResourcesData, VResourceID, VStart, VState,
VTaskCompleteField, VTaskIndexField, VTaskLinksField,
         VTaskStatusField, ToWBSIDList, VParent);

      FETCH NEXT FROM CrsrWBSOut (ActualFinish, ActualStart, Caption,
EventType, Finish, ID, LabelColor, Location, Message,
      Options, ParentID, RecurrenceIndex, RecurrenceInfo, ReminderDate,
ReminderMinutesBeforeStart,
      ReminderResourcesData, ResourceID, Start, State,
TaskCompleteField, TaskIndexField, TaskLinksField,
      TaskStatusField, WBSListID, Parent) INTO VActualFinish,
VActualStart, VCaption, VEventType, VFinish, VID, VLabelColor,
VLocation, VMessage,
      VOptions, VParentID, VRecurrenceIndex, VRecurrenceInfo,
VReminderDate, VReminderMinutesBeforeStart,
      VReminderResourcesData, VResourceID, VStart, VState,
VTaskCompleteField, VTaskIndexField, VTaskLinksField,
      VTaskStatusField, VWBSListID, VParent;
      END WHILE;
      COMMIT;

      EXCEPTION
      ROLLBACK;
   END;

   CLOSE CrsrWBSOut;
   CLOSE CrsrWBSIn;

END
-----

Tim Young [Elevate Software] wrote:
> John,
>
> << Inserting a record into a live cursor will move the record pointer to
> that record.  In this case the record is falling outside the scope of the
> where clause so the record pointer sits on the last record and eof is
> true.>>
>
> Nice catch - I completely missed that he was inserting into the same cursor.
>
> << I think you need to declare a second sensitive cursor for the insert (eg
> SELECT * FROM WBSTemplates). >>
>
> Or just execute an insert directly into the table using:
>
> EXECUTE IMMEDIATE 'INSERT INTO....'
>
> or
>
> PREPARE/EXECUTE..USING with parameters.
>