Remote function with DECIMAL return type crashes the server

[Former Member]

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)

[VolkerBarth]

In addition to Breck's comment:

Here's another FAQ on proxy functions (here for MS SQL) that seems to give a clue that remote stored functions should be mapped to proxy functions and not to proxy procedures:

How to pass parameters to a remote procedure from Sybase to MS SQL Server

[Breck_Carter]

Please show us ALL the code for the "crash": the CREATE statements on both databases.

[Former Member]

Here is the code:

On DB1:

CREATE FUNCTION "Ergon"."Func1"()
RETURNS DECIMAL(12,5)
NOT DETERMINISTIC
BEGIN
   RETURN 1.00;
END;


On DB2:

    CREATE SERVER "RemoteServer" CLASS 'SAODBC' USING 'Driver=/opt/sqlanywhere12/lib64/libdbodbc12.so; dbn=DB1; uid=ergon; pwd=xxxxxx;';
CREATE FUNCTION "ergon"."Remote_Func1"()
RETURNS DECIMAL(12,5)
AT 'RemoteServer;;Ergon;Func1';


SELECT Remote_Func1();

... CRASH ...

[VolkerBarth]

Offtopic: Where's the undo button? I just converted a comment from Breck into an answer, and now it's gone... - Sorry, Breck!

Correction: It's not gone, it under the original question, until some administrator will fix this...

[Former Member]

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.

[Breck_Carter]

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

[Former Member]

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

[VolkerBarth]

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

[Former Member]

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

[VolkerBarth]

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

[Breck_Carter]

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.