db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From david myers <david.myers.scibearsp...@gmail.com>
Subject Re: Problem using user-defiined function in query
Date Wed, 29 Aug 2012 21:10:14 GMT
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




Mime
View raw message