db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Rick Hillegas <rick.hille...@gmail.com>
Subject Re: Calling a java function from a trigger
Date Thu, 12 Sep 2019 13:39:59 GMT
Your sample program needs some tweaking:

1) The CREATE TRIGGER statement needs a REFERENCING clause in order to 
bind a new row transition variable. That is why you are getting the 
error 'Column name 'TEST' appears in a statement without a FROM list'

2) After that, you will run into a further issue: CALL statements invoke 
procedures rather than functions. A procedure is a Java method with no 
return value. A function is a Java method with a return value.

The following example works for me:

First compile this Java method:

public class TestProcs

{

     // sample db procedure

     public static void printOnConsole(String text)

     {

         System.out.println("Printing text on console: '" + text + "'");

     }

}


Now run the following ij script:

connect 'jdbc:derby:memory:db;create=true';

CREATE TABLE myTable(keyCol BIGINT, textCol VARCHAR(20));

CREATE PROCEDURE printOnConsole(textArg VARCHAR(20))

LANGUAGE JAVA

PARAMETER STYLE JAVA

NO SQL

EXTERNAL NAME 'TestProcs.printOnConsole';

CREATE TRIGGER insertAlert

AFTER INSERT ON myTable

REFERENCING NEW AS newRow

FOR EACH ROW

CALL printOnConsole(newRow.textCol);

INSERT INTO myTable VALUES

   (1, 'Hello')

, (2, 'World')

;


Hope this helps,
-Rick


On 9/12/19 2:13 AM, fkalim wrote:
> Column name 'TEST' appears in a statement
> without a FROM list.



Mime
View raw message