apache > db
Apache DB Project
 
Font size:      

Derby Network Client

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.

JDBC Interface Derby network client class
java.sql.Driver org.apache.derby.jdbc.ClientDriver
javax.sql.DataSource org.apache.derby.jdbc.ClientDataSource
javax.sql.XADataSource org.apache.derby.jdbc.ClientXADataSource
javax.sql.ConnectionPoolDataSource org.apache.derby.jdbc.ClientConnectionPoolDataSource

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.


Standard JDBC Data Source Properties
PropertyTypeDescriptionDefaultReq'dUrl Attr
databaseName*Stringname of database X 
dataSourceName*Stringa data source name   
description*Stringa description of this data source   
user*Stringuser's account nameAPP user
password*Stringuser's database password  password
serverNameStringhost name or TCP/IP address where the server is listening for requestslocalhost  
portNumberintport number where the server is listening for requests1527  
Client Specific Data Source Properties
traceFileStringfilename for tracing output. Setting this property turns on tracing. (See Tracing)  traceFile
traceDirectoryStringdirectory for tracing output. Each connection will output to a separate file. Setting this property turns on tracing. (See Tracing)  traceDirectory
traceLevelintlevel of client tracing if traceFile or traceDirectory are setTRACE_ALL traceLevel
traceFileAppendbooleantrue if tracing output should append to the existing trace file.false traceFileAppend
securityMechanismintsecurity mechanism (See Security)USER_ONLY_SECURITY securityMechanism
retrieveMessageTextbooleanretrieve message text from server. A stored procedure is called to retrieve the message text with each SQLException and may start a new unit of work. Set to false if you do not want the performance impact or encounter issues with a new unit of work being started.true retrieveMessageText
Server Specific Data Source Properties
These properties are translated into server specific URL attributes which are passed to the Derby embedded driver by appending them to the databaseName. For example, calling setDatabaseName("mydb") followed by setConnectionAttributes("create=true") will cause the string mydb;create=true to be sent to the server as the database name . The URL to connect to the database will therefore include these attributes.
connectionAttributes*Stringset to the list of Derby embedded connection attributes separated by semi-colons. E.g., to specify an encryption bootPassword of "x8hhk2adf", and set upgrade to true, do the following: ds.setConnectionAttributes("bootPassword=x8hhk2adf;upgrade=true");  various. See Derby Documentation for a full list.
 

* 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.
Trace LevelValue
org.apache.derby.jdbc.ClientDataSource.TRACE_NONE0x0
org.apache.derby.jdbc.ClientDataSource.TRACE_CONNECTION_CALLS0x1
org.apache.derby.jdbc.ClientDataSource.TRACE_STATEMENT_CALLS0x2
org.apache.derby.jdbc.ClientDataSource.TRACE_RESULT_SET_CALLS0x4
org.apache.derby.jdbc.ClientDataSource.TRACE_DRIVER_CONFIGURATION0x10
org.apache.derby.jdbc.ClientDataSource.TRACE_CONNECTS0x20
org.apache.derby.jdbc.ClientDataSource.TRACE_PROTOCOL_FLOWS0x40
org.apache.derby.jdbc.ClientDataSource.TRACE_RESULT_SET_META_DATA0x80
org.apache.derby.jdbc.ClientDataSource.TRACE_PARAMETER_META_DATA0x100
org.apache.derby.jdbc.ClientDataSource.TRACE_DIAGNOSTICS0x200
org.apache.derby.jdbc.ClientDataSource.TRACE_XA_CALLS0x800
org.apache.derby.jdbc.ClientDataSource.TRACE_ALL0xFFFFFFFF;

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.

Security mechanisms supported by the Derby network client
Security mechanismsecurityMechanism property valueComments
User ID and passwordClientDataSource.CLEAR_TEXT_PASSWORD_SECURITY (0x03)Default if password is set
User ID onlyClientDataSource.USER_ONLY_SECURITY (0x04)Default if password is not set
Encrypted user ID and encrypted passwordClientDataSource.ENCRYPTED_USER_AND_PASSWORD_SECURITY (0x09)Encryption requires a JCE Implementation that supports Diffie-Helman algorithm with a prime of 32 bytes




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.

URL
Derby Network Client URLDocumented URL
jdbc:derby://server[:port]/databaseName[;attributeKey=value]..jdbc:derby:net//server[:port]/databaseName[;embeddedAttributeKey=value]..:clientAttributeKey=value;.


Driver and DataSource Names
JDBC InterfaceDerby Network ClientDocumented Class Name
java.sql.Driverorg.apache.derby.jdbc.ClientDrivercom.ibm.db2.jcc.DB2Driver
javax.sql.DataSourceorg.apache.derby.jdbc.ClientDataSourcecom.ibm.db2.jcc.DB2DataSource
javax.sql.XADataSourceorg.apache.derby.jdbc.ClientXADataSourcecom.ibm.db2.jcc.DB2XADataSource
javax.sql.ConnectionPoolDataSourceorg.apache.derby.jdbc.ClientConnectionPoolDataSourcecom.ibm.db2.jcc.DB2ConnectionPoolDataSource


Miscellaneous Changes
 Derby Network ClientDocumented
jar file namederbyclient.jardb2jcc.jar, db2jcc_licence_c.jar
attribute to retrieve message text*retrieveMessageText (default true)retrieveMessagesFromServerOnGetMessage (default false)
required datasource property settingsdatabaseName only required propertyrequires databaseName, serverName,driverType, user, password
 

*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]