db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Rick Hillegas <rick.hille...@oracle.com>
Subject Re: Problem using user-defiined function in query
Date Wed, 29 Aug 2012 19:23:31 GMT
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

Mime
View raw message