The
SET PASSTHRU statement opens and closes a session for submitting a
data source's native SQL directly to that data source.
The statement is not under transaction control.
Invocation
This statement can be issued
interactively. It is an executable statement that can be dynamically
prepared.
Authorization
The privileges held by the
authorization ID of the statement must provide authorization to:
- Pass through to the data source
- Satisfy security measures at the data source
Syntax
>>-SET PASSTHRU--+-server-name-+-------------------------------><
'-RESET-------'
Description
- server-name
- Names the data source for which a pass-through session is to be
opened. server-name must identify a data
source that is described in the catalog.
- RESET
- Closes a pass-through session.
Notes
- The following restrictions apply to Microsoft SQL Server, Sybase, and Oracle
data sources:
- User-defined transactions cannot be used for Microsoft SQL Server and Sybase data sources
in pass-through mode, because Microsoft SQL
Server and Sybase restrict which SQL statements can be specified within
a user-defined transaction. Because SQL statements that are processed
in pass-through mode are not parsed by the database manager, it is
not possible to detect whether the user specified an SQL statement
that is permitted within a user-defined transaction.
- The COMPUTE clause is not supported on Microsoft SQL Server and Sybase data sources.
- DDL statements are not subject to transaction semantics on Microsoft SQL Server, Oracle
and Sybase data sources. The operation, when complete, is automatically
committed by Microsoft SQL
Server, Oracle or Sybase. If a rollback occurs, the DDL is not rolled
back.
Examples
- Example 1: Start a pass-through session to data source
BACKEND.
strcpy (PASS_THRU,"SET PASSTHRU BACKEND");
EXEC SQL EXECUTE IMMEDIATE :PASS_THRU;
- Example 2: Start a pass-through session with a PREPARE
statement.
strcpy (PASS_THRU,"SET PASSTHRU BACKEND");
EXEC SQL PREPARE STMT FROM :PASS_THRU;
EXEC SQL EXECUTE STMT;
- Example 3: End a pass-through session.
strcpy (PASS_THRU_RESET,"SET PASSTHRU RESET");
EXEC SQL EXECUTE IMMEDIATE :PASS_THRU_RESET;
- Example 4: Use the PREPARE and EXECUTE statements to end
a pass-through session.
strcpy (PASS_THRU_RESET,"SET PASSTHRU RESET");
EXEC SQL PREPARE STMT FROM :PASS_THRU_RESET;
EXEC SQL EXECUTE STMT;
- Example 5: Open a session to pass through to a data source,
create a clustered index for a table at this data source, and close
the pass-through session.
strcpy (PASS_THRU,"SET PASSTHRU BACKEND");
EXEC SQL EXECUTE IMMEDIATE :PASS_THRU;
EXEC SQL PREPARE STMT pass-through mode
FROM "CREATE UNIQUE
CLUSTERED INDEX TABLE_INDEX
ON USER2.TABLE table is not an
WITH IGNORE DUP KEY"; alias
EXEC SQL EXECUTE STMT;
strcpy (PASS_THRU_RESET,"SET PASSTHRU RESET");
EXEC SQL EXECUTE IMMEDIATE :PASS_THRU_RESET;