Conference orarep::nomahs::sql

3884.0. "how to handle CONVERT_ERROR in a module ?" by 8292::PJACOB (Patrick [email protected]) Tue Apr 29 1997 11:18

Rdb 6.1-1

A customer wants to convert dates stored in CHAR(8) into DATE ANSI in a stored
procedure. Obviously, to achieve this he uses CAST. However, how can he trap 
conversion errors. For examples, some dates in CHAR(8) datatypes may not be
valid. In the following trace, 
	- 19970229 is invalid because for 1997 february has not 29 days
	- 19921131 is invalid because october has not 31 days
	- 18581116 is invalid because date starts on 17-NOV-1858

Thus, how can we jump to an error routine to handle these cases and then
continue on the other valid dates?
Is there existing scripts/functions detecting invalid days, month , years?

Patrick.
 
$ @    cies.com
$ set noon
$ mcr sql$
attach 'filename mf_personnel';
drop module mod_date;
drop table  test_verif;
drop table  test_date;
create table test_verif
    (MSG char(10),
     Date_c char(8),
     Date_d date ansi    );
create table test_date
    (Date_c char(8)     );
insert into test_date values('19960512');
1 row inserted
insert into test_date values('19970229');
1 row inserted
insert into test_date values('19921131');
1 row inserted
insert into test_date values('18581116');
1 row inserted
insert into test_date values('19921012');
1 row inserted
create module mod_date language sql
procedure proc_date();
begin
declare :vd_date date ansi;
declare :vc_1_2  char(2);
declare :vc_3_4  char(2);
declare :vc_5_6  char(2);
declare :vc_7_8  char(2);
for :c_date
   as each row of
   select date_c from test_date
do
   insert into  test_verif
      values ('VERIF', :c_date.date_c, NULL);
      set :vc_1_2 = substring(:c_date.date_c from 1 for 2);
      set :vc_3_4 = substring(:c_date.date_c from 3 for 2);
      set :vc_5_6 = substring(:c_date.date_c from 5 for 2);
      set :vc_7_8 = substring(:c_date.date_c from 7 for 2);
      set :vd_date = cast(
          :vc_1_2 || :vc_3_4 || '-' ||
          trim(:vc_5_6) || '-' || :vc_7_8
          as date ansi);
      insert into  test_verif
         values ('OK', :c_date.date_c, :vd_date);
end for;
end;
end module;
commit;
call proc_date();
%RDB-E-CONVERT_ERROR, invalid or unsupported data conversion
-COSI-F-IVTIME, invalid date or time
select * from  test_date;
 DATE_C
 19960512
 19970229
 19921131
 18581116
 19921012
5 rows selected
select * from  test_verif;
 MSG          DATE_C     DATE_D
 VERIF        19960512   NULL
 OK           19960512   1996-05-12
 VERIF        19970229   NULL
3 rows selected
exit
$ set noverify
$ 

Well Tech Note #20 (see the RDB_60 note 9.125) contains a procedure called
LAST_DAY which has the leap year calculation and max days per month.
As for the illegal date that should be pretty straight forward to check.

You probabably need to check for illegal characters also.

Ian

Well, it seems also that error handling is not satisfactory for the customer.
Compared to Oracle7, the EXCEPTION label in stored procedure has no equivalence 
in Rdb7 module. The consequence is that, in the script in .0, Rdb7 will store 
only the first date which is valid ; with Oracle7 it will store the first and
the last date which is also valid. The customer is running Rdb 6.1-1 but I 
don't see any difference in Rdb 7.0. Bad news.

Patrick 

That is true, we have not yet added exception handling (I wanted to believe
me)

However, why isn't calling a small validation routine adequate?

Ian