Proxy {Variables}

There's a new feature in SQL Anywhere 12.0.1 that didn't get documented in the Help:

{Variable} references in remote access CREATE SERVER and CREATE EXISTING TABLE statements which allow you to dynamically change the remote server and remote table names without altering or re-executing the CREATE statements

Whew! That's a mouthful, not exactly suitable as a name for this new feature, so let's call it this:

Proxy {Variables}

While it doesn't have its own Help topic yet, it did get mentioned: Go to the Create directory access servers topic in the Help, then scroll down to Example 2 where you will see these examples of proxy {variables} in the CREATE SERVER ... USING and CREATE TABLE ... AT clauses:

CREATE SERVER dir CLASS 'directory' USING 'root={@directory}\\users;subdirs={@subdirs}';

CREATE EXISTING TABLE dbo.userdir AT '{@server};;;{@curuser}';

Here's another example; this code runs on database ddd1 to display row counts on databases ddd2 and ddd3 via proxy tables:

BEGIN
DECLARE other_engine_name    VARCHAR ( 128 );
DECLARE other_database_name  VARCHAR ( 128 );
DECLARE other_table_name     VARCHAR ( 128 );
DECLARE row_count            BIGINT;

CREATE SERVER other_server CLASS 'SAODBC' 
   USING 'DRIVER=SQL Anywhere 11;ENG={other_engine_name};DBN={other_database_name}';

SET other_engine_name   = 'ddd2';
SET other_database_name = 'ddd2';
SET other_table_name    = 'SYSARTICLE';

CREATE EXISTING TABLE proxy_systable
   AT 'other_server..SYS.SYSTABLE';

CREATE EXISTING TABLE proxy_table 
   AT 'other_server..SYS.{other_table_name}';

FOR f_fetch1 AS c_fetch1 INSENSITIVE CURSOR FOR
SELECT proxy_systable.table_name AS proxy_table_name 
  FROM proxy_systable
 WHERE proxy_systable.table_type = 'VIEW'
   AND proxy_systable.creator    = 0      -- SYS
 ORDER BY proxy_systable.table_name
FOR READ ONLY
DO

   SET other_table_name = proxy_table_name;

   SELECT COUNT(*)
     INTO row_count
     FROM proxy_table;

   MESSAGE STRING ( other_engine_name, ' ', other_table_name, ' ', row_count ) TO CLIENT;

END FOR;

SET other_engine_name   = 'ddd3';
SET other_database_name = 'ddd3';

FOR f_fetch2 AS c_fetch2 INSENSITIVE CURSOR FOR
SELECT proxy_systable.table_name AS proxy_table_name 
  FROM proxy_systable
 WHERE proxy_systable.table_type = 'VIEW'
   AND proxy_systable.creator    = 0      -- SYS
 ORDER BY proxy_systable.table_name
FOR READ ONLY
DO

   SET other_table_name = proxy_table_name;

   SELECT COUNT(*)
     INTO row_count
     FROM proxy_table;

   MESSAGE STRING ( other_engine_name, ' ', other_table_name, ' ', row_count ) TO CLIENT;

END FOR;

END;

• The CREATE SERVER statement on lines 7 and 8 contains two dynamic {variable} references to the local DECLARE variables defined on lines 2 and 3. Those variables don't have values yet because CREATE SERVER statements aren't actually executed until necessary; e.g., when a CREATE EXISTING TABLE is executed.

• The CREATE EXISTING TABLE statement on lines 17 and 18 contains another dynamic {variable} reference to the local variable defined on line 4. At this point the local variable must have a value (see line 12) because CREATE EXISTING TABLE is an executable statement. The other two {variables} must also have values at this point (see lines 10 and 11).

• The FOR loop on lines 20 through 37 displays the row counts for all the system views on the remote database, in this case ddd2. The SET statement on line 29 dynamically changes the {variable} that controls which remote table is actually used to satisfy the FROM clause on line 33.

• The SET statements on lines 39 and 40 change the remote database to ddd3, which causes the identical FOR loop on lines 42 through 59 to display the row counts for a completely different set of system views.

Here's the output:

ddd2 ST_GEOMETRY_COLUMNS 0
ddd2 ST_SPATIAL_REFERENCE_SYSTEMS 5
ddd2 ST_UNITS_OF_MEASURE 5
...
ddd2 SYSVIEW 128
ddd2 SYSVIEWS 128
ddd2 SYSWEBSERVICE 0
ddd3 ST_GEOMETRY_COLUMNS 0
ddd3 ST_SPATIAL_REFERENCE_SYSTEMS 5
ddd3 ST_UNITS_OF_MEASURE 5
...
ddd3 SYSVIEW 128
ddd3 SYSVIEWS 128
ddd3 SYSWEBSERVICE 0

Tip: This stuff doesn't actually work in the GA build of 12.0.1, you have to get the first EBF 12.0.1.3298 here.