You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-user@db.apache.org by TXVanguard <br...@lmco.com> on 2012/08/29 20:06:01 UTC
Problem using user-defiined function in query
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.
Re: Problem using user-defiined function in query
Posted by Rick Hillegas <ri...@oracle.com>.
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
Re: Problem using user-defiined function in query
Posted by david myers <da...@gmail.com>.
On 29/08/12 20:06, 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.)
>
Hi TXVanguard,
I think your problem may be that your java method is expecting a
"string" value in its 3rd variable, rather than a reference to a
table.column in the DB.
If this is the case, how does the method know that the value t2.col is a
reference or not?
If it is a reference I'm guessing that the table t2 has lots of entries
(rows), you will need to define exactly the specific row you want to
catch in an sql statement.
If however the tables both join on a single unique column value, you
probably need to tell your java method how to get this value.
Can I propose overloading your replace method with the following java
method header, with the addition of a boolean switch....
public static String replace(final String str, final String oldText,
final String newText, final boolean isRelatedTable){
if(isRelatedTable){//the newText variable is actually a table reference
String sql = //create a join statement between the 2 tables to get the desired result from newText
// set the local variable newText to be result of above SQL;
}
return str.replace(oldText, newText);
}//end replace()
Your other option is to perform the lookup on the second table prior to
calling the replace() method.
The reason this works in Access is probably because you are doing it
from a user interface, you have the 2 fields on the same layout, and the
tables are were automatically linked (which you did when you created the
layout). The reference you are using isn't actually the reference to the
table (although for all intents it looks like it is from the way it is
expressed), but a reference to the contents of the text box on the
interface (often the text box defaults to having the same name as the
table.column).
Access is great for this sort of stuff, but if you try to do what you
are doing from an automation script you'll pull your hair out trying to
get it work, even if you use VB or .NET