From the Archives: Limitations of Proxy Tables
In this post, originally written by Glenn Paulley and posted to sybase.com in March of 2012, Glenn talks about some of the limitations related to the SQL Anywhere remote data access functionality.
Proxy tables, sometimes referred to Remote Data Access or OMNI, are a convenient way to query or modify tables in different databases all from the same connection. SQL Anywhere’s proxy tables are an implementation of a loosely-coupled multidatabase system. The underlying databases do not have to be SQL Anywhere databases – any data source that supports ODBC will do, so the underlying base table for the proxy can be an Oracle table, a Microsoft SQL Server table, even an Excel spreadsheet. Once the proxy table’s schema is defined in the database’s catalog, the table can be queried just like any other table as if it was defined as a local table in that database.
That’s the overall idea, anyway; but there are some caveats that get introduced as part of the implementation, and I’d like to speak to one of these in particular. My post is prompted by a question from a longstanding SQL Anywhere customer, Frank Vestjens, who in early February in the NNTP newsgroup sybase.public.sqlanywhere.general queried about the following SQL batch:
begin declare dd date; declare tt time; declare resultaat numeric; // set dd = '2012-06-07'; set tt = '15:45:00.000'; // message dd + tt type info to console; // select first Id into resultaat from p_mmptankplanning where arrivalDate + IsNull(arrivaltime,'00:00:00') <= dd+tt order by arrivaldate+arrivalTime,departuredate+departureTime; end
The batch works fine with a local table
p_mmptankplanning but gives an error if the table is a proxy table; the error is “Cannot convert 2012-06-0715:45:00.000 to a timestamp”.
In SQL Anywhere, multidatabase requests are decomposed into SQL statements that are shipped over an ODBC connection to the underlying data source. In many cases, the complete SQL statement can be shipped to the underlying server, something we call “full passthrough mode” as no post-processing is required on the originating server – the server ships the query to the underlying DBMS, and that database system returns the result set which is percolated back to the client. Since the originating server is a SQL Anywhere server, the SQL dialect of the original statement must be understood by SQL Anywhere. If the underlying DBMS isn’t SQL Anywhere, then the server’s Remote Data Access support may make some minor syntactic changes to the statement, or try to compensate for missing functionality in the underlying server.
The SQL statement sent to the underlying DBMS, whether or not the statement can be processed in full passthrough mode or in partial passthrough mode, is a string. Moreover, SQL Anywhere can ship
MERGE statements to the underlying DBMS – among others – but lacks the ability to ship batches or procedure definitions.
So in the query above, the problem is that the query refers to the date/time variables
tt, and uses the operator
+ to combine them into a
TIMESTAMP. Since SQL Anywhere lacks the ability to ship an SQL batch, what gets shipped to the underlying DBMS server is the SQL statement
select first Id into resultaat from p_mmptankplanning where arrivalDate + IsNull(arrivaltime,'00:00:00') <= '2012-06-07' + '15:45:00.000' order by arrivaldate+arrivalTime,departuredate+departureTime;
and now the problem is more evident: in SQL Anywhere, the ‘+’ operator is overloaded to support both operations on date/time types, and on strings; with strings, ‘+’ is string concatentation. When the statement above gets sent to the underlying SQL Anywhere server, it concatenates the two date/time strings to form the string ‘2012-06-0715:45:00.000’ – note no intervening blank – and this leads directly to the conversion error. Robust support for SQL batches would solve the problem, but we have no plans to introduce such support at this time. A workaround is to compose the desired
TIMESTAMP outside the query, so that when converted to a string the underlying query will give the desired semantics. However, even in that case care must be taken to make sure that the
DATEFORMAT option settings are compatibile across the servers involved.
My thanks to my colleague Karim Khamis for his explanations of Remote Data Access internals.