[Info-Ingres] Effect of errors in a table procedure

[Martin Bowes]

Hi All,

 

In a DEP if an error occurs then a certain set of events occur. Things like:

·         no byref parameters are updated,

·         a rollback of all statements upto the error occurs,

·         execution recommences from the first statement after the one which produces the error.

 

So in a Row producing or Table procedure what should happen?

 

What if I've already generated some rows?

 

create procedure is_date(

    a_string varchar(40) not null not default

)

result row myrow (is_date integer not null)

as declare

    int_date ingresdate not null not default;

    int_string varchar(40) not null not default;

    a_date   integer not null not default;

begin

    for select :a_string into :int_string

    do

        int_date = date(:a_string);

        select iierrornumber into :a_date;

        return row (:a_date);

    endfor;

end;

\p\g

declare global temporary table x_date(

    a_string    varchar(40) not null not default

    ) on commit preserve rows with norecovery;

insert into x_date(a_string) values('1/1/2011 11:13:45');

insert into x_date(a_string) values('1/11/2010');

insert into x_date(a_string) values('not a date');

insert into x_date(a_string) values('31/1/2002');

\p\g

select x.a_string, y.is_date

from x_date x, is_date(a_string = x.a_string) y

\p\g

 

On execution this will produce:

E_US10CE 'not a date' is not a valid date/time value.

E_QE0018 Illegal parameter in control block.

 

What do people think should happen here?

 

Marty

[Roy Hann]

Martin Bowes wrote:

> Hi All,
>
> In a DEP if an error occurs then a certain set of events occur. Things like:
>

> * no byref parameters are updated,
>
> * a rollback of all statements upto the error occurs,
>
> * execution recommences from the first statement after the one which produces the error.

>
> So in a Row producing or Table procedure what should happen?
>
> What if I've already generated some rows?

[snip]

> On execution this will produce:
> E_US10CE 'not a date' is not a valid date/time value.
> E_QE0018 Illegal parameter in control block.
>
> What do people think should happen here?

Atomic fail. End of.

--
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.