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
