Problems trying to create a Derby datasource

[Frank Griffin]

I'm trying to create a Derby datasource in WildFly 23  via CLI, but the server can't seem to find the driver class(es) even though they are in the JARs included in the module.xml.

Here is the CLI:

=====================================================================

module add --name=org.apache.derby --resources=/usr/local/derby/lib/derbytools.j
ar,/usr/local/derby/lib/derbyclient.jar,/usr/local/derby/lib/derbynet.jar,/usr/l
ocal/derby/lib/derby.jar --resource-delimiter=,  --dependencies=javax.api,javax.
transaction.api
/subsystem=datasources/jdbc-driver=derbyDS:add(driver-name=derbyDS,driver-module
-name=org.apache.derby,driver-class-name=org.apache.derby.jdbc.ClientDriver)
/system-property=derby.stream.error.file:add(value="${jboss.server.log.dir}/derb
y.log")
/system-property=derby.infolog.append:add(value="true")
data-source add --name=DerbyDS --driver-name=derbyDS --connection-url=jdbc:derby
:${jboss.server.data.dir}${/}derby${/}localDVDDB --connection-properties=[create
=>true] --jndi-name=java:jboss/jdbc/DVDDerbyDS --user-name=sa --password=sa
/subsystem=datasources/data-source=DerbyDS:test-connection-in-pool

=================================================

The module definition works, and here is the module.xml:

=================================================

<?xml version='1.0' encoding='UTF-8'?>
<module xmlns="urn:jboss:module:1.1" name="org.apache.derby">
    <resources>
        <resource-root path="derbyclient.jar"/>
        <resource-root path="derbytools.jar"/>
        <resource-root path="derbyshared.jar"/>
    </resources>
    <dependencies>
        <module name="javax.api"/>
        <module name="javax.transaction.api"/>
    </dependencies>
</module>

====================================================

The only odd thing about this is that the CLI specified 4 JARs and only 3 end up in the module.xml (derby.jar is missing).  But, as you'll see below, this should not affect the result, since the class that claims not to be found is not in derby.jar.

Here's the result of running the CLI:

=====================================================

[jboss@ftgme2 ~]$ ./w*l/bin/jboss-cli.sh -c <derbytest
[standalone@localhost:9990 /] module add --name=org.apache.derby --resources=/us
r/local/derby/lib/derbytools.jar,/usr/local/derby/lib/derbyclient.jar,/usr/local
/derby/lib/derbynet.jar,/usr/local/derby/lib/derby.jar --resource-delimiter=,  -
-dependencies=javax.api,javax.transaction.api
[standalone@localhost:9990 /] /subsystem=datasources/jdbc-driver=derbyDS:add(dri
ver-name=derbyDS,driver-module-name=org.apache.derby,driver-class-name=org.apach
e.derby.jdbc.ClientDriver)
{
   "outcome" => "failed",
   "failure-description" => "WFLYCTL0158: Operation handler failed: java.lang.N
oClassDefFoundError: Failed to link org/apache/derby/jdbc/ClientDriver (Module \
"org.apache.derby\" from local module loader @24111ef1 (finder: local module fin
der @1f3f02ee (roots: /home/jboss/wildfly-23.0.0.Final/modules,/home/jboss/wildf
ly-23.0.0.Final/modules/system/layers/base))): org/apache/derby/jdbc/ClientDriver r",                                                                  
   "rolled-back" => true                                                        
}                                                                                
[standalone@localhost:9990 /] /system-property=derby.stream.error.file:add(value
="${jboss.server.log.dir}/derby.log")
{
   "outcome" => "failed",                                                       
   "failure-description" => "WFLYCTL0212: Duplicate resource [(\"system-propert
y\" => \"derby.stream.error.file\")]",                                           
   "rolled-back" => true                                                        
}                                                                                
[standalone@localhost:9990 /] /system-property=derby.infolog.append:add(value="t
rue")
{
   "outcome" => "failed",                                                       
   "failure-description" => "WFLYCTL0212: Duplicate resource [(\"system-propert
y\" => \"derby.infolog.append\")]",                                              
   "rolled-back" => true                                                        
}                                                                                
[standalone@localhost:9990 /] data-source add --name=DerbyDS --driver-name=derby
DS --connection-url=jdbc:derby:${jboss.server.data.dir}${/}derby${/}localDVDDB -
-connection-properties=[create=>true] --jndi-name=java:jboss/jdbc/DVDDerbyDS --u
ser-name=sa --password=sa
{"WFLYCTL0062: Composite operation failed and was rolled back. Steps that failed
:" => {"Operation step-1" => {"WFLYCTL0412: Required services that are not insta
lled:" => ["jboss.jdbc-driver.derbyDS"],"WFLYCTL0180: Services with missing/unav
ailable dependencies" => ["jboss.driver-demander.java:jboss/jdbc/DVDDerbyDS is m
issing [jboss.jdbc-driver.derbyDS]","org.wildfly.data-source.DerbyDS is missing
[jboss.jdbc-driver.derbyDS]"]}}}                                                 
[standalone@localhost:9990 /] /subsystem=datasources/data-source=DerbyDS:test-co
nnection-in-pool
{
   "outcome" => "failed",                                                       
   "failure-description" => "WFLYCTL0216: Management resource '[                
   (\"subsystem\" => \"datasources\"),                                          
   (\"data-source\" => \"DerbyDS\")                                             
]' not found",                                                                   
   "rolled-back" => true                                                        
}                                                                                
[standalone@localhost:9990 /]

[jboss@ftgme2 ~]$

========================================================

The root problem is that org/apache/derby/jdbc/ClientDriver can't be found.  However,

=========================================================

[jboss@ftgme2 ~]$ cd w*l/modules/org/apache/derby/main
[jboss@ftgme2 ~/wildfly-23.0.0.Final/modules/org/apache/derby/main]$ ls
derbyclient.jar  derby.jar  derbynet.jar  derbytools.jar  module.xml
[jboss@ftgme2 ~/wildfly-23.0.0.Final/modules/org/apache/derby/main]$ jar tf derb
ytools.jar
META-INF/MANIFEST.MF
module-info.class
....

org/apache/derby/jdbc/BasicClientConnectionPoolDataSource40.class
org/apache/derby/jdbc/BasicClientDataSource40.class
org/apache/derby/jdbc/BasicClientXADataSource40.class
org/apache/derby/jdbc/BasicEmbeddedConnectionPoolDataSource40.class
org/apache/derby/jdbc/BasicEmbeddedDataSource40$1.class
org/apache/derby/jdbc/BasicEmbeddedDataSource40$2.class
org/apache/derby/jdbc/BasicEmbeddedDataSource40.class
org/apache/derby/jdbc/BasicEmbeddedXADataSource40.class
org/apache/derby/jdbc/ClientConnectionPoolDataSource.class
org/apache/derby/jdbc/ClientConnectionPoolDataSource40.class
org/apache/derby/jdbc/ClientDataSource.class
org/apache/derby/jdbc/ClientDataSource40.class
org/apache/derby/jdbc/ClientDriver.class
org/apache/derby/jdbc/ClientDriver40.class
org/apache/derby/jdbc/ClientXADataSource.class
org/apache/derby/jdbc/ClientXADataSource40.class
org/apache/derby/jdbc/EmbeddedConnectionPoolDataSource.class

....

=========================================================

which shows that ClientDriver is clearly there.

Obviously what I've got in the CLI is not what WildFly needs to locate the driver class, in spite of the fact that the JARs in the module.xml directory clearly contain the needed class.  So what am I doing wrong ?

[Wolfgang Mayer]

I guess your driver class is wrong. It should be: org.apache.derby.client.ClientAutoloadedDriver as mentioned in the META-INF.services.java.sql.Driver.

But it should work if you just add the driver like.
/subsystem=datasources/jdbc-driver=derby:add(driver-name=derby,driver-module-name=org.apache.derby)

[Frank Griffin]

I'm sorry, do you me, but an that I should add a jdbc-driver command to the CLI for ClientAutoloadedDriver or replace the one for ClientDriver ?  Every Derby example I've seen on the net uses ClientDriver, buorg/apacht the errors seem to indicate that ClientDriver can't be found.  True, a later message indicates that ClientAutoloadedDriver can't be found, but I suspect that it's because the JARs mentioned in the modules.xml can't be found, since both of those classes are present in the JARs which have been copied into the org/apache/derby/main subdirectory.

[Frank Griffin]

Sorry for the typos, I really wish comments here could be edited.

The above should have read:

I'm sorry, do you mean that I should add a jdbc-driver command to the CLI for ClientAutoloadedDriver or replace the one for ClientDriver ?  Every Derby example I've seen on the net uses ClientDriver, but the errors seem to indicate that ClientDriver can't be found.  True, a later message indicates that ClientAutoloadedDriver can't be found, but I suspect that it's because the JARs mentioned in the modules.xml can't be found, since both of those classes are present in the JARs which have been copied into the org/apache/derby/main subdirectory.

[Frank Griffin]

I've finally solved this.  Here are the CLI commands needed to create the datasource for a networked Derby datasource:

#  Create the module directory, including all of the required derby JARs.  The base driver class

#  you need is ClientDriver, but it requires others which are scattered in other JARs.  As you

#  progress from just the client JAR you'll get errors for other missing classes.

module add --name=org.apache.derby --resources=/usr/local/derby/lib/derbyclient.
jar,/usr/local/derby/lib/derbyshared.jar,/usr/local/derby/lib/derbytools.jar,/us
r/local/derby/lib/derby.jar --resource-delimiter=,  --dependencies=javax.api,jav
ax.transaction.api

#  The basic name of the datasource (as it would be displayed in the admin console) is

#  DerbyDS, and the full name of the driver class is

#       org.apache.derby.jdbc.ClientDriver

#  obtained by concatenating the "driver-module-name" with the "driver-class-name".

/subsystem=datasources/jdbc-driver=derbyDS:add(driver-name=derbyDS,driver-module
-name=org.apache.derby,driver-class-name=org.apache.derby.jdbc.ClientDriver)

#  This just removes any existing datasource of this name, and will fail if none exists.

data-source remove --name=DerbyDS

#  Now add the datasource.  The -name must match the name used in the second

#  command above for both jdbc-driver  and driver-name.  "driver-name" need not match anything.

#  You need to have defined tables in Derby that match your Entity Bean descriptions.

#  It's interesting that the schema name given in the "connection-url" doesn't have to

#  match the schema you actually use (as you will see below, I used "DVD").

#

#  However, the jndi-name has to match "java:jboss/datasources/" plus the datasource

#  name.  Other places on the net suggest that it should be "java:jboss/jdbc", but

#  displaying the info in the admin console datasource description for the shipped

#  ExampleDS shows that "datasources" is the required value.  "--user-name" and

#  "--password" should match whatever you defined in Derby for the tables.

data-source add --name=DerbyDS --driver-name=derbyDS --connection-url=jdbc:derby

://localhost:1527/DerbyDS --connection-properties=[create=>true] --jndi-name=jav
a:jboss/datasources/DerbyDS --user-name=sa --password=sa 

#  This is just a sanity test to see if DerbyDS can be connected to.

/subsystem=datasources/data-source=DerbyDS:test-connection-in-pool

The persistence.xml in your project should look something like:

<?xml version="1.0" encoding="UTF-8"?>

<persistence version="1.0"
             xmlns="http://java.sun.com/xml/ns/persistence"
             xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
             xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_1_0.xsd">
  <persistence-unit name="GlobalEM" transaction-type="JTA">
    <!--
      Note that the following needs to match the JNDI value given above
    -->
    <jta-data-source>java:jboss/datasources/DerbyDS</jta-data-source>
    <properties>
      <!--

       It's not clear that all of these properties are needed, but if they are

      they must match what was given in the CLI above.  Note that here we use

      the actual schema name "DVD" used to create the tables.

      -->
      <property name="javax.persistence.jdbc.driver"
                value="org.apache.derby.jdbc.ClientDriver"/>
      <property name="javax.persistence.jdbc.url"
                value="jdbc:derby://localhost:1527/DVD;create=true"/>
      <property name="javax.persistence.jdbc.user" value="sa"/>
      <property name="javax.persistence.jdbc.password" value="sa"/>

      <!--

        The following are possibly optional, with the possible exception of

        "dialect".  I'm not sure how much of Derby processing gets filtered

        through Hibernate processing.

      -->

      <property name="javax.persistence.schema-generation.database.action"
                value="drop-and-create"/>
      <property name="hibernate.dialect" value="org.hibernate.dialect.DerbyDialect"/>
      <property name="show_sql" value="true"/>
      <property name="hibernate.temp.use_jdbc_metadata_defaults" value="false"/>      
    </properties>
  </persistence-unit>
</persistence>

and finally, here is the "ij" input used to create the tables.  Obviously most of this

won't match your application, but the schema name needs to match what you put

in persistence.xml:

/bin/ksh $DERBY_HOME/bin/ij -p /tmp/ij.properties <<EOF2
Connect 'DVD;create=true';
CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.database.sqlAuthorization','T
RUE');
CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.user.dvd', $DVD_PASSWORD);
DROP TABLE DVD.DBUTDVDDISKSENTITYBEAN;
DROP TABLE DVD.DBUTDVDACTORSENTITYBEAN;
DROP TABLE DVD.DBUTDVDSTATEENTITYBEAN;
DROP SCHEMA DVD RESTRICT;
CREATE SCHEMA DVD AUTHORIZATION dvd;
CREATE TABLE DVD.DBUTDVDDISKSENTITYBEAN (title VARCHAR(64), season INTEGER, case
numbers VARCHAR(256), lcasenumbers BLOB(64K), tv CHAR(1), url VARCHAR(64), PRIMA
RY KEY(title,season));
CREATE TABLE DVD.DBUTDVDACTORSENTITYBEAN (id VARCHAR(64), lastname VARCHAR(64),
middlename VARCHAR(64), firstname VARCHAR(64), qualifier VARCHAR(64), titles BLO
B(64K));
CREATE TABLE DVD.DBUTDVDSTATEENTITYBEAN (id BIGINT, changestate BIGINT);
exit
EOF2