Sybase Technical Library - Product Manuals Home
[Search Forms] [Previous Section with Hits] [Next Section with Hits] [Clear Search] Expand Search
Chapter 7 Using Sybase Mode commands [Table of Contents] Chapter 9 Understanding the Transfer process

Access Service User's Guide DirectConnect

[-] Chapter 8 Issuing Remote Procedure Calls

Chapter 8

Issuing Remote Procedure Calls

This chapter describes how any CT-Library application can generate Remote Procedure Call (RPC) events. The RPC feature allows a stored procedure to initiate an event in a remote database--that is, to make a remote procedure call.

A client application can invoke an RPC to:

This chapter covers the following topics:

When Sybase Adaptive Server issues an RPC event to the access service, it tries to establish a connection using the same service name as the identifier for the Sybase Adaptive Server in the sql.ini file. To set up Sybase Adaptive Server and DirectConnect connections for RPCs, see Chapter 8, "Issuing RPCs."

Setting up Adaptive Server and DirectConnect connections

To set up Adaptive Server for remote procedure calls (RPCs) against the DirectConnect service, perform the following steps:

To set up connectivity for a remote server, see the appropriate Adaptive Server documentation for the platform on which your Adaptive Server resides.

Creating a SQL Server Stored Procedure

The following example shows a SQL Server stored procedure that can execute a remote stored procedure (RSP). The SQL Server stored procedure must specify an existing RSP and provide any arguments that the RSP requires. 

create procedure newcust @custname varchar(nn), @custno varchar (nn) as 
begin
     execute servername...addcust
     @addname=custname, @addno=@custnoend

where:

Configuring Sybase Adaptive Server for remote access

To configure Sybase Adaptive Server for remote access, log in to Sybase Adaptive Server as an administrator and check the current sp_configure setting using the following command: 

sp_configure 'remote access'

Defining the DirectConnect service as a remote server

To define the DirectConnect service as a remote server, enter each DirectConnect service name in the Sybase Adaptive Server SYSSERVERS table using the following command: 

sp_addserver service_name

where service_name is the name of the DirectConnect service you want to set up as a remote server. The name is case-sensitive and must match the name you used to define the connectivity between Sybase Adaptive Server and the DirectConnect service.

To verify that the DirectConnect service is successfully defined as a remote server, enter: 

select service_name from sysservers

If data rows return, you successfully defined the DirectConnect service as a remote server.

When Sybase Adaptive Server issues an RPC to a DirectConnect access service, it attempts to connect using a service name identical to the Sybase Adaptive Server identifier in the interfaces file.

To support Sybase Adaptive Server RPC events, perform either one of the following procedures:

Executing a SQL Server Stored Procedure

To execute the preceding Sybase Adaptive Server stored procedure example using ISQL, connect to Sybase Adaptive Server and enter the following at the prompts: 

C:>ISQL -Ssybone -Uuser -Ppasswrd
 1> execute newcust xxxx,yyyy
 2> go

where:

For backward compatibility, the following syntax is acceptable: use procedure newcust xxxx, yyyy

Executing a language statement as an RPC

To execute a SQL language statement to the access service through a SQL Server RPC, use the following syntax: 

C:> isql -Ssqlserver -Uuser -Ppassword
 1> execute directconnect...dcon "select * from
     user.authors"
 2> go

where:

The access service RPC event handler is sensitive to several key RPC names. In this case, the RPC keyword dcon is a special name that DirectConnect recognizes. As a result, the access service translates the first parameter into a dynamic SQL statement, submits it to the target database, and then returns the result set to the client application.

For backward compatibility with the MDI Database Gateway, pcsql is recognized in place of the dcon keyword. For backward compatibility with Net-Gateway, syrt is recognized in place of the dcon keyword.

To execute a language statement as an RPC:

Rules for using language statements as RPCs

Sybase Adaptive Server has a strict model for processing language statements as RPC events: It connects to the remote server (DirectConnect) and submits the RPC. After results process, it disconnects.

These quick connects and disconnects are a basic part of the Sybase Adaptive Server RPC design to provide minimal network traffic.

Validation

The user ID and password you use to sign on to Sybase Adaptive Server must be a valid DirectConnect target database user ID and password.

Transformation mode and syntax

All SQL transformation rules apply:

Commitment control

Consider the following:

Creating a transfer SQL Request

The client application can create a stored procedure within Sybase Adaptive Server that invokes the access service transfer function. In this case, the access service receives the transfer command as an RPC event with the arguments shown in the following table:

Arguments for transfer command

Argument

Definition

Argument 1

The secondary connection information ({to | from} "server userid pw")

Argument 2

Either the bulk copy target command (for example, with replace into) or the destination-template sourceselectstatement

Argument 3

Either the bulk copy sourceselectstatement or the destination-template sourceselectstatement

Following is an example of a Sybase Adaptive Server stored procedure that initiates a bulk copy transfer statement. The access service executes the transfer statement against DB2, and DB2 receives it as an RPC. 

create procedure replauth as
 begin
     execute servername...transfer        "to 'servername2 userid password';",
         "with replace into authors;",
         "select * from authors;"
 end

where:

Executing a transfer SQL Request

As shown in the following example, to execute the replauth stored procedure using ISQL, connect to the Sybase Adaptive Server database and enter the following at the ISQL prompts: 

C:>ISQL -Ssybone -Uuser -Ppasswrd1> execute replauth
 2> go

When Sybase Adaptive Server executes replauth, it passes an RPC to the access service. The access service then returns any result rows or messages to the client application, not to Sybase Adaptive Server.

For more information about transfers, see Chapter 9, "Understanding the Transfer process"

Triggers

You can set up any Sybase Adaptive server stored procedure as a trigger that executes automatically when the triggering condition is met.

The following example shows a trigger, which, when the phone column is updated in the authors table in Sybase Adaptive server, automatically calls an RSP named pchowdy. In turn, pchowdy updates the authors table on DB2, using au_id to specify the row to update. 

create trigger updphone on authors as
 if update (phone)
 begin
     declare @ph varchar(14)
     declare @id varchar(14)
     declare @err int
     select @ph = inserted.phone from inserted
     select @id = inserted.au_id from inserted
         execute servername...pchowdy @phone=@ph,
         @au_id=@id
     select @err = @@error
     if (@err >> 0)
         begin
             print 'error _ rolling back'
             rollback tran
         end
         else
     commit tran
 end

Once it is created, the updphone trigger starts up whenever phone is updated, as shown: 

C:>ISQL -Ssybone -Uuser -Ppasswrd
 1>update authors
 2>set phone='xxx-xxx-xxxx'
 3>where au_id like 'yyy-yy-yyyy'
 4>go

The access service does not support a two-phase commit. The update can succeed and be committed on either platform, independently of the success or failure of the update on the other platform.

In the previous example, if the DB2 update fails, the access service rolls back the Sybase Adaptive Server transaction and shows the following message: 

@ERR >> 0

For more information about RPCs, see the Sybase Open Server Server-Library/C Reference Manual or the Sybase Open Client Client-Library/C Reference Manual.

use [Table of Contents] Chapter 9 Understanding the Transfer process