Seeing as how I spent sometime this morning on getting OC4J DataSources to work with Derby, and there wasn't much information out there in googleland on it, I thought I'd post some information.
The request, paraphrased was: "I need to use the ClientXADataSource with OC4J to connect to a remote Derby server, but it only seems to connect to localhost".
Taking a look at it, this required a few things:
1. A crash course in using Derby. Dead easy to download, unzip, run. Bit harder to work out where to specify users, properties, and how to configure datasources. There's quite a bit of information out there, but it seems to need some previous level of experience with Derby to make sense of it quickly.
2. OC4J needs to use the derbyclient.jar file to establish a JDBC connection to the Derby server. Now this is similar to the MySql situation -- the derbyclient.jar needs to be placed within OC4J where it can be found by the various classloaders that need it.
The most obvious solution is to put it in the j2ee/home/applib directory, which will make it available to the default application classloader, which should suffice for runtime.
However the thing to keep in mind here is that the applib directory is configured as part of the "global.libraries" shared-library, which is explicitly NOT imported by the ascontrol application. Thus if you want to use ascontrol to configure a DataSource for Derby, you need to make the library available to it as well.
I have a previous post on how this applied to
ascontrol/DataSources and MySql, which should explain away this problem as well.
The other options are to publish derbyclient.jar as an explicit named:versioned shared-library and import it wherever its needed. This could be into the default application, a specific application, wherever.
3. Configuring a DataSource. There are several ways to configure a database connection on OC4J -- ConnectionPools with Managed DataSources, or Native DataSources. The doc explains the differences between the two.
Likewise, there are several classes in the derbyclient.jar you can use to connect to a Derby database. Most of the information in googleland shows the use of the org.apache.derby.jdbc.ClientDriver and a simple JDBC URL, and most often being done from within Java code. There are also two additional classes which implement the javax.sql.DataSource and javax.sql.XADataSource interfaces. These seem to be what we should be using for a datasource, and more particularly a datasource for use with XA.
With the classes identified, the next step is to actually create the datasource. This can be done with ascontrol or manually. I initially started using ascontrol, but I ran into some problems. The first was that when I was specifying the Derby DRIVER_URL, which includes the databasename "jdbc:derby://localhost:1521/wombat;create=true", when I tested the connection, I kept getting an error message indicating that the databaseName must be set. Looking at the org.apache.derby.jdbc.ClientDataSource implementation, there's a setter for the database name. To force the setting of the value, I used the <property> configuration setting on the datasource configuration to set the databaseName as follows:
<connection-pool name="DerbyCP">
<connection-factory
factory-class="org.apache.derby.jdbc.ClientXADataSource"
user="foo" password="bar"
url="jdbc:derby://localhost:1527/wombat">
<property name="databaseName" value="wombat"/>
<property name="createDatabase" value="true"/>
</connection-factory>
</connection-pool>
The problem I ran into then, was that with ascontrol if you "test this connection" at this point, it still throws an error saying the databaseName is not set. I don't know for sure, but I think the problem here may be that the supplied properties are not being used when this quick test is done. To get beyond this, click finish to save the connection pool, and then test it from the resulting JDBC resources page.
Another problem I ran into here is that we need a user/password to connect with. The default Derby network server runs without needing authentication. I don't know if this is the correct approach, but I ended up running Derby with a derby.properties file that specified the following:
derby.authentication.provider=BUILTIN
derby.user.foo=bar
So that a user "foo" exists and can be used to connect with.
The next issue is that once you have user foo, if you try and do the "test connection" and use the standard sql query "select * from dual" you'll get an error saying the "FOO" schema doesn't exist. I found how to create a schema from some online doc, and used the "ij" utility to create the database "wombat" and the schema "foo".
That finally allowed me to successfully execute a test to verify the connection could be established. Some further reading seemed to indicate there is a default schema called "APP" so perhaps if the test query was modified to explicitly name that schema "select * from APP.dual" maybe that would have worked too. Worth noting is that the table "dual" here does not exist, but I was considering a return error of "table does not exist" as proof that the connection was successfully created.
Once you have the connection pool connecting correctly, then you can wrap it with a datasource which is bound to the JNDI team with a specified name, and hopefully all will be good from then.
For completeness, here is the final data-sources.xml file that shows the final configuration:
<native-data-source
user="foo" password="bar"
url="jdbc:derby://localhost:1527/wombat;create=true"
data-source-class="org.apache.derby.jdbc.ClientDataSource"
jndi-name="jdbc/DerbyNDS"
name="DerbyNDS">
<property name="databaseName" value="wombat"/>
<property name="createDatabase" value="true"/>
</native-data-source>
<managed-data-source connection-pool-name="DerbyCP" jndi-name="jdbc/DerbyMDS" name="DerbyMDS"/>
<connection-pool name="DerbyCP">
<connection-factory
factory-class="org.apache.derby.jdbc.ClientXADataSource"
user="foo" password="bar"
url="jdbc:derby://localhost:1527/wombat">
<property name="databaseName" value="wombat"/>
<property name="createDatabase" value="true"/>
</connection-factory>
</connection-pool>
Note: as I have alluded to, this may not be the most appropriate way to make use of Derby so please don't read this in that way. I need have to do some more reading, thinking and a lot of testing if I had to do anything more than just prove quickly that it could work with OC4J.
