SAP SQL Anywhere

Question

Based on Breck's following blog article and the fact that I generally prefer FOR loops over the explicit DECLARE/OPEN CURSOR syntax, I've tried to use both together.

I.e. I tried to run something like

begin
   for forCrs as crs cursor for
   call sp_forward_to_remote_server 
      ('MS_SVR', 'select count(*) as nCnt from sysobjects')
   do
      message 'number of objects = ' || nCnt to client;
   end for; 
end;

This fails with SQCLCODE -143, "Column 'nCnt' not found".

Just to confirm my understanding (and Breck's expectation):

Am I right that FOR loops cannot be used with this new system procedure as the local server seems unable to infer the number and the name and data type of the result set's columns?

(And therefore the explicit DECLARE/OPEN CURSOR is needed to bind each of the result set's columns to the local variables?)

(Note: Independent of the answer to come, it's a very nice enhancement IMHO!)

Accepted Answer

You are correct that the sp_forward_to_remote_server procedure cannot be used with the for statements. The reason for this restriction is identical to the reason why the new procedure cannot be used in the from clause of a select statement. For both the for statement and using a procedure in the from clause of a select statement, the schema of the returning result set must be predefined and the engine uses the procedure result clause to predefine the result set schema of the procedure. Unfortunately, with a procedure like sp_forward_to_remote_server, the returning result set can have any schema. Also, the reason the procedure is defined with a dummy result set at all is to make sure API's like ODBC explicitly open a cursor when a call to the procedure is made in order to properly define the resulting result set once the schema for that result set is determined.

On a completely side note, I'm glad you like the new feature. I should point out that the procedure can also be used to retrieve multiple result sets. For example, executing the following for an SA remote server will return two result sets:

call sp_forward_to_remote_server( '...', 'select * from systable\nselect * from syscolumn' )

Answer 2

(Note, this is not as answer nor an explanation but another hint:)

The reason seems that for such procedures, the server seems to expect a result set with a single int column called dummy, as the following call shows:

sa_describe_query('select * from sp_forward_to_remote_server(
  ''MS_SVR'', ''select count(*) as nCnt from sysobjects'')')

This seems to be independent of the SQL statement sent to the remote server - and seems to be a consequence of the entries in the system catalog for procedure sp_forward_to_remote_server:

select proc_name, parm_id, parm_name, parm_type, sd.domain_name
from sysprocedure sp key join sysprocparm spp key join sysdomain sd
where proc_name = 'sp_forward_to_remote_server' and parm_type = 1

returns the same information.

As a consequence, the following (not very useful) usage of the dummy result column (instead of nCnt) will work in this particular case of a result set with exactly one integer column:

begin
   for forCrs as crs cursor for
   call sp_forward_to_remote_server 
      ('MS_SVR', 'select count(*) as nCnt from sysobjects')
   do
      message 'number of objects = ' || dummy to client;
   end for; 
end;

But in the general case FOR loops won't be able to handle the particular result set of remote queries.

SAP SQL Anywhere

Can FOR loops be used with sp_forward_to_remote_server?

Follow this question

Related questions