Coolest New Feature In 12.0.1? [fixed]

The code in Coolest New Feature In 12.0.1? was almost correct, except for the (re)discovery of a new entry to add to the list in How many times have you done this?:

17. SQL Anywhere: Coding a SET or other executable statement ahead of a DECLARE CURSOR.

Here is the tested code for the sp_forward_to_remote_server system procedure which lets you send a native query to a remote database and receive a result set in return without having to define proxy tables and write the query in SQL Anywhere syntax; first, two tables defined on "the other database" called ddd2:

CREATE TABLE t1 ( 
   pkey   INTEGER NOT NULL PRIMARY KEY,
   data1  INTEGER NOT NULL,
   data2  INTEGER NOT NULL );

INSERT t1 VALUES ( 1, 2, 2 );
INSERT t1 VALUES ( 2, 2, 2 );
COMMIT;

CREATE TABLE t2 ( 
   pkey   INTEGER NOT NULL PRIMARY KEY,
   data3  INTEGER NOT NULL );

INSERT t2 VALUES ( 1, 98 );
INSERT t2 VALUES ( 2, 99 );
COMMIT;

SELECT * FROM t1 ORDER BY t1.pkey;

SELECT * FROM t2 ORDER BY t2.pkey;

pkey,data1,data2
1,2,2
2,2,2

pkey,data3
1,98
2,99

Here's a call to sp_forward_to_remote_server from the ddd1 database:

CREATE SERVER ddd2_server CLASS 'SAODBC' 
   USING 'DRIVER=SQL Anywhere 11;ENG=ddd;DBN=ddd2';

BEGIN

DECLARE @pkey     INTEGER;
DECLARE @data3    INTEGER;
DECLARE @SQLSTATE VARCHAR ( 5 );

DECLARE @select_statement LONG VARCHAR;

DECLARE c_fetch INSENSITIVE CURSOR FOR
   CALL sp_forward_to_remote_server ( 'ddd2_server', @select_statement );

SET @select_statement = '
SELECT t1.pkey,
       t2.data3
  FROM t1 INNER JOIN t2 ON t1.pkey = t2.pkey
 ORDER BY t1.pkey';

OPEN c_fetch WITH HOLD;

FETCH c_fetch INTO
      @pkey,
      @data3;

SET @SQLSTATE = SQLSTATE;

WHILE @SQLSTATE = '00000' LOOP

   MESSAGE STRING ( 't1.pkey = ', @pkey, ', t2.data3 = ', @data3 ) TO CLIENT;

   FETCH c_fetch INTO
         @pkey,
         @data3;

   SET @SQLSTATE = SQLSTATE;

END LOOP;

CLOSE c_fetch;

END;

t1.pkey = 1, t2.data3 = 98
t1.pkey = 2, t2.data3 = 99

To quote the earlier article:

What's cool about that? Well, the SQL code on database ddd1 runs "SELECT FROM t1 INNER JOIN t2" and receives a result set without having to define any proxy tables, and without having to worry about whether "FROM proxy_t1 INNER JOIN proxy_t2" will run quickly (over on the other database) or slowly (because all the rows are brought across and the join is done on ddd1).

No more FORWARD TO 'CREATE VIEW ...' just so your SELECT FROM proxy_view will run quickly, now you can just run the SELECT as-is and not worry about the middleware.