Use Java functions with Apache Derby

Question

I've encountered problems with using functions in Apache Derby, in that I can't get Derby to find my self-defined functions. The typical error message looks like this:

The class 'de.uniba.kinf.projm.hylleblomst.database.sql.utils.GroupConcat' does not exist or is inaccessible. 
This can happen if the class is not public.

The documentation states that the method must be public and static. This is what I want to use:

package de.uniba.kinf.projm.hylleblomst.database.sql.utils;
public final class GroupConcat {
    public static String groupConcat(String separator, String... arguments) {
        StringBuilder result = new StringBuilder("");
        for (String arg : arguments) {
            result.append(arg + separator + " ");
        }
        result.delete(result.length() - 2, result.length());
        return result.toString();
    }
}

Basically I just want to add the GroupConcat functionality to the database which is not provided in Derby by standard. The statement I use to add the function to the DB is this:

CREATE FUNCTION SQL_UTIL.GROUP_CONCAT 
    ( SEPARATOR CHAR, ARGS VARCHAR(255) ... )
    RETURNS VARCHAR(2000)
    PARAMETER STYLE DERBY
    NO SQL LANGUAGE JAVA
    EXTERNAL NAME 'de.uniba.kinf.projm.hylleblomst.database.sql.utils.GroupConcat.groupConcat'

I've also packed the GroupConcat class in a jar and added it to the classpath where Derby should be able to find it and added it to the database directly. Here's what I did: The .jar lies in the lib-folder of the project, it is also part of the build path (I'm using Eclipse). Its entry in the CLASSPATH-file of the project looks like this:

<classpathentry kind="lib" path="lib/groupConcat.jar"/>

And for good measure and some desperation I've also added it to my system's classpath:

. ; ..;%DERBY_HOME%\lib\derby.jar;%DERBY_HOME%\lib\derbytools.jar;C:\Users\workspace\kinf-workspace\General\lib

My error is probably very trivial, but I'm quite new to Derby and databases in general, so I'd appreciate any help.

Thanks in advance!

The first thing to check, double-check, and triple-check is the CLASSPATH. Can you give more details about **precisely** what you did when you "added it to the classpath where Derby should be able to find it and added it to the database directly"? — Bryan Pendleton, Jul 11 '15 at 14:18
I've edited my question for more details about the CLASSPATH. — styps, Jul 11 '15 at 14:38

score 2 · Accepted Answer · answered Jul 11 '15 at 23:14

The problem turned out to be more specific to Eclipse and in its nature trivial. Although the jar containing the Java-class already was in the classpath, Eclipse did not add the jar to the run configurations. Apparently, the classpath is passed on to Derby from there and then of course it can't find the class. So all there was to do was packing the class containing the method in question into a jar, adding it to the project's library, add it to the Run Configuration's classpath and run it.

Lessons learned: Never trust your IDE to do (all of) your work.

score 1 · Answer 2 · answered Jul 11 '15 at 16:05

I'm not totally sure what's going wrong with your case, but I'd continue to review and check the CLASSPATH and package name handling in your build process.

I took your code, edited out the package statement, and used it in a very trivial example, successfully (as far as I can tell):

C:\Users\Bryan\derby\tests\jfunc>vim GroupConcat.java
C:\Users\Bryan\derby\tests\jfunc>javac GroupConcat.java
 Directory of C:\Users\Bryan\derby\tests\jfunc
07/11/2015  08:53 AM               832 GroupConcat.class
07/11/2015  08:53 AM               440 GroupConcat.java
C:\Users\Bryan\derby\tests\jfunc>java -cp \users\bryan\derby\trunk\jars\sane\derbyrun.jar;. org.apache.derby.tools.ij
ij version 10.12
ij> connect 'jdbc:derby:brydb;create=true';
ij> CREATE FUNCTION SQL_UTIL.GROUP_CONCAT
    ( SEPARATOR CHAR, ARGS VARCHAR(255) ... )
    RETURNS VARCHAR(2000)
    PARAMETER STYLE DERBY
    NO SQL LANGUAGE JAVA
> > > > >     EXTERNAL NAME 'GroupConcat.groupConcat';
0 rows inserted/updated/deleted
ij> create table t1 (a int, b int);
0 rows inserted/updated/deleted
ij> insert into t1 values (1, 2);
1 row inserted/updated/deleted
ij> select sql_util.group_concat(a,b) from t1;
ERROR 42821: Columns of type 'CHAR' cannot hold values of type 'INTEGER'.
ij> create table t2 (a varchar(10), b varchar(10));
0 rows inserted/updated/deleted
ij> insert into t2 values ('a', 'b');
1 row inserted/updated/deleted
ij> select sql_util.group_concat(a,b) from t2;
1

--------------------------------------------------------------------------------

b


1 row selected

Now, I expect that you probably expected to see 'ab' as the result, but that's a different issue, I believe, and is probably because I didn't pass a value for 'separator' when I ran your function.

Possibly, running your program with

java -verbose:class

will help you figure out if Derby is simply looking in the wrong place for your class. (A tool like MSDN's ProcessMonitor might also be useful for that)

Good luck, and let us know what you find!

Good to see that in ij it works, and if an additional char like ',' is passed it also returns 'a,b'. The main difference I can see at the moment is that the class is not in any package, but besides I'm quite confused why it didn't work with Eclipse. Anyhow, many thanks and I will post my solution when I find it. — styps, Jul 11 '15 at 17:23

Use Java functions with Apache Derby

2 Answers2

--------------------------------------------------------------------------------