Re: [Info-Ingres] DEP, For loop and commit

#1

On Nov 2, 2011, at 7:48 AM, Martin Bowes wrote:

Quote:

Hi All,

I have a database procedure which uses a for loop to control a large amount of 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 occur on 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.

Oops. I guess this is one situation where some sort of multiple or nested transaction
might help.

Quote:

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.

Thinking about the implementation, I'm not too surprised either. The FOR loop is the closest
thing in DB procedure-land to a cursor. The commit would close and reopen the loop table
but it would lose the current position. I guess that somehow tells the FOR loop control to
stop looping.

Quote:

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

WHILE loops have no direct tie to a driving table, so that might actually work.

Karl

#2

Try this for debug logging in a DB procedure:

message 'proc5 entered' with destination = (error_log);

It writes the message to your $II_SYSTEM/files/errlog.log and is very
useful for debugging rules+procedures.

#3

Hi Karl,

Yes I just wrote a quicky test and the while loop will get around the problem.

Sweet!

Marty

-----Original Message-----
From: Karl Schendel [mailto:schendel (AT) kbcomputer (DOT) com]
Sent: 02 November 2011 12:00
To: Ingres and related product discussion forum
Subject: Re: [Info-Ingres] DEP, For loop and commit

On Nov 2, 2011, at 7:48 AM, Martin Bowes wrote:

Quote:

Hi All,

I have a database procedure which uses a for loop to control a large amount of 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 occur on 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.

Oops. I guess this is one situation where some sort of multiple or nested transaction
might help.

Quote:

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.

Thinking about the implementation, I'm not too surprised either. The FOR loop is the closest
thing in DB procedure-land to a cursor. The commit would close and reopen the loop table
but it would lose the current position. I guess that somehow tells the FOR loop control to
stop looping.

Quote:

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

WHILE loops have no direct tie to a driving table, so that might actually work.

Karl

_______________________________________________
Info-Ingres mailing list
Info-Ingres (AT) kettleriverconsulting (DOT) com
http://ext-cando.kettleriverconsulti...fo/info-ingres

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Re: [Info-Ingres] DEP, For loop and commit

comp.databases.ingres comp.databases.ingres

Re: [Info-Ingres] DEP, For loop and commit - 11-02-2011 , 05:59 AM

Re: [Info-Ingres] DEP, For loop and commit - 11-02-2011 , 06:17 AM

Re: [Info-Ingres] DEP, For loop and commit - 11-02-2011 , 06:39 AM