Icon View Incident Report

Serious Serious
Reported By: Shane Sturgeon
Reported On: 11/15/2008
For: Version 2.02 Build 3
# 2856 Calling a Procedure from Within a Trigger Can Cause an AV if the Procedure Returns a Result Set

When I call a procedure from a trigger and then open the table in EDBManager and make a change which causes the trigger to fire, it generates an EDB AV (see attached) which is worrying me (although the changes seem to be made).

TRIGGER "AfterDeactivation"
BEGIN  
  CALL monUpdate_ReplaceWith(NEWROW.monID, NEWROW.monReplaceWith);
END

PROCEDURE "monUpdate_ReplaceWith" (IN "OldValue" INTEGER, IN "NewValue"
INTEGER)
BEGIN 
-- Prepare the Selection Query with parameter.
  DECLARE MoneyCmpCursor CURSOR WITH RETURN FOR Stmt;
  PREPARE Stmt FROM 'SELECT * FROM MoneyCmp WHERE monReplaceWith = ?';
  OPEN MoneyCmpCursor USING OldValue;

-- Now update the selected records.
  START TRANSACTION ON TABLES MoneyCmp;
  BEGIN
    FETCH FIRST FROM MoneyCmpCursor;
    WHILE ROWCOUNT(MoneyCmpCursor) > 0 DO
      UPDATE MoneyCmpCursor SET monReplaceWith = NewValue;
        FETCH FIRST FROM MoneyCmpCursor;
    END WHILE;
    COMMIT;
  EXCEPTION
    ROLLBACK; 
    RAISE ERROR CODE 10000 MESSAGE 'Timeasure DB Error - ' + ERRORMSG();
  END;
END



Comments Comments and Workarounds
The procedure must return a result set (DECLARE CURSOR WITH RETURN). The workaround is to define the procedure without the WITH RETURN clause for the cursor declaration.


Resolution Resolution
Fixed Problem on 11/17/2008 in version 2.02 build 4


Products Affected Products Affected
ElevateDB Additional Software and Utilities
ElevateDB DAC Client-Server
ElevateDB DAC Client-Server with Source
ElevateDB DAC Standard
ElevateDB DAC Standard with Source
ElevateDB DAC Trial
ElevateDB LCL Standard with Source
ElevateDB VCL Client-Server
ElevateDB VCL Client-Server with Source
ElevateDB VCL Standard
ElevateDB VCL Standard with Source
ElevateDB VCL Trial

Image