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
