PJarosak
asked on
DB2 on AIX: Error when creating trigger that calls a stored procedure
I am working with DB2 8.1 on AIX 5.2. I am trying to call a stored procedure from a trigger. The trigger is written in SQL. The stored procedure is declared in SQL and defined as an external routine in C. The declarations are as follows:
create procedure testschema.proc_test (in chat_id char(96), in in_clob clob)
dynamic result sets 0
language c
parameter style general
fenced not threadsafe
modifies sql data
program type sub
external name 'proc_test!proc_test'@
create trigger testschema.proc_test
after
insert on testschema.w_chat_transcri pt
referencing new as n
for each row
begin atomic
declare rs integer default 0;
call testschema.proc_test (n.chat_id, n.transcript);
get diagnostics rs = return_status;
values (case when rs < 0 then raise_error ('70001', 'proc call failed'));
end@
I can catalog the stored proc just fine but when I go to catalog the trigger I get the following error:
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0104N An unexpected token "call" was found following "integer default 0;
". Expected tokens may include: "<compound_return>". LINE NUMBER=8.
SQLSTATE=42601
I have looked at many examples and they are all using the 'call' statement just as I am. Any insight as to why I am seeing this error?
create procedure testschema.proc_test (in chat_id char(96), in in_clob clob)
dynamic result sets 0
language c
parameter style general
fenced not threadsafe
modifies sql data
program type sub
external name 'proc_test!proc_test'@
create trigger testschema.proc_test
after
insert on testschema.w_chat_transcri
referencing new as n
for each row
begin atomic
declare rs integer default 0;
call testschema.proc_test (n.chat_id, n.transcript);
get diagnostics rs = return_status;
values (case when rs < 0 then raise_error ('70001', 'proc call failed'));
end@
I can catalog the stored proc just fine but when I go to catalog the trigger I get the following error:
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0104N An unexpected token "call" was found following "integer default 0;
". Expected tokens may include: "<compound_return>". LINE NUMBER=8.
SQLSTATE=42601
I have looked at many examples and they are all using the 'call' statement just as I am. Any insight as to why I am seeing this error?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER