Error trapping in ingres SQL

Fabio DONNA

unread,

Jul 30, 2001, 11:45:51 AM7/30/01

to info-...@ams.org

Hello,
I need to run following SQL in ingres:

echo"
drop table tbl_name;
create table tbl_name as ....
some other rows...
" | sql db_name

Is there a way to trap error generated if table tbl_name does not exist
when the script is run or to not do the drop table statement if the
table doesn't exist?

I'd like to have something like this:

if exists tbl_name then
drop table tbl_name;
fi

or this:

on error continue
drop table tbl_name
off error

Thanks in advance

Kind regards

--
Fabio DONNA (Agilent Technologies Italia S.p.A.)

Karl & Betty Schendel

unread,

Jul 30, 2001, 6:09:21 PM7/30/01

to info-...@ams.org

At 5:09 PM +0200 7/30/01, Fabio DONNA wrote:
>I need to run following SQL in ingres:
>
>echo"
>drop table tbl_name;
>create table tbl_name as ....
> some other rows...
>" | sql db_name
>
>Is there a way to trap error generated if table tbl_name does not exist
>when the script is run or to not do the drop table statement if the
>table doesn't exist?
>
>I'd like to have something like this:
>
>if exists tbl_name then
> drop table tbl_name;
>fi
>
>or this:
>
>on error continue
>drop table tbl_name

You can't do the first one easily(*), but you can do the second with the
\nocontinue and \continue pseudo-commands.

By the way, don't forget to end your input stream with a \go, or else
the terminal monitor will happily buffer up your input -- and then exit
without sending it!

(*) If you drop into QUEL mode you can use \b against a system catalog
query. In quite a few years of terminal monitor hacking I've been moved to
do this maybe once.
--
Karl R. Schendel, Jr.
K/B Computer Associates sche...@kbcomputer.com
Ingres, Unix, VMS

Martin Bowes

unread,

Jul 30, 2001, 6:52:40 PM7/30/01

to Fabio DONNA, ingres newsgroup

Hi Fabio,

This cant be done in the manner you've described. However, SQL provides
what is referred to as 'go-block' processing and this can be used to
achieve the results you are after.

A go-block is the set of statements between succesive \g commands.
eg.
statement1; /* 3 statements in this first block */
statement2;
statement3;
\p\g
statement4 /* 1 statement in this second block */
\p\g

A SQL script can be run with \nocontinue. The effect of which is
determined by a few things. Under Ingres6.4 the effect was that as each
statement was executed the system would check for any error messages. If a
message wes detected then execution of the current go-block was terminated
and resumed at the start of the next go-block.

Some damn fool decided to change this under OpenIngres and IngresII.
However, after howls of protest, The above effect would be used as long as
the Ingres Environment variables were set globally or locally as follows:
II_TM_ON_ERROR=nocontinue
II_TM_SWITCH=true

So to do what you want:
\nocontinue
drop table tbl_name;
\p\g
create table tbl_name as ...;
commit;
\p\g
rollback
\p\g
\q

This code will create the table whether or not the drop table executes
without error. Furthermore, if the create table fails for whatever reason
then the following statement in its go-block (ie. commit) is NOT executed.
The thread of execution now jumps to the next go-block, which executes a
rollback.

If the create succeeds then the commit is executed and the following
rollback is executed but has no effect.

Martin Bowes

--
Random Earthworm Jim Quote #22:
Jim - Lets all just acknowledge that I made a mistake and move on!

Fabio DONNA

unread,

Jul 31, 2001, 4:40:35 AM7/31/01

to info-...@ams.org

Hi Martin,
first let me thank you for your kind and fast reply.

I missed to specify that my SQL code is run in a report in the .setup
section.
I've also realized that I've written a wrong example (I wasn't drunk, I
swear! :-)).
Please take a look to the following one:

.setup
drop table tbl_name;
create table tbl_name as ......

.query
select * from tbl_name

If table tbl_name doesn't exist when I run the report an error is
displayed and the report quits at the line that generated the error
(drop table...).

Thank you very much

Kind regards

Fabio

mailto:fabio...@agilent.com
http://benc01rm.italy.agilent.com

Fabio DONNA

unread,

Jul 31, 2001, 4:55:30 AM7/31/01

to info-...@ams.org

Hello Karl,
thank you for your reply.

I've wrongly described the situation. Please look at my reply to Martin
for details.

Thanks again

Regards

Fabio

Mason, Paul

unread,

Jul 31, 2001, 5:08:18 AM7/31/01

to Martin Bowes, info-...@e-math.ams.org

> Some damn fool decided to change this under OpenIngres
> and IngresII.

Sorry Martin I can't let that one go!

In the sql terminal monitor (not isql) there are two on error states 'continue' and 'nocontinue'. The default is 'continue' - which means continue after an error. This can be changed by using the "\nocontinue" in the sql script or by setting II_TM_ON_ERROR=nocontinue. The script command will take precedence over the value of II_TM_ON_ERROR.

In 6.4 the nocontinue behaviour was to jump to the next 'go block' i.e. after the next \g. People found this very useful because you could perform simple logic e.g.

        statement1;
      statement2;
      statement3;

      commit;
      \g
      rollback;
      \g

Under the 6.4 nocontinue - if any of the 3 statements fail all will be rolled back. Normally only deadlock and logfile full cause a transaction rollback other errors cause a statement rollback.

Anyway a customer raised a call that II_TM_ON_ERROR did not behave in the documented way. If you check the section for II_TM_ON_ERROR in the 6.4/1.2 DBA guide (or II Sys Ref Guide) you'll see that it says that the valid values are "continue" for continue on error or "terminate" for terminate on error. This customer expected that terminate meant terminate the whole script (the manual doesn't mention go blocks in that section). A bug was raised (77034) and fixed.

Most people noticed the change (if at all) in OpenIngres 1.2 or Ingres II after upgrading - but if you have a later patch for 6.4 with the 77034 fix in you will have the new behaviour.

Why wasn't the usefulness of the old behaviour taken into account? I can only speculate - but maybe the technician involved wasn't aware people used it in this way. I used Ingres for nearly 10 years before joining CA and I had never come across the kind of scripting above in that time.

Anyway as Martin says - the howls came and soon bug 87415 was raised about the behaviour changing. The fix for which was to introduce II_TM_SWITCH - which when set to "true" reverts to the old "jump to next go block" behaviour. So now you can have which nocontinue behaviour you prefer.

I just wanted to make people aware this was not changed on some idle whim.

Regards

Paul Mason

Karl & Betty Schendel

unread,

Jul 31, 2001, 5:50:43 AM7/31/01

to Fabio DONNA, info-...@ams.org

At 10:03 AM +0200 7/31/01, Fabio DONNA wrote:
>
>I missed to specify that my SQL code is run in a report in the .setup
>section.
>I've also realized that I've written a wrong example (I wasn't drunk, I
>swear! :-)).
>Please take a look to the following one:
>
>.setup
>drop table tbl_name;
>create table tbl_name as ......
>
>.query
>select * from tbl_name
>
>If table tbl_name doesn't exist when I run the report an error is
>displayed and the report quits at the line that generated the error
>(drop table...).

Ah, that changes things. I don't think there is any good way to test
for table existence in a .SETUP section. Fortunately there is a better
way: use session temporary tables. Change your create table to
declare global temporary table ... ... on commit preserve rows with norecovery;
and everywhere you use the table name prefix it with session:
select * from session.tbl_name

Not only does that remove the hassle of dealing with table existence,
it should be a good bit faster too.

Martin Bowes

unread,

Jul 31, 2001, 7:07:27 PM7/31/01

to Mason, Paul, ingres newsgroup

Hi Paul,

The 'Damn Fool' line was in fact the quiet aftershock of a fairly powerful
explosion from many years before! Sort of like Krakatoa circa 550AD.

I suspect you can guess how we found out about the 'change in
interpretation' of \nocontinue as we went for Ingres 6.4 to OpenIngres.
Something it never occurred to me to test and something which so much hung
on. I found that out the next day as we started to clean up the mess. May
I suggest to all DBA's the importance of keeping large bits of crooked
timber to nail across the doors and windows to prevent the rampaging
hordes from extracting revenge.

At the time this occurred I re-read the manual on nocontinue and I must
admit found like the local CA reps suggested that it was ambiguous.

But it didnt help my temper at the time!

Now after many years of therapy, for which CA still havent paid up, I can
look back and laugh. Sort of! In the clear light of day, I know the
programmer did not change this on a whim, I know this was not part of the
Conspiracy Against Me, I know threats to torture and then kill are wrong.

I will forgive,... but I will never forget!

I will go spend some time in my happy place.

Marty
>
> This message is in MIME format. Since your mail reader does not understand
> this format, some or all of this message may not be legible.
>
> ------_=_NextPart_001_01C1199F.493D67A0
> Content-Type: text/plain;
> charset="iso-8859-1"

> ------_=_NextPart_001_01C1199F.493D67A0
> Content-Type: text/html;
> charset="iso-8859-1"
> Content-Transfer-Encoding: quoted-printable
>
> <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
> <HTML>
> <HEAD>
> <META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; =
> charset=3Diso-8859-1">
> <META NAME=3D"Generator" CONTENT=3D"MS Exchange Server version =
> 5.5.2654.19">
> <TITLE>RE: Error trapping in ingres SQL</TITLE>
> </HEAD>
> <BODY>
>
>         =
>          
> >     Some damn fool decided =
> to change this under OpenIngres 
> > and IngresII.
> 
>
> Sorry Martin I can't let that one go! 
> 
>
> In the sql terminal monitor (not isql) there are two =
> on error states 'continue' and 'nocontinue'. The default is 'continue' =
> - which means continue after an error. This can be changed by using the =
> "\nocontinue" in the sql script or by setting =
> II_TM_ON_ERROR=3Dnocontinue. The script command will take precedence =
> over the value of II_TM_ON_ERROR.
>
> In 6.4 the nocontinue behaviour was to jump to the =
> next 'go block' i.e. after the next \g. People found this very useful =
> because you could perform simple logic e.g.
> 
>
>         SIZE=3D2>statement1;
>       statement2;
>       statement3;
>        
>       commit;
>       \g
>       rollback;
>       \g
> 
>
> Under the 6.4 nocontinue - if any of the 3 statements =
> fail all will be rolled back. Normally only deadlock and logfile full =
> cause a transaction rollback other errors cause a statement rollback. =
> 
>
> Anyway a customer raised a call that II_TM_ON_ERROR =
> did not behave in the documented way. If you check the section for =
> II_TM_ON_ERROR in the 6.4/1.2 DBA guide (or II Sys Ref Guide) you'll =
> see that it says that the valid values are "continue" for =
> continue on error or "terminate" for terminate on error. This =
> customer expected that terminate meant terminate the whole script (the =
> manual doesn't mention go blocks in that section). A bug was raised =
> (77034) and fixed. 
>
> Most people noticed the change (if at all) in =
> OpenIngres 1.2 or Ingres II after upgrading - but if you have a later =
> patch for 6.4 with the 77034 fix in you will have the new behaviour. =
> 
>
> Why wasn't the usefulness of the old behaviour taken =
> into account? I can only speculate - but maybe the technician involved =
> wasn't aware people used it in this way. I used Ingres for nearly 10 =
> years before joining CA and I had never come across the kind of =
> scripting above in that time. 
>
> Anyway as Martin says - the howls came and soon bug =
> 87415 was raised about the behaviour changing. The fix for which was to =
> introduce II_TM_SWITCH - which when set to "true" reverts to =
> the old "jump to next go block" behaviour. So now you can =
> have which nocontinue behaviour you prefer.
>
> I just wanted to make people aware this was not =
> changed on some idle whim.
> 
>
> Regards
> 
>
> Paul Mason      =
>         =
>         =
>         
> 
>
> </BODY>
> </HTML>
> ------_=_NextPart_001_01C1199F.493D67A0--
>

--
Random Earthworm Jim Quote #17:
Bob(The Killer GoldFish) -
If you want something done right, hire a guy with a monkey for a head.
Thats what I always say.

Fabio DONNA

unread,

Aug 1, 2001, 5:10:24 AM8/1/01

to info-...@ams.org

Ok, I'll try this solution!

Thank you all!

Ciao

Fabio

--