With Elevate DB 2.05 Build 10, I get :

ElevateDB Error #1011 An error occurred with the  routine at line 2 and column 6 (An error occurred with the statement at line 24 and column 12 (An error occurred with the row NEWROW (The row value cannot be referenced in an DELETE operation)))

when executing e.g.

SCRIPT()
BEGIN
CALL DeleteCustomer44();
END

with the Stored Proc defined as :

CREATE PROCEDURE DeleteCustomer44()
BEGIN
  DECLARE CustCursor CURSOR WITH RETURN FOR Stmt;
  DECLARE CustomerCode INT;
  
  PREPARE Stmt FROM 'SELECT * FROM Customer';

  OPEN CustCursor;

  --START TRANSACTION ON TABLES 'Customer';
  --BEGIN

       FETCH FIRST FROM CustCursor('CustomerCode') INTO CustomerCode;
                   
       WHILE NOT EOF(CustCursor) DO

           IF (CustomerCode = 44) THEN
               DELETE FROM CustCursor;
               FETCH FROM CustCursor('CustomerCode') INTO CustomerCode;
           ELSE
               FETCH NEXT FROM CustCursor('CustomerCode') INTO CustomerCode;
           END IF;

       END WHILE;

      --COMMIT;

      --EXCEPTION
      --    ROLLBACK;
      --END;

   CLOSE CustCursor;
END

(I commented out the transaction statements to enable the error message)

I  get the same error in a more complex stored proc (which we believe worked without problems with previous versions of ElevateDB) , but the above (based on DeleteFLCustomers() in the ElevateDB Version2 SQL manual) demonstrates the issue. Is this a bug ? or tighter adherence to the SQL standard ?  If so, how can I work-around the issue ?

TonyWood wrote:

With Elevate DB 2.05 Build 10, I get :

ElevateDB Error #1011 An error occurred with the  routine at line 2 and column 6 (An error occurred with the statement at line 24 and column 12 (An error occurred with the row NEWROW (The row value cannot be referenced in an DELETE operation)))

when executing e.g.

SCRIPT()
BEGIN
CALL DeleteCustomer44();
END

with the Stored Proc defined as :

CREATE PROCEDURE DeleteCustomer44()
BEGIN
  DECLARE CustCursor CURSOR WITH RETURN FOR Stmt;
  DECLARE CustomerCode INT;
  
  PREPARE Stmt FROM 'SELECT * FROM Customer';

  OPEN CustCursor;

  --START TRANSACTION ON TABLES 'Customer';
  --BEGIN

       FETCH FIRST FROM CustCursor('CustomerCode') INTO CustomerCode;
                   
       WHILE NOT EOF(CustCursor) DO

           IF (CustomerCode = 44) THEN
               DELETE FROM CustCursor;
               FETCH FROM CustCursor('CustomerCode') INTO CustomerCode;
           ELSE
               FETCH NEXT FROM CustCursor('CustomerCode') INTO CustomerCode;
           END IF;

       END WHILE;

      --COMMIT;

      --EXCEPTION
      --    ROLLBACK;
      --END;

   CLOSE CustCursor;
END

(I commented out the transaction statements to enable the error message)

I  get the same error in a more complex stored proc (which we believe worked without problems with previous versions of ElevateDB) , but the above (based on DeleteFLCustomers() in the ElevateDB Version2 SQL manual) demonstrates the issue. Is this a bug ? or tighter adherence to the SQL standard ?  If so, how can I work-around the issue ?


Addendum :
I should add that the above was performed as the Admiistrator user.
I just noticed that I get the same error with the statement :

delete from customer where customercode = 44

having previously succesfully issued :

GRANT DELETE ON TABLE Customer
TO administrator

Am i going nuts ?

Tony

Do you have any triggers on the table?  It looks like there may be an insert trigger may also be set for delete.

John

Tony,

<< ElevateDB Error #1011 An error occurred with the  routine at line 2 and
column 6 (An error occurred with the statement at line 24 and column 12 (An
error occurred with the row NEWROW (The row value cannot be referenced in an
DELETE operation))) >>

John is correct - any reference to the OLDROW/NEWROW values indicates that
the error is occurring in a trigger, so I would start looking there.  To
that end, you can look at the logged events in the EDB Manager under the
session node (View Logged Events), or just execute:

SELECT * FROM Configuration.LogEvents

manually, and you should see a complete log of the error along with where it
occurred.

--
Tim Young
Elevate Software
www.elevatesoft.com

Thanks for your response John.

Yes the problem was associated with a trigger on the table. A trap for young players


"John Hay" wrote:

Tony

Do you have any triggers on the table?  It looks like there may be an insert trigger may also be set for delete.

John