Considerations for developing applications with SQL passthrough access rights

The passthrough feature is available only for validated users. The validation mechanism is based on the GRANT PASSTHROUGH statement.

The user access to a table may be restricted. If there is a statement that is restricted by the privilege restrictions in the frontend database, no corresponding statement is passed over to the backend database, even though there would be no privilege restrictions in the backend database.

▪All SQL statements can be passed to the backend, except for the SET [TRANSACTION] PASSTHROUGH itself, the SET [TRANSACTION] ISOLATION LEVEL statement, and the data aging related statement SET DELETE CAPTURE. The following restrictions apply also:

– In SELECT statements, only forward cursors are supported.

– UPDATE/DELETE ... WHERE CURRENT OF statements are not supported and may cause unexpected results if used.

– An error is returned if anything else than next row is fetched.

– Database-wide metadata queries are always executed in the frontend.

For example, even if the SQL passthrough mode is set to force, queries such as SELECT * FROM TABLES or JDBC function calls such as getTables return information about the tables in the solidDB^® database.

Statement-specific metadata queries (for example, ODBC SQLColAttr()) use the data received from the solidDB^® database when possible, and otherwise from the backend database.

▪If an SQL statement can be executed on both databases, the table definitions (for example, column types) are always taken from the solidDB^® database. If the solidDB^® and backend definitions are different, the data between the frontend and backend is converted when possible. The number of columns and column names must match.

▪If the solidDB^® server participates in distributed transactions using the Java Transaction API (JTA) interface, only read statements (SELECT) are passed through.

▪You can define that complex SQL statements are always passed through to the backend. Complex queries might be executed more effectively in the backend. The level of the complexity at which a statement is passed through is defined with the following parameters:

– Passthrough.ComplexNumTables – specifies the minimum number of tables in a complex statement. If a statement has less tables than specified with this parameter, the statement is not complex and it is not passed through to the backend.

– Passthrough.ComplexNumNonindexedConstr – specifies the minimum number of non-indexed WHERE clause constraints in a complex statement. If a statement has less non-indexed constraints of the following type, the statement is not complex and it is not passed through to the backend: the WHERE clause constraint does not resolve with index, the index does not exist, or the optimizer chooses different index for constraint.

– Passthrough.ComplexNumOrderedRows – specifies the minimum estimated number of rows which must be sorted in a complex statement. If a statement has less than the estimated number of sortable rows, the statement is not complex and it is not passed through to the backend.

The factory value for all three parameters is 0 (zero), which means that the given property is not used when estimating if the statement is complex.

▪SQL passthrough supports all standard SQL standard data types that are supported by the solidDB^® server. For more details, see ODBC data type support in SQL passthrough.

▪At the application-side driver, the column binding is based on standard ODBC binding methods.

– If the solidDB^® database mode is Unicode, SQL passthrough supports use of different code pages in the frontend and backend without any loss of information.

– If the solidDB^® database mode is partial Unicode, only Latin-1 (or ASCII, a subset of Latin-1) code pages are supported.

If your data uses encoding outside the Latin-1 character set, use the solidDB^® database in the Unicode mode.

▪If your solidDB^® database mode is Unicode (General.InternalCharEncoding=UTF8), you need to set the backend ODBC driver to expect data from solidDB^® in UTF-8 encoding. This is because in Unicode mode, character data types are stored in solidDB^® in UTF-8 encoding.

In Unicode mode environments, the backend ODBC driver handles the conversion between the UTF-8 encoding in the solidDB^® database and the encoding in the backend. At the application, any binding method available can be used because the conversion between the application and the solidDB^® frontend encoding is handled by the solidDB^® ODBC or JDBC driver, as described in “Using Unicode” in the solidDB^® Programmer Guide.

▪If the solidDB^® database mode is partial Unicode (General.InternalCharEncoding=Raw) and the application and solidDB^® environments use ASCII or Latin-1 encoding (Western languages), the backend ODBC driver is likely to handle the character translations correctly without setting any code page support in the ODBC driver explicitly.

In partial Unicode mode, character data types are stored in the solidDB^® database in raw (binary) format, without any conversion between the application encoding and the solidDB^® internal representation — the assumption is that applications are aware of this and handle the conversion as necessary.

Tip: By default, the installation of the IBM Data Server Driver for ODBC and CLI sets the driver to use the system locale of the installation node.

If your backend database uses encoding other than ASCII or Latin-1, you need to set the backend ODBC driver to expect data from the solidDB^® server in ASCII or Latin-1 encoding.

▪The other solidDB^® tools are not supported, they can only be used with the frontend.

▪Errors from the backend are preceded with SQLSTATE, showing the native backend error code and text.

▪The backend native error codes can be mapped against solidDB^® error codes using a mapping file. The mapping file is defined with the Passthrough.ErrorMapFileName parameter.