Step 2: declare the stored procedure as an external function

FUNCTION or SUBROUTINE declaration

You can declare a non-result-set database stored procedure as an external function or external subroutine in a PocketBuilder application. If the stored procedure has a return value, declare it as a function (using the FUNCTION keyword). If the stored procedure returns nothing or returns VOID, declare it as a subroutine (using the SUBROUTINE keyword).

RPCFUNC and ALIAS FOR keywords

You must use the RPCFUNC keyword in the function or subroutine declaration to indicate that this is a remote procedure call (RPC) for a database stored procedure rather than for an external function in a dynamic library. Optionally, you can use the ALIAS FOR "spname" expression to supply the name of the stored procedure as it appears in the database if this name differs from the one you want to use in your script.

For complete information about the syntax for declaring stored procedures as remote procedure calls, see the chapter on calling functions and events in the PowerScript Reference.

StepsTo declare stored procedures as external functions for the user object:

  1. In the Script view in the User Object painter, select [Declare] from the first list and Local External Functions from the second list.

  2. Place your cursor in the Declare Local External Functions view. From the pop-up menu or the Edit menu, select Paste Special>SQL>Remote Stored Procedures.

    PocketBuilder loads the stored procedures from your database and displays the Remote Stored Procedures dialog box. It lists the names of stored procedures in the current database.

    Figure 16-1: Remote Stored Procedures dialog box

    The Remote Stored Procedures dialog box displays a scrollable list of the stored procedures in the current database. In the example, a procedure called s p _ raise is highlighted. To the right of the list are three buttons, OK, Cancel, and Help.
  3. Select the names of one or more stored procedures that you want to declare as functions for the user object, and click OK.

    PocketBuilder retrieves the stored procedure declarations from the database and pastes each declaration into the view.

    Here is the declaration that displays (on one line) when you select sp_raise:

    function double sp_raise(double salary) RPCFUNC ALIAS FOR "~"dba~".~"sp_raise~""
    
  4. Edit the stored procedure declaration as needed for your application.

    Use either of the following syntax formats to declare the database remote procedure call (RPC) as an external function or external subroutine:

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

    For details about the syntax, see the PowerScript Reference or the online Help.