StreamBase JDBC DataSource Configuration

If you want to restrict this object to be associated with specific engines, do so here. Each entry can be a specific engine name or a regular expression that can applies to more than one engine. This name-value pair is optional and has no default value. If not present, the configuration is associated with all engines.

For example:

associatedWithEngines = [ "javaengine", "otherengine[0-9]" ]

Data source URL. You can provide an enciphered string for the value; generate the enciphered string with the sbcipher command and prefix #! to the generated string. This name-value pair is required.

For example:

serverURL = "jdbc:mysql://mysqlserver:3306/test"

Data source driver. This name-value pair is required.

For example:

driverClassName = "com.mysql.jdbc.Driver"

The database user name. You can provide an enciphered string for the value; generate the enciphered string with the sbcipher command and prefix #! to the generated string. This name-value pair is optional and has no default value. If it is not specified, then it must appear in the data source URL.

For example:

userName = "fred"

The database user password. You can provide an enciphered string for the value; generate the enciphered string with the sbcipher command and prefix #! to the generated string. This name-value pair is optional and has no default value. If it is not specified, then it must appear in the data source URL.

For example:

password = "secret"

Int. Sets the number of rows that will be read at a time. This is a hint to the JDBC driver and may be interpreted differently by each driver. A value of 0 means use the driver default. This name-value pair is optional and its default value is 0. Most drivers throw a SQLException if the value is less than 0.

Consider limiting the fetch size when your StreamBase application experiences memory problems related to large result sets returned from queries to the JDBC data source. Consider increasing fetch size if deadlocks occur because you are performing multiple queries against the same JDBC data source and query result sets are larger than the current database fetch size.

MySQL, however, will allocate and read the entire query result into memory unless the fetchSize is set to the magic value -2147483648 (Integer.MIN_VALUE). In the case where a SELECT returns a large amount of data, memory will be exhausted unless the fetchSize is set to the magic value.

A positive integer serves as a hint to non-MySQL JDBC drivers for the size to allocate for result set buffers. The actual size allocated depends on how a given driver interprets it, which may vary among drivers. A positive integer is ignored by MySQL drivers.

For example:

fetchSize = 123

Int. The timeout in milliseconds of background JDBC operations in StreamBase Studio and at runtime. This parameter can affect typechecking efficiency. StreamBase Studio communicates with the data source server in order to typecheck an application that has a JDBC data source connection. If the database server does not respond during this interval, the typecheck fails.

The default value of 15 seconds is adequate for normal local area network connections. Consider increasing this value if you experience typecheck failures due to a slow network connection. This name-value pair is optional and its default value is 15000.

For example:

operationTimeoutMilliseconds = 12000

Int. The timeout in milliseconds that the StreamBase engine is to wait for JDBC Query operations to execute. This name-value pair is optional and its default value is 0, which means it waits forever.

For example:

queryTimeoutMilliseconds = 321

Int. The amount of idle time in milliseconds to keep a connection open, after which it is automatically disconnected. This prevents the application from holding a connection indefinitely. This name-value pair is optional and its default value is 0.

For example:

connectionTimeoutMilliseconds = 222

Long. The maximum column size expected in a JDBC table. Columns that are larger than this value will cause a typecheck exception. You can use HOCON power-of-ten or power-of-two suffixes like kB, MB, K, or M as an abbreviation.

This name-value pair is optional and its default value is 2048.

For example:

maxColumnSizeBytes = 4K

String. Retry any select, insert, or update JDBC operation that returns the specified SQLSTATE for this database driver. The operation is retried until it succeeds. This name-value pair is optional and has no default value.

For example:

retrySQLStateValues = ["001", "002"]

Int. Allows a Query operator to perform an insert or update operation in batch mode, and sets the number of statements that will be executed at one time. This name-value pair is optional and has no default value.

For example, if jdbc-batch-size is set to 10, then when the first nine tuples arrive at the Query operator, they are held by the JDBC connection instead of being executed immediately. When the tenth tuple arrives and is added to the batch of statements, the entire batch is executed at once.

If an update is interrupted normally (for example, by stopping the sbd server), the Query operator updates the JDBC table using all the accumulated statements, even if there are fewer than the specified number. However, if the application is stopped abnormally, the updates may not occur.

The value must be an integer greater than 1 (a value of 1 is valid, but not meaningful for a batch operation).

Note that your application may require more memory when you perform query updates in batch mode.

For example:

operationBatchSize = 10

Int. If specified, a batch is executed at the specified timeout in milliseconds, even if it is not currently full. This is useful for making sure that the batch is executed if data stops coming into the operator (for example, the end of a trading day).

Tuples may be written sooner than this number, or more frequently, for any number of reasons. This name-value pair is optional and its default value is 60000 (60 seconds).

For example:

batchTimeoutMilliseconds = 1000

String. If a specified string is matched in the text of any error message, the JDBC operator assumes a communications failure has occurred, and attempts to open a new connection to the database server in order to re-execute the current command. If the error text is not matched in the error strings, and the JDBC operator has an error port, an error tuple is sent to the error port. If there is no error port, a tuple is sent on the error stream.

You can specify more than one jdbc-reconnect-regexp parameters to specify several error message strings to be considered communications failures. Each specified string is compared in order to the received error message text.

This name-value pair is optional. If you do not specify a reconnectRegexps value, the pattern .* (period-asterisk) is used as the pattern string, which means any received error causes a reconnection attempt.

For example, see the following array:

reconnectRegexps = [
".*duplicate private key.*"
".*some other temporary failure.*"
]

String. An array of regular expressions which, if matched, cause the JDBC operator to NOT assume a communication failure and attempt to open a new connection to the database server to re-execute the command. This array is checked BEFORE the jdbcReconnectRegexp array. This name-value pair is optional and has no default value.

For example, see the following array:

dontReconnectRegexps = [ 
".*a fatal error for which we don't want to reconnect.*" 
]

Int. This name-value pair controls the number of reconnections the StreamBase engine is to attempt. If the connection to the database server is lost, a value for this parameter of five attempts to establish a connection to the database server five times. If five connection attempts fail, an error message is printed and the StreamBase engine exits with an error status.

If a connection is reestablished to the database server, and then the connection is lost some time thereafter, the reconnection count is reset so that five more attempts to reconnect are made again.

The retry count is reset for each SQL exception, not for the lifetime of the connection. A value of -1 specifies that the server is to continue reconnection attempts without limits. This name-value pair is optional and its default value is 0, which disables reconnection attempts. Supplying a nonzero value is therefore strongly recommended.

For example:

maxReconnectAttempts = 42

Int. The time in milliseconds to wait between execute attempts. This name-value pair is optional and its default value is 500; a value of 0 means an immediate reconnect.

For example:

reconnectSleepMilliseconds = 50

Bool. If set to true, connections using the same <datasource> name are shared. This option can serve as an optimization that reduces the time to fetch and release a connection, but it can lead to blocks if updates and selects occur in the same application.

This name-value pair is optional and its default value is false, which means that, in an application with two or more JDBC operators, each JDBC operator gets its own connection from the StreamBase Server to the specified data source.

For example:

shareConnection = true

Bool. Some databases (such as Vertica) require the strings passed into a PreparedStatement to be enclosed in single quotes ('Dallas'). Set this to true if the database vendor documentation specifies quoting the strings passed into PreparedStatement.setString.

This name-value pair is optional and its default value is false.

For example:

quoteStrings = false

String. An associative array of any other JDBC driver parameters. You can provide an enciphered string for the value; generate the enciphered string with the sbcipher command and prefix #! to the generated string.

For example:

additionalDriverArguments =
"com.oracle.someSpecificOracleJDBCSetting" = "my specific oracle setting value"
"com.other.setting" = "#!another specific setting"