By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,028 Members | 1,869 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,028 IT Pros & Developers. It's quick & easy.

DB2 Java stored procedure call

P: n/a
Hi, is there any way to call a JAVA stored procedure from a SQL Trigger?
We are having difficulties with this and cannot verify whether DB2 even
*has* this capability?

Thank you very much....
Aug 30 '06 #1
Share this Question
Share on Google+
4 Replies


P: n/a
Pakna wrote:
Hi, is there any way to call a JAVA stored procedure from a SQL Trigger?
We are having difficulties with this and cannot verify whether DB2 even
*has* this capability?

Thank you very much....
CALL in trigger is supported with DB2 V8.2 (V8.1 FP7) for LUW.
What sort of error do you get?
If you call a procedure from a BEFORE trigger make sure the proc is
defined as READS SQL DATA.

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/
Aug 30 '06 #2

P: n/a
Serge Rielau wrote:
Pakna wrote:
>Hi, is there any way to call a JAVA stored procedure from a SQL
Trigger? We are having difficulties with this and cannot verify
whether DB2 even *has* this capability?

Thank you very much....
CALL in trigger is supported with DB2 V8.2 (V8.1 FP7) for LUW.
What sort of error do you get?
If you call a procedure from a BEFORE trigger make sure the proc is
defined as READS SQL DATA.

Cheers
Serge
Serge Rielau wrote:
Pakna wrote:
>Hi, is there any way to call a JAVA stored procedure from a SQL
Trigger? We are having difficulties with this and cannot verify
whether DB2 even *has* this capability?

Thank you very much....
CALL in trigger is supported with DB2 V8.2 (V8.1 FP7) for LUW.
What sort of error do you get?
If you call a procedure from a BEFORE trigger make sure the proc is
defined as READS SQL DATA.

Cheers
Serge
OK, here's the deal...we have a Java stored procedure which takes six
parameteres, two of these are input parameters (the first two in the
parameter brackets, both integer) and the other four are output
parameters (integer, sqlstate varchar, sqlcode integer and sqlmessage
varchar).

Now, the we've eventually narrowed down the problem to output parameter
definition - the best we could come up with is those four NULLS that can
be observed at the end of the brackets.

The catch is this - we cannot figure out how to explicitly define output
parameters (nor can I personally understand why is there a need to even
do so).

So the question is - how to define those pesky output parameters (we've
tried to omit them, it won't do).

******BEGIN TRIGGER SEGMENT*************

CREATE TRIGGER DB2CROSS.AIR_triger_proba
AFTER
INSERT
ON DB2CROSS.proba
REFERENCING
NEW AS N
FOR EACH ROW
MODE DB2SQL
BEGIN ATOMIC
CALL DB2CROSS.INS_DOST_LIST_CLAN (2, 10, null, null, null, null);
END;

*********ERROR SEGMENT***********

DB21034E The command was processed as an SQL statement because it was not a

valid Command Line Processor command. During SQL processing it returned:

SQL0469N The parameter mode (IN, OUT, or INOUT) is not valid for a
parameter

in procedure "INS_DOST_LIST_CLAN" with specific name "SQL060713101412210"

(parameter number "2", name "P_USPJEH"). LINE NUMBER=1. SQLSTATE=42886

***********END ERROR SEGMENT****************
Aug 30 '06 #3

P: n/a
--#SET TERMINATOR @
CREATE TRIGGER DB2CROSS.AIR_triger_proba
AFTER
INSERT
ON DB2CROSS.proba
REFERENCING
NEW AS N
FOR EACH ROW
BEGIN ATOMIC
DECLARE mysqlcode INTEGER;
DECLARE mysqlstate CHAR(5);
DECLARE myMsg VARCHAR(70);
CALL DB2CROSS.INS_DOST_LIST_CLAN (2, 10, mysqlcode, mysqlstate,
mymsg);
END
@

All languages I ever worked with will complain if you try to assign a
value to a value, so out parameters require out which are variable.
No expressions, no constants allowed.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/
Aug 30 '06 #4

P: n/a
Serge Rielau wrote:
--#SET TERMINATOR @
CREATE TRIGGER DB2CROSS.AIR_triger_proba
AFTER
INSERT
ON DB2CROSS.proba
REFERENCING
NEW AS N
FOR EACH ROW
BEGIN ATOMIC
DECLARE mysqlcode INTEGER;
DECLARE mysqlstate CHAR(5);
DECLARE myMsg VARCHAR(70);
CALL DB2CROSS.INS_DOST_LIST_CLAN (2, 10, mysqlcode, mysqlstate,
mymsg);
END
@

All languages I ever worked with will complain if you try to assign a
value to a value, so out parameters require out which are variable.
No expressions, no constants allowed.

Cheers
Serge
Thank you very much, it did work, though we didn't need the terminator.
Where did you notice we were trying to assign a value to value, we
didn't notice that?
Sep 1 '06 #5

This discussion thread is closed

Replies have been disabled for this discussion.