Running a Remote Procedure

[Former Member]

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?

[VolkerBarth]

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.

[Former Member]

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.

[Former Member]

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

[VolkerBarth]

Please show us the code of both.

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

[Former Member]

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

[VolkerBarth]

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

[Former Member]

Hi Volker,

Bingo!

Thanks for your help, works great

[VolkerBarth]

Glad to hear that.

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