Friday, February 18, 2011

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?:

  1. 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.

1 comment:

Anonymous said...

Breck,

Since you are looking at new features in 12.0.1, you might want to consider revisiting your p_drop_other_connections() procedure and see if you can simplify the SQL code by removing the ALTER SERVER and using the new "variables in USING clause" feature.

Karim