473,320 Members | 1,947 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Re: How to check SQLCODE in trigger

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....m-db2/200807/1

Jul 15 '08 #1
3 7284
lenygold via DBMonster.com wrote:
I TRIED ONLY SP AND ALSO AN ERROR:

CALL execute_immediate('select * FROM FAMILY',out_sqlcode);

CALL execute_immediate('select * FROM FAMILY',out_sqlcode)
SQL0206N "OUT_SQLCODE" is not valid in the context where it is used.
SQLSTATE=42703
sqlcode: -206
Of course out_sqlcode is not defined.

in your trigger this presumably looks like this:

CREATE TRIGGER ....
BEGIN ATOMIC
DECLARE out_sqlcode INTEGER;
CALL execute_immediate('....', out_sqlcode);
END
@
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Jul 16 '08 #2
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
Jul 16 '08 #3
Thank you Serge for promt responce.
But i am using this trigger for screen edit.
When edit is passed (sqlcode = 0 after insert) row is deleted from the
table.
But if i have several DATES on the screen -180 OR -181 does not tell me what
date field is wrong.
Message generated by trigger: '292 INVALID HIREDATE' tells me what
field on the screen is invalid and 292 is the HIREDATE screen position which
will be used in SEND MAP CICS statement ,to position cusrsor in invalid field.

Using triggers for edit saved us 70% coding time and also made available
all best DB2 features insted 1000's lines of COBOL coding.
So if i can not use triggers to overlay system generated error message,
how can I resolve this issue. May be Constraint on data fields will help?
Thahk You again Serge. I learn a lot new things on this board.
Date is invalid.

Serge Rielau wrote:
>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
--
Message posted via DBMonster.com
http://www.dbmonster.com/Uwe/Forums....m-db2/200807/1

Jul 16 '08 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: ezra epstein | last post by:
Hi, I've got a table: <code language="SQL"> CREATE TABLE "common"."dynamic_enum" ( "pk_id" integer DEFAULT nextval('"common"."pw_seq"') , "enum_type" ...
2
by: Tborn2b | last post by:
DB2 V 7, Z/OS: I receive an SQLCODE -104 for the following trigger : CREATE TRIGGER TRSERED1 NO CASCADE BEFORE INSERT ON SEMINAR REFERENCING NEW AS ZUGANG
5
by: Anthony Robinson | last post by:
Consider the following tables: CREATE TABLE "AIMD "."CHANNELSESSION" ( "CHANNELSESSIONID" DECIMAL(13,0) NOT NULL GENERATED ALWAYS AS IDENTITY ( START WITH +1 , INCREMENT BY +1 , CACHE 20 ) ,...
5
by: Jean-Marc Blaise | last post by:
Dear all, Could you explain why some SQL messages do not possess a SQLSTATE ? Why not transmitting the SQLSTATE of the original <sqlcode>, for example if you get a -911 on a RUNSTATS ? ...
5
by: db2sysc | last post by:
ALL: I have created a INSERT trigger on table A in subsystem X. This AFTER INSERT trigger will call a COBOL stored procedure, which inturn CONNECTs to subsystem Y and inserts the same row into...
1
by: Serge Rielau | last post by:
Hi All, I'd like to get feedback (through email) from folks who use CALL in Trigger and do or do not get in trouble with the -746 SQLCODE: SQL0746N Routine "<routine-name>" (specific name...
15
by: Twan Kennis | last post by:
Hi, I have a DB2 database on the IBM iSeries platform, on which I created several Stored Procedures with the SQLCODE as a return-parameter. These Stored Procedures are called from a Windows...
2
by: Richard | last post by:
Our web programmer was looking in his application log an found the following error: 2006-08-31 16:33:35,129 ERROR org.hibernate.util.JDBCExceptionReporter - < SQL0723N An error occurred in a...
1
by: amarhegde | last post by:
Hi , This error is got in the logs ErrorLogger E org.quartz.core.ErrorLogger schedulerError An error occured while scanning for the next trigger to fire. ...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.