You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-user@db.apache.org by fkalim <ka...@illinois.edu> on 2019/09/12 09:13:02 UTC
Calling a java function from a trigger
Hi,
I'm looking for a clear example on how to call a java function from a
trigger based on an inserted row. Any help would be appreciated.
I have attached what I have so far. Currently, the error I get is on the the
last line of the trigger:
org.jooq.exception.DataAccessException: SQL [CREATE TRIGGER
"extract_keyword_trigger" AFTER INSERT ON "TEMP" FOR EACH ROW CALL
"extract_keyword" (new."TEST") ]; Column name 'TEST' appears in a statement
without a FROM list.
public class TestListener {
public static double capitalize(String input) {
System.out.println(input.toUpperCase());
return 0.0;
}
@Test
public void testHavingClause() {
// create database
final DSLContext conn = setup();
conn.execute("create table testtable\n" +
"(test varchar(100) not null primary key)");
conn.execute("CREATE FUNCTION extract_keyword (test VARCHAR(100)) "
+
"RETURNS DOUBLE " +
"PARAMETER STYLE JAVA " +
"NO SQL LANGUAGE JAVA " +
"EXTERNAL NAME 'com.example.TestListener.capitalize'");
conn.execute("CREATE TRIGGER \"extract_keyword_trigger\" " +
"AFTER INSERT " +
"ON \"testtable\" " +
"FOR EACH ROW " +
"CALL \"extract_keyword\" (new.\"test\")");
conn.execute("insert into testtable values ('test1')");
conn.execute("insert into testtable values ('test2')");
}
private DSLContext setup() {
final Properties properties = new Properties();
properties.setProperty("foreign_keys", "true");
try {
// The following block ensures we always drop the database
between tests
try {
final String dropUrl = "jdbc:derby:memory:test;drop=true";
getConnection(dropUrl, properties);
} catch (final SQLException e) {
// We could not drop a database because it was never
created. Move on.
}
// Create a fresh database
final String connectionURL =
"jdbc:derby:memory:test;create=true";
final Connection conn = getConnection(connectionURL,
properties);
final DSLContext using = using(conn, SQLDialect.DERBY);
return using;
} catch (final SQLException e) {
throw new RuntimeException(e);
}
}
@CanIgnoreReturnValue
private Connection getConnection(final String url, final Properties
properties) throws SQLException {
return DriverManager.getConnection(url, properties);
}
}
--
Sent from: http://apache-database.10148.n7.nabble.com/Apache-Derby-Users-f95095.html
Re: AW: Calling a java function from a trigger
Posted by fkalim <ka...@illinois.edu>.
Hi,
So I think I was able to figure it out. The listener was in the main source
code, whereas the updates to the table were done from the tests. Once I
moved the listener to the tests, it worked.
Thanks for all your help!
--
Sent from: http://apache-database.10148.n7.nabble.com/Apache-Derby-Users-f95095.html
AW: Calling a java function from a trigger
Posted by "Hohl, Gerrit" <g....@aurenz.de>.
Hello fkalim,
hello Rick,
just saw your discussion here and I guess the problem is maybe - without having tested it - that you have to use the full qualified name oft the class including the package name.
Just by specifying the JAR and the class-name Derby / the class-loader shouldn't be able to find the right class as the JAR might contain classes having the same name in different packages.
"Try the following (assuming that TestListener lives in the root of the package space):"
So the point Rick already mentioned maybe be the vital point here: Maybe your TestListener isn't in the root (means you haven't any packages at all?).
Let's say your TestListener in the package my.project use my.project.TestListener instead.
Best regards,
Gerrit
-----Ursprüngliche Nachricht-----
Von: fkalim <ka...@illinois.edu>
Gesendet: Freitag, 13. September 2019 05:57
An: derby-user@db.apache.org
Betreff: Re: Calling a java function from a trigger
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
Posted by fkalim <ka...@illinois.edu>.
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
Posted by Rick Hillegas <ri...@gmail.com>.
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('<path>/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
Posted by fkalim <ka...@illinois.edu>.
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('<path>/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
Posted by fkalim <ka...@illinois.edu>.
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
Posted by Rick Hillegas <ri...@gmail.com>.
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.