|
Remote functions AT remote servers are not documented in SA 12.0.1. But beside that, they exist, and we can create a proxy function on the current database for a remote function (both SA 12.0.1 databases). One thing is unknown - what are supported return types? Integer is supported, but char or varchar aren't. Anyone have list of supported return types in SA 12.0.1 remote functions? UPDATE 2016-03-07 : Please check this PDF document with error descriptions, create and query statements, step by step what you can do to replicate this issue. |
|
Thanks for clarifying that. The PDF supplied 2016-03-07 shows the pattern clearly. Assuming versions 16 and up are behaving the correct way for you, I am going to just assume the question is strictly about the version 12 behaviour. Basically, V12 is only behaving the way it is because you are using a few off-specification attributes when you attempt to do this that way. The feature you are looking for wasn't intended and never fully implemented until version 16 (and is documented there in the CREATE FUNCTION "at clause", and Remote Procedure Calls (RPCs)) In the V12 docs the RPC reference only describes datatype support for Procedure Parameters only:
whereas the V16 version of the same RPC article added " . . . and RETURN values" just for functions. The reason that it seems to 'kind-of works' for 12.0.1 is a combination of these factors:
and all sorts of undocumented behaviour can ensue ... as you have found out already. So no. This is neither an intended feature nor a desireable behaviour of V12 and should not be relied upon or expected to behave well. That was really the long way around but hopefully that explains the muddy waters better now. OK, so I should correct my older statement "[...], it's obviously a lack in the v12's CREATE FUNCTION statement's page that the "| AT location-string" syntax and the expanations for that clause are missing." (see a comment on your other answer):)
(09 Mar '16, 00:38)
Volker Barth
Comment Text Removed
Comment Text Removed
Nick, so you were wrong before saying "Remote access has been a feature of SQL Anywhere since ~6.0.1/.2 so it is the same feature set no matter what version.". And now if I understand correctly, in SA12 I can use only integer as return and input parameters in remote functions.
(09 Mar '16, 15:45)
BlueMark
Replies hidden
Basically Remote Access including Proxy Tables and RPCs have been part of the design all along. Of course, since that time Directory Access and some additional support has been added. But, for all intents and purposes RFCs are properly new to version 16. Before that they were not intended to be a feature and so you are restricted to whatever behaviour you incidentally get when they are treated as an RPC ... in an unofficial and undocumented side-effect way. I would not expect any specific functionality to be robust unless it is behaviour directly related to actually supportted RPC features for stored procedures. There should be no restriction parameters except those that documented as applying to RPCs when they are actually stored procedures.
(10 Mar '16, 08:44)
Nick Elson S...
Well, then it would be fine if it were documented as a "new" or "changed" behaviour there - I can only find changes to "CREATE FUNCTION statement [Web service]", related to certificates, and the enhancements to Remote Data Access only list HANA support. - Or have I missed an according entry?
(10 Mar '16, 08:59)
Volker Barth
|
|
It sounds like you are referencing our RPC to remote server (Remote Access) features. The return types supported for those are documented here HTH You are referencing SA 17, and I was asking about SA 12. Also you are referencing remote procedures, and I was asking about remote functions. Yes, I am asking about Remote data access.
(03 Mar '16, 12:53)
BlueMark
Remote access has been a feature of SQL Anywhere since ~6.0.1/.2 so it is the same feature set no matter what version. It is true there is no DCX entry under the CREATE FUNCTON article that documents that syntax but 'remote functions' are mentioned in the RPC article already provided (any version). "You can also fetch result sets from remote procedures, . . . . As well, . . . remote functions can be used to fetch return values from remote procedures and functions. " I'll be adding a note to the DCX articles affected shortly.
(03 Mar '16, 13:33)
Nick Elson S...
Replies hidden
Just to add: In contrast to some other DBMSes, with SQL Anywhere stored functions and stored procedures are rather similar objects - except for the obvious aspects like different call syntax, different parameter types (only "in" vs. "in", inout", "out") and different ways to return information they do share many common properties. That includes that both have variants to call native and remote functions/procedures. That being said, it's obviously a lack in the v12's CREATE FUNCTION statement's page that the "| AT location-string" syntax and the expanations for that clause are missing.
(04 Mar '16, 02:42)
Volker Barth
Nick, this is not true, Remote access do not works the same in each SA version: I tried to use CHAR(100) as return type for remote function and SA returned error that this return type is unsupported. But I can find CHAR on the list you provided.
(04 Mar '16, 08:29)
BlueMark
If the remote server is a SQL Anywhere one and if the object on the remote server is a stored procedure, then that stored procedure can only return integers (and there can be other restrictions in that case). So it should be true for integers. If on the other hand, your remote server is another SAP database production or a non-SAP product then the question can be even more subtle. But maybe you should show exactly what you are trying to do here? That way we can differentiate between a remote 'function' call (using create function ... at remote-location syntax) or a remote procedure call accessing a remote object that is a function at the remote server or a combination of both. A simplified example should suffice; pls include the create object definition as well as the create remote-object definition.
(04 Mar '16, 10:54)
Nick Elson S...
Replies hidden
I updated my question, there is url to PDF document with exact code I use with all create statements and errors.
(07 Mar '16, 09:01)
BlueMark
|
According to the list cited by Nick (or the according one for v12 they should be - although that list only names "char", not "varchar". - What error message do you get when trying with VARCHAR?
With both char and varchar I get error that used return type is unsupported in remote connections.
Please show us the exact code for the CREATE FUNCTION and tell us what software is used for the remote server.
I updated my question, there is url to PDF document with exact code I use.