THROUGH (SQL)

A command that sends a statement to a remote data source to explore execution options.

Synopsis

THROUGH [ FOREIGN ] SERVER server-name remote-statement

Description

A THROUGH statement sends a SQL query to an external data source and returns the desired result set. The query is parsed, prepared, and executed only by the external data source. Any errors encountered in the execution of the query against the external data source are returned as if you had queried a foreign table.

THROUGH statements can only be used on foreign servers whose foreign data wrapper supports this feature, such as JDBC.

Users must have the %Gateway_Object:USE permission on the foreign server to execute a THROUGH statement. InterSystems IRIS does not perform any other permission checking. However, the query may be subject to restrictions in the external data source, depending on the permissions implied by the foreign server definition, including the user and credentials required to connect to the external data source. Be sure to give this user appropriate permissions inside the external data source.

A THROUGH statement can be used in Dynamic SQL or with the use of a database driver. It cannot be used through Embedded SQL.

The system does not create a query plan for THROUGH statements. Passing it to the EXPLAIN command or any analogous utility results in an error.

Arguments

server-name

The name of an existing foreign server that uses the a foreign data wrapper that supports passthrough queries, such as JDBC. This server should be configured to use a connection that is appropriately privileged on the external data source to access the desired data.

remote-statement

A SELECT statement, passed with no delimiter quotes. This statement is sent to the external data source verbatim. As a result, the syntax of this statement should follow the remote server’s syntax rules and may not be a valid statement in InterSystems IRIS SQL. No literal substitution or whitespace normalization is performed.

The statement should not include any parameters, such as “?”, as the syntax for such constructions in the external data source may differ substantially.

Example

The following example sends the SELECT query to the external data source, which prepares and executes the statement.

THROUGH SERVER MySource.External 
   SELECT Customers.CustomerID,Customers.FirstName,Order.OrderID,Orders.OrderDate 
   FROM Customers JOIN Orders ON Customers.CustomerID = Order.CustomerID
   WHERE YEAR(Orders.OrderDate) = 2023