Calling a java function from a trigger

classic Classic list List threaded Threaded
8 messages Options
Reply | Threaded
Open this post in threaded view
|

Calling a java function from a trigger

fkalim
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
Reply | Threaded
Open this post in threaded view
|

Re: Calling a java function from a trigger

Rick Hillegas-3
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.


Reply | Threaded
Open this post in threaded view
|

Re: Calling a java function from a trigger

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
Reply | Threaded
Open this post in threaded view
|

Re: Calling a java function from a trigger

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('<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
Reply | Threaded
Open this post in threaded view
|

Re: Calling a java function from a trigger

Rick Hillegas-3
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
>

Reply | Threaded
Open this post in threaded view
|

Re: Calling a java function from a trigger

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
Reply | Threaded
Open this post in threaded view
|

AW: Calling a java function from a trigger

Hohl, Gerrit
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 <[hidden email]>
Gesendet: Freitag, 13. September 2019 05:57
An: [hidden email]
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
Reply | Threaded
Open this post in threaded view
|

Re: AW: Calling a java function from a trigger

fkalim
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