Hello

It isn't clear to me where this psuedo-code could go wrong, but the real stored procedure invariably leaves a single record behind.

The Payment table does not have an AcctCode field, hence the necessity to find all the Payment records with the matching AcctCode, then loop through that result set and call the SPArchPayment procedure. I could avoid the problem by using a couple of sub-queries, but the SPArchPayment procedure has to exist for archiving individual records, so I thought I should use it.

Note that I use FETCH LAST then loop from the bottom of the result set, which I thought should be sufficient to avoid a problem such as I find - a record that isn't archived, or deleted. The raw SQL in the select statement (SELECT ALL "P"."PaymentID" AS "PaymentID" FROM "Payment"...) works as it should.

CREATE PROCEDURE "SPArchiveAcctCode" (IN "AcctCode" VARCHAR(6) COLLATE "UNI")
BEGIN
DECLARE DBCursor CURSOR FOR DBStmt;
DECLARE VPaymentID INTEGER DEFAULT -1;
DECLARE SQLStatement VARCHAR DEFAULT 'SELECT ALL
"P"."PaymentID" AS "PaymentID" FROM "Payment" AS "P"
WHERE ("P"."AcctCode" = ?) AND ("P"."PaidAmt" = "P"."ChargeAmt")';
PREPARE DBStmt FROM SQLStatement;
OPEN DBCursor USING AcctCode;
FETCH LAST FROM DBCursor ('PaymentID') INTO VProcessID;
 WHILE NOT BOF(DBCursor) DO
  BEGIN
   CALL SPArchPayment(VPaymentID);
  END;
FETCH PRIOR FROM DBCursor ('PaymentID') INTO VProcessID;
END WHILE;
CLOSE DBCursor;
END

The called SPArchPayment procedure works on individual records...

CREATE PROCEDURE "SPArchPayment" (IN "PaymentID" INTEGER, OUT "AResult" BOOLEAN)
BEGIN
SET AResult = False;
START TRANSACTION ON TABLES 'Payment', 'PayArchive';
BEGIN -- 1st DELETE clears existing arhive with same ID
 EXECUTE IMMEDIATE 'DELETE FROM "PayArchive" WHERE (PaymentID = ?);' USING PaymentID;
 EXECUTE IMMEDIATE 'INSERT INTO PayArchive (col1, col2, col3)
                   (SELECT col1, col2, col3
                    FROM Payment WHERE (PaymentID = ?));' USING PaymentID;
 EXECUTE IMMEDIATE 'DELETE FROM "Payment" WHERE (PaymentID = ?);' USING PaymentID;
 COMMIT;
 SET AResult = True;
EXCEPTION
 ROLLBACK;
END;
END

Any suggestions as to why I don't archive all the matching records would be greatly appreciated.

Regards

Peter

Peter


Bit of a guess here. When I've had that problem in Delphi its invariably because the cursor has moved which is why, as you've done I generally start at the bottom and work backwards. I can't remember wether the default cursor in a script is insensitive or sensitive. Try asking for an insensitive cursor so that altering the table is definitely not going to affect your progression through it.

Roy Lambert

Peter,

<< Note that I use FETCH LAST then loop from the bottom of the result set, which I thought should be sufficient to avoid a problem such as I find - a record that isn't archived, or deleted. >>

Use this instead (or use an insensitive result set cursor like Roy suggests):

CREATE PROCEDURE "SPArchiveAcctCode" (IN "AcctCode" VARCHAR(6) COLLATE "UNI")
BEGIN
DECLARE DBCursor CURSOR FOR DBStmt;
DECLARE VPaymentID INTEGER DEFAULT -1;
DECLARE SQLStatement VARCHAR DEFAULT 'SELECT ALL
"P"."PaymentID" AS "PaymentID" FROM "Payment" AS "P"
WHERE ("P"."AcctCode" = ?) AND ("P"."PaidAmt" = "P"."ChargeAmt")';
PREPARE DBStmt FROM SQLStatement;
OPEN DBCursor USING AcctCode;
FETCH FIRST FROM DBCursor ('PaymentID') INTO VProcessID;
WHILE ROWCOUNT(DBCursor) > 0 DO
   CALL SPArchPayment(VPaymentID);
   FETCH FIRST FROM DBCursor ('PaymentID') INTO VProcessID;
END WHILE;
CLOSE DBCursor;
END

Tim Young
Elevate Software
www.elevatesoft.com

Thank you Roy, thank you Tim.

Regards

Peter