Connection Information

To perform the requested action, WordPress needs to access your web server. Please enter your FTP credentials to proceed. If you do not remember your credentials, you should contact your web host.

Connection Type

Using Transaction objects to call stored procedures – PB Docs 70 – PowerBuilder Library

Using Transaction objects to call stored procedures – PB Docs 70

Using Transaction objects to call stored procedures

SQLCA is a built-in global variable of type transaction that
is used in all PowerBuilder applications. In your application, you
can define a specialized version of SQLCA that performs certain
processing or calculations on your data.

If your database supports stored procedures, you may already
have defined remote stored procedures to perform these operations.
You can use the remote procedure call (RPC) technique to define
a customized version of the Transaction object that calls these
database stored procedures in your application.

note.gif Result sets You cannot
use the RPC technique to access
result sets returned by stored procedures. If the stored procedure
returns one or more result sets, PowerBuilder ignores the values
and returns the output parameters and return value. If your stored
procedure returns a result set, you can use the embedded SQL DECLARE
Procedure statement to call it.

For information about the DECLARE Procedure
statement, see the chapter on SQL statements in the PowerScript
Reference

.

Overview of the RPC procedure

To call database stored procedures from within your PowerBuilder
application, you can use the remote procedure call technique and
PowerScript dot notation (object
.function
)
to define a customized version of the Transaction object that calls
the stored procedures.

proc.gif To call database stored procedures in your application:

  1. From the Objects tab in the New dialog
    box, define a standard class user object inherited from the built-in
    Transaction object.

  2. In the Script view in the User Object painter,
    use the RPCFUNC keyword to declare the stored procedure as an external
    function or subroutine for the user object.

  3. Save the user object.

  4. In the Application painter, specify the user object
    you defined as the default global variable type for SQLCA.

  5. Code your PowerBuilder application to use the
    user object.

For instructions on using the User Object
and Application painters and the Script view in PowerBuilder, see
the PowerBuilder User’s Guide
.

Understanding the example

u_trans_database user object The following sections give step-by-step instructions for
using a Transaction object to call stored procedures in your application.
The example shows how to define and use a standard class user object
named u_trans_database.

The u_trans_database user object is a descendant
of (inherited from) the built-in Transaction object SQLCA. A descendant is
an object that inherits functionality (properties, variables, functions,
and event scripts) from an ancestor object. A descendent object
is also called a subclass.

GIVE_RAISE stored procedure The u_trans_database user object calls an ORACLE7
database stored procedure named GIVE_RAISE that calculates
a five percent raise on the current salary. Here is the ORACLE7
syntax to create the GIVE_RAISE stored procedure:

note.gif SQL terminator character The syntax shown here for creating an ORACLE7 stored procedure
assumes that the SQL statement terminator character is ` (backquote).

Step 1: define the standardclass user object

proc.gif To define the standard class user object:

  1. Start PowerBuilder.

  2. Connect to a database that supports stored procedures.

    The rest of this procedure assumes you are connected to an
    ORACLE7 database that contains remote stored procedures on the database
    server.

    For instructions on connecting to an ORACLE7
    database in PowerBuilder and using ORACLE7 stored procedures, see Connecting
    to Your Database

    .

  3. Click the New button in the PowerBar.

    or

    Select File>New from the menu bar.

    The New dialog box displays.

    datrnnew.gif

  4. On the Object tab, select the Standard Class icon
    and click OK to define a new standard class user object.

    The Select Standard Class Type dialog box displays:

    datrnsct.gif

  5. Select transaction
    as the
    built-in system type that you want your user object to inherit from,
    and click OK.

    The User Object painter workspace displays so you can assign
    properties (instance variables) and functions to your user object:

    datrnuop.gif

Step 2: declare the storedprocedure 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 PowerBuilder
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

.

proc.gif To 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 listbox and Local
    External Functions from the second listbox.

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

    PowerBuilder 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.

    datrnsp.gif

  3. Select the names of one or more stored procedures
    that you want to declare as functions for the user object, and click
    OK.

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

    For example, here is the declaration that displays on one
    line when you select sp_addlanguage:

    datrnfc.gif

  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 (for details about the syntax, see the PowerScript
    Reference

    ):

    Here is the edited RPC function declaration for sp_addlanguage:

Step 3: save the user object

proc.gif To save the user object:

  1. In the User Object painter, click the Save
    button.

    or

    Select File>Save from the menu bar.

    The Save User Object dialog box displays.

  2. Specify the name of the user object, comments
    that describe its purpose, and the library in which to save the
    user object:

    Datrn080.gif

  3. Click OK to save the user object.

    PowerBuilder saves the user object with the name you specified
    in the selected library.

Step 4: specify the defaultglobal variable type for SQLCA

In the Application painter, you must specify the user object
you defined as the default global variable type for SQLCA. When
you execute your application, this tells PowerBuilder to use your
standard class user object instead of the built-in system Transaction
object.

note.gif Using your own Transaction object instead of SQLCA This procedure assumes that your application uses the default
Transaction object SQLCA. But you can also declare and create an
instance of your own Transaction object and then write code that
calls the user object as a property of your Transaction object.

For instructions, see the chapter on working
with user objects in the PowerBuilder User’s
Guide

.

proc.gif To specify the default global variable type for
SQLCA:

  1. Click the Open button in the PowerBar.

    or

    Select File>Open from the menu bar.

    The Open dialog box displays.

  2. Select Applications from the Object Type dropdown
    listbox. Choose the application where you want to use your new user
    object and click OK.

    The Application painter workspace displays.

  3. Select the General tab in the Properties view.
    Click the Additional Properties button.

    The Additional Properties dialog box displays.

  4. Click the Variable Types tab to display the Variable
    Types property page.

  5. In the SQLCA box, specify the name of the standard
    class user object you defined in the Steps 1 through 3:

    datrnapp.gif

  6. Click OK or Apply.

    When you execute your application, PowerBuilder will use the
    specified standard class user object instead of the built-in system
    object type it inherits from.

Step 5: code your applicationto use the user object

What you’ve done so far In the previous steps, you defined the GIVE_RAISE
remote stored procedure as an external function for the u_trans_database
standard class user object. You then specified u_trans_database
as the default global variable type for SQLCA. These steps give
your PowerBuilder application access to the properties and functions encapsulated
in the user object.

What you do now You now need to write code that uses the user object to perform
the necessary processing.

In your application script, you can use PowerScript dot notation
to call the stored procedure functions you defined for the user
object, just as you do when using SQLCA for all other PowerBuilder
objects. The dot notation syntax is:

For example, you can call the GIVE_RAISE stored procedure
with code similar to the following:

proc.gif To code your application to use the user object:

  1. Open the object or control for which you
    want to write a script.

  2. Select the event for which you want to write the
    script.

    For instructions on using the Script view,
    see the PowerBuilder User’s Guide
    .

  3. Write code that uses the user object to do the
    necessary processing for your application.

    Here is a simple code example that connects to an ORACLE7
    database, calls the GIVE_RAISE stored procedure to calculate
    the raise, displays a message box with the new salary, and disconnects
    from the database:

  4. Compile the script to save your changes.

note.gif Using error checking An actual script would include error checking after the CONNECT
statement, DISCONNECT statement, and call to the GIVE_RAISE
procedure.

For details, see “Error handling after
a SQL statement”
.


Document get from Powerbuilder help
Thank you for watching.
Was this article helpful?
Subscribe
Notify of
guest
0 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
0
Would love your thoughts, please comment.x
()
x