Re: [Info-Ingres] commit question

Hi David,

The action of \[no]continue depends very much on your Ingres Version.
But assuming you are running something more uptodate that 6.4....

The answer to question 1 is yes and the answer to 2 is maybe!

The \nocontinue effect may be modified by the setting of the environment
variable II_TM_SWITCH. If set to true then you will get (almost) the
effect that you are after - we used to call this 'go-block' processing.
If an error occurs anywhere in the block of code then execution will
automatically skip to the first statement following the \g.

So what you want is...
setenv II_TM_SWITCH true
sql whatever <<SQL_END
modify medat0002 to truncated;
insert into medat0002 select * from hafakot.medat02_for_hafakot;
commit;
\p\g\t
rollback
\g\t
....
SQL_END

Note that I have encapsulated the three commands into a single block
terminated by the \g. The next block has been turned into a rollback
statement.

Thus if the insert causes an error control is passed to the next block,
hence the next command executed is the rollback and not the commit. If
the insert does not cause an error then the commit is executed and then
the rollback - which in this case would have no effect.

You may also like to check out the setting of II_TM_ON_ERROR setting as
well.

Note that these variables may be set globally with ingprenv, and those
settings overridden by local environment settings.

Martin Bowes

-----Original Message-----
From: info-ingres-bounces@xxxxxxxxxxxxxxxxxxxxxxxxx
[mailto:info-ingres-bounces@xxxxxxxxxxxxxxxxxxxxxxxxx] On Behalf Of
David Greenberg
Sent: 23 August 2007 10:14
To: info-ingres@xxxxxxxxxxxxxxxxxxxxxxxxx
Subject: [Info-Ingres] commit question

Hi

This is my code ....

modify medat0002 to truncated;\g\t
insert into medat0002 select * from hafakot.medat02_for_hafakot;
commit;\g\t


Our default on entering "sql" is "\continue".

The situation we had was that the "modify to truncate" command succeeded

but the insert failed. Because of the "\continue" default the run
continued to other sql commands.

My questions are:
1. The medat0002 table was truncated even though the insert failed. Is
that because of the \continue which ran the "commit" and saved the
result of the truncate ?

2. Had we have set "\nocontinue", would the truncate command have been
rolled back ?

Thanks

David Greenberg

_______________________________________________
Info-Ingres mailing list
Info-Ingres@xxxxxxxxxxxxxxxxxxxxxxxxx
http://www.kettleriverconsulting.com/mailman/listinfo/info-ingres

.

Relevant Pages