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)

asked 20 Jan '15, 13:13

ncisternino's gravatar image

ncisternino
16114
accept rate: 0%

edited 21 Jan '15, 09:03

Volker%20Barth's gravatar image

Volker Barth
31.5k318461676

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

(20 Jan '15, 15:14) Volker Barth

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

(21 Jan '15, 07:31) Breck Carter
Replies hidden

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 ...
(21 Jan '15, 08:49) ncisternino
1

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

alt text

(21 Jan '15, 09:06) Volker Barth

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.

permanent link

answered 20 Feb '15, 12:15

Karim%20Khamis's gravatar image

Karim Khamis
5.6k53870
accept rate: 40%

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%20Carter's gravatar image

Breck Carter
27.0k444614889
accept rate: 21%

edited 21 Jan '15, 09:34

Graeme%20Perrow's gravatar image

Graeme Perrow
8.7k374112

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
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
×28
×27
×25

question asked: 20 Jan '15, 13:13

question was seen: 1,094 times

last updated: 20 Feb '15, 12:15