Hi All,

Continuing my investigation of table procedures....how do you raise an error? Should you?

Here's an example.

create procedure hex2int(
hex_string varchar(32) not null not default
)
result row my_int(int_val integer8 with null)
as
declare
pos integer not null not default;
hex_length integer not null not default;
ch char(1) not null not default;
msg varchar(256) not null not default;
int_val integer8 with null;
next_val integer1 not null not default;
hex_char char(16) not null not default;
begin
hex_char = '0123456789ABCDEF';
int_val = 0;
hex_length = length(hex_string);
pos = hex_length;
while (pos > 0) do
ch = uppercase(charextract(hex_string, pos));
next_val = locate(hex_char, ch);
if (next_val > 1 and next_val <= 16) then
int_val = int_val + ((next_val - 1) * (16 ** (hex_length - pos)));
elseif (next_val > 16) then
/*
msg = varchar(hex_string) + ' is not a hex string!';
raise error 99999 :msg;
*/
int_val = null;
endloop;
endif;
pos = pos - 1;
next_val = next_val -1;
endwhile;

return row (:int_val);
end;
\p\g
declare global temporary table x(
a varchar(32) not null
)
on commit preserve rows with norecovery;
insert into x values('a');
insert into x values('ff');
insert into x values('this is not a hex string');
insert into x values('00002AAACB65C200');
insert into x values('r7824578t1fbui785g789');

select a, b.int_val
from x, hex2int(a) b
\p\g

In the above example if you uncomment the attempt to raise error the procedure will:

* create without error.

* But when executed will produce error: E_QE0310 Unsupported procedure statement type: EMSG

Martin Bowes