Based on current own work with proxy tables and Adam's series of questions, I'd like to check my understanding of the way remote data access does work.
Are the following four assumptions correct?
Each statement run against a proxy
table must be a valid SQL statement
for the local SQL Anywhere database
server; each referenced column,
procedure or function must exist in
the current database.
The server capabilities are
responsible what part of a SQL
statement can be sent to the remote
server or has to be processed
locally; i.e. they have influence
whether full or partial passthrough
mode can be used.
Except the mentioned splitting into
partial statements, there is no
"rewriting" of statements to make
them compatible with the remote
server, i.e. if a remote server does
support a particular SQL feature but
does use a different syntax (or a
different name for a semantically
equal feature) then the remode data
access layer won't rewrite the
statement to be sent to the remote
server with that syntax. (As a consequence, one won't be able to use such a feature.)
In contrast, when using FORWARD
TO, the statement has only to
be compatible and valid for the
remote server.
Question:
Are these assumptions correct?
Some samples used:
I created a readonly MS SQL 2000 remote server (named MS_SVR) and tested with ASA 8.0.3 and SA 11.0.1 and the MS SQL system table sysobjects (as proxy table named MS_sysobjects).
Running the following statement in FORWARD TO mode succeeds both with ASA 8 and SA 11 as the syntax is valid on MS SQL:
forward to MS_SVR { select name, unicode(name) from sysobjects }
Running the same statement
select name, unicode(name) from MS_SysObjects
against the ASA 8.0.3 proxy table fails, as unicode is not a known ASA 8 function. However, running against SA 11.0.1 succeeds as that does support the unicode function. Note, however, that there's no capability for such a function, and therefore the statement is not processed in full passthrough mode:
The query is being processed in NO PASSTHRU mode
The capability check of Unknown builtin function failed
The Remote Statement for vt_1 is
select MS_SysObjects.name from MS_SysObjects
An example for the "rewriting" mentioned in Assumption 3 would be the syntax differences between "SELECT TOP" in SA and "SELECT ... LIMIT" in MySQL or function "length" in ASA 8.0.3 and "len" in MS-SQL (SA 11.0.1 seems to support "len", too, but will process it locally with the default MS SQL capabilities).
asked
10 Feb '11, 12:44
Volker Barth
39.7k●358●546●815
accept rate:
34%
The 1. assumption seems to be stated in a similar wy in the docs, cf. http://dcx.sybase.com/index.html#1200en/dbusage/ug-accessrd-sectb-5161698.html.