Technology and Community

Jim O'Neil

Subscribe to Jim O'Neil: eMailAlertsEmail Alerts
Get Jim O'Neil: homepageHomepage mobileMobile rssRSS facebookFacebook twitterTwitter linkedinLinkedIn

PowerBuilder: Article

IBM & Informix

Moving beyond standard SQL statements

Using Stored Procedures
Like many other database management systems, IBM Informix supports procedures and functions, collectively known as user-defined routines (UDRs), written in Stored Procedure Language (SPL) or in other languages such as C and Java.

UDRs that return result sets are usually defined in IBM Informix with the CREATE FUNCTION statement. Functions can also support multiple input parameters and one optional output parameter used as the return value of the function definition. PowerBuilder does allow input parameters in stored procedure calls; however, with IBM Informix specifically, it does not support the use of the optional output parameter. Because of that restriction, UDRs that return data to PowerBuilder should return that data in the form of a result set. In contrast, UDRs that use input parameters but don't return any data should be defined using the CREATE PROCEDURE statement.

While IBM Informix supports all of the data types shown in Table 2 in Part 1 (PBDJ, Vol. 12, issue 7) as parameters or return values from UDRs, PowerBuilder does not support the TEXT or BYTE data type in this context. You can, of course, use TEXT and BYTE data types within the routine body; you just can't return any of these data types to PowerBuilder from a UDR.

Within DataWindows
To specify a stored procedure as the source of a DataWindow, you can simply use the DataWindow wizard as you would to create a DataWindow based on a SQL statement. The wizard will present a list of procedures and allow you to examine the syntax and select one as the DataWindow source. PowerBuilder will build the result set description on the basis of the data types and order specified within the RETURNING clause of the CREATE FUNCTION statement. For example, the UDR in Listing 3 will return a list of pay-dates for orders placed by customers within the state whose abbreviation is passed as the single input parameter. Note especially the following aspects of this UDR's syntax:

  • The RETURNING clause defines the characteristics of the result set passed to PowerBuilder. Since the clause provides only data types and not named identifiers, PowerBuilder will create generic column names automatically in the format of compute_0001, computer_0002,.... Since these are not very descriptive, you should consider renaming the columns manually within the DataWindow painter or using the Manual Result Set option within the DataWindow wizard when initially creating the DataWindow object.
  • The WITH RESUME clause on the RETURN statement allows PowerBuilder to fetch from the cursor that is created when using the FOREACH statement. If you omit the WITH RESUME, you'll find only the first record is retrieved.
  • IBM Informix uses the semi-colon as a statement terminator. If you are defining UDRs within the ISQL pane of the PowerBuilder Database painter, you will need to change the SQL Terminator Character used by PowerBuilder to something other than the default of semi-colon. Do this via the Database Preferences dialog accessible via the Design->Options... menu item.

You can also use UDRs within DataWindow object definitions to specify a procedure that will be executed to update, insert, or delete rows from the database as a result of modifications to the DataWindow control or DataStore. By default when the PowerScript update function on a DataWindow or DataStore is executed, PowerBuilder will generate a SQL INSERT statement for each row added, a SQL UPDATE statement for each modified row, and a SQL DELETE statement for each deleted row. Any or all of these three actions can be replaced with a stored procedure invocation via the Stored Procedure Update dialog accessible from the Rows->Stored Procedure Update... option within the DataWindow painter menu. In Figure 2, for instance, we have specified that the updatedatepaid UDR should be used to handle updates generated from the DataWindow. It's presumed that this UDR would accept an order number as the input and update the paid_date column of the orders table.

When a UDR has no RETURNING clause, consider using the remote procedure call (RPC) FUNC method to execute the procedure. An RPC FUNC is a local external function declared on a descendant of the transaction standard class user object. For example, you might create a new object called n_tr to extend (inherit from) the PowerBuilder transaction standard class user object. You can then select the UDR from the Paste Special->SQL->Remote Stored Procedure(s)... menu in the Declare Local External Functions pane of the User Object painter to generate the appropriate RPC FUNC syntax (as shown in the comments of Listing 4).

Once the UDR is associated with a user object function, you can call that user object function on any variable of type n_tr. For convenience you can even indicate that the default transaction object, SQLCA, is of type n_tr rather than the default type of transaction. You make this association via the Variable Types dialog displayed after pressing the Additional Properties button on the application object's property sheet. After you've done all this, the invocation of the UDR is reduced to the single line of executable code shown below:

//subroutine setdate(long order_num, date date_paid) &
//RPCFUNC ALIAS FOR "informix.updatedatepaid"
// then the following code would invoke the UDR
// updatedatepaid and pass in an order number contained
// in a single-line edit control on the current window
SQLCA..setdate(Long(sle_ordernum.text), today())

Remember, since PowerBuilder does not support output parameters from IBM Informix user-defined functions, all parameters to RPC calls are input arguments and passed by value.

Within Embedded SQL
A final option for using stored procedures is via embedded SQL. In general, first consider using a DataWindow or DataStore if returning results, or an RPC FUNC declaration to execute stored procedures within PowerBuilder. Embedded SQL is the least efficient way to interact with stored procedures and requires more coding on the part of the developer. There are, however, circumstances where embedded SQL may be required or desired, for instance:

  • To interact with BYTE and TEXT data within PowerScript
  • When using the various four formats of dynamic SQL, especially when the characteristics of the procedure to be executed are not known until runtime
  • To issue database-specific statements that cannot be interpreted by PowerBuilder or perhaps violate PowerScript syntax

To complement the example presented above, Listing 4 shows how to invoke the same listOrdersByState UDR from within embedded SQL. This example represents a window object function that accepts a state abbreviation as an argument (as_stateAbbr) and uses that value to display all orders paid for within the last 30 days.

More Stories By Jim O'Neil

Jim is a Technology Evangelist for Microsoft who covers the Northeast District, namely, New England and upstate New York. He is focused on engaging with the development community in the area through user groups, code camps, BarCamps, Microsoft-sponsored events, etc., and just in general serve as ambassador for Microsoft. Since 2009, Jim has been focusing on software development scenarios using cloud computing and Windows Azure. You can follow Jim on Twitter at @jimoneil

Comments (0)

Share your thoughts on this story.

Add your comment
You must be signed in to add a comment. Sign-in | Register

In accordance with our Comment Policy, we encourage comments that are on topic, relevant and to-the-point. We will remove comments that include profanity, personal attacks, racial slurs, threats of violence, or other inappropriate material that violates our Terms and Conditions, and will block users who make repeated violations. We ask all readers to expect diversity of opinion and to treat one another with dignity and respect.