Question posted by: lenygold via DBMonster.com
(Guest)
on
July 15th, 2008 11:40 PM
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:
Quote:
Originally Posted by
Quote:
Originally Posted by
>I have an edit trigger:
>>
|
>[quoted text clipped - 52 lines]
Quote:
Originally Posted by
>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...bm-db2/200807/1
|
|
July 16th, 2008 12:15 PM
# 3
|
Re: Re: How to check SQLCODE in trigger
Hmm, OK perhaps I am a bit slow these days, but I think I start to get
what you are doing... and it won't work...
When you insert into a DATE column. DB2 will aggressively(!) ensure the
date is sane. That is this error is being raised before your trigger is
being called.
Now clearly DB2 will not allow bad dates into the table (which is what
you rely on with your AFTER trigger.
But DB2 will also not allow bad dates to flow through it's runtime code.
Thus even a BEFORE trigger will do you no good.
There are three ways to do what you want to do on the database side:
* Use an instead of trigger on a view where the view maps the DATe in
the table to a VARCHAR and the INSTEAD OF trigger maps it back
That is a horrible idea
* Store a string in the database instead of a date.
Preferably in a yyyymmdd format, so you can do comparisons on it.
That is slightly less horrid
* Use a stored procedure instead of an INSERT to drive your
modification.
There are users who do this on principle.
"No SQL other than a CALL in my app"
This way your proc can do everything it wants to and you
insert once you are satisfied.
Now, all this can be avoided if you shift your thinking:
DB2 gave you a perfectly good error message saying exactly what you
wanted to say. Why not use it? Let the application catch the -181 (or
the associated SQLSTATE which is likely ANSI Standard)
Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
