SET TEMPORARY OPTION "return_date_time_as_string" = 'Off' Error when using remote table in a view

[former_SQLA_member1694992]

Dear Community

we run a Sybase SQL Anywhere 17 server with multiple databases. To be able to create a comprehensive view we added the 1st database as remote server on the 2nd. Then we added in the 2nd server the proxy tables which point to views in the 1st server. the data gets shown correctly when we just use those proxy tables by themselves.

When using the proxy tables in a view and joining them with local tables, we get the following error:

[SAP][ODBC Driver][SQL Anywhere]Laufzeit-SQL-Fehler - FEHLER Assertierung fehlgeschlagen: 106104 (17.0.10.6285)[] Field unexpected during compilation SQLCODE: -300 SQLSTATE: HY000 SQL Statement: SET TEMPORARY OPTION "return_date_time_as_string" = 'Off'

Please support and provide detailed instruction on how the get this marriage done 🙂

Best Regards Raoul

[chris_keating]

The assertion is unlikely directly related to the return_date_time_as_string option. There is a bug addressed in build 6290 that may explain this issue. If you can reproduce this easily, I would consider trying a newer build to see of the issue resolves. (I think the patch you are using is about 2.5 years old at this point but there is only one fix that I found for this assertion). But there are many other fixes that that have been made including security related updates/fixes.

[fvestjens]

I would suggest to create webservices especially when data is needed for a web application.

[former_SQLA_member1694992]

Thanks for the suggestion. How would I go about that? Do you have an example configuration? Would it be possible to combine there data from two or more databases running on the same server?

[former_SQLA_member1694992]

Found in %SQLANYSAMP17%\\SQLAnywhere\\HTTP\\ Any more recommended documentation/links?

[fvestjens]

Start from there.

I'll give a small example of what we do.

In the database service add a new line '-xs http(port=8082)'

Then create a webservice. In the example below the resultset is a json

CREATE SERVICE jsonCurrencyList
TYPE 'JSON' 
AUTHORIZATION OFF USER "USR" 
URL ELEMENTS AS 
SELECT *
FROM Currency

Then you can get the result in a browser using

http://ip-number db machine:8082/jsonCurrencyList

This will show the data in the browser

[
    {
        "Id": 157,
        "Code": "EUR",
        "Name": "European Currency",
        "Factor": "1.000000",
        "Per": 1.000000
    },
    {
        "Id": 264,
        "Code": "OMR",
        "Name": "Omani Riyal",
        "Factor": "190.000000",
        "Per": 100.000000
    },......
]

[VolkerBarth]

Note, support requests in this forum are answered voluntarily and for free, so if you do NEED support, you better check your support contract with SAP...

---

I have never faced that error but 4 suggestions come to my mind - I can't tell whether they might help or not:

• Are the column definitions of the remote view in the 1st database and the proxy view in the 2nd database identical? (I'm asking because I had issues with slightly different varchar sizes a while ago....)

• Have you set that option "return_date_time_as_string" to "On" - is it possibly set differently for the current user (running the query) and the possibly different user used for remote access (see sysexternlogin)?

• Does this behaviour also appear when you select the proxy view into a local temporary table and use that temp table within your local join? (IMHO it's often way faster to cache remote data in local temp tables and use those for further joins with local data...)

• You can "debug" remote data access with the help of the "cis_option", see the docs. This does (among others) show how the query is sent to the remote server.

[former_SQLA_member1694992]

Thank you for the multiple suggestions. It got me in a better direction.

Upgrade to newest version was done, but the error still persists.

I think it is an assertion error, due to the proxy tables being complex views by themselves and them also referring other views. The encapsulation is deep and I might need to simplify it.

Some queries already could be simplified, but others take ages to complete, even tough I added indexes on the proxy tables (are they even working as they point to views on the remote?)

Howto create the temp tables? Would we need this to run for every client request? The goal is to use the data in a web application, so that it can be filtered from one request.

[VolkerBarth]

When a desired view (say V) in my local database should contain data from local and remote data and those are quite complex (and performance is not sufficient), I often use a stored procedure instead of a view, and the procedure (say SP) would return the same data as the view but cache the remote data locally.

You can still use SP pretty much like a view because SQL Anywhere allows procedures that return a result set to be used in the FROM clause of queries, too. One main difference would probably be that any filters you would do on the view's columns might be better used as parameters to the procedure

So procedure P would do the following.

• declare a local temporary table (say LT) with the same schema (or a subset) as the proxy view (say PV) and select (possibly filtered) remote data in that local temporary table
• use the contents of the temporary table within your joins to your other local data and return the result set based on that
• As the local temp table is automatically created and dropped when the procedure is called, there's almost none overhead when it is declared as NOT TRANSACTIONAL.

Here's a short pseudo-code sample:

create procedure SPT_MyMixedResult(...parameters)
result (... description of result set columns...)
begin
   declare local temporary table LT like PV
   not transactional; -- possibly also adding a PK in the LIKE clause

   insert LT select * from PV
   -- where --- possibly applying parameters to filter remote data
   -- possibly add indexes on the local temp table if needed

   -- Now join with your local data... providing the result set
   -- as described in the RESULT clause
   select * from LT inner join SomeOtherLocalsTables
   order by ...
end;

[justin_willey]

We've found using selectable stored procedures (with proxy procedures on the "central" database mapping to the real procedures on the other databases) by far the most efficient way of combining queries from multiple databases. It means you control where the filtering of records happens, so that you scan be sure you are using the right indexes etc.

Look at the AT clause in CREATE PROCEDURE