PowerBuilder – Things to Avoid

Posted on Thursday, September 2nd, 2010 at 8:16 pm in

“Give someone enough rope and he’ll hang himself”

There are a number of things to avoid in Powerbuilder since they are either resource hogs or make maintenance/debugging a real pain. In no particular order.

Code in the Other event
I worked for awhile on a large accounting application, a commercially available product, written in PB back in version 6.5 days which had some ancestor code in the Other event. I don’t remember exactly why the code was there but it makes debugging almost impossible since it is fired so frequently. From the 11.5 help file,
“The Other event is no longer useful, because you can define your own user events. You should avoid using it, because it slows performance while it checks every Windows message.”
This is an understatement if there ever was one.

RetrieveRow datawindow event
Again from the 11.5 help file, “If you want to guard against potentially large queries, you can have code in the RetrieveRow event check the row argument and decide whether the user has reached a maximum limit. When row exceeds the limit, you can return 1 to abort the retrieval (in which case the retrieval cannot be resumed). A script in the RetrieveRow event (even a comment) can significantly increase the time it takes to complete a query.”

Do not follow this advise for limiting large queries. A much more useful way to achieve this is by altering the datawindow object SQL in the sqlpreview event. Add an instance variable and set that to the record limit you want to enforce. Here is the syntax for SQL Server, Oracle, and MySQL.

SQL Server:
SELECT TOP 10 partNo, partDesc, categoryNo 
FROM partMaster

SELECT partNo, partDesc, categoryNo
FROM partMaster 

SELECT partNo, partDesc, categoryNo
FROM partMaster

So just take apart the sql string, insert the syntax in the proper place, and then let the datawindow do its thing.

Breakpoints in Mousemove
Forget about even trying this. Put a couple of text controls on your window and populate the text property with the X,Y coordinates or whatever else you are looking for.

Global Functions in Datawindow expressions
These can really send the processor utilization on the PC upwards of 100% depending upon what is being done. If you have no alternative try to limit the calls to the function by means of a flag. Add an additional column (‘checkstatus’) to the datawindow retrieve with the default value of ‘Y’. Then in the expression calling the global function put something like:

if(checkstatus[0] <> 'Y', gf_fix_global_warming,0)

Make sure the ‘false’ portion of the expression is the same datatype as the return from the global function. Now when you want the datawindow row to call the function you modify the value in checkstatus with:

dw1.setretraw(TRUE) // this may or may not be needed

And afterwards modify the value back to ‘Y’ so the global is not called again until it needs to.

Large data retrievals in Timers
If you are coding a dashboard type window where data modifications are monitored in near real time, be very careful with the SQL which gets called. Even if the SQL is very efficient and tuned, if it is called every five seconds it can send the cpu utilization on the database server skyward. Try to narrow down the number of columns you update frequently to as few as possible and put these in a separate datastore. The main datawindow retrieve can be called at a longer interval with updates to the display being made based on the smaller datastore retrieve.

Naming global, instance, and local variables the same
Believe it or not I once worked on an application where the global, instance, and local variable names were exactly the same. The folly of doing this should be obvious.

Using database cursors to manipulate data instead of datastores
If you are modifying data in a series of rows and using a cursor in PB to do so, stop. There are very good reasons to use cursors within PB but straightforward updates is best done with a datastore.

Not providing context for error messages
Although it may be meaningless to an end user, putting the object and method name in your error messages are invaluable to the support staff. If you want to get really fancy you can log these things somewhere to provide as much additional information as possible.

March 2010 update:  Modern security standards frown on this last issue.  Just make sure the error is logged via some mechanism for easier troubleshooting.

You might also be interested in