Remote procedures as proxy tables

You can add an optional clause to the create existing table statement to indicate the remote object is actually a stored (or other) procedure instead of a table. Without this clause, the remote object is assumed to be a table or view:

create existing table t1
	(
				column_1     int,
				column_2     int
	)
	EXTERNAL PROCEDURE AT "SYBASE.mydb.dbo.p1"

If the remote object is type procedure, several processing differences occur:

• No indexes are created for objects of this type.

• You must provide a column list that matches the description of the remote procedure’s result set. No verification of the list’s accuracy is provided.

• You can use column names beginning with underscore (‘_’) to specify columns that are not part of the remote procedure’s result set. These columns are referred to as parameter columns. For example:

  create existing table t1
  (
  		a		int,
  		b		int,
  		c		int,
  		_p1		int null,
  		_p2		int null
  	)
  external procedure
  at “SYBASE.sybsystemprocs.dbo.myproc”
  
  select a, b, c from t1
  where _p1 = 10 and _p2 = 20
  

• In this example, the parameter columns _p1 and _p2 are not expected in the result set, but can be referenced in the query. Component Integration Services passes the search arguments to the remote procedure via parameters, using the names @p1 and @p2.

• If a parameter column is included in the select list, its value is equivalent to the values specified for it in the where clause, if it was passed to the remote procedure as a parameter. If the parameter column did not appear in the where clause, or was not able to be passed to the remote procedure as a parameter, but was included in the select list, its value would be NULL.

• A parameter column can be passed to the remote procedure as a parameter if it is what the Adaptive Server query processor considers to be a searchable argumen. It is generally a searchable argument if it is not included in any “or” predicates. For example, the following query would prevent the parameter columns from being used as parameters.

  select a, b, c from t1
  where _p1 = 10 OR _p2 = 20
  

• Rules exist for the definition of parameter columns in the create existing table statement:

  • Parameter columns must allow NULL.

  • Parameter columns cannot precede normal, result columns (they must appear at the end of the column list).

  Allowing the definition of remote procedures as local tables allows Component Integration Services to treat the result set of a remote procedure as a “virtual table,” which can be sorted, joined with other tables, or inserted into another table via insert/select syntax. However, virtual tables are considered read-only:

  • You cannot issue a delete, update, or insert command against a table of type procedure;

  • You cannot issue a create index, truncate table, or alter table command against virtual tables.

If an object of the type procedure has been defined within the server, a query is not issued to the remote server on which the object resides. Instead, Component Integration Services issues an RPC and treats the results from the RPC as a read-only table.

create existing table rtable
 (	col1		int,
 	col2		datetime,
 	col3		varchar(30)
 )
external procedure at “SYBASE...myproc “

select * from rtable

When this query is issued, Component Integration Services sends the RPC named myproc to server SYBASE. Row results are treated like the results from any other table; they can be sorted, joined with other tables, grouped, inserted into another table, and so forth.

RPC parameters should represent arguments that restrict the result set. If the RPC is issued without parameters, the entire result set of the object is returned. If the RPC is issued with parameters, each parameter further limits the result set. For example:

select * from rtable where col1 = 10

results in a single parameter, named @col1, that is sent along with the RPC. Its value is 10.

Component Integration Services attempts to pass as many of the search arguments as possible to the remote server, but depending on the SQL statement being executed, Component Integration Services might perform the result set calculation itself. Each parameter represents a search for an exact match, for example, the = operator.

The following rules define the parameters sent to the RPC. If an RPC is used as a Component Integration Services object, you should keep these rules in mind during development.

• Component Integration Services sends = operators in the where clause as parameters. For example, this query results in Component Integration Services sending two parameters:

   select * from rpc1 where a = 3 and b = 2
  

  Parameter a has a value of 3 and parameter b has a value of 2. The RPC is expected to return only result rows in which column a has a value of 3 and column b has a value of 2.

• Component Integration Services does not send any parameters for a where clause, or portion of a where clause, if there is not an exact search condition. For example:

  select * from rpc1 where a = 3 or b = 2
  

  Component Integration Services does not send parameters for a or b because of the or clause.

  Another example:

  select * from rpc1 where a = 2 and b < 3
  

  Component Integration Services sends parameters for a and b, and filters rows containing b with values smaller than 3.