Setting and modifying SQL passthrough mode

Universal Cache User Guide : SQL passthrough : Configuring and using SQL passthrough : Setting and modifying SQL passthrough mode

The default SQL passthrough mode is set with the SqlPassthroughRead and SqlPassthroughWrite parameters. The parameter settings can be overridden per session or per transaction by using the SET PASSTHROUGH and SET TRANSACTION PASSTHROUGH command. Alternatively, the passthrough mode can also be defined per connection with the ODBC connection attributes or JDBC connection properties.

There are three SQL passthrough modes (levels):

▪NONE: SQL passthrough is not used; no commands are passed from the frontend to the backend

▪CONDITIONAL: SQL passthrough is activated by a missing table or a syntax error.

▪FORCE: all statements are passed from the front end to the backend

For the SET TRANSACTION PASSTHROUGH and SET PASSTHROUGH statements, there is also a fourth option, DEFAULT, which returns the passthrough mode to the current session default.

The precedence hierarchy is, from high precedence to low:

▪SET TRANSACTION PASSTHROUGH: transaction-level settings

▪SET PASSTHROUGH: session-level settings

▪ODBC connection attributes and JDBC connection properties

▪Parameter settings specified by the value in solid.ini configuration file

▪solidDB^® factory value for the parameter; the factory value for SqlPassthroughRead and SqlPassthroughWrite is NONE.

Setting transaction-level passthrough mode with the SET TRANSACTION PASSTHROUGH command

The SET TRANSACTION PASSTHROUGH command has effect in the beginning of a transaction, and it affects the transaction until commit or abort. If the statement is issued in the middle of a transaction, an error is returned.

SET TRANSACTION PASSTHROUGH {READ <passthrough level>
[WRITE <passthrough level>]} | {WRITE <passthrough level> |
[READ <passthrough level>]} | <passthrough level>

where

<passthrough level> ::= NONE | CONDITIONAL | FORCE | DEFAULT

Setting session-level passthrough mode with the SET PASSTHROUGH command

The SET PASSTHROUGH statement takes effect immediately, starting from the next SQL statement, until it is reverted by a similar statement or SET TRANSACTION PASSTHROUGH.

The syntax for the SET PASSTHROUGH command is:

SET PASSTHROUGH {READ <passthrough level>
[WRITE <passthrough level>]} | {WRITE <passthrough level> |
[READ <passthrough level>]} | <passthrough level>

where

<passthrough level> ::= NONE | CONDITIONAL | FORCE | DEFAULT

Setting connection level settings for ODBC or JDBC

ODBC

The SQL passthrough mode can be set with the following connection attributes:

▪SQL_ATTR_PASSTHROUGH_READ; values: “NONE”, "CONDITIONAL", "FORCE"

▪SQL_ATTR_PASSTHROUGH_WRITE; values: “NONE", "CONDITIONAL", "FORCE"

JDBC

The SQL passthrough mode can be set with the following connection properties

▪property name: "solid_passthrough_read"; values: "NONE", "CONDITIONAL", "FORCE"

▪property name: "solid_passthrough_write"; values: "NONE", "CONDITIONAL", "FORCE"

Changing default settings with the ADMIN COMMAND

The SqlPassthroughRead and SqlPassthroughWrite parameters are of type R/W; the parameter value can be changed with the ADMIN COMMAND and the change takes effect immediately.

ADMIN COMMAND ’parameter Passthrough.<parameter name>=<value>’;

where

<parameter name> is SqlPassthroughRead or SqlPassthroughWrite value is NONE, CONDITIONAL, or FORCE.