Coolest New Feature In 12.0.1?

Here's a candidate for the Coolest New Feature in the next version of SQL Anywhere: The sp_forward_to_remote_server system procedure. (No, 12.0.1 isn't out yet, but the Help is.)

Here's how I think sp_forward_to_remote_server works: Like the wonderful FORWARD TO statement, you can send a raw other-dialect SQL statement over to a different database (say, Oracle or Microsoft SQL Server) and have that statement executed on the other server as-is, without having to futz around with proxy tables or proxy procedures or remote procedure calls.

Unlike the FORWARD TO statement, however, you will be able to receive a result set back from the other server. And, again unlike FORWARD TO, you won't have to wrap your code with EXECUTE IMMEDIATE to get it to run inside a stored procedure.

Previously, getting a result set back from FORWARD TO was an unattainable dream unless you were willing to run it manually in dbisql. Now, you can do it with sp_forward_to_remote_server, with just a teeny little wrinkle: you have to use a cursor, you can't call sp_forward_to_remote_server from the FROM clause.

And (I think... I'm just guessing) it has to be an old-school cursor... you know, DECLARE, OPEN, FETCH, check SQLSTATE, LOOP, CLOSE, all that crap... none of that fancy newfangled ease-of-use stuff with the FOR statement.

To repeat: I'm guessing about not using FOR... remember, 12.0.1 isn't available yet, just the Help.

But, let's keep the guesswork going; here are 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;

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

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

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

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.