The forum will experience an outage sometime between February 10 at 7:00pm EST and February 12 at 11:59 EST for installation of security updates. The actual time and duration of the outage are unknown but attempts will be made to minimize the downtime. We apologize for any inconvenience.

I have two ASA 12 databases. DB1 and DB2. A remote server connection using ODBC has been set up between DB2 to DB1 with read only set as false.

In DB1 I have created a function with twelve parameters.

I have added a new remote procedure to the remote server in DB2. When I look at the parameters for the remote procedure in DB2 I see nothing.

Have I missed something in the creation of the function in DB1, or something in creating the remote procedure in DB2?

asked 19 Jan '15, 00:39

KGINOZZ's gravatar image

KGINOZZ
50226
accept rate: 0%

edited 19 Jan '15, 01:16


Please show us the code of both...

When I look at the parameters for the remote procedure in DB2 I see nothing.

Where/how do you "look" at these parameters?


The definition of both the local function in DB1 and the remote function in DB2 should be identical (though the name may be different), they need to have the same number (and usually the same types) of parameters.

The main difference is that the local function would have a compound statement whereas the remote (proxy) function will have an "AT clause" instead.

The same is true for stored procedures.

Note: There are some restrictions for the possibly data types of remote functions/procedures, as documented here.

permanent link

answered 19 Jan '15, 03:45

Volker%20Barth's gravatar image

Volker Barth
29.3k287438644
accept rate: 32%

edited 19 Jan '15, 03:46

Hi Volker,

When viewing the parameter list from Sybase Central on the remote function no parameters were showing.

I have since discovered that no parameters were added when creating the remote function from DB2. So I have added them via the alter procedure statement such as:

Alter procedure fnMyFunct(in @Parm1 varchar(5)....) at 'RemoteServer;;DBA;;fnMyFunct'

Now works.

(19 Jan '15, 20:47) KGINOZZ

Why doesn't the call/select of the remote procedure return any results.

My function returns a smallint either 0 or 1 to indicate whether the function was successful or not.

When I use: select fnMyFunct('Blah','Blah'...) as Result I always get 0 returned, even when it was supposed to return 1

(19 Jan '15, 22:56) KGINOZZ
Replies hidden

Please show us the code of both.

You seem to somewhat mix stored functions and stored procedures...

(20 Jan '15, 03:07) Volker Barth

Hi Volker,

Yes sorry am using remote functions.

Code: Here is the function in DB1

CREATE FUNCTION "DBA"."fnAddNewTherapistToTelemed"( in @TherapistCode varchar(5),in @Surname varchar(50),in @FirstName varchar(50),in @AddressLine1 varchar(50),in @AddressLine2 varchar(50),in @Suburb varchar(30),in @State varchar(3),in @Zip varchar(4),in @WorkPhoneNo varchar(30),in @MobilePhoneNo varchar(30),in @FaxNumber varchar(30),in @EmailAddress varchar(50) ) 
returns smallint
NOT DETERMINISTIC
begin
  /* Type the procedure statements here */
  ... code removed for simplicity
END

Here is the code for remote server and remote function in DB2

CREATE SERVER "TelemedParticipants" CLASS 'SAODBC' USING 'D12_64';
DROP PROCEDURE "DBA"."fnAddNewTherapistToTelemed";
CREATE PROCEDURE "DBA"."fnAddNewTherapistToTelemed"(in @TherapistCode varchar(5), in @Surname varchar(50),in @FirstName varchar(50),in @AddressLine1 varchar(50),in @AddressLine2 varchar(50),in @Suburb varchar(30),in @State varchar(3),in @Zip varchar(4),in @WorkPhoneNo varchar(30),in @MobilePhoneNo varchar(30),in @FaxNumber varchar(30),in @EmailAddress varchar(50))
at 'TelemedParticipants;;DBA;fnAddNewTherapistToTelemed';

Calling SQL from within a trigger in DB2

set nParticipantCreated = (select dba.fnAddNewTherapistToTelemed(n.TherapistCode,n.Surname,n.GivenName,n.AddressLine1,n.AddressLine2,n.Suburb,n.State,n.PostCode,n.PhoneNo,n.MobileNo,n.FaxNo,n.EmailAddress));

Regards

(21 Jan '15, 18:37) KGINOZZ
Replies hidden

Aside: A pair of "<pre>" and "</pre>" tags is handy to preserve the format of code blocks within comments...

As stated, I'd recommend to declare the proxy function as function and not as procedure, i.e. to turn the second block into

CREATE FUNCTION "DBA"."fnAddNewTherapistToTelemed"(in @TherapistCode varchar(5), in @Surname varchar(50),in @FirstName varchar(50),in @AddressLine1 varchar(50),in @AddressLine2 varchar(50),in @Suburb varchar(30),in @State varchar(3),in @Zip varchar(4),in @WorkPhoneNo varchar(30),in @MobilePhoneNo varchar(30),in @FaxNumber varchar(30),in @EmailAddress varchar(50))
returns smallint
NOT DETERMINISTIC
at 'TelemedParticipants;;DBA;fnAddNewTherapistToTelemed';

If it is a function at the remote, the mapping proxy object should be a function, too.


Note: You can set a variable to a function value without the need to use a query, i.e. the following should do:

set nParticipantCreated = dba.fnAddNewTherapistToTelemed(...);

(22 Jan '15, 03:30) Volker Barth

Hi Volker,

Bingo!

Thanks for your help, works great

(26 Jan '15, 22:19) KGINOZZ
Replies hidden

Glad to hear that.

Feel free to accept that as answered if your problem is really solved now:)

(27 Jan '15, 03:37) Volker Barth
showing 4 of 7 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:

×73
×46
×26
×12

question asked: 19 Jan '15, 00:39

question was seen: 567 times

last updated: 27 Jan '15, 03:37