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

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):

object.function

1	object.function

You can call database procedures in SAP, 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  rtndatatype functionname ( { { REF } datatype1 arg1,...,
   { REF } datatypen argn } ) RPCFUNC { ALIAS FOR "spname" }
SUBROUTINE  functionname ( { { REF } datatype1 arg1 , ..., 
   { REF } datatypen argn } ) RPCFUNC { ALIAS FOR "spname" }

FUNCTION rtndatatype functionname ( { { REF } datatype1 arg1,...,

{ REF } datatypen argn } ) RPCFUNC { ALIAS FOR "spname" }

SUBROUTINE functionname ( { { REF } datatype1 arg1 , ...,

{ REF } datatypen argn } ) RPCFUNC { ALIAS FOR "spname" }

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 the section called “Using
Transaction objects to call stored procedures” in 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
double rv
rv = SQLCA.give_raise(val)

double val = 20000

double rv

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
myresult = SQLCA.spm8(myresult)
IF SQLCA.sqlcode &lt;&gt; 0 THEN
      messagebox("Error", SQLCA.sqlerrtext)
END IF

int myresult

myresult = SQLCA.spm8(myresult)

IF SQLCA.sqlcode <> 0 THEN

messagebox("Error", SQLCA.sqlerrtext)

END IF

Document get from Powerbuilder help

Thank you for watching.

Powerbuilder Documentation 2021

August 21, 2023

Was this article helpful?

Connection Information

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

Declaring DBMS stored procedures as remote procedure
calls

My Website Links