Hi Group,

Our database requires some events to run at specific times throughout
each day. Firebird does not have a built in schedule or cron, so I
have written a service that makes a connection every minute and fires
a stored procedure, logging any exceptions.

From time to time, we add functionality that needs to be scheduled,
but I would like to avoid constantly having to change the service to
make additional calls.

Assuming I had two SP I wanted to run (SP_1 and SP_2 for this example)
I had the idea to create a SP as follows:


CREATE PROCEDURE CRON
RETURNS
(
EXCEPTIONMESSAGE VARCHAR(200)
)
AS
BEGIN
BEGIN
EXECUTE PROCEDURE SP_1;

WHEN ANY DO
BEGIN
EXCEPTIONMESSAGE = 'Problem with SP_1';
SUSPEND;
END
END

BEGIN
EXECUTE PROCEDURE SP_2;

WHEN ANY DO
BEGIN
EXCEPTIONMESSAGE = 'Problem with SP_2';
SUSPEND;
END
END
END
^

The DDL to add the new feature can just become part of the next
database upgrade in the form of a pretty simple alter procedure statement.

The SP works great, the service application just calls
"Select ExceptionMessage from SP_CRON" every minute, but what I would
like to be able to do is to pass on the actual exception message.

From what I have read, I only get SQLCODE or GDSCODE. In this case,
SP_1 and SP_2 raise custom exceptions which would be more meaningful
in the log than the standard didn't work message here. SQLCODE and
GDSCODE will always be 0 for the custom message anyway

The equivalent concept in delphi of what I am trying to do

try
// Execute SP_1
except
on e : exception do LogThis(e.Message);
end;