Thank you very much SERGE for your help.
I found example in Graeme Birchall COOKBOOK wich i think exactly what i
need
for SQL
check in triggers:
• User query joins to table function - sends DML or DDL statement to be
executed.
• Table function calls stored procedure - sends statement to be
executed.
• Stored procedure executes statement.
• Stored procedure returns SQLCODE of statement to the table function.
• Table function joins back to the user query a single-row table with
two
columns: The
SQLCODE and the original input statement.
--#SET TERMINATOR !
CREATE PROCEDURE execute_immediate (IN in_stmt VARCHAR(1000)
,OUT out_sqlcode INTEGER)
LANGUAGE SQL
MODIFIES SQL DATA
BEGIN
DECLARE sqlcode INTEGER;
DECLARE EXIT HANDLER FOR sqlexception
SET out_sqlcode = sqlcode;
EXECUTE IMMEDIATE in_stmt;
SET out_sqlcode = sqlcode;
RETURN;
END!
--#SET TERMINATOR !
CREATE FUNCTION execute_immediate (in_stmt VARCHAR(1000))
RETURNS TABLE (sqltext VARCHAR(1000)
,sqlcode INTEGER)
LANGUAGE SQL
MODIFIES SQL DATA
BEGIN ATOMIC
DECLARE out_sqlcode INTEGER;
CALL execute_immediate(in_stmt, out_sqlcode);
RETURN VALUES (in_stmt, out_sqlcode);
END!
Then i tryied to test it:
select 1,stm.sqlcode as sqlcode, CHAR(stm.sqltext,100) as sqltext
from sysibm.sysdummy1
,table(execute_immediate('select * from emp_screen_edit')) as stm;
and got the followung error:
sqlstate: 429BL
The function "EXECUTE_IMMEDIATE" (specific "SQL080715180239600") modifies
SQL
data and is invoked in an illegal context. Reason code = "3
3. The table function is preceded by a table reference which is not
referenced by a function argument.
Serge please help.
Thank's in advance
Leny G.
Serge Rielau wrote:
>> I have an edit trigger:
>>
>[quoted text clipped - 52 lines]
>> Is it possible to process this error code in the trigger and populate
reason
>> field.i
>
>In DB2 for LUW not directly. To do things like condition handling inside
>of a trigger push the logic into a stored procedure and CALL that.
>The SQL Procedure has the full power of SQL PL at its disposal.
>
>Cheers
>Serge
>
--
Message posted via DBMonster.com
http://www.dbmonster.com/Uwe/Forums.aspx/ibm-db2/200807/1
|
