Derby Network Client

Created: 4/11/05
Last update: 10/25/05 -- changed h4 tags to h3 (forrest hid data), and removed manual table of contents

Overview

The Derby network client provides network connectivity to the Derby Network Server. It is distributed as an additional jar file, derbyclient.jar, with an entirely independent code base from the embedded driver.

The Derby network client is a type 4, JDBC compliant Driver, which attempts to match the Derby Embedded JDBC driver as much as possible. Initially, it will have some differences from the embedded driver, for example, some SQL States will remain null and error messages will not be localized in the initial offering. Hopefully, over time the functionality of the two drivers will converge.

Functionality

All functionality for the Derby network client is packaged in the derbyclient.jar file which will be added to the Derby distribution. The client application needs only to include the new jar and application classes in its classpath. derbyclient.jar has no dependencies on the tools, server, or derby database engine classes, nor do those classes depend on derbyclient.jar. Some new client classes are in the same package as the embedded driver, such as org.apache.derby.jdbc.ClientDriver, but the actual classes are contained only in the client jar file.

The Derby network client allows connections via JDBC URL using DriverManager by its implementations of JDBC20 data sources, javax.sql.DataSource, javax.sql.ConnectionPoolDataSource. An implementation of javax.sql.XADataSource is also provided for network XA Support. The Derby network client provides a tracing facility to collect JDBC trace information and view protocol flows for debugging. Security mechanisms are also provided for user and password encryption. DataSource properties and connection attributes can be used to enable tracing, as well as security settings.

Tools support for ij and dblook as well as test harness support is provided.

The sections that follow will describe the functionality and provide examples of using each characteristic mentioned here.

JDBC Driver and DataSource names

The Derby network client classes and JDBC interfaces they implement are listed below.

Connection URL Format

The protocol for the client URL is jdbc:derby://. The embedded and client attributes are specified the same way. The embedded driver has been changed to reject URL's with a // following derby:

Derby network client URL Format

jdbc:derby://server[:port]/databaseName[;attributeKey=value]..

An example of using this URL to connect to the Derby network server running on the localhost at port 1527 in ij is:

ij> connect `jdbc:derby://localhost:1527/sample;create=true;user=user1;password=secret4me';

Ways to Connect using the Derby Client Driver

Client CLASSPATH

Prior to using the Derby network client, the derbyclient.jar file needs to be added to the classpath on the client. On Windows, to add the derbyclient.jar file to your classpath, use the following command:

set CLASSPATH=%CLASSPATH%;%DERBY_INSTALL%\lib\derbyclient.jar;

When using the Derby client driver to connect to the Derby database, there are two general methodologies used to connect. The first method is to use the DriverManager. The second way to connect is to use one of the three DataSource classes listed above.

To use the DriverManager, the first step is to load the ClientDriver.

Loading the driver:

port java.sql.* ; ... try { Class.forName("org.apache.derby.jdbc.ClientDriver").newInstance(); } catch (ClassNotFoundException e) { e.printStackTrace(); } catch(java.lang.InstantiationException instant) { instant.printStackTrace(); } catch(java.lang.IllegalAccessException illegal) { illegal.printStackTrace(); } ...

Now a connection can be made with the DriverManager. 


Connecting with the DriverManager: 

String url = "jdbc:derby://myhost.mydomain.com:1527/mydb;create=true;user=user1;password=secret4me"; connection = DriverManager.getConnection(url);

Connecting with the ClientDataSource: 

import java.sql.*; import javax.sql.*; import org.apache.derby.jdbc.ClientDataSource; ... ClientDataSource ds=new ClientDataSource(); ds.setDatabaseName("sample"); ds.setServerName("localhost"); // host with listening network server. ds.setPortNumber(1527); // port of listening network server. ds.setUser("user1"); // Assign the user ID ds.setPassword("secret4me"); // Assign the password Connection con= ds.getConnection(); // Create a Connection object

Data Source properties

The following data source properties are available on the ClientDataSource, 
ClientConnectionPoolDataSource and the ClientXADataSource classes. 

 

* This property is also available on the EmbeddedDataSource. All other data 
  source properties are supplemental to the ClientDataSources. 
 
Note: setAttributesAsPassword , which is available for the embedded data 
  sources is not available for the client data source. It is not clear how to 
  communicate it to the server. 

Tracing

The Derby network client provides a tracing facility to collect JDBC trace 
information and view protocol flows. Listed below are various ways to obtain 
trace output. However, the easiest way to turn on tracing is to use the 
traceFile attribute on the url in ij. The example below shows all tracing going 
to the file trace.out from an ij session. 

ij>connect 'jdbc:derby://localhost:1527/mydb;create=true;traceFile=trace.out;user=user1;password=secret4me';

ClientDataSource Tracing

Three methods can be used to collect tracing data if obtaining connections 
from the ClientDataSource. 

1. Use the setLogWriter(java.io.PrintWriter) method of ClientDataSource and set the PrintWriter to a non null value.
2. Use the setTraceFile(String filename) method of ClientDataSource.
3. Use the setTraceDirectory(String dirname) method of ClientDataSource to trace each connection flow in its own file for programs that have multiple connections.

DriverManager Tracing

If the DriverManager is used to obtain connections, two ways are available to 
enable and collect tracing information. 

• Use the setLogWriter(java.io.PrintWriter) method of DriverManager and set the PrintWriter to a non null value.
• Use the URL attributes traceFile or traceDirectory to set these properties prior to creating the connection with the DriverManager.getConnection() method.

ClientDataSource Tracing Examples

Shown below are examples of each tracing type for the ClientDataSource 
Tracing. 

1. Use the setLogWriter(java.io.PrintWriter) method of ClientDataSource and set the PrintWriter to a non null value.

/**
*  Example to show how to set /enable tracing  when using DataSource
*  - using the setLogWriter method on the datasource
*/
private static void sampleConnectUsingSimpleDataSource()
 throws Exception
{
java.sql.Connection conn = null;
 try
 {
javax.sql.DataSource ds =
new org.apache.derby.jdbc.ClientDataSource();
((org.apache.derby.jdbc.ClientDataSource) ds).setServerName("localhost");
((org.apache.derby.jdbc.ClientDataSource) ds).setPortNumber(1527);
((org.apache.derby.jdbc.ClientDataSource) ds).setDatabaseName("sampleDB;create=true");
System.out.println("---------------------------------------------------------------");
System.out.println("[TraceWithSetLogWriter]  Setting setLogWriter to trace.log.");
System.out.println("Traces will be written into trace.log");
java.io.PrintWriter pw = new PrintWriter(new FileOutputStream("trace.log"),true);
ds.setLogWriter(pw);
 
 
// Refine the level of tracing detail
((org.apache.derby.jdbc.ClientDataSource) ds).
setTraceLevel(org.apache.derby.jdbc.ClientDataSource.TRACE_CONNECTS |
org.apache.derby.jdbc.ClientDataSource.TRACE_PROTOCOL_FLOWS);
 
// This connection request is traced using trace level
// TRACE_CONNECTS | TRACE_PROTOCOL_FLOWS
conn = ds.getConnection("user1", "secretPassword");
  java.sql.Statement s = conn.createStatement();
try
{
 s.executeUpdate("DROP TABLE sampleTable");
  }
  catch(Exception e)
  {
  // ignore drop of table error
}
 
s.executeUpdate("CREATE TABLE sampleTable( sampleColumn int)");
PreparedStatement ps = conn.prepareStatement("insert into sampleTable values(?)");
 
  // do some inserts
conn.setAutoCommit(false);
ps.setInt(1,1);
ps.setInt(1,2);
ps.executeUpdate();
conn.commit();
 
// cleanup resources
s.close();
ps.close();
conn.close();
pw.flush();
pw.close();
}
catch(java.sql.SQLException e)
{
e.printStackTrace();
}
}

• Use the setTraceFile(String filename) method of ClientDataSource.

/**
*  Example to show how to set /enable tracing when obtaining connections from DataSource
*  - using the setTraceFile method on the datasource
*/
private static void sampleConnectUsingSimpleDataSource()
 throws Exception
{
java.sql.Connection conn = null;
 
 try
 {
javax.sql.DataSource ds =
new org.apache.derby.jdbc.ClientDataSource();
((org.apache.derby.jdbc.ClientDataSource) ds).setServerName("localhost");
((org.apache.derby.jdbc.ClientDataSource) ds).setPortNumber(1527);
((org.apache.derby.jdbc.ClientDataSource) ds).setDatabaseName("sampleDB;create=true");
System.out.println("---------------------------------------------------------------");
System.out.println("[TraceWithSetTraceFile]  Setting setTraceFile to trace.log.");
System.out.println("Traces will be written into trace.log");
((org.apache.derby.jdbc.ClientDataSource) ds).setTraceFile("trace.log");
 
 
// Refine the level of tracing detail
((org.apache.derby.jdbc.ClientDataSource) ds).
setTraceLevel(org.apache.derby.jdbc.ClientDataSource.TRACE_CONNECTS |
org.apache.derby.jdbc.ClientDataSource.TRACE_PROTOCOL_FLOWS);
 
// This connection request is traced using trace level
// TRACE_CONNECTS | TRACE_PROTOCOL_FLOWS
conn = ds.getConnection("user1", "secretPassword");
   java.sql.Statement s1 = conn.createStatement();
try
{
 s1.executeUpdate("DROP TABLE sampleTable");
  }
  catch(Exception e)
  {
  // ignore drop of table error
}
 
s1.executeUpdate("CREATE TABLE sampleTable( sampleColumn int)");
PreparedStatement ps = conn.prepareStatement("insert into sampleTable values (?)");
 
conn.setAutoCommit(false);
ps.setInt(1,1);
ps.executeUpdate();
conn.commit();
 
// cleanup resources
s1.close();
ps.close();
conn.close();
}
catch(java.sql.SQLException e) {
e.printStackTrace();
}
}

• Use the setTraceDirectory(String dirname) method of ClientDataSource to trace each connection flow in its own file for programs that have multiple connections.

Call the setTraceDirectory(String directoryName) method on the 
ClientDataSource or use the URL attribute traceDirectory=<directory> to 
specify a directory for trace output. The directory must already exist. If the 
traceDirectory property is enabled, then tracing of multiple connections on the 
same datasource will be directed to separate files under the specified 
directory. 

When tracing to a directory, each connection will be traced to a separate 
file named traceFile_origination_n, where origination indicates the origination 
of the log writer in use and can be "xads", "cpds", "ds", or "driver", 
and n represents the nth connection against the datasource. The log writer 
origination indicator in the file name maps to the log writer as follows: 

• ds - ClientXADataSource
• cpds - ClientConnectionPoolDataSource
• ds - ClientDataSource
• driver - ClientDriver

 
/**
*  Example to show how to set /enable tracing when obtaining connections from DataSource
*  - using the setTraceDirectory method on the DataSource
*/
private static void sampleConnectUsingSimpleDataSource()
 throws Exception
{
java.sql.Connection conn = null;
 
try {
javax.sql.DataSource ds =
new org.apache.derby.jdbc.ClientDataSource();
((org.apache.derby.jdbc.ClientDataSource) ds).setServerName("localhost");
((org.apache.derby.jdbc.ClientDataSource) ds).setPortNumber(1527);
((org.apache.derby.jdbc.ClientDataSource) ds).setDatabaseName("sampleDB;create=true");
((org.apache.derby.jdbc.ClientDataSource) ds).setTraceDirectory("myTraceDir");
 
conn = ds.getConnection("user1", "secretPassword");
   java.sql.Statement s1 = conn.createStatement();
try
{
 s1.executeUpdate("DROP TABLE sampleTable");
  }
  catch(Exception e)
  {
      System.out.println("First time, expected exception in drop table as table not yet created in database" +e.getMessage());
}
 
s1.executeUpdate("CREATE TABLE sampleTable( sampleColumn int)");
PreparedStatement ps = conn.prepareStatement("insert into sampleTable values (?)");
 
conn.setAutoCommit(false);
ps.setInt(1,1);
ps.executeUpdate();
conn.commit();
 
// cleanup resources
s1.close();
ps.close();
conn.close();
 
  // Each connection trace will be in a file of its own prefixed
  // by name set using the setTraceFile
  // example : traceFile_origination_n. n is the nth connection against the datasource.
  // origination indicates the origination of the logwriter in use and can be
  // cpds, xads, ds, driver
Connection conn2 = ds.getConnection("user2","password2");
conn2.close();
}
catch(java.sql.SQLException e) {
e.printStackTrace();
}
}

DriverManager Tracing Examples

Shown below is an example of enabling tracing when obtaining connections 
using the DriverManager API. 

1. Use the setLogWriter(java.io.PrintWriter) method of DriverManager and set the PrintWriter to a non null value.

/**
*  Example to show how to set /enable tracing when obtaining connections from
*  java.sql.DriverManager api.
*  - using the setLogWriter method on the DriverManager api
*/

import java.sql.*;
import javax.sql.*;       
import org.apache.derby.jdbc.ClientDataSource;
import java.io.PrintWriter;
import java.io.FileOutputStream;

...
private static void sampleConnectWithURLUsingDriverManager()
throws Exception
{
java.sql.Connection conn = null;
 
try
{
Class.forName("org.apache.derby.jdbc.ClientDriver");
}
catch(ClassNotFoundException e)
{
System.out.println("[TraceWithSetLogWriter] Derby Client driver is not in the classpath.Check if derbyclient.jar is in your classpath!");
return;
}
try
{
 System.out.println("---------------------------------------------------------------------");
 System.out.println("Setting logging/tracing using setLogWriter on DriverManager to trace1.log");
 java.sql.DriverManager.setLogWriter(new java.io.PrintWriter(new FileOutputStream("trace1.log")));
}
catch(Exception e)
{
System.out.println("[TraceWithSetLogWriter] Unable to set tracing/logging PrintWriter to trace.log");
}
 
// The traceLevel property is established through the URL syntax,
// and driver tracing is directed to file "trace1.log"
String databaseURL =
"jdbc:derby://localhost:1527" +
"/sampleDB;create=true;traceLevel=" +
(org.apache.derby.jdbc.ClientDataSource.TRACE_PROTOCOL_FLOWS|org.apache.derby.jdbc.ClientDataSource.TRACE_CONNECTS);
 
// Set other properties
java.util.Properties properties = new java.util.Properties();
properties.setProperty("user", "myname");
properties.setProperty("password", "mypass");
try
{
// This connection request is traced using trace level
// TRACE_CONNECTS | TRACE_PROTOCOL_FLOWS
conn = java.sql.DriverManager.getConnection(databaseURL, properties);
 
  java.sql.Statement s = conn.createStatement();
try
{
 s.executeUpdate("DROP TABLE sampleTable");
  }
  catch(Exception e)
  {
      System.out.println("First time, expected exception in drop table as table not yet created in database" +e.getMessage());
}
 
s.executeUpdate("CREATE TABLE sampleTable( sampleColumn int)");
PreparedStatement ps = conn.prepareStatement("insert into sampleTable values(?)");
 
  // do some inserts
conn.setAutoCommit(false);
ps.setInt(1,1);
ps.setInt(1,2);
ps.executeUpdate();
conn.commit();
 
// cleanup resources
s.close();
ps.close();
conn.close();
}
catch(java.sql.SQLException e)
{
e.printStackTrace();
}
}

Changing the Default Trace Level

The trace level defaults to ClientDataSource.TRACE_ALL. The tracing can be 
changed by calling the setTraceLevel(int level) method or setting the URL 
attribute traceLevel, as shown below. 

String url = "jdbc:derby://myhost.mydomain.com:1528/mydb" +
 ";traceFile=/u/user1/trace.out" +
 ";traceLevel=" +
 org.apache.derby.jdbc.ClientDataSource.TRACE_PROTOCOL_FLOWS;
DriverManager.getConnection(url,"user1","secret4me");

This table lists the available tracing levels and values. 

To specify more than one trace , use one of these techniques: 

• Use bitwise OR (|) operators with two or more trace values. For example, to trace PROTOCOL flows and connection calls, specify this value for traceLevel:
  TRACE_PROTOCOL_FLOWS|TRACE_CONNECTION_CALLS
• Use a bitwise complement ( ~ ) operator with a trace value to specify all except a certain trace. For example, to trace everything except PROTOCOL flows, specify this value for traceLevel:
  ~TRACE_PROTOCOL_FLOWS

Security

Derby network client allows you to select a security mechanism by specifying 
a value for the securityMechanism property. You can set this property in one of 
the following ways: 

• When using the DriverManager interface, set securityMechanism in a java.util.Properties object before you invoke the form of the getConnection method which includes the java.util.Properties parameter.
• When using the DataSource interface, and you are creating and deploying your own DataSource objects, invoke the DataSource.setSecurityMechanism method after you create a DataSource object.

The table below lists the security mechanisms the Derby network client 
supports, and the corresponding property value to specify to obtain this 
securityMechanism. The default security mechanism is the user ID only if no password is set and User ID and password if password is set.
The default user, if none is specified is APP. 

Packaging, Distribution and Versioning

The Derby network client will assume the version of the Derby release in 
which it is offered (currently thought to be 10.1). 

A new jar file, derbyclient.jar, needs to be added to the distribution, which 
will contain the client classes. The client classpath will require only 
derbyclient.jar to access the server. Demo programs and server framework batch 
files and scripts will need to be changed to use the Derby network client. 

Comparision and/or differences with the Derby Embedded Driver

The following known differences exist between the embedded driver and the 
network client driver. Many of these are already documented as Network Server 
differences at http://db.apache.org/derby/docs/10.0/manuals/admin/hubprnt19.html#HDRSII-APPS-. 
Three differences that are listed in the server guide are not relevant with the 
network client. XA is supported, error messages are retrieved by default and 
user/password are not required. Below is a summary of the differences. 

• Error messages and SQLStates are sometimes different and are sometimes null on the client, particularly for data conversion errors.
• Multiple SQL Exceptions and Warnings will only return a single Exception on the client. It will have the SQLState of the first exception and in the text will have the text of the additional Exceptions appended.
• There are no localized error messages for the client.
• The client driver has not been tested under security manager.
• The client driver fully materializes LOBS when the row is retrieved.
• Scrollable cursors (ResultSet.TYPE_SCROLL_SENSITIVE or ResultSet.TYPE_SCROLL_INSENSITIVE) are not supported if the resultset contains LOB data. TYPE_FORWARD_ONLY must be specified for result sets containing LOB data.
• To use encrypted user id and password, you need to have IBM JCE (Java Cryptography Extension) 1.2.1 or later. (DERBY-65)

Effects to existing documentation

The following Derby Manuals should be updated to include the new client 
driver. The Server Guide, Tools and Getting Started guides at least will be 
affected. Currently they document using the network server with the IBM DB2 
Universal JDBC Driver. In general, the current documentation is relevant to the 
new driver except for the items listed below. 

 

*Note: The retrieveMessageText attribute should not be included in most of 
  the examples as it now defaults to true. 
 
Client data source properties, tracing and security needs to be documented 
  as described above. 

Miscellaneous

Tools

ij will recognize the new URL and load the driver automatically. dblook will 
accept the client URL. sysinfo has been changed to locate the new jar file. 

Testing

You can run the client regression tests by running the suite 
derbynetclientmats. You can run individual tests with the client framework by 
setting the framework system property to DerbyNetClient. An example of this is 
shown below. java -Dframework=DerbyNetClient 
org.apache.derbyTesting.functionTests.harness.RunTest lang/supersimple.sql 


You can run the Derby network client test suite as follows:

java org.apache.derbyTesting.functionTests.harness.RunSuite derbynetclientmats

[Back to Derby Papers]