Declaring DBMS stored procedures as remote procedure calls

Description

In PowerBuilder, you can use dot notation for calling non-result-set
stored procedures as remote procedure calls (RPCs):

<span>object</span>.<span>function</span>

1	<span>object</span>.<span>function</span>

You can call database procedures in Sybase, Oracle, Informix,
and other ODBC databases with stored procedures.

RPCs provide support for Oracle PL/SQL tables and parameters that are defined
as both input and output. You can call overloaded procedures.

Applies to

Transaction object

Syntax

FUNCTION  <span>rtndatatype</span> <span>functionname</span> ( { { REF } <span>datatype1</span> <span>arg1</span>,...,<br>    { REF } <span>datatypen</span> <span>argn</span> } ) RPCFUNC { ALIAS FOR "<span>spname</span>" }

1	FUNCTION <span>rtndatatype</span> <span>functionname</span> ( { { REF } <span>datatype1</span> <span>arg1</span>,...,<br> { REF } <span>datatypen</span> <span>argn</span> } ) RPCFUNC { ALIAS FOR "<span>spname</span>" }

SUBROUTINE  <span>functionname</span> ( { { REF } <span>datatype1</span> <span>arg1</span> , ..., <br>    { REF } <span>datatypen</span> <span>argn</span> } ) RPCFUNC { ALIAS FOR "<span>spname</span>" }

1	SUBROUTINE <span>functionname</span> ( { { REF } <span>datatype1</span> <span>arg1</span> , ..., <br> { REF } <span>datatypen</span> <span>argn</span> } ) RPCFUNC { ALIAS FOR "<span>spname</span>" }

Table 3-13: RPC declaration parameters
Argument	Description
FUNCTION or SUBROUTINE	A keyword specifying the type of call, which determines the way return values are handled. If there is a return value, declare it as a FUNCTION. If it returns nothing or returns VOID, specify SUBROUTINE.
rtndatatype	In a FUNCTION declaration, the datatype of the value returned by the function.
functionname	The name of the database procedure as you will call it in PowerBuilder. If the name in the DBMS is different, use ALIAS FOR to associate the DBMS name with the PowerBuilder name.
REF	Specifies that you are passing by reference the argument that follows REF. The stored procedure can store a value in arg that will be accessible to the rest of the PowerBuilder script. When you pass a string by reference, all memory management is done in PowerBuilder. The string variable must be long enough to hold the returned value. To ensure that this is true, first declare the string variable, and then use the Space function to fill the variable with blanks equal to the maximum number of characters that you expect the function to return.
datatype arg	The datatype and name of the arguments for the stored procedure. The list must match the definition of the stored procedure in the database. Each datatype arg pair can be preceded by REF.
RPCFUNC	A keyword indicating that this declaration is for a stored procedure in a DBMS, not an external function in a DLL. For information on declaring external functions, see “Declaring external functions “.
ALIAS FOR “spname” (optional)	Keywords followed by a string naming the procedure in the database. If the name in the database is not the name you want to use in your script or if the name in the database is not a legal PowerScript name, you must specify ALIAS FOR “spname” to establish the association between the PowerScript name and the database name.

Usage

If a function does not return a value (for example, it returns Void),
specify the declaration as a subroutine instead of a function.

RPC declarations are always associated with a transaction
object. You declare them as local external functions. The Declare
Local External Functions dialog box has a Procedures button (if
the connected database supports stored procedures), which gives
you access to a list of stored procedures in the database.

For more information, see Application
Techniques.

Examples

Example 1

This declaration of the GIVE_RAISE_PROC stored
procedure is declared in the User Object painter for a transaction
object (the declaration appears on one line):

FUNCTION double GIVE_RAISE(ref double SALARY) RPCFUNC ALIAS FOR "GIVE_RAISE_PROC"

1	FUNCTION double GIVE_RAISE(ref double SALARY) RPCFUNC ALIAS FOR "GIVE_RAISE_PROC"

This code calls the function in a script:

double val = 20000

1	double val = 20000

double rv

double rv

rv = SQLCA.give_raise(val)

1	rv = SQLCA.give_raise(val)

Example 2

This declaration for the stored procedure SPM8 does
not need an ALIAS FOR phrase, because the PowerBuilder
and DBMS names are the same:

FUNCTION integer SPM8(integer value) RPCFUNC

1	FUNCTION integer SPM8(integer value) RPCFUNC

This code calls the SPM8 stored procedure:

int myresult

1	int myresult

myresult = SQLCA.spm8(myresult)

1	myresult = SQLCA.spm8(myresult)

IF SQLCA.sqlcode &lt;&gt; 0 THEN

1	IF SQLCA.sqlcode <> 0 THEN

        messagebox("Error", SQLCA.sqlerrtext)

1	messagebox("Error", SQLCA.sqlerrtext)

END IF

END IF

Document get from Powerbuilder help

Thank you for watching.

Powerbuilder Documentation 12.5

August 9, 2023

Was this article helpful?

Connection Information

Declaring DBMS stored procedures as remote procedure calls – PB Docs 125