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 for AS/400

[-] 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:

Any reference to Adaptive Server version 12.0 in this chapter applies to SQL Server 11.1 and later, unless otherwise specified.

This chapter covers the following topics:

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 Adaptive Server stored procedure

The following example shows an Adaptive Server stored procedure that can execute an AS/400 stored procedure. The Adaptive Server stored procedure must specify an existing AS/400 stored procedure and provide any arguments that the AS/400 stored procedure requires. 

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

where:

To creat an AS/400 Stored procedure refer to Chapter 14, "Using AS/400 Stored Procedures"

Executing a Adaptive Server Stored Procedure

To execute the preceding Adaptive Server stored procedure example using ISQL, connect to 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 an Adaptive 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

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 Adaptive Server RPC design to ensure minimal network traffic.

Validation

The user ID and password you use to sign on to 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 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 ex ample of an Adaptive Server stored procedure that initiates a bulk copy transfer statement. The access service executes the transfer statement against the AS/400, and the AS/400 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 Adaptive Server database and enter the following at the ISQL prompts: 

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

When 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 Adaptive Server.

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

Triggers

You can set up any 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 Adaptive Server, automatically calls an AS/400 stored procedure named pchowdy. In turn, pchowdy updates the authors table on the AS/400, 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 AS/400 update fails, the access service rolls back the 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.

Chapter 7 Using Sybase mode commands [Table of Contents] Chapter 9 Understanding the Transfer process