I have a user-defined REPLACE function that was created like this:
CREATE FUNCTION REPLACE
(STR VARCHAR(512), oldText VARCHAR(512), newText VARCHAR(512))
RETURNS VARCHAR(512)
PARAMETER STYLE JAVA NO SQL LANGUAGE JAVA
EXTERNAL NAME 'Setup.SQL_Functions.replace'
The external function it calls look like this:
public static String replace(final String str, final String oldText,
final String newText)
return str.replace(oldText, newText);
}
When I use this function in a query, it works if I do something like this:
REPLACE(t1.col1, 'abc', 'def')
but not if I do something like this
REPLACE(t1.col1, 'abc', t2.col2)
(in these example, t1 and t2 are tables).
When I use REPLACE as in the second example above, the following exception
is thrown:
Exception in thread "main" java.sql.SQLException: The exception
'java.lang.ArrayIndexOutOfBoundsException: -1' was thrown while evaluating
an expression.
at
org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown
Source)
at org.apache.derby.impl.jdbc.Util.newEmbedSQLException(Unknown Source)
at org.apache.derby.impl.jdbc.Util.seeNextException(Unknown Source)
at
org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknown
Source)
at
org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Unknown
Source)
at org.apache.derby.impl.jdbc.EmbedConnection.handleException(Unknown
Source)
at org.apache.derby.impl.jdbc.ConnectionChild.handleException(Unknown
Source)
at org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(Unknown
Source)
at org.apache.derby.impl.jdbc.EmbedStatement.execute(Unknown Source)
at org.apache.derby.impl.jdbc.EmbedStatement.executeUpdate(Unknown
Source)
How can I make this work? (Just for the record, the built-in REPLACE
function in Access works as intended; I'm trying to write my own
user-defined REPLACE function for use in Derby.)
--
View this message in context:
http://old.nabble.com/Problem-using-user-defiined-function-in-query-tp34348255p34348255.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.
