The C-JDBC driver is a generic JDBC driver that is designed to replace any database specific JDBC driver that could be used by a client. The client only has to know on which node the C-JDBC controller is running and the name of the database to access. The C-JDBC driver implements most of the JDBC 2.0 interface and some functionalities from JDBC 3.0 such as the support for autogenerated keys.

Users reported successful usage of C-JDBC with the following RDBMS: Oracle®, PostgreSQL, MySQL, Apache Derby, IBM DB2®, Sybase®, SAP DB (MySQL MaxDB), HyperSonic SQL, Firebird, MS SQL Server and InstantDB.

The C-JDBC driver can be loaded as any standard JDBC driver from the client program using:

 
        Class.forName("org.objectweb.cjdbc.driver.Driver"); 
      

	Note
	The c-jdbc-driver.jar file must be in the client classpath else the driver will fail to load.

The JDBC URL expected for the use with C-JDBC is the following: jdbc:cjdbc://host1:port1,host2:port2/database.

host is the machine name (or IP address) where the C-JDBC controller is running, port is the port the controller is listening for client connections.

At least one host must be specified but a list of comma separated hosts can be specified. If several hosts are given, one is picked up randomly from the list. If the currently selected controller fails, another one is automatically picked up from the list.

The port is optional is the URL and the default port number is 25322 (C-JDBC on the phone!) if it is omitted. Those two examples are equivalent:

 
        DriverManager.getConnection("jdbc:cjdbc://localhost/tpcw"); 
        DriverManager.getConnection("jdbc:cjdbc://localhost:25322/tpcw"); 
        

Examples using two controllers for fault tolerance:

 
        DriverManager.getConnection("jdbc:cjdbc://c1.objectweb.org,c2.objectweb.org/tpcw"); 
        DriverManager.getConnection("jdbc:cjdbc://localhost,remote.objectweb.org:2048/tpcw"); 
        DriverManager.getConnection("jdbc:cjdbc://smpnode.com:25322,smpnode.com:1098/tpcw"); 
        

 
        DriverManager.getConnection("jdbc:cjdbc://host/db?user=me&password=secret")

 
        DriverManager.getConnection("jdbc:cjdbc://host/db;user=me;password=secret")

By default, the C-JDBC driver interprets the PreparedStatement locally and forwards a pre-processed statement. The C-JDBC controller executes directly these statements on the backends as statements without recreating a whole PreparedStatement and re-calling all setXXX() methods on the PreparedStatement. The default setting is the one that consumes the less resources and carries the minimum information over the network.

However, it might happen that this behavior is not desired or that the C-JDBC driver interpretation is unsuitable for some data types or database specific syntax. Therefore, it is possible to make the driver act as a real proxy that will forward all setXXX() calls to the database native driver. This will usually result in slightly lower performance but better portability.

The proxying mode can be enabled for a connection by setting a specific variable named driverProcessed to false (default value if omitted is true). To enable PreparedStatement proxying in C-JDBC use a connection URL like this:

 
				DriverManager.getConnection("jdbc:cjdbc://host/db?driverProcessed=false")
		  

Note: since any optional blob encoding is performed by the driver, blob encoding is disabled by driverProcessed=false. If you ever encoded blobs, you CANNOT switch to driverProcessed=false anymore.

Another way to use the C-JDBC driver is to use its DataSource implementation. Data sources have been introduced in JDBC 2.0 Standard Extension API and are also a part of JDBC 3.0. They use the Java Naming and Directory Interface (JNDI) to break the application dependence on the JDBC driver configuration (i.e., driver class name, machine name, port number, etc.). With a data source, the only thing an application has to know is the name assigned to the DataSource object in the jdbc naming subcontext of the JNDI namespace.

The example below registers a data source object with a JNDI naming service. It is typically used by an application server.

      import org.objectweb.cjdbc.driver.DataSource;
      import javax.naming.Context;
      import javax.naming.InitialContext;
      import javax.naming.NamingException;
      ...
      private final static String NAME = "jdbc/c-jdbc";
      private final static String URL = "jdbc:cjdbc://localhost:25322/mysql";
      
      // Initializing data source
      DataSource ds = new DataSource();
      ds.setUrl(URL);

      // Get initial context
      Context ctx;
      try {
        ctx = new InitialContext();
      } catch (javax.naming.NamingException _e) {
        ... // Naming exception
      }
		
      // Bind data source to a JNDI name
      try {
        ctx.bind(NAME, ds);
      } catch (javax.naming.NamingException _e) {
        ... // Naming exception
      }
      

The org.objectweb.cjdbc.driver.DataSource class implements the javax.sql.DataSource JDBC 3.0 interface. The setUrl line initializes the data source properties (the URL in this case). The data source object is bound to a logical JNDI name by calling ctx.bind(). In the example above, the JNDI name specifies a "jdbc" subcontext and a "c-jdbc" logical name within this subcontext.

Once a data source object is registered to JNDI, it can be used by an application. The example below gets the data source using the JNDI naming service. Such a piece of code is typically a part of an application that uses JDBC.

      import javax.naming.Context;
      import javax.naming.InitialContext;
      import javax.naming.NamingException;
      import java.sql.Connection;
      import javax.sql.DataSource;
      ...
      private final static String NAME = "jdbc/c-jdbc";

      // Lookup for the data source object
      try {
        Context ctx = new InitialContext();
        Object obj = ctx.lookup(NAME);
        if (null == obj) {
          ... // Something wrong: NAME not found
        }
        ctx.close( );
      } catch (javax.naming.NamingException _e) {
        ... // Naming exception
      }
      
      // Get a new JDBC connection
      try {
        DataSource ds = (DataSource) obj;
        Connection conn = ds.getConnection("user", "c-jdbc");
        ... // Use of the connection retrieved
        ...
      } catch (SQLException _e) {
        ... // SQL exception
      }
      

The ctx.lookup() line in the example uses the retrieved initial JNDI naming context to do a lookup using the data source logical name. The method returns a reference to a Java object which is then narrowed to a javax.sql.DataSource object. Such an object can be then used to open a new JDBC connection by invoking one of its getConnection() methods. The application code is completely independent of the driver details, such as the Driver class name, URL, etc. (the user name and password used by the connection can be also set by the application server - look at the C-JDBC javadoc documentation for more details). The only information a JDBC application has to know is the logical name of the data source object to use.

	Note
	The URL used for the C-JDBC data source is the same as for the Driver decribed in the previous section.

Stored procedures are supported by C-JDBC since version 1.0b6. Note that C-JDBC only support calls in the form {call <procedure-name>[<arg1>,<arg2>, ...]} but does not support {? = call <procedure-name>[<arg1>,<arg2>, ...]}.

A call to a stored procedure is systematically broadcasted to all backends since there is no way to know if the stored procedure will update the database or not. Therefore, the query cache (see Section 11.6.3, “Request Cache”), is completely flushed on every stored procedure call. To prevent cache flushing, the user can force the connection to read-only before calling the stored procedure. But never set a connection to read-only when calling a stored procedure that updates the database. If C-JDBC detects a read-only connection, it will not flush the cache. However, the call will still be broadcasted to all nodes resulting in duplicated jobs on each backend. Here is an example on how to prevent cache flushing when calling a stored procedure that does only read-only:

 
        ... 
        CallableStatement cs = connection.prepareCall("{call myproc(?)}"); 
        cs.setString(1,"parameter1");
        // Force no cache flush  
        connection.setReadOny(true); 
        // Call the stored procedure without flushing the cache ... 
        ResultSet rs = cs.executeQuery(); 
      

In the case of horizontal scalability, only read-only stored procedures are not broadbasted. All other stored procedures returning an int or a ResultSet are executed by all backends at all controllers.

	Note
	It is not allowed to set a connection to read-only in the middle of a transaction. If you need to set a connection to read-only, you must do so before starting the transaction.

Binary large objects can now be stored using the C-JDBC driver since 1.0b10. Data is encoded into hexadecimal to be portable accross database engines.

FIXME: this whole section is outdated and should be re-written. Blob encoding is now configured in controller's DTD and set to "none" by default. It requires driverProcessed=true, which is the default.

	Note
	The column type used to store large objects with MySQL is text. The column type used to store large objects with PostgreSQL is bytea.

You should not have to change your code for storing blobs into your database, but previous blobs have to be converted to their hexadecimal form. You can use Octopus to perform this transformation.

Please refer to the following lines of code for storing and retrieving of large objects:

        // In the code below:
        // The signature of the readBinary method is:
        // byte[] readBinary(File file) throws IOException
        // it just read a file, and convert its content into an array of bytes
        
        // Store file in database 
        File fis = new File(storeFile); 
        query = "insert into ... values(...,?)"; 
        ps1 = con.prepareStatement(query); 
        if (callBlobMethods) 
        {
          org.objectweb.cjdbc.driver.Blob bob = 
            new org.objectweb.cjdbc.driver.Blob(readBinary(fis)); 
          ps1.setBlob(1, bob); 
        } 
        else 
        { 
          ps1.setBytes(1, readBinary(fis)); 
        } 
        ps1.executeUpdate();
        // Read File from database 
        query = "select * from ... where id=..."; 
        ps1 = con.prepareStatement(query); 
        ResultSet rs = ps1.executeQuery(); 
        rs.first(); 
        byte[] lisette; 
        if (callBlobMethods) 
        { 
          Blob blisette = rs.getBlob("blobcolumnname"); 
          lisette = blisette.getBytes((long) 1, (int) blisette.length());
        }
        else 
        {
          lisette = rs.getBytes("blobcolumnname"); 
        } 
      

CLOB is a built-in type that stores a Character Large Object as a column value in a row of a database table. By default drivers implement Clob using an SQL locator (CLOB), which means that a Clob object contains a logical pointer to the SQL CLOB data rather than the data itself. A Clob object is valid for the duration of the transaction in which it was created.

Clobs in C-JDBC are handled like strings. You can refer to the section of code below to make good usage of clobs. This code is part of the C-JDBC test suite.

 
        String clob = "I am a clob"; 
        ps = con.prepareStatement("insert into ... values(...,?)"); 
        ps.setString(1, clob); 
        ps.executeUpdate(); 
        
        // Test retrieval 
        String ret; 
        ps = con.prepareStatement("Select * from ... where id=..."); 
        rs = ps.executeQuery(); 
        rs.first(); 
        clob = rs.getClob("name"); 
        ret = clob.getSubString((long) 0, (int) clob.length()); 
      

In its default mode, when a query is executed on a backend, C-JDBC makes a copy of the backend's native ResultSet into a C-JDBC serializable ResultSet. If the result contains many rows or very large objects, the controller might run out of memory when trying to copy the whole ResultSet.

Since C-JDBC 1.0rc6, it is possible to fetch ResultSets by blocks using the Statement.setFetchSize(int rows) method. In this case, the ResultSet will be copied by block of rows and returned when needed by the client. Note that the current implemtation only allows to fetch forward streamable ResultSet, which basically means that you are only allowed to call ResultSet.next() on a streamable ResultSet.

C-JDBC will try to call setFetchSize() on the backend's driver to let the backend driver also perform the necessary optimizations. However, some driver requires a prior call to setCursorName() in which case you will also have to call setCursorName() on C-JDBC to pass it to the backend's driver.

A typical usage of the ResultSet streaming feature is as follows:

...        
Connection con = getCJDBCConnection();
con.setAutocommit(false);
Statement s = con.createStatement();
s.setCursorName("cursor name");
s.setFetchSize(10);
rs = s.executeQuery(sql);
while (rs.next())
{ // Every 10 calls, C-JDBC will transfer a new block of rows
  XXX o = rs.getXXX("some column name");
}
...
con.commit();
      

Note

Streamable ResultSets are not cacheable. The result cache automatically detects this kind of ResultSet and does not keep them in the cache. However, as database specific ResultSets are copied into C-JDBC ResultSets, the memory footprint of the fetched blocks will be twice the one obtained without C-JDBC. If you have memory restrictions, you can reduce your fetch size by half to reduce the memory footprint of streamed ResultSets. Streamable ResultSets do not work properly in autocommit mode as the connection used for retrieving the ResultSet is handed back to the pool. The workaround is to always encapsulate the query in a transaction. Note that databases such as PostgreSQL do not support streamable ResultSets in autocommit mode as well.

The C-JDBC driver currently does not support the following features: