SQLstream JDBC Driver

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.

This topic contains information on the following subtopics:

Overview of the s-Server JDBC Driver

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-6.0.0-clienttools-linux.run or SQLstream-client-tools-6.0.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 access the SQLstream JDBC API as a Javadoc or as a zip file.

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:

URI Segment Definition
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.

JDBC Driver Attributes

The following attributes are allowed:

Attribute Value
user s-Server or DB Username
password s-Server or 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.");
     }
}

Closing a Result Set

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:

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 of Using the JDBC Driver

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.

Using 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");

Connection Attributes

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

Using Rowtime Bounds with the JDBC Driver

While the JDBC driver has its own internal threadpool, the driver has no threads of its own exposed to the calling code. Instead, it relies upon client application threads to prepare new statements, wait for query results, insert new data, and so on. The driver’s internal threads are generally waiting upon select/poll/epoll calls and handle the low level interactions with the network. The data is then handed off to the calling threads for the application to process.

It is a fundamental characteristic of SQLstream s-Server that there may be arbitrarily long intervals between client thread calls into our driver. For example, a data-producing thread might insert a row of data, then block, waiting for another part of the application to produce more data. A data-consuming thread might block in the driver waiting for stream data, thus preventing that thread from doing other useful work. In either case, the thread may fail while executing in application code and never return to the driver for data or an orderly cleanup.

Consider the cursor loop in the receiving messages example described below. The client app’s thread might be busy for long intervals of time in between periods of servicing the stream by executing this loop. Processes may be waiting for the client application thread to service the SDP connection before they can write or read more data.

Conversely, in the context of the client app’s thread:

  • next() might block forever waiting for data
  • next() might endlessly return data and never return false

An application written against the SQLstream JDBC driver must be aware of these system characteristics. SQLstream s-Server presently enables the following possible models of client application interaction with the driver:

  1. blocking - wait, possibly forever, for data to arrive
  2. timeout - wait for an interval for data to arrive

These two options match a conventional system’s use of JDBC; the blocking model is the most common for applications operating on tables that are not expected to block.

Blocking

In this model, all client application calls into the driver will wait forever until data is delivered (for INSERTs) or received (for SELECTs). The client application is responsible for managing its own threads to ensure that other application features are not starved.

To prevent the main application from blocking, the developer must do one of the following:

  • Place ResultSet processing in its own thread so it can safely block indefinitely.
  • Use query timeouts to exit the ResultSet.next() if no data is available.

The second option above constitutes “polling” the ResultSet, a technique that allows main application processing to proceed between polls.

Timeout

In this model, you can set per-query timeouts; if the timeout limit is exceeded, an exception is thrown.

The StreamingStatement interface is an extension to the standard Java Statement.

StreamingPreparedStatement extends the standard Java PreparedStatement interface, and also SQLstream’s StreamingStatement.

All Statement objects created by a SQLstream driver will implement StreamingStatement. All PreparedStatement objects created by a SQLstream driver will implement both StreamingStatement and StreamingPreparedStatement.

Rowtime Bounds: Forcing Timely Output

Another issue that the JDBC driver must deal with is timely output, which relates to the current time of a stream (the stream’s ro.

A message stream is a sequence of timestamped messages (or rows): each row has a rowtime, and the sequence is ordered by rowtime. The current time of a stream is the rowtime of the latest row. When a relational operator executes, rows are passing downstream through that operator. The current time of its output stream(s) cannot be later than the latest of the rowtimes in all of its inputs. (It can be earlier, as discussed below.)

For efficiency, current time is implicit in the data. (The stream is implemented as an “asynchronous” series of messages). But this means the current time of a stream advances only when the next message arrives. This can be a problem for certain operations, which pause waiting for one more input message. Some examples are merging and rolling windows.

Merging and Rolling Windows

Multiple inputs merge into one output stream when several clients insert into the same named stream; or when the UNION ALL operator executes. The issue is the same: the output row is the earliest input row; that is, it is taken from the input stream with the earliest current time. But to know the current time of all input streams, the system must wait for a row to arrive on each. This can cause the operator to wait and introduce a delay in downstream processing. Note that this happens only when there is a noticeable real time gap between input rows, or else the waiting is negligible or there is almost always a next row already buffered up.

A window of a stream is a sequential block of messages. Rolling windows (also called sliding windows) means a ing of a stream into a series of windows: for instance, a series that groups together all messages with a rowtime in the same hour (called rolling one hour windows).

  • An example rolling-window query is to find totals and other statistics for the last hour of a stream of trades. This, too, has an issue with timeliness. When does a window end? Only when the next window begins, when a row arrives that belongs to the next window. Here again the system delays, waiting for a row to arrive.

To solve the problems of rolling windows and merging, a mechanism is needed to advance the current time of a stream explicitly and immediately, without waiting for an additional message. Generally, this solution is needed infrequently, because there is usually a next message, and advancing the current time implicitly works well.

Setting and Getting Rowtime Bounds

For rolling hourly windows, the client inserting the data would also set a rowtime bound (constraint) on the hour, in order to close off an hourly window. The only information this constraint carries is a rowtime, and we extend the PreparedStatement interface to handle such constraints.

Note that the rowtime bounds are supplied by the data source. By setting a rowtime bound with a value of noon, a data source is asserting a constraint on the future of the stream: noon is a lower bound to all forthcoming rows. A down-to-earth interpretation is that the source is announcing that it has nothing more to say until noon.

The rowtime bound applies to the inserted data, not to the rolling averages query that makes use of it, nor to any client reading from that query.

The JDBC driver does not know the actual ROWTIMEs of the rows passing through the driver.

Canceling Statements

The JDBC 3.0 spec does not mention canceling statements. The SQLstream JDBC driver implements Statement.cancel as described in the Sun document guidance for JDBC driver writers. The Sun document describes Statement.cancel as being primarily for multithreaded processing where one thread needs to cancel a long-running statement in another thread. The assumption is that a “long-running statement” is taking a long time to compute, either to perform its insert/update or to return a row, and that the thread running that statement is blocked, requiring the intervention of a second thread.

The SQLstream JDBC driver also implements Statement.cancel in a single-threaded context to interrupt long-running PreparedStatements, while retaining the streaming machinery necessary to handle new values that may be supplied to the statement’s bind variables. When a client application supplies new parameter values and re-executes, the statement is already prepared and the streaming machinery is ready to handle inputs satisfying the new values.

Here is a sequence description of such an app/driver interaction:

Application Actions Driver Actions
Application prepares query with bind params. Driver prepares statement.
Application supplies param values and executes PreparedStatement. Driver executes statementId w/ first set of param values.
Application gets first ResultSet and reads rows. Driver reads data from SDP stream.
Application cancels PreparedStatement. Driver cancels statementId;Driver flushes SDP stream by reading and discarding data until EOS;Driver closes first ResultSet, but SDP stream remains open for Statement.
Application supplies 2nd set of param values and re-executes PreparedStatement. Driver executes statementId w/ 2nd set of param values.
Application gets 2nd ResultSet and reads rows. Driver reads data from same SDP stream.
Application closes PreparedStatement. Driver shuts down SDP stream;Driver disposes statementId;Driver closes ResultSet, if left open .

A typical client application may not (and is not required to) explicitly cancel the Java PreparedStatement in between executions. More likely the application simply stops reading incoming rows, supplies the 2nd set of parameter values, and re-executes. In this case, the driver implicitly cancels the running statement and closes the first ResultSet before performing the 2nd execute.

Sending Rowtime Bounds

To illustrate the use of rowtime bounds, consider the example of a rolling hourly summary query against a stream of trades.

-- stream definition
 CREATE STREAM trades
       ( rowtime TIMESTAMP
       , ticker  VARCHAR(6)
       , shares  INTEGER
       , price   DECIMAL
       );
 -- rolling window query, benefits from rowtime bounds
 SELECT STREAM hour, ticker, SUM(shares), AVG(price) FROM
   (SELECT STREAM FLOOR(rowtime TO HOUR) as hour
         , ticker, shares, price
      FROM trades)
 GROUP by hour, ticker;

A client application writes data into the stream trades by executing a prepared INSERT statement.

 java.sql.PreparedStatement pstmt = connection.prepareStatement(
    "INSERT INTO trades(rowtime, ticker, shares, price) VALUES (?,?,?,?)"
    ); ...
 while (true) {
    -- somehow check for data
    if (haveData) {
        String ticker;
        int shares;
        java.sql.Decimal price;
        ...
        -- bind the columns
        pstmt.setTimestamp(1, new java.sql.Timestamp()); -- rowtime = current time
        stmt.setString(2, ticker);
        pstmt.setInteger(3, shares);
        pstmt.setDecimal(4, price);
        -- insert another row
        pstmt.executeUpdate();
    } else {
        -- somehow check for new hour and whether idle since hour changed
        if (needRowtimeBound) {
            -- downcast the PreparedStatement,
            -- to use the SQLstream extended interface
            StreamingStatement sstmt = (StreamingStatement) pstmt;
            sstmt.setRowtimeBound(new Timestamp(System.currentTimeMillis()));
        }
    }
 }

In order to cooperate with the rolling hourly averages, the client is well-mannered and chimes in every hour on the hour to announce, “no more inserts for the current hour”, using setRowtimeBound.

Extensions to the JDBC API

The standard JDBC API is described online at:

The SQLstream driver extends the standard JDBC API for time-constraints, millisecond-granularity timeouts, and access to server-side statement IDs for correlating with error stream entries.

The JDBC driver’s public classes and interfaces are in package com.sqlstream.jdbc.

class Driver

class Driver implements java.sql.Driver { Driver(); }

Interfaces That Extend the JDBC API

StreamingStatement

Every Statement and PreparedStatement created by the SQLstream JDBC driver implements the StreamingStatement interface This object is used to execute a streaming SQL statement and return its results. The following code shows the getRowtimeBound method of this interface.

interface StreamingStatement extends java.sql.Statement {
   long getStatementId();
   long getQueryTimeoutMillis();
   void setQueryTimeoutMillis(long);
   Timestamp getRowtimeBound()
;
}

Streaming Statement Interface Summary

public interface StreamingStatement
extends Statement

This interface extends Statement to include methods for getting the statement’s unique identifier, getting rowtime bounds, and managing millisecond-granularity statement timeouts. See http://docs.oracle.com/javase/7/docs/api/java/sql/Statement.html for details on Statement.

Field Summary

Modifier and Type Field and Description
static long TIMEOUT_NO_WAITTimeout interval for no-wait polling.

Fields inherited from interface java.sql.Statement

CLOSE_ALL_RESULTS, CLOSE_CURRENT_RESULT, EXECUTE_FAILED, KEEP_CURRENT_RESULT, NO_GENERATED_KEYS, RETURN_GENERATED_KEYS, SUCCESS_NO_INFO

Method Summary

Modifier and Type Method and Description
long getQueryTimeoutMillis()Returns the query timeout value of this statement, in milliseconds, as set by setQueryTimeoutMillis(long).
Timestamp getRowtimeBound()Returns the latest rowtime bound from the target stream.
long getStatementId()Returns the unique identifier of this statement.
void setQueryTimeoutMillis(long millis)Sets the query timeout of this StreamingStatement.

Methods inherited from interface java.sql.Statement

addBatch, cancel, clearBatch, clearWarnings, close, closeOnCompletion, execute, execute, execute, execute, executeBatch, executeQuery, executeUpdate, executeUpdate, executeUpdate, executeUpdate, getConnection, getFetchDirection, getFetchSize, getGeneratedKeys, getMaxFieldSize, getMaxRows, getMoreResults, getMoreResults, getQueryTimeout, getResultSet, getResultSetConcurrency, getResultSetHoldability, getResultSetType, getUpdateCount, getWarnings, isClosed, isCloseOnCompletion, isPoolable, setCursorName, setEscapeProcessing, setFetchDirection, setFetchSize, setMaxFieldSize, setMaxRows, setPoolable, setQueryTimeout

Methods inherited from interface java.sql.Wrapper

isWrapperFor, unwrap

Method Detail

Method Details
getStatementId *long getStatementId()*throws SQLExceptionReturns the unique identifier of this statement.Returns:statement identifierThrows:SQLException - if the statement is not open or statement type does not prepare a server-side statement
getQueryTimeoutMillis long getQueryTimeoutMillis() throws SQLExceptionReturns the query timeout value of this statement, in milliseconds, as set by setQueryTimeoutMillis(long).Returns:the current query timeout limit in milliseconds; zero means there is no limitThrows:SQLException - if a database access error occurs
getRowtimeBound *Timestamp getRowtimeBound()*throws SQLExceptionReturns the latest rowtime bound from the target stream. This is a lower bound on the rowtime of the next row to arrive on the stream.For a SELECT statement or other query, the target is the stream of results. For an INSERT statement the target is the stream into which rows are being inserted. Other kinds of statements (such as DDL) have no target statement.Returns:rowtime bound (UTC)Throws:SQLException
setQueryTimeoutMillis void setQueryTimeoutMillis(long millis) throws SQLExceptionSets the query timeout of this StreamingStatement.When a timeout t is set, the JDBC driver will wait no longer than t milliseconds for the server to execute the statement. If this time limit is exceeded during statement execution, the statement throws a SQLException. This method is like Statement.setQueryTimeout(int), but with millisecond precision.The timeout applies each time this statement is executed, by a call to Statement.execute(java.lang.String), Statement.executeUpdate(java.lang.String), or Statement.executeQuery(java.lang.String). The timeout clock starts when such a method is called and stops when it returns: thus the timeout is a maximum time allowed to execute a DDL or DML statement, or to wait for the ResultSet returned by executing a query. Note that the timeout reflects the passage of real time, which need not be related to the rowtime of a stream.By default, a statement has no timeout (will wait forever); this is denoted as a timeout value 0.Fetch timeoutsWhen the statement is a query, execution returns a ResultSet, and the query timeout value becomes the fetch timeout. This timeout affects all methods that fetch new data from the server, such as ResultSet.next().Parameters:millis - the new query timeout limit in milliseconds; zero means there is no limit (wait forever), and TIMEOUT_NO_WAIT means do a no-wait pollThrows:SQLException - if a database access error occursSee Also:getQueryTimeoutMillis(), Statement.getQueryTimeout(), Statement.setQueryTimeout(int)

StreamingPreparedStatement

Every PreparedStatement created by the SQLstream JDBC driver implements the StreamingPreparedStatement interface. The following code shows the setRowtimeBound method.

interface StreamingPreparedStatement extends
               java.sql.PreparedStatement, StreamingStatement {
   void setRowtimeBound(Timestamp bound)
;
}

However, the setRowtimeBound method is only meaningful for INSERT EXPEDITED prepared statements. A SQLException is thrown if setRowtimeBound is called on other prepared queries.

Example usages:

long nextRowDelay = 5000L;
pstmt.setRowtimeBound(new Timestamp(System.currentTimeMillis() +
nextRowDelay))

or

long nextRowDelay = 5000L;
java.sql.Timestamp ts = <some retrieved value>
pstmt.setRowtimeBound(new Timestamp(ts.getTime() + nextRowDelay));

setRowtimeBound(Timestamp bound) sets a rowtime bound, which has a single attribute, a timestamp.

Streaming Prepared Statement Interface Summary

public interface StreamingPreparedStatement
extends PreparedStatement, StreamingStatement

Aspen streaming prepared statement interface.

This interface extends PreparedStatement and StreamingStatement to include methods for sending rowtime bounds.

Streaming Prepared Statement Field Summary

Fields inherited from interface com.sqlstream.jdbc.StreamingStatement

TIMEOUT_NO_WAIT

Fields inherited from interface java.sql.Statement

CLOSE_ALL_RESULTS, CLOSE_CURRENT_RESULT, EXECUTE_FAILED, KEEP_CURRENT_RESULT, NO_GENERATED_KEYS, RETURN_GENERATED_KEYS, SUCCESS_NO_INFO

Streaming Prepared Statement Method Summary

Modifier and Type Method and Description
void setRowtimeBound(Timestamp bound)Sets the rowtime bound for the current stream.
void setRowtimeBound(Timestamp bound, Calendar cal)Sets the rowtime bound for the current stream.

StreamingPreparedStatement Method Detail

Method Detail
setRowtimeBound void setRowtimeBound(Timestamp bound) throws SQLExceptionSets the rowtime bound for the current stream. The caller thereby indicates that no more tuples will be written to the stream until the UTC time specified by bound.Parameters:bound - earliest time (UTC) at which next tuple will be writtenThrows:SQLException
setRowtimeBound void setRowtimeBound(Timestamp bound, Calendar cal) throws SQLExceptionSets the rowtime bound for the current stream. The caller thereby indicates that no more tuples will be written to the stream until the time specified by bound and cal. Implementation note: cal is currently ignored and this method behaves exactly as setRowtimeBound(Timestamp).Parameters:bound - earliest time at which next tuple will be writtencal - Calendar context for the bound TimestampThrows:SQLException

JDBC Compatability

The SQLstream JDBC driver is JDBC 4.1 compatible and will run on any JVM from version Java 8 on up

Note: SQLstream s-Server 6.0.0 is not backwards compatible with earlier versions of the SQLstream JDBC driver.

The driver is delivered as two independent JARs:

  • driver-only JAR — /lib/sqlstreamjdbc.jar – contains only 1.7-compatible SQLstream driver code: no third-party libraries.
  • “complete” JAR — /lib/sqlstreamjdbc_complete.jar — contains same classes as driver-only JAR plus 1.7-compatible versions of any third-party libraries upon which the implementation depends.

The lib directory is found where SQLstream s-Server is installed: $SQLSTREAM_HOME/lib

The jar files are also found in the jdbc directory when the ClientTools are installed (via SQLstream-XXX-clienttools-linux.run).

The JDBC Driver and Daylight Savings Time

The s-Server JDBC driver processes TIMESTAMP strings without timezones. This can cause confusing output displays during the transitions from and to Daylight Savings Time.

Suppose you have a pipeline which runs in San Francisco and uses our JDBC driver. If you INSERT a TIMESTAMP string ‘2018-07-17 11:00:00’, this string will be ingested and flow through s-Server’s pipeline as 2018-07-17 11:00:00 GMT.

When you SELECT the same timestamp through the JDBC driver (as part of a dashboard or report), the output TIMESTAMP will be displayed as 2018-07-17 11:00:00 PDT. Generally, this will produce consistent results: timestamps ingested with local timezones will output with the same timestamp in the same timezone.

However, during the transitions to Standard and Daylight Savings time, ouput timestamps will be skewed by an hour for a period lasting the length of the local displacement from GMT (7 or 8 hours in San Francisco). In other words, output timestamps will display as one hour later than input timestamps for an eight hour stretch around the transition from Standard to Daylight Savings Time. Similarly, output timestamps will display as one hour earlier than input timestamps for a seven hour stretch around the transition from Daylight Savings to Standard Time.

Note on JDBC Driver and Perl

Perl integration with JDBC is supported using three CPAN modules: Inline, Inline::Java, and JDBC. You need to install these modules in order to get Perl to work with JDBC. See http://www.cpan.org/modules/INSTALL.html for more information on installing CPAN modules.

To get these modules to work with all versions of Perl, you also need to install the following patch for the JDBC driver:

http://www.cpan.org/modules/INSTALL.html

JDBC Driver and Failure Scenarios

SQLstream s-Server fails or is unreachable

If the SQLstream s-Server fails, the driver reports an error. The JDBC driver reports an error. If the client application is waiting in a driver method at that time, it will be notified of the error immediately. Otherwise the client application may not learn of the server crash until it next calls into the driver.

When first asked to create a JDBC connection, the driver attempts to establish an SDP connection. If the SQLstream s-Server is unreachable and either of these connections cannot be made, the driver reports an error.

(The system administrator might prevent this situation from happening by implementing a hot- or cold-swap for the SQLstream s-Server, and a load balancer so that clients can address it using the same name.)

Client application crashes

If the SQLstream s-Server detects that the client application crashed, the SQLstream s-Server then cleans up any server-side orphaned statements.

Server-side distributed garbage collection (DGC) eventually harvests the remote objects abandoned by the crashed application and finalizers in these objects perform any remaining cleanup.