Re: Problem using user-defiined function in query

[Rick Hillegas]

On 8/29/12 11:06 AM, TXVanguard wrote:
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.)

Can you share more information about this problem? E.g., the query which 
invokes the replace() function and the full stack trace from derby.log? 
The following naive attempt to reproduce the problem did not manage to 
trigger it:

public class StringUtils
{
    public  static  String  replace( String base, String oldText, 
String newText )
    {
        if ( (base == null) || (oldText == null) || (newText == null) ) 
{ return null; }
        return base.replace( oldText, newText );
    }
}

connect 'jdbc:derby:memory:db;create=true';

create function replace
(
    baseText varchar( 512),
    oldText varchar( 512 ),
    newText varchar( 512 )
)
returns varchar( 512 )
language java parameter style java no sql
external name 'StringUtils.replace';

create table t1( a varchar( 512 ) );
create table t2( a varchar( 512 ) );

insert into t1( a ) values ( 'abc1abc' );
insert into t2( a ) values ( 'def' );

select t1.a, replace( t1.a, 'abc', t2.a )
from t1, t2;

Thanks,
-Rick