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.

asked 02 Mar '16, 13:49

BlueMark's gravatar image

BlueMark
246111624
accept rate: 50%

edited 07 Mar '16, 08:58

but char or varchar aren't.

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?

(04 Mar '16, 03:04) Volker Barth
Replies hidden

With both char and varchar I get error that used return type is unsupported in remote connections.

(04 Mar '16, 08:32) BlueMark

Please show us the exact code for the CREATE FUNCTION and tell us what software is used for the remote server.

(04 Mar '16, 13:47) Breck Carter
Comment Text Removed

I updated my question, there is url to PDF document with exact code I use.

(07 Mar '16, 09:00) BlueMark

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:

  • Data types for remote procedures:

    The following data types are allowed for remote procedure call parameters

whereas the V16 version of the same RPC article added " . . . and RETURN values" just for functions.
Also, the definition of the AT-clause for RFCs was not added until V16 either. (as noted)

The reason that it seems to 'kind-of works' for 12.0.1 is a combination of these factors:

  • In the parser and inside the engine UDFs and UDPs shares much code and can be sometimes used interchangeably
  • CIS/OMNI is treating your attempt at a RFC as a RPC and treating that as a remote Procedure
  • For procedures, the RETURN statement is a T/SQL extension and can only return an integer ... any other datatype should fail
  • But since your remote procedure is actually (& unexpectedly) a function, it can return other (albeit unsupported) data types
  • . . . as such CIS/OMNI is obviously getting confused over return verses result set counts && UDF function returns vs UDP procedure returns (which are different mechanisms actually)

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.

permanent link

answered 08 Mar '16, 14:57

Nick%20Elson%20SAP%20SQL%20Anywhere's gravatar image

Nick Elson S...
7.3k35107
accept rate: 31%

edited 09 Mar '16, 12:54

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...

But, for all intents and purposes RFCs are properly new to version 16.

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

permanent link

answered 02 Mar '16, 16:21

Nick%20Elson%20SAP%20SQL%20Anywhere's gravatar image

Nick Elson S...
7.3k35107
accept rate: 31%

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

you are referencing remote procedures, and I was asking about remote functions

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
showing 3 of 6 show all flat view
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×415
×48
×28
×27
×25

question asked: 02 Mar '16, 13:49

question was seen: 514 times

last updated: 10 Mar '16, 09:10