An alternative to using a SQL statement as the source for a DataWindow's data is to use a stored procedure. Stored procedures are used for interaction with the database in order to improve performance (because the SQL statements are precompiled by the database) or to ensure data hiding (because the users don't need to be granted explicit select or update rights to the underlying tables).

For example, the following stored procedure could be used to retrieve all of the rows from the employee table:

CREATE PROCEDURE employee_select ( )
BEGIN
    select * from employee ;
END

If a stored procedure is being used for the data source to ensure data hiding, then stored procedures will also be used to update the database. For example, the following might be used in our example for the delete method:

CREATE PROCEDURE employee_delete (
   a_emp_id integer
  )
BEGIN
   delete from employee where emp_id = a_emp_id ;
END ;

The insert method is a bit more complex (see Listing 1).

In this particular example, the update method is the most complex one because both the initial and new values are passed for each column to allow for an update collision check (see Listing 2).

This particular example also assumes that the end user is not allowed to update the emp_id, though the procedure could certainly support it. The examples provided are quite simplistic. Your own should include appropriate error checking and data validation.

The Rows->Stored Procedure Update option is then selected in the DataWindow painter to bring up the Stored Procedure Update dialog. This dialog allows the developer to specify which stored procedure to use for each of the update, insert, and delete statements and to map DataWindow columns to the stored procedure arguments.

Note in particular the use of the Use Original option to pass both the original and the modified values of the column to the update stored procedure.

Stored Procedures
A stored procedure can also be called directly from within the PowerBuilder application. The preferred way to do this is through a RPCFUNC call on a user object of type transaction. Take, for example, the following stored function, which takes the employee ID as an argument and returns the employee's full name using our previously defined full name function:

create function getEmpFullname (
   a_emp_id integer)
returns char(61)
begin
   declare name char(61);
   select fullname(
    emp_fname,
    emp_lname ) into name
   from employee where
   emp_id = a_emp_id;
   return(name)
end ;

We can now create a standard class user object of type transaction, basically creating a class that inherits from the PowerBuilder transaction class.

We can then declare a local external function on this user object and select from the stored procedures in our database.

In this particular example, our call gets defined as:

protected function string getempfullname ( &
   long a_emp_id ) &
   RPCFUNC ALIAS FOR &
   "~"asademo~".~"getempfullname~""

Note that the function has been defined to have protected scope. This isn't actually necessary in this case, but done for demonstration purposes as we're going to create a wrapper object function on this user object for the call. Some of the stored procedures you end up calling may require some preparation before the call or special handling afterward, which can be included in the object function. For example, string arguments passed by reference to stored procedures must be padded with spaces to the length of the largest possible return to prevent runtime errors. Wrapping such calls in an object function that prepares the argument and forcing your developers to use that object function helps reduce the chance for error using the stored procedure.

The object level function in this case is pretty simple:

public function string of_getempfullname (integer emp_id);
Return getempfullname ( emp_id )
end function

We would like to avoid having to create this object and connect it to the database each time we want to use its methods. What we can do instead is modify the application object so that this custom class is used to create the SQLCA transaction object.

Now the object function we've just defined can be called as a method of SQLCA:

long ll_empid
string ls_empname

IF IsNumber ( sle_1.text ) THEN
   ll_empid = Long ( sle_1.text )
   ls_empname = SQLCA.of_getempfullname ( ll_empid )
MessageBox ( "Employee Full Name", ls_empname )
END IF

Embedded SQL
Embedded SQL is a less preferred means of interacting with the database because:

1. The SQL is static, whereas the SQL from a DataWindow can be dynamically generated to match the target database syntax.
2. It results in data access being spread throughout objects in the application, which makes later maintenance more difficult.

The second issue can be mitigated somewhat by centralizing data access through object-level functions in a custom transaction object, as illustrated above for stored procedures. However, instead of calling a stored procedure from within the object level function on the transaction object, we could simply use an embedded SQL statement:

string ls_fullname
SELECT getempfullname ( :emp_id )
   INTO :ls_fullname
   FROM dummy
   USING this ;
Return ls_fullname

Conclusion
Despite its small footprint and minimal administrative demands, ASA is a very powerful database and makes an ideal candidate database for applications intended for individuals or workgroups.

This article is based on PowerBuilder 9 Internet and Distributed Application Development by various authors (ISBN 0672324997), published by Sams Publishing.