DB2 Java stored procedure call

[Pakna]

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....

[Serge Rielau]

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/

[Pakna]

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****************

[Serge Rielau]

--#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/

[Pakna]

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?