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:
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:
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.
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. |
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.
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.
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:
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.
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.
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.
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).
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.
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:
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.
Let’s look at what happens when an application uses the JDBC driver to insert application tracing messages into a stream.
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:
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");
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 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);
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:
The important thing is for the client program to release (close) a resource as soon as it no longer needs it.
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");
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
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:
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:
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.
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:
The second option above constitutes “polling” the ResultSet, a technique that allows main application processing to proceed between polls.
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.
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.
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).
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.
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.
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.
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.
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 implements java.sql.Driver { Driver(); }
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()
;
}
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.
Modifier and Type | Field and Description |
---|---|
static long | TIMEOUT_NO_WAITTimeout interval for no-wait polling. |
CLOSE_ALL_RESULTS, CLOSE_CURRENT_RESULT, EXECUTE_FAILED, KEEP_CURRENT_RESULT, NO_GENERATED_KEYS, RETURN_GENERATED_KEYS, SUCCESS_NO_INFO
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. |
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
isWrapperFor, unwrap
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) |
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.
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.
public interface StreamingPreparedStatement
extends PreparedStatement, StreamingStatement
Aspen streaming prepared statement interface.
This interface extends PreparedStatement and StreamingStatement to include methods for sending rowtime bounds.
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
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. |
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 |
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:
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 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.
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
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.)
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.