Operating Condition
Syntax: SET PASSTHROUGH ON/OFF
Default: OFF
When PASSTHROUGH is set on, SELECT statements are sent directly to a foreign data source and are not processed by R:BASE. In addition to SELECT, the underlying retrieval for the PROJECT command is a query that can also be passed directly to the server and the results put in the "returned rows" on the fly. If the original table had 30 columns and you only need one, the PASSTHROUGH ON only asked the server for one column, wheres when PASSTHROUGH is OFF, the entire row is built by the SATTACHED table and then the column asked for is sent to the calling program. Another command that can take advantage of this includes INSERT when coming from a SELECT on a server table.
PASSTHROUGH can also allow special syntax, such as non-SQL syntax, or syntax that is not supported by R:BASE. With PASSTHROUGH set to ON, you can use functions that are only defined on the server system in a SELECT command, because those functions get "passed through" to the server for processing.
About the PASSTHROUGH Setting
If PASSTHROUGH is set to ON and foreign database is connected with SCONNECT, the following will happen with a SELECT command:
1.If the table is a regular R:BASE table, the command works normally
2.If the table is a foreign data source table already attached with SATTACH, the query is sent directly to the ODBC server and the returned values are shown.
3.If the table is not known to R:BASE at all, as either an R:BASE table or a foreign data source table, the query is sent directly to the ODBC server. If the server cannot process the query, an error is returned. If the server can process the query, the SELECT will work.
Example:
CONNECT RRBYW20
SET PASSTHROUGH ON
SCONNECT NorthWind
SELECT * FROM dbo.shippers
OrderID Customer EmployeeID OrderDate RequiredDate
---------- -------- ---------- ------------------- -------------------
10249 TOMSP 6 07/05/1996 00:00:00 08/16/1996 00:00:00
10251 VICTE 3 07/08/1996 00:00:00 08/05/1996 00:00:00
10258 ERNSH 1 07/17/1996 00:00:00 08/14/1996 00:00:00
10260 OTTIK 4 07/19/1996 00:00:00 08/16/1996 00:00:00
10265 BLONP 2 07/25/1996 00:00:00 08/22/1996 00:00:00
10267 FRANK 4 07/29/1996 00:00:00 08/26/1996 00:00:00
10269 WHITC 5 07/31/1996 00:00:00 08/14/1996 00:00:00
10270 WARTH 1 08/01/1996 00:00:00 08/29/1996 00:00:00
10274 VINET 6 08/06/1996 00:00:00 09/03/1996 00:00:00
10275 MAGAA 1 08/07/1996 00:00:00 09/04/1996 00:00:00
10280 BERGS 2 08/14/1996 00:00:00 09/11/1996 00:00:00
10281 ROMEY 4 08/14/1996 00:00:00 08/28/1996 00:00:00
10282 ROMEY 4 08/15/1996 00:00:00 09/12/1996 00:00:00
10284 LEHMS 4 08/19/1996 00:00:00 09/16/1996 00:00:00
The data from the dbo.shippers table in the Northwind database is available, but the table has not been attached.
A SELECT... INTO .. syntax can also be used to place values into variables.
SELECT CustomerID,EmployeeID INTO vCustID INDI iv1, vEmpID INDI iv2 FROM dbo.Orders WHERE OrderID = 10263
R>SHOW VAR
Variable = Value Type
------------------ ------------------------------ -------
vCustID = ERNSH WIDETEXT
vEmpID = 9 INTEGER
Notes:
•When PASSTHROUGH is ON, only the SELECT command can work with a server table that is not SATTACHed. If you want to do INSERT, UPDATE, or DELETE to a server table that is not SATTACHed, then you must use the SSQL command.
•With PASSTHROUGH set to ON, you can actually use functions that are only defined on the server system in a SELECT command, because those functions get "passed through" to the server for processing. For example, assume you have an SQL Server table called dbo.Testing with an integer column named cInt , the following can be executed:
SELECT cInt, power(1.1,cInt) AS PW FROM dbo.Testing
This will use the SQL Server POWER function to raise 1.1 to the "cint" power and the results will come back to R:BASE. If PASSTHROUGH is OFF, this command generates an error because the "power" function is not defined in the R:BASE program.
