db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From TXVanguard <brett.den...@lmco.com>
Subject Problem using user-defiined function in query
Date Wed, 29 Aug 2012 18:06:01 GMT

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.


Mime
View raw message