SQLstream JDBC Driver

<< Click to Display Table of Contents >>

Navigation:  Integrating Blaze with Other Systems >

SQLstream JDBC Driver

Previous pageReturn to chapter overviewNext page

The SQLstream JDBC driver lets other applications get data in and out of s-Server. Such applications can be located anywhere that can reach s-Server via JDBC.

JDBC stands for Java DataBase Connectivity, a standard Java API for connecting to relational databases and other data sources that can produce relational data. JDBC works on both Linux and Windows, and is supplied either as part of the distributed SQLstream s-Server product or as part of the ClientTools download from the SQLstream website (via SQLstream-5.1.0-clienttools-linux.run or SQLstream-client-tools-5.1.0-windows.exe).

The following diagram shows how the JDBC driver can fit into various application scenarios:

jdbc_driver_fs_arch

Application A uses JDBC to communicate with SQLstream s-Server.

Application B uses the log4j driver, such that every log4j message generated by application B is automatically converted into a bind + execute of a prepared INSERT statement of the JDBC driver. The log4j driver's needs are fully met by the JDBC driver: it does not need to communicate with the SQLstream s-Server directly.

Application C uses the JMS driver, implemented partly in terms of the JDBC driver, and partly using driver extensions.

You can download the JDBC API as a Javadoc here.

You can use SSL with the JDBC driver. For more information, see the topic Using SSL in the Administrator Guide.

Connect string

The connect string for the JDBC driver has the following format:

  jdbc:sqlstream:sdp://host[:port];[ attribute = value ];...

 

The syntax conforms to the Microsoft OLE DB spec. See http://msdn2.microsoft.com/en-us/library/ms713643.aspx for more details.

The "authority" portion of the URI, //host[:port], refers to the SQLstream s-Server:

host

Address of the SQLstream s-Server.

Valid hosts include a local name ("marmolite"), a fully-qualified name ("jhydro.dyndins.org"), or an IP address ("64.85.61.21").

The default value is your local hostname as returned by the hostname command on Linux.

 

port

Port of the SQLstream s-Server.

The JDBC driver uses SDP for all communications; port will be the port on which the SDP server is listening.

The default value is 5570.

 

The following attributes are allowed:

Attribute

Value

user

DB Username

password

DB Password

sessionName

Name of session, e.g., "sqllineClient:user@host.domain.com"

clientUserName

OS login name, e.g., "fjfarrago"

clientUserFullName

Full name, e.g., "Franklin J. Farrago"

clientProgramName

Name of program making the connection, e.g., "Accounts Payable"

clientProcessId

Process ID of program making the connection, e.g., "12345"

autoCommit

true or false (all other values are invalid, and will generate an exception).

Here is a connect string that uses the default host and port but supplies all the above sample attributes:

jdbc:sqlstream:sdp:;user=someudoesn't mattermser;password=somepass; \

  sessionName=sqllineClient:user@host.domain.com;clientUserName=fjfarrago; \

  clientUserFullName="Franklin J. Farrago"; \

  clientProgramName='Accounts Payable'; \

  clientProcessId=12345; \

  autoCommit=false

 

The example illustrates the use of several valid quoting styles, even though none of these sample attribute values require any quoting.

If the application uses java.sql.Driver#connect(String, Properties) or java.sql.DriverManager#getConnection(String, Properties), then any connection attribute can instead be supplied as an entry in a java.util.Properties object. An attribute on the URI takes precedence over the same-named attribute in the Properties object.

A more typical example would be to supply the optional host and port in the connect string, jdbc:sqlstream:sdp://marmolite:1234, and then to supply the username and password credentials plus any other connection attributes in the Properties object.

autoCommit and s-Server

The JDBC specification provides a connection attribute autoCommit. It is accessed via the java.sql.Connection methods void setAutoCommit(boolean) and boolean getAutoCommit(). In SQLstream, the meaning of the "commit" SQL command differs from an RDBMS context: its meaning is nearer to "flush and commit."

We recommend keeping autoCommit set to false, its default setting. Suppose a SQLstream s-Server connection prepares an INSERT command and executes it several times. Each execution inserts a record into the stream.

If autoCommit is false, the execute will not necessarily cause a round trip. In practice, it is likely to make a round trip only when the client-side buffer is full. Since the client side buffer is 32k bytes, the buffer may be full only after many tens or hundreds of rows. When the JDBC driver ultimately sends the rows, the operation is again irrevocable. (This is different than usual DBMS semantics, which permits a "rollback" operation. In SQLstream, rollback has no effect.) By default, autoCommit is set to false for the SQLstream JDBC driver.
If autoCommit is true, each execute causes the JDBC driver to send a buffer containing a single row to the server and to insert the record irrevocably into the stream.

Note: In past versions of s-Server, autoCommit was set to true by default, in keeping with the standard JDBC API. But in a streaming context, setting autoCommit to true significantly lowers performance, as it lowers the throughput of INSERT to streams.

If your application needs good network throughput, we strongly recommend that you keep autoCommit set to false. Having said that, with autoCommit set to false, records will sit in the client buffer until one of the following happens:

an INSERT fills up the buffer.
the statement is closed.
the client explicitly calls Statement.commit.

If the connection is not creating rows regularly, this may cause rows not to be sent in a timely manner. You can solve this problem by periodically calling Statement.commit. (If the buffer is empty, commit is a no op.)

Note: client tools like sqllineClient and sqllineRemote continue to set autoCommit to true. This is because in a command line context, you generally want to commit immediately after executing a SQL statement.

Autocommit and flush()

With Autocommit set to true, the JDBC driver will flush after each row. This is slightly faster as the driver does not need to check for transactions. Best practice is to flush only when needed.

Sending Messages

Insert

To insert a single record into a stream, the application creates a statement and executes it directly:

java.sql.Statement stmt = connection.createStatement();

 stmt.execute(

    "INSERT INTO Logger(timestamp, level, message) " +

    "VALUES ('2004-09-26 22:59:00', 'FINE', 'Login by jhyde')");

 

A prepared statement is a more efficient way to insert multiple records:

java.sql.PreparedStatement pstmt = connection.prepareStatement(

    "INSERT INTO Logger(timestamp, level, message) " +

    "VALUES (?, ?, ?)");

pstmt.setTimestamp(1, new java.sql.Timestamp());

pstmt.setString(2, "FINE");

pstmt.setString(3, "Login by jhyde");

pstmt.executeUpdate();

pstmt.setTimestamp(1, new java.sql.Timestamp());

pstmt.setString(2, "FINER");

pstmt.setString(3, "Session initialized successfully");

pstmt.executeUpdate();

 

By default, the JDBC API uses the Blocking model. If it would cause a problem for the application to block while trying to insert each record, the application can use the Timeout model. The following code is basically the same, but times out if the insert does not complete within 5 seconds. A statement which has thrown a TimeoutException is still valid; after catching the exception the application can retry the insert.

java.sql.PreparedStatement pstmt = connection.prepareStatement(

    "INSERT INTO Logger(timestamp, level, message) " +

    "VALUES (?, ?, ?)");

pstmt.setQueryTimeout(5);

pstmt.setTimestamp(1, new java.sql.Timestamp());

pstmt.setString(2, "FINE");

pstmt.setString(3, "Login by jhyde");

while (true) {

    try {

       pstmt.executeUpdate();

       break;

    } catch (com.sqlstream.jdbc.TimeoutException e) {

       System.out.println("Unable to insert for 5 seconds.");

    }

}

pstmt.setTimestamp(1, new java.sql.Timestamp());

pstmt.setString(2, "FINER");

pstmt.setString(3, "Session initialized successfully");

while (true) {

    try {

       pstmt.executeUpdate();

       break;

    } catch (com.sqlstream.jdbc.TimeoutException e) {

       System.out.println("Unable to insert for 5 seconds.");

    }

}

 

A real application would likely package into a separate method the while loop that retries the insert until successful. Moreover, rather than retrying immediately, the application might perform some other work before the next retry.

Note: Once done with a Statement or PreparedStatement, you should perform the following code:

try {

    pstmt.close();

} catch (SQLException e) {

    -- handle or ignore exception as appropriate for context

}

See also Closing a connection and closing a result set.

Insert Expedited

The most efficient way to insert multiple records is with a prepared "insert expedited" statement:

java.sq.PreparedStatement pstmt = connection.prepareStatement(

   "INSERT EXPEDITED INTO Logger(timestamp, level, message)"

         + "VALUES (?, ?, ?)");

 

Substitute this statement for the INSERT INTO Logger... statement prepared in the previous example, leaving the rest of the example's code unchanged. The JDBC driver and SQLstream s-Server optimize expedited insert to bypass vJDBC for sending bind parameter values, using SDP to communicate directly with the server. SDP (Streaming Data Protocol) efficiently transmits only the parameter values to the server. The tradeoff is that there is no "back-channel" for reporting the row count for each executeUpdate call.

Note: Insert Expedited does not affect autocommit and vice versa. Insert Expedited determines how rows are sent (using SDP), while autocommit determines when rows are sent (either immediately or when the buffer is full/statement is closed/explicit commit called).

Choosing between Insert and Insert Expedited

An insert statement without bind parameters should be implemented by the application as a "plain" Insert. The values are present in the SQL itself and the statement should be executed directly, without even a prepare unless the same values are to be inserted repeatedly. In this case, a single vJDBC call efficiently prepares and executes the statement.

If an insert statement will be executed repeatedly with different values each time, however, best practices dictate using bind parameters for security reasons and efficiency. If an insert statement with bind parameters is to be executed frequently over a sustained period of time, the application should implement this as Insert Expedited. If the same statement is to be executed only infrequently, the application should use "plain" Insert to avoid maintaining an open but little-used SDP connection.

Receiving Messages

To receive messages, prepare a query.

java.sql.Statement stmt = connection.createStatement();

   java.sql.ResultSet rs = stmt.executeQuery(

       "SELECT * FROM Logger");

 

A cursor loop reads messages and prints them out:

while (rs.next()) {

    Timestamp ts = rs.getTimestamp(1);

    String message = rs.getString(3);

    System.out.println(ts + ": " + message);

}

 

By default, the JDBC API uses the blocking model. If it would cause a problem for the application to block while waiting for a message, the application can use the timeout model. The following code is basically the same, but times out if there is no data for 5 seconds or more. A statement or result set which has thrown a TimeoutException is still valid; after catching the exception, the application can ask for more rows.

import java.sql.*;

import com.sqlstream.jdbc.TimeoutException;

stmt.setQueryTimeout(5);

while (true) {

    try {

        while (rs.next()) {

            Timestamp ts = rs.getTimestamp(1);

            String message = rs.getString(3);

            System.out.println(ts + ": " + message);

        }

        break;

    } catch ( TimeoutException e) {

        System.out.println("No data for 5 seconds.");

    }

}

 

Note: Once done with a ResultSet , you should perform the following code:

try {

    rs.close();

} catch (SQLException e) {

    -- handle or ignore exception as appropriate for context

}

 

When you are done with both the ResultSet and the PreparedStatement used to obtain that ResultSet, you can close them both by performing the following code:

try {

    rs.close();

    pstmt.close();

} catch (SQLException e) {

    -- handle or ignore exception as appropriate for context

}

 

See also Closing a connection and closing a result set.

Examples

Let's look at what happens when an application uses the JDBC driver to insert application tracing messages into a stream.

Connecting

On startup, the application registers the SQLstream JDBC driver (if not already loaded by System property jdbc.Drivers ):

Class.forName("com.sqlstream.jdbc.Driver");

 

The application creates a JDBC session in one of the three traditional ways, as follows:

Style 1

In style 1, the host, port, and parameter values are specified as separate strings in the getConnection method, as in the following example:

java.sql.Connection connection = DriverManager.getConnection(

    "jdbc:sqlstream:sdp://host:port",

    "username",

    "password",

    "autoCommit=false");

 

In general, the term "host" defaults to your local machine name, and "port" defaults to "5570". Using these defaults, and "branston" for param1 and "pickle" for param2, the code above becomes the following:

java.sql.Connection connection = DriverManager.getConnection(

    "jdbc:sqlstream:sdp://myhostname:5570",

    "branston",

    "pickle",

    "autoCommit=false");

Style 2

In style 2, the host, port, and parameter values are specified as a single string, in which semicolons separate the host, port, and parameters, and each parameter is preceded by its name, as in the following example using two parameters:

java.sql.Connection connection = DriverManager.getConnection(

   "jdbc:sqlstream:sdp://host:port;param1=value1;param2=value2;autoCommit=false");

 

When defaults are substituted, the code becomes the following:

java.sql.Connection connection = DriverManager.getConnection(

   "jdbc:sqlstream:sdp://myhostname;port=5570;user=branston;password=pickle;autoCommit=false")

Style 3

Style 3 uses the same single-string method of style 2, but also passes properties as a second parameter to the getConnection method, as in the following example:

java.util.Properties props = new java.util.Properties();

props.setProperty("host", "myhostname");

props.setProperty("port", "5570");

props.setProperty("user", "branston");

props.setProperty("password", "pickle");

props.setProperty("autoCommit", "false");

java.sql.Connection connection = DriverManager.getConnection(

   "jdbc:sqlstream:sdp:", props);

Closing a Connection

When done with a connection, the client program should close it as follows:

try {

    connection.close();

} catch (SQLException e) {

    -- handle or ignore exception as appropriate for context

}

 

And, of course, you can close all three objects at once like this:

try {

    rs.close();

    pstmt.close();

    connection.close();

} catch (SQLException e) {

    -- handle or ignore exception as appropriate for context

}

 

Note that the most efficient way to close objects is in the order shown above (ResultSet, Statement, Connection). However, be aware of the following considerations:

When you close a Statement, it automatically closes any still-open ResultSets.
When you close a Connection, it automatically closes any still-open Statements (which in turn auto-closes any still-open ResultSets).

The important thing is for the client program to release (close) a resource as soon as it no longer needs it.

DataSource

In enterprise applications, it is typical to wrap the connection logic in a DataSource object. Application Servers allow declarative configuration of DataSources to be used at runtime. The various style choices described above can apply to DataSources as well. Here's a sample Tomcat 5.x DataSource configuration:

<Resource

   name="jdbc/sqlstream/node1"

   type="javax.sql.DataSource"

   factory="com.sqlstream.jdbc.DataSourceFactory"

   auth="Container"

   username="sa"

   password="mumble"

   driverClassName="com.sqlstream.jdbc.Driver"

   url="jdbc:sqlstream:sdp://myhostname"

   maxActive="8"

   maxIdle="4" />

 

The application makes JNDI calls to obtain a DataSource instance from Tomcat. Using a DataSource this way requires that optional parameters be supplied on the URI.

An application could also create a SQLstream DataSource at runtime:

Class clazz =    Class.forName("com.sqlstream.jdbc.Driver");

com.sqlstream.jdbc.Driver driver =

   (com.sqlstream.jdbc.Driver)clazz.newInstance();

java.util.Properties props = new java.util.Properties();

-- add connection    properties to props...

javax.sql.DataSource dataSource =

   new com.sqlstream.jdbc.DataSource(

      driver,

      "jdbc:sqlstream:sdp://myhostname",

      props)

 

Creating a DataSource at runtime this way means that optional parameters can be supplied either on the URI or in the Properties.

Whichever way the application obtains its DataSource, getting a connection is simple:

javax.sql.DataSource dataSource;

java.sql.Connection conn = dataSource.getConnection("branston", "pickle");

-- or, if DataSource was already configured with login credentials ....

conn = dataSource.getConnection();

 

A DataSource is more amenable to connection-pooling, and shields the application from the gory details of connection strings. Getting a connection from a data source is simple:

MarmiteDataSource marmiteDataSource;

java.sql.Connection connection =

    marmiteDataSource.getConnection("branston", "pickle");

 

Miscellaneous

Connection Attributes in General

The following table lists the supported client-specified connection parameters.

Attribute

SESSIONS_VIEW Column

SQL Expression

Example

sessionName

SESSION_NAME

 

"Payments Logging"

clientUserName

SYSTEM_USER_NAME

SYSTEM_USER

"fjfarrago"

clientUserFullName

SYSTEM_USER_FULLNAME

 

"Franklin J. Farrago"

clientProgramName

PROGRAM_NAME

 

"Acme Accounts Payable"

clientProcessId

PROCESS_ID

 

"12345"

These may be specified as entries in a Properties object or as parameters on the URI. The JDBC driver supplies a default value for clientUserName from the user.name system property. Each of the above attributes can be queried using the system management "sessions_view", e.g.,

SELECT session_name FROM sys_boot.mgmt.sessions_view