My presentation at TechWave 2006 is intended for the beginner or the moderately experienced PowerBuilder developer. I hope to share some of the useful tips and tricks I have picked up in my 12 years as a PowerBuilder developer.

I began using PowerBuilder in 1994 with release 3.0. The PFC did not exist; in fact, the concept of a "class library" was brand new. We had to use native PowerBuilder and build every object from scratch. Even so, PowerBuilder and, in particular, the DataWindow made RAD truly rapid. So let's start with a few simple, but truly useful, items.

Converting a Tabular DataWindow to a Grid DataWindow
Perhaps the most useful trick is the ability to convert a tabular DataWindow to a grid and vice versa. In version 9.x of PowerBuilder, you can right-click and edit source on an object. This is much less frightening than exporting to a script and then importing the object. To convert a tabular DataWindow to a grid DataWindow, simply right-click on the DataWindow object in the library painter and select Edit Source (see Figure 1).

When you select Edit Source, a script editor window opens (see Figure 2).

The "processing" variable controls the presentation style of grid versus tabular. Change this to 1 and the DataWindow is a grid; change it to 0, and the DataWindow is tabular.

Changing the Source of a DataWindow from a Select to a Stored Procedure
This, too, is incredibly useful. It involves the same basic technique. First, create or locate the appropriate stored procedure, then create a new DataWindow using this stored procedure as the source. In the library painter, right-click on this DataWindow and select edit source; right-click and select Edit Source. Locate the line that begins with "Procedure".

Make sure the entire line is selected. Copy this line to the clipboard, then locate the DataWindow that you wish to convert to a stored procedure. Again select Edit Source and locate the line that begins with "retrieve."

It is critical that you select the entire line (it may be several lines). Now replace the entire retrieve statement with the line already on the clipboard. Close the script editor and you will now have a stored procedure-based DataWindow.

Why is this useful? This technique allows you to preserve an elaborate GUI in a DataWindow while still utilizing the power of stored procedures. This is particularly important if you have a lot of DataWindows that are based on SQL statements and you start having performance issues as your database grows. Not only does this technique save many, many hours of developer time, it also completely preserves the GUI so users won't notice the change at all.

Updating a DataWindow Using a Stored Procedure
When the source of a DataWindow is changed to a stored procedure, the ability to update the DataWindow is lost. This prevents many developers from using stored procedures to source DataWindows. For years I avoided using stored procedures for precisely this reason. However, the "Stored Procedure Update" functionality is wonderful. First, identify or create the stored procedures that should be used to update, insert, and delete records. Open the DataWindow object, then from the Rows menu, select "Stored Procedure Update" (see Figure 3).

On each tab, select the stored procedure that will be used for each update operation: insert, update, or delete. PowerBuilder will produce a line for each of the arguments of the stored procedure. Specify which columns of the DataWindow are used for each argument.

If you look at the update properties after specifying a stored procedure update, you'll see the words "Stored Procedure" where a table name would normally be.

The stored procedure update is an excellent option if you will be updating more than one table. Anyone who has utilized resetting the update flags, etc., to perform a multitable update will certainly find this to be extremely valuable.

Fetching from a Stored Procedure in PowerScript
As you begin to utilize stored procedures more frequently, you will undoubtedly wish to utilize stored procedures from PowerScript to get values from the database. Typically, in PowerScript we use a DataStore to accomplish this task. Using a stored procedure instead results in one less DataWindow object that needs to exist, and it allows even more code reuse in that the very same procedure that is called from the back end to accomplish a "Get" action can be used from the front end. To fetch from a stored procedure in PowerScript, use the code snippet shown in Figure 4.

OpenUserObjectWithParm
Many years ago I worked as a consultant for PowerSoft. Once I was attending a meeting of PowerSoft consultants and overheard one say that a client had asked for advice on using the OpenUserObjectWithParm function. The consultant had never even heard of the OpenUserObjectWithParm function. I've never forgotten that conversation and it makes me believe that the OpenUserObjectWithParm function is largely overlooked. This function can be very useful as it allows you to pass parameters to a user object that tell it where to create itself on the window, its position in an array of userobjects (for manipulation at runtime), and virtually any other parameter a developer can think of. A simple example of this technique is to open a DataWindow that allows a user to select a date from a calendar object.

The following line of code in the open event of the window will create a userobject of type uo_drop_down_calendar at an x, y position of 25, 50 and entitle it "Begin Date'.

OpenUserObjectWithParm(iuo_begin, 'Begin Date', 25, 50)

There are many other uses for this technique.

SQL Tool
Many times, in support of our PowerBuilder applications, we are required to troubleshoot data issues and we're not always connected to the production database. In these situations, a methodology for a system administrator to execute SQL statements from the front end of a PowerBuilder application can be very useful. I have developed a very useful tool for accomplishing this goal (see Figure 5).

All of the database tables and columns are available in dropdown DataWindows so that SQL statements can be constructed easily without having to remember column names. A statement can be named and saved for future use. So, for example, let's say your user must find all the address types available in the database. Since this is very simple, you could talk him or her through the creation of the statement.

The user would type the SQL statement into the DataWindow and click "Execute" and the results would then appear in the lower area. The user can then Save this data to Excel, etc....

Should a user require a more elaborate statement, you can write the statement and e-mail it. The user can paste it into the window and execute. This can be used to clean up a data problem, to create and run stored procedures, to create an ad hoc report, to grant permissions, most anything you can do from an enterprise manager. This tool has been extremely useful, however, it must be tightly secured so that a user does not create a Cartesian product or update data maliciously.

Saving a Dynamically Created DataWindow in Your Application
To go one step further, what if you had a user who needed a certain report immediately. You might want to create that report for them "on the fly" and come back later and format it and put it on the application menu. So, create a stored procedure on your laptop and e-mail the script to your system administrator. Your system administrator now opens the script in the SQL Tool window.

When the user executes this script, the stored procedure is created (see Figure 6). You can then tell them the values of the variables to use, and the procedure can be executed. The results will show in the result set window. Now suppose you want to save this result set as a DataWindow.

Access the DataWindow tab and click "Create DataWindow" (see Figure 7).

Now click "Retrieve DataWindow" and the same result set appears. If you wish to have this DataWindow saved to use later, click "Save DataWindow". You can format it as appropriate and turn it into a canned report.

Conclusion
As PowerBuilder developers we are asked to do accomplish an amazing variety of tasks, and users and project managers can be extremely demanding. It's very important that a developer accumulate an "arsenal" of techniques and code samples that he or she can rely on. The PowerBuilder community is not as large or as strong as it once was. I routinely speak with IT managers at large corporations and government agencies and am all too often forced to rebut the assertion that PowerBuilder is "legacy," "outdated," "old," or even "'60s technology." In truth, PowerBuilder is the best RAD development tool in existence and allows developers to both prototype and develop production-ready code in a matter of hours, rather than days or weeks. This ability to produce results can be the difference between a team-oriented environment and an adversarial one.

As PowerBuilder developers, we have an extremely powerful tool and a tremendous advantage over our fellow IT professionals. It is my hope that some of these techniques will assist beginner and intermediate developers and help to create more PowerBuilder loyalty in the industry.