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:
But .... using DB2:
... the result is always "0" (zero) |
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. |
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). Thank you Breck.
It works !!
... 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:
(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.
(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
|
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
Please show us ALL the code for the "crash": the CREATE statements on both databases.
Here is the code:
On DB1:
On DB2:
CREATE SERVER "RemoteServer" CLASS 'SAODBC' USING 'Driver=/opt/sqlanywhere12/lib64/libdbodbc12.so; dbn=DB1; uid=ergon; pwd=xxxxxx;';
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...