Derby and Perl with DBD::JDBC

It is possible to use Perl to access Derby through DBD::JDBC. DBD::JDBC is a DBD server in pure Java which acts as a proxy server for any JDBC compliant database. This fits neatly with embedded Derby. You then get just one database server to worry about.

What you need

How to do it

Start the dbd_jdbc server like this:

java -Djdbc.drivers=org.apache.derby.EmbeddedDriver \
     -Ddbd.port=12345 \
     -classpath db-derby-10.5.1.1-lib/lib/derby.jar:DBD-JDBC-0.71/dbd_jdbc.jar:apache-log4j-1.2.15/log4j-1.2.15.jar \
     com.vizdom.dbd.jdbc.Server

And then just run your perl program:

use DBD::JDBC;

$url = "jdbc:derby:dbtest;create=true";
## Since space, ';' and '=' is used in DBD dsn, 
## we need to encode them in the url:
$url =~ s/([=;])/uc sprintf("%%%02x",ord($1))/eg;
$dsn = "dbi:JDBC:hostname=localhost:12345;url=$url";

$dbh = DBI->connect($dsn, undef, undef, undef);

print "Derby system tables\n--------------------------\n";
$sth = $dbh->prepare("select tablename from SYS.SYSTABLES");
$sth->execute();
while (my ($i) = $sth->fetchrow_array) {
    print "Found: $i\n";
}

Which will produce the following output:

Derby system tables
--------------------------
Found: SYSALIASES
Found: SYSCHECKS
Found: SYSCOLPERMS
Found: SYSCOLUMNS
Found: SYSCONGLOMERATES
Found: SYSCONSTRAINTS
Found: SYSDEPENDS
Found: SYSDUMMY1
Found: SYSFILES
Found: SYSFOREIGNKEYS
Found: SYSKEYS
Found: SYSROLES
Found: SYSROUTINEPERMS
Found: SYSSCHEMAS
Found: SYSSTATEMENTS
Found: SYSSTATISTICS
Found: SYSTABLEPERMS
Found: SYSTABLES
Found: SYSTRIGGERS
Found: SYSVIEWS

A server for combined Perl and Java access

And, since Derby allows you to combine an application using the embedded driver with the Derby network server, one may then easily set up at server which may be accessed both from Perl and from Java Programs:

java -Djdbc.drivers=org.apache.derby.EmbeddedDriver \
     -Ddbd.port=12345 \
     -Dderby.drda.startNetworkServer=true \
     -classpath db-derby-10.5.1.1-lib/lib/derbynet.jar:DBD-JDBC-0.71/dbd_jdbc.jar:apache-log4j-1.2.15/log4j-1.2.15.jar \
     com.vizdom.dbd.jdbc.Server