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 Anywhere

[-] Chapter 8 Issuing RPC events

Chapter 8

Issuing RPC events

This chapter describes how any CT-Library API can generate remote procedure call (RPC) events.

This chapter covers the following topics:

General description

The RPC feature allows a stored procedure to initiate an event in a remote database. A client API can invoke an RPC to perform the following:

Creating and executing Adaptive Server Enterprise Stored Procedures

All references to Adaptive Server Enterprise version 12.0 include Sybase SQL Server version 11.1 and later, unless otherwise specified.

Use the following example to create an Adaptive Server Enterprise stored procedure that executes a remote stored procedure (RSP). The new 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:

To execute the stored procedure in the preceding example using ISQL, perform the following steps.

Executing a language statement as an RPC

To execute a SQL language statement to the DirectConnect for Informix Access Service through an RPC, use the following syntax: 

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

where:

The access service RPC event handler is sensitive to several RPC keywords, including the keyword dcon used in this example. RPC can have many parameters. Before the resulting string is executed, all parameters are concatenated. The access service translates the first parameter into a dynamic SQL statement, submits it to the target database, and 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.

The event sequence is as follows:

Rules for using language statements as RPCs

Adaptive Server Enterprise has a strict model for processing language statements as RPC events:

These quick connects and disconnects provide minimal network traffic.

Additional rules follow in this section.

Validation

The user ID and password that you use to log on to Adaptive Server Enterprise must be a valid DirectConnect for Anywhere target database user ID and password.

Transformation mode and syntax

All SQL transformation rules apply, including the following:

Commitment control

The following rules apply:

Creating a transfer RPC event

The client application can create a stored procedure within Adaptive Server Enterprise that invokes the DirectConnect Server library transfer function. The service library receives the transfer command as an RPC event with the following arguments:

transfer RPC event example

The following example shows a stored procedure that initiates a bulk copy transfer statement. The access service executes the transfer statement against Informix, which 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 RPC event

A client may log in to the Adaptive Server Enterprise on which this procedure is defined and invoke it as follows: 

execute replauth

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

Using Triggers

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

The following example shows a trigger that calls an RSP named pcrsp when the phone column is updated in the Adaptive Server Enterprise authors table. In turn, pcrsp updates the authors table on Informix, using au_id to specify the row to update. 

create trigger updatephone  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.  . .pcrsp @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, updatephone starts up whenever phone is updated, as shown in the following example: 

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

If the Informix update fails, the DirectConnect for Informix Access Service rolls back the Adaptive Server Enterprise transaction and shows the following message: 

@ERR >> 0

For more information about RPCs, see the following sources:

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