Home
What's New
Dev Tools
Services
Feedback
Site Map

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.

f_IsEqual()

// 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

Consider:
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:

f_NotNullValue()

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 won’t cause a Null result

Null Parameters to PowerBuilder Functions

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.

f_MessageBox(as_title, as_text)

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 PowerBuider’s 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 PB’s 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.

f_IsEmpty()

string number date
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

Conversion Functions

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.

SetTrans vs. SetTransObject

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.

Creating Executables

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.


Copyright Woodger Computing Inc.