SAP SQL Anywhere

Question

Note: This was originally posted as an answer to that question but seems to be a question on its own.

I moved it via the "convert to question" button and hope the title will fit...

Hi. I've the same problem. In my DB1 I have created a function (named "fn-Date") with no parameters that returns a varchar(8) value. In my DB2 I have created a remote function as follow:

CREATE PROCEDURE "ergon"."Remote-fn-Date"() at 'RemoteServer;;Ergon;fn-Date';

But .... using DB2:

SELECT Remote-fn-Date()

... the result is always "0" (zero)

Answer 1

Thank you for reporting this issue. DECIMAL types are not supported as RETURN values for a remote function. That being said, the server certainly should not crash if you try to make a remote function call with a DECIMAL return type. The problem has now been fixed and a future SP will properly report that DECIMAL types are not supported as RETURN values for remote functions rather than having the server crash.

Answer 2

2

Try creating a function, not a procedure:

   CREATE FUNCTION "ergon"."Remote-fn-Date"()
   RETURNS VARCHAR ( 8 )
   at 'RemoteServer;;Ergon;fn-Date';

Procedures do have a "return value" that is an integer return code, which may explain the zero... but I'm just guessing at that part (almost nobody uses the procedure-return-value feature).

permanent link

answered 20 Jan '15, 14:02

Breck Carter
32.5k●541●726●1050
accept rate: 20%

edited 21 Jan '15, 09:34

Graeme Perrow
9.6k●3●79●124

Thank you Breck. It works !!
I've re-generated a test case using a simple Function that returns an integer.
... But using a function that return a decimal type ... I've experienced a server CRASH !!! (SA 12.0.1.3873)
From doc: ....

NUMERIC and DECIMAL data types are allowed for IN parameters, but not for OUT or INOUT parameters ...

... nothing about remote function return values ...

(21 Jan '15, 03:55) ncisternino

Replies hidden

1

Well, that is a documented restriction, cf. the docs on CREATE FUNCTION - to cite:

A proxy function can return any data type except DECIMAL, NUMERIC, LONG VARCHAR, LONG NVARCHAR, LONG BINARY, XML, or any spatial data type.

(I ain't saying that ignoring this restriction should lead to a server crash, of course...)

(21 Jan '15, 04:57) Volker Barth

Thank you.
Thus ... what's your suggestion to remotely "wrap" a function that returns a decimal type ?

(21 Jan '15, 05:10) ncisternino

I guess you will have to wrap the remote function on the remote database (say, by wrapping "fnFunc" with "fnFuncAsDouble") and then map the proxy function to that remote wrapper (and declare the proxy function with the according return datatype).

Besides that, you may raise another question here to post the server crash issue - I would expect that an unfitting "remote function return datatype" should raise an SQL error code, not a crash...

(21 Jan '15, 05:29) Volker Barth

By definition, functions CANNOT have OUT or INOUT parameters, only procedures can. Functions can only return values via the single RETURNS value. If you want to return values via the parameter list use a CREATE PROCEDURE.

(21 Jan '15, 07:29) Breck Carter

flat view

SAP SQL Anywhere

Remote function with DECIMAL return type crashes the server

Follow this question

Related questions