Re: Calling a java function from a trigger

2019-09-12 Thread fkalim 
Thanks! I tried that first and got the following error. It was only after
that that I went down this path of trying to set derby.database.classpath.
Any ideas would be great!

SQL [CREATE TRIGGER  extract_keyword_trigger AFTER INSERT ON TEMP
REFERENCING NEW AS newTable FOR EACH ROW CALL extract_keyword(newTable.test)
]; No method was found that matched the method call
TestListener.capitalize(java.lang.String), tried all combinations of object
and primitive types and any possible type conversion for any  parameters the
method call may have. The method might exist but it is not public and/or
static, or the parameter types are not method invocation convertible or the
derby.database.classpath property is missing or incorrectly defined.






--
Sent from: 
http://apache-database.10148.n7.nabble.com/Apache-Derby-Users-f95095.html

Re: Calling a java function from a trigger

2019-09-12 Thread Rick Hillegas 
You don't need the schema name in the EXTERNAL NAME clause. The 
INSTALL_JAR and SYSCS_SET_DATABASE_PROPERTY calls wire together a custom 
classpath for your database. All of the classes in the installed jar 
files will appear to your database session as though they are on your 
classpath. So you just need to refer to classes by their ordinary class 
names.


Try the following (assuming that TestListener lives in the root of the 
package space):


CREATE procedure extract_keyword (test VARCHAR(100))   LANGUAGE JAVA
PARAMETER STYLE JAVA  NO SQL EXTERNAL NAME 'TestListener.capitalize';

Hope this helps,
-Rick

On 9/12/19 5:14 PM, fkalim wrote:

To give more information, I compile my code into a jar, install the jar and
then try to use the Java file in the jar. The name of the java class is
TestListener. When I use the sql code below, I get the error:

ERROR 42X51: The class 'APP.TestListener' does not exist or is inaccessible.
This can happen if the class is not public or the derby.database.classpath
property is missing or incorrectly defined.
ERROR XJ001: Java exception: 'APP.TestListener:
java.lang.ClassNotFoundException'.

SQL:

CALL SQLJ.INSTALL_JAR('/listener.jar', 'APP.TestListener', 0);

CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY( 'derby.database.classpath',
'APP.TestListener');

CREATE procedure extract_keyword (test VARCHAR(100))   LANGUAGE JAVA
PARAMETER STYLE JAVA  NO SQL EXTERNAL NAME 'APP.TestListener.capitalize';




--
Sent from: 
http://apache-database.10148.n7.nabble.com/Apache-Derby-Users-f95095.html

Re: Calling a java function from a trigger

2019-09-12 Thread fkalim 
To give more information, I compile my code into a jar, install the jar and
then try to use the Java file in the jar. The name of the java class is
TestListener. When I use the sql code below, I get the error:

ERROR 42X51: The class 'APP.TestListener' does not exist or is inaccessible.
This can happen if the class is not public or the derby.database.classpath
property is missing or incorrectly defined.
ERROR XJ001: Java exception: 'APP.TestListener:
java.lang.ClassNotFoundException'.

SQL:

CALL SQLJ.INSTALL_JAR('/listener.jar', 'APP.TestListener', 0);

CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY( 'derby.database.classpath', 
'APP.TestListener');

CREATE procedure extract_keyword (test VARCHAR(100))   LANGUAGE JAVA
PARAMETER STYLE JAVA  NO SQL EXTERNAL NAME 'APP.TestListener.capitalize';




--
Sent from: 
http://apache-database.10148.n7.nabble.com/Apache-Derby-Users-f95095.html

Re: Calling a java function from a trigger

2019-09-12 Thread fkalim 
Thanks Rick! This is really helpful. How would I let derby know where the
compiled classes are placed? 

In fact, if I am creating the connection to the database in Java, and also
creating the tables and triggers programmatically, how do tell derby where
the classes are located?



--
Sent from: 
http://apache-database.10148.n7.nabble.com/Apache-Derby-Users-f95095.html

Re: Calling a java function from a trigger

2019-09-12 Thread Rick Hillegas 

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.