Declaring DBMS stored procedures as remote procedure calls


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


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


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" }




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.


In a FUNCTION declaration, the datatype of the value returned by the function.


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.


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.


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.


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.


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


This code calls the function in a script:

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

This code calls the SPM8 stored procedure:

int myresult
myresult = SQLCA.spm8(myresult)
IF SQLCA.sqlcode <> 0 THEN
      messagebox("Error", SQLCA.sqlerrtext)