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.
