The eXtremeSQL JDBC API

This topic describes the eXtremeSQL JDBC Driver implementation and how to use the JDBC driver to connect to existing eXtremeDB databases.

The eXtremeSQL JDBC driver provides access for Java applications to the eXtremeSQL server using a widely applicable industry connectivity standard. The eXtremeSQL JDBC driver is a pure-java 'Type 3' driver designed for JDK/JRE 1.6.x that interacts with the eXtremeSQL server-side middle-ware that then accesses the eXtremeDB database.

The JDBC driver is very similar to the ODBC driver working in remote SQL mode. It actually implements the client-side of a remote SQL protocol in pure-java and translates JDBC API calls into requests to the eXtremeSQL server. This is similar to the effect of using the xSQL utility as a remote SQL server or using a custom C or C++ application with the RemoteSqlEngine API as demonstrated in SDK sample api_sql_10_rsql_client. However, unlike the ODBC driver, the JDBC driver needs no installation step. It is implemented in the Java archive file extremedb_jdbc.jar in the target/bin directory.

Examples

SimpleJDBC

The SDK sample SimpleJDBC demonstrates how a client application connects to the eXtremeSQL JDBC driver and issues simple SQL statements. The connection string syntax is:

 
    jdbc:extremedb:host:port:connection_timeout:datetime_precision:modes_mask:trace_file
     

where the components are as follows:

• host:port - the IP address and port number of the xSQL server or SqlServer application (the default is localhost:5001)
• connection_timeout - the database connection timeout (the default is 90000)
• datetime_precision - the datetime resolution for date/time fields (the default is 1 which means seconds)
• modes_mask - the compatibility modes(the default is 0; possible values are 1 for "mimic PostgeSQL", which is necessary for the WebFocus business edition)
• trace_file - the filename for tracing output (default is no tracing)

It is possible to specify multiple SQL servers via the JDBC URL that enables the connection to switch between these servers without the need to explicitly reconnect. The JDBC driver is connection can be switched in two ways:

• automatically when a connection error occurs; the JDBC driver will try to connect and execute the next SQL statement using the next server in a specified list;
• by explicitly specifying the zero-based identifier of a server with command set_server Id'.

One important application of this functionality could be to switch between an HA_Overview.htm master and replica application, or between cluster nodes.

Some examples of different url specifications follow:

 
    jdbc:extremedb:localhost:9001:90000:1000:1
     

This sets the server address to localhost on port 9001, with connection_timeout of 90 seconds, datetime resolution in milliseconds and with the "mimic PostgeSQL" option enabled.

     
    jdbc:extremedb:localhost
 

This sets the server address to localhost on default port 5001, with default connection_timeout of 90 seconds, and default datetime resolution in seconds.

     
    jdbc:extremedb:localhost:::1000
 

This sets the server address to localhost on default port 5001, with default connection_timeout of 90 seconds, and datetime resolution in milliseconds.

     
    jdbc:extremedb:192.168.49.1:8301::::trace.dbg
     

This sets the server address to 192.168.49.1 on port 8301, with default connection_timeout of 90 seconds, default datetime resolution in seconds and tracing output to file trace.dbg.

Alternatively, it is possible to specify driver properties (property_name = value tuples) in the driver manager for JDBC-enabled utilities. Custom JDBC driver manager programs should use the DriverManager method getConnection(String url, Properties info) to obtain the Properties class instance info; then set the following properties in the info object: connection_timeout, datetime_precision, modes_mask and trace_file. Also, the driver property encoding can be set; it is UTF-8 by default. When specified in the connection url, any of these connection parameters override the driver property in the configuration file.

JDBCBatch

The SDK sample JDBCBatch demonstrates batch processing with JDBC.

JDBC and eXtremeDB High Availability

When using JDBC with eXtremeDB High Availability it is possible to specify multiple SQL servers via the JDBC URL in order to be able to switch between these servers within a single connection and without any additional re-connect procedure. This capability can be used to switch between eXtremeDB High Availability master and replica applications or between nodes in an eXtremeDB Cluster network. The current server the JDBC driver is connected to can be switched in two ways:

• automatically when a connection error occurs: the JDBC driver will try to connect and execute the next SQL statement using the next server in a list; or
• by specifying the identifier (zero-based) of a server in the list by executing command set_server Id.

The list of servers is specified by adding a semicolon delimiter between host:port pairs in the URL. For example:

 
    jdbc:extremedb:host1:port1;host2:port2;host3:port3
     

Data Compression with JDBC

The JDBC driver supports data compression by setting the property "compression_level". Valid values for the compression level are 0 through 9: 0 = no compression; 1 = best speed, 9 - best compression.

For example:

 
    java.util.Properties props = new java.util.Properties();
    props.setProperty("compression_level", "9");
    con = DriverManager.getConnection(url, props);