Hi All,

I have a database procedure which uses a for loop to control a large amountof processing. The body of the for loop has places where status messages are inserted in a table, plus many places where a database error could occuron other tasks.

The trouble is that if an error occurs then it causes a rollback in the directly exected procedure. This then erases all my status messages from the table.

So it occurred why not commit each status message insert....

But the for loop terminated on the first commit! Is it meant to do that? I didn't expect that, but reading between thelines on the commit documentation I suppose I'm not too surprised.

I tried putting the status message insert and commit into a separate procedure and calling that, but the same premature loop termination occurred.

Anyone have any thought on making the status message inserts bullet proof?

I'm thinking of attempting to rewrite the procedure to use a while loop, but I suspect the commit will have the same effect.

The only other thought I've had is to get it to raise an event. But then I need an event handler to capture these events and do the job. Which seems way to complicated!

FYI. I've made it log the status messages into a table as I couldn't work out how to get Perl DBI to get messages generated by the message command inside the DEP.

Martin Bowes

Martin Bowes wrote:

The MESSAGE statement allows you to direct the message to errlog.log or
the audit log using WITH DESTINATION=ERROR_LOG or AUDIT_LOG. Writing to
the log files is not transactional.

--
Roy

UK Ingres User Association Conference 2012 will be on Tuesday June 19 2012.
The latest information is available from www.uk-iua.org.uk.