PowerBuilder Hard Lessons
PowerBuilder has a number of features that are counter-intuitive, different from other languages or are commonly misunderstood by those new to the tool. This page seeks to explain a number of features that can trip up the developer who is unaware of them. The "hard lessons" are discussed under the following headings:
NULL Handling in PowerBuilder
What is Null: Null is a database concept that is carried into data-centric tools like PowerBuilder. Null represents a missing or unknown value -- it is not zero and it is not a zero length string ("EmptyString"). PowerScript variables generally take on Null values after they are assigned to columns that were retrieved from the database.
General Rule: unfortunately, PowerBuilder took an unchareristically theorectical approach when it decided how to handle Null. When Null is added, compared to or concatenated with any variable, the entire result is Null.
Null Processing in Comparisons
If either parameter is null, then the entire comparision evaluates to Null and the "Else" portion of the "IF" will be executed.
Consider these examples:
IF A = B THEN f_1() ELSE f_2() END IF
IF NOT (A > B + 1) THEN f_1() ELSE f_2() END IF
IF A = 1 OR B =2 THEN f_1() ELSE f_2() END IF
For all the examples above, if either A or B (or both) are Null then the entire comparison will evaluate to Null causing f_2() to execute.
Solution for Comparisons
The solution is to explicitly check parameter values for Null. To perform a comparison where one value might be Null, you might call a general purpose routine like f_IsEqual() below to avoid undesirable/unintended Null handling.
// This function compares two values and always returns either True or False (never Null) // if neither value is Null, it does a straight comparison // if one value is Null and the other is not, it returns "False" // if both values are Null, it treats them as equal and returns "True"
boolean lb_Return IF IsNull(as_Parm1) OR & IsNull(as_Parm2) THEN // at least one parm is Null // Handle Null <> Non-null. Either both parms are Null or // we have a Mismatch (since know at least one is Null) lb_Return = IsNull(as_Parm1) AND IsNull(as_Parm2) ELSE // No Nulls to worry about lb_Return = (as_Parm1 = as_Parm2) END IF Return lb_Return
Null Processing for Concatenations and Arithmetic Operations
A = B + C // this is concatenation for string or an addition for numerics
If either B or C is null, A will be given a Null value.
Solution for Concatenation / Additions
Again, the solution is to avoid having a null value when performing the operation. A simple way to avoid Null is to create/use a function like f_NotNullValue() below:
|String version||Numeric version|
IF IsNull(as_Parm) THEN as_Parm = "" END IF Return(as_Parm)
IF IsNull(al_Parm) THEN al_Parm = 0 END IF Return(al_Parm)
The above concatenation/addition operation would be rewritten, as follows: A = f_NotNullValue(B) + f_NotNullValue(C) // nulls wont cause a Null result
When given a Null parameter, most PowerBuilder functions will simply return Null and perform no action. A common mistake is to pass a function like MessageBox a Null text value (because concatenated strings can accidentally become Null). When this happens, no MessageBox displays and processing continues (probably in an unintended manner).
Solution for Passing Nulls to Functions
The first solution for avoiding this Null parameter problem is not to avoid getting Null inputs (e.g. see contentation solution above). Since mistakes can still happen, the next item to consider is to create special function like f_MessageBox below.
IF IsNull(as_title) OR IsNull(as_text) THEN // stop application when message will not show (message // could be critical to application) MessageBox("Serious Error","Null Value was passed to MessageBox function") HALT CLOSE ELSE // make regular call to PowerBuiders MessageBox MessageBox(as_title, as_text) END IF
If f_MessageBox is always called instead of MessageBox, then the accidental passing of a Null value will quickly be identified so it can be corrected.
Full Evaluation of IFs
PowerBuilder will always evaluate all portions of an IF condition (While condition, etc.). The reason: because if any part of the condition is Null, the entire will evaluate to Null, as described above.
For example, in the following statement:
IF 1=1 OR A> 5 THEN f_1() END IF
you might think that A>5 would not be evaluated. In many languages, 1=1 being True would make it unnecessary to evaluate the other portion of the "OR". In PowerBuilder, you can be assured (and should plan on) the fact that both 1=1 and A>5 will be evaluated before PowerBuidler decides on the final value for the IF condition. Ahh, the joy (misery?) of PBs Null handling!
Variables and Column Initialization / Check for "Empty" Value
Uninitialized variables/columns can take on a variety of values, depending on their source. If a PowerScript variable is not assigned an explicit value, PowerBuilder initializes them as follows: string is EmptyString (or zero length string), number is zero, date is 01/01/1900. Databases initialize variables as Null (when the column value is not explicitly specified). A newly inserted row in a DataWindow will generally initialize columns to Null (but strings will get an "EmptyString" value, if "EmptyStringIsNull" is set to false).
Depending on the data type, you might need to check a few values to see if the variable or column is "Empty" or Uninitialized. To simplify coding, it can help to write generalized functions to do this checking for you. See f_IsEmpty below.
boolean lb_Return = False IF IsNull(as_Parm) THEN lb_Return = True ELSEIF Trim(as_Parm) = & "" THEN lb_Return = True END IF Return lb_Return
boolean lb_Return = False IF IsNull(al_Parm) THEN lb_Return = True ELSEIF al_Parm) = 0 THEN lb_Return = True END IF Return lb_Return
boolean lb_Return = False IF IsNull(ad_Parm) THEN lb_Return = True ELSEIF ad_Parm = & date("01/01/1900") THEN lb_Return = True END IF Return lb_Return
Beware when using conversion functions. If the input is invalid (e.g. non-numeric to the "Integer" function), you will simply get a default value back. For example, functions that convert to numbers will return Null (if input is Null) and they will return 0, if the value is not numeric. Similarly, functions that convert to date will return Null (if input is null) or Jan 1, 1900 if the value is not a date.
The trick is to always check whether the value is a valid number or date, before calling the converion function. For example, call IsNumber to verify that value is valid, as shown here:
IF NOT IsNull(as_arg) THEN IF IsNumber(as_arg) THEN al_Number = Long(as_arg) ELSE ... handling for invalid number END IF END IF
While there are functions to check if a value is a date (IsDate) or a time (IsTime), PowerBuilder provides no function to check if a value is a valid DateTime. A function to convert to DateTime and check if the value is a valid DateTime is provided in a tip on this site.
A common problem for PowerBuilder "beginners" is not understanding whether to use SetTrans or SetTransObject when "tying" your DataWindow to the database.
Clearly, SetTrans is a considerably slower operation to use (even when the database is local) and it should be avoided. Database connection operations are amongst the slowest operations you can perform. Just remember to setup your database connection in your application open event when using SetTransObject() -- typically done with a "SQLCA.Connect()". Once the connection has been established, you can re-use that connection across the application.
In theory, creating executables in PowerBuilder isn't particularly difficult. One creates a project file which:
The PowerBuilder Online books describe the creation of executables in more detail.
In reality, creating executables for large or complex projects can be a very tricky process -- with a number of possible problems arising. Various tips relating to creating EXEs and diagnosing EXE-related problems are provided on this site.