Using SQL Passthru

In this section: Rules for SQL Passthru SQL Passthru Processing

SQL Passthru Services can be used under certain conditions on a Hub or Full-Function Server.

A Hub or Full-Function Server can utilize SQL Passthru to operate in SQL Passthru mode by session or per request-the user is responsible for activating and deactivating SQL Passthru as needed. This requires the RDBMS engine to be set on a client, using either a stored procedure or the API.

In addition, a Hub or Full-Function Server using its dialect translation capability attempts to pass SQL requests to the target RDBMS using SQL Passthru.

The user must ensure that dialect-specific SQL is being passed to the source RDBMS when using SQL Passthru. A client application passing anything other than dialect-specific SQL receives an error message. SQL Passthru is shown in the solid-line circle in the figure below.

The solid-line circle represents the supported dialect-specific SQL requests of SQL Passthru (some of which are also supported by Automatic Passthru).

Dialect translation and DML generation are discussed in Dialect Translation and Data Manipulation Language (DML) Generation, respectively.

Rules for SQL Passthru

Because SQL Passthru sends SQL requests directly to the RDBMS, it only supports valid dialect-specific SQL. Valid dialect-specific SQL complies with the syntax required by the source RDBMS.

The user initiating the SQL request must ensure that the request:

• Uses the valid dialect-specific SQL of the RDBMS.
• Refers to fully-qualified table and column names of the RDBMS.

Note: A three-part name can be used if the target RDBMS allows three qualifiers, such as LOCATION.CREATOR.TABLE.

If the RDBMS receives SQL requests that are not compatible with its dialect or table names, it generates an error message and returns it to the server. The server then returns the error message to the application.

SQL Passthru Processing

This section provides an example of SQL requests targeting a single RDBMS using SQL Passthru.

1. The client application issues a request in the dialect-specific SQL of the targeted RDBMS.

2. The server invokes an SQL Passthru agent, which passes the request to the applicable data adapter.

3. On receiving the request, the data adapter attaches to the RDBMS, using standard database attachment calls. The data adapter then passes the request to the RDBMS.

4. The RDBMS processes the request.

5. The results are returned to the client application.

For details on the SQL features supported for a specific RDBMS, see the documentation for the RDBMS and SQL Reference.