hello folks,

I have a situation where I am updating several tables in a process.

One of the tables is a log table. If any records fail in the "other" tables, then I want to abort, but still, have the failure logged in the log table.

So, what I want is to commit on the log table but abort on the others... is this possible with some special way of commiting or db.insert with commit?

Otherwise I need to simulate the process for failures to commit the logs, and then rerun the process with commit in case there are no errors for a particular record....... if that is even possible.

Thanks in advance

Can't you just abort the first transaction for the other tables. Then call another routine which starts a new transaction with its own commit? I am thinking you call the abort.transaction and then a new transaction for the log. Never done this myself.

function main()
{
if update.table.1() = 0 then
insert.to.log.table()
endif
if update.table.2() = 0 then
insert.to.log.table()
endif

}

function long update.table.1()
{
process...
if no problem then
commit.transaction()
else
abort.transaction()
return(DALHOOKERROR)
endif

return(0)
}

function long update.table.2()
{
process...
if no problem then
commit.transaction()
else
abort.transaction()
return(DALHOOKERROR)
endif

return(0)
}

function insert.to.log.table()
{
process to insert...
commit.transaction()
}

Thanks for the replies but no I cannot do that.

The insert (or failure) to main tables 1,2,3 should be logged in table 4. The inserts are running in parallel - literally...

But, if insert to tables 1,2 or 3 fails, then those transactions should be aborted. But, insert to table 4 should not. I wonder if this could work with multiple db.retry.points and multiple commit.transactions??? With multiple db.retry.points will the first commit.transaction commit all, or only last records selected after the last db.retry.point?

I would suggest to use a temporary log file. You can write all information you need to a log file, not to a table. If you really MUST put your results into a table, you may re-read logfile at the end of processing and write to that table.

You can also start another 3GL process that updates the log table. That process will get its own transaction. See the process communication section of the programmers manual (activate, bms send/receive, event loops, etc.)