Home
What's New
Dev Tools
Services
Feedback
Site Map

---------------------------------------------------------- back to tips menu

Details of General PowerBuilder Tips

Gen Tip 1: Setting Focus to the TabPage with the Control

A common problem when using Tab objects is that you can set focus to a control (e.g. a DataWindow) on the tab but that control is not actually visible, since another Tab Page is selected. This may happen because another TabPage (e.g. TabPage 2) has focus when the save process starts and the DW on this Tabpage (e.g. TabPage 3) has an ItemChanged Error message to display.

It is useful to create a general-purpose function that determines whether the control is on a Tab object and, if it is, sets focus to the Tab Page where the control is placed. The general-purpose function below was placed on the ancestor DataWindow object (u_dw in PFC) but it could be placed on any ancestor object.

Function Name:  f_MakeTabPageCurrent

Purpose: This function determines whether this control is on a Tab Object.
If so, it makes the tab page that has this control current. If this object
is not on a tabpage, then nothing happens.

Parameters:     None

Returns:  Long  >0  Index of tabpage where object was placed (and focus was set)
                 0  the object was not placed on a tab object.
                -1  Error encountered

integer li_Index
boolean lb_TabParentFound = False
boolean lb_TabPageFound = False
long    ll_RetCode

u_tab       lu_tab // u_tab is a standard visual user object of type "tab"
powerobject lo_CurrentParent, lo_CurrentChild, lo_TabPage

// Loop through control hierarchy until a Tab control or Window is found
lo_CurrentParent = This.GetParent()
DO WHILE (NOT lb_TabParentFound) AND (lo_CurrentParent.TypeOf() <> Window!)
  // we found tab control
  IF lo_CurrentParent.TypeOf() = Tab! THEN
    lu_tab = lo_CurrentParent          // keep handle to Tab control
    lb_TabParentFound = True
  ELSE
    // if we're not at window yet, keep looking for a window or tab control
    IF lo_CurrentParent.TypeOf() <> Window! THEN
      lo_CurrentChild = lo_CurrentParent // keep handle for the Child object
      lo_CurrentParent = lo_CurrentParent.GetParent()  // Get next parent
    END IF
  END IF
LOOP

// If a tab parent was found for this control, then make
// the tab page containing our control current
IF lb_TabParentFound THEN
  lo_TabPage = lo_CurrentChild // tabpage was second last object looked at
  li_Index = UpperBound(lu_Tab.Control[])

  // Loop through child tabpages of Tab control looking for the tabpage
  DO WHILE NOT lb_TabPageFound and (li_Index > 0)
    IF lu_Tab.Control[li_Index] = lo_TabPage THEN
      // tab page found ... select it
      lu_tab.SelectTab(li_Index)
      lb_TabPageFound = True
    ELSE
      li_Index = li_Index - 1
    END IF
  LOOP

  IF lb_TabPageFound THEN
    ll_RetCode = li_Index
  ELSE
    // TabPage not found -- maybe not in control array? (PB5 possibility)
    ll_RetCode = -1
  END IF
ELSE
  ll_RetCode = 0
END IF

Return ll_RetCode

Gen Tip 2: Obsolete

Gen Tip 3: Re-initializing an Unbounded Array

Suppose that you have been filling an array with values. Now you want to reinitialize the array to its original empty state. PowerBuilder doesn’t have an explicit function to help re-initialize the array, so you have to do it manually as described below.

Declare a dummy array of the same type. Never put any values into this array. When you want to re-initialize your "working array", assign it to the value of the empty array. This will clear out the "working" array and make the UpperBound function return the correct value (zero). See sample code below:

string ls_EmptyArray[]
string ls_NameArray[] 
ls_NameArray[1] = "James"
... more work ...
// empty out Name Array
ls_NameArray = ls_EmptyArray

Gen Tip 4: Calling Oracle Stored Procs/Functions from PB

Whenever you want to make a call to an Oracle stored procedure or stored function, a good approach is to first declare it as an external function and then invoke it based on that declaration.

Step 1: Declaring an Oracle Stored Procedure so that PowerBuilder Knows About it

This function/procedure declaration is done in the transaction user object (e.g. n_tr for a PFC App). Once inside the transaction user object, choose "Declare-Local External Functions" and follow the syntax below.

1.1 Stored Procedure (no package)

The declaration syntax for a stored procedure (on its own, outside package) is:

SUBROUTINE SubRtnName(args) RPCFUNC

In example 1.1, the declaration passes a string by value (i.e. IN) and a string by reference (i.e. IN OUT or OUT).

SUBROUTINE CalcAmount(string LS_In1, ref string LS_Out2) RPCFUNC

Notes:
- if the procedure is not in a package and does not take any array parameters, then you can click the procedures button to paste in the procedure declaration directly from the database.
- an optional alias clause can be added to allow PowerBuilder to use a different function name from Oracle (see alias format used with package declarations).

1.2 Procedure inside an Oracle package

The declaration syntax for a stored procedure inside a package is:

SUBROUTINE SubRtnName(args) RPCFUNC ALIAS FOR "PackageName.ProcName"

In example 1.2, the declaration passes a string by value (i.e. IN) and a string array by reference
(i.e. IN OUT or OUT).

SUBROUTINE CalcPenaltyAmt(string LS_In1, ref string LS_Out2[]) RPCFUNC ALIAS FOR "Penalty.P_Calc_Amount"

1.3 Stored Function (no package)

The declaration syntax for a stored function (on its own, outside package) is:

FUNCTION ReturnType FcnName(args) RPCFUNC

In example 1.3, the declaration passes a string by value (i.e. IN) and a string array by reference (i.e. IN OUT or OUT) and it returns a long.

FUNCTION long CalcAmount(string LS_In1, ref string LS_Out2[]) RPCFUNC

Note: the same notes given for stored procedure declarations apply to stored functions.

1.4 Function inside an Oracle package

The declaration syntax for a stored function inside a package is:

FUNCTION ReturnType FcnName(args) RPCFUNC ALIAS FOR "PackageName.FunctionName"

In example 1.4, the declaration passes a string by value (i.e. IN) and a string array by reference (i.e. IN OUT or OUT) and returns a long.

FUNCTION long CalcPenaltyAmt(string LS_In1, ref string LS_Out2[])) RPCFUNC ALIAS FOR "Penalty.f_Calc_Amount"

2. Invoking an Oracle Stored Procedure/Function

This is the invocation syntax for a stored procedure/function that has been declared in the transaction object is shown below.

Notes on Variables passed by Reference

Dynamically-sized output variables (i.e. strings and arrays) must be preallocated up to the size needed. When using this invocation method, PowerBuilder does not dynamically allocate the space needed for them.

Array Size Limitation: number of array elements times maximum element size cannot exceed 32K.

2.1 Invoking a Stored Procedure

The invocation syntax for a stored procedure is:

TransactionObjectName.ProcName(args)

Sample invocation:

string in_parm1
string out_parm2

in_parm1 = "input value"
out_parm2 = space(50) // preallocating space for string

SQLCA.CalcAmount(in_parm1, out_parm2)

2.2 Invoking a Stored Function (shown using an array variable)

The invocation syntax is:

ReturnValue = TransactionObjectName.FcnName(args)

Sample invocation:

string in_parm1
string out_parm2[5] // defining fixed sized array
long ll_return

in_parm1 = "input value"

// preallocating space for 500 chars for whole string array.
// Each element will effectively be 500 bytes by allocating
// the first.
out_parm2[1] = space(500)

ll_Return = SQLCA.CalcAmount(in_parm1, out_parm2[])

Gen Tip 5: Checking for Errors after Inline SQL or Stored Procedures

It is important to check the results after executing inline SQL or a stored procedure. This is different from checking DataWindow errors which is done in DBError (see sample). A traditional approach to checking non-DataWindow database results might look something like:

SELECT Cust_Name
INTO :ls_Name
FROM CUST
WHERE Cust_Id = as_CustId;

CHOOSE CASE SQLCA.SQLCode
  CASE 0
    // all OK

  CASE 100
    // not found

  CASE ELSE
    // serious error
    ... standard message ...
END CHOOSE

There are a few disadvantages with having developers key in this type of error checking each time:

A preferable approach is to write a general purpose error checking routine on the transaction object (i.e. on n_tr in PFC). Sample code for such a function is shown next (it can be overloaded to default parms):

Function Name: f_CheckDBError

Purpose: This function checks the DB Error code and returns the error
parameters on this transaction object.
If an unexpected error occurs, it displays an explanatory error message.
The first parameter (ai_HandleCode)indicates which errors are "expected".

Parameters:
  in - ai_HandleCode (optional): code indicating how to handle non-zero SQL codes:
       0 means ANY non-zero value is considered an error (default value when parm not specified)
       1 means zero and "not found" are not considered errors
	Note: other codes with special handling could be added
  out - al_SQLCode (optional): SQL code coming back from DB.
  out - al_SQLDBCode (optional): SQL DB code coming back from DB.
  out - as_SQLText (optional): SQL text coming back from DB.

Returns: integer:
 1: no error found
 0: errors found but these fit into predefined "acceptable" category, according to ai_HandleCode value.

-1: errors found (i.e. an error not anticipated by the caller).

integer li_Return
string as_MsgParms[]
string ls_MessageID

// get the error codes from the specified transaction object.
al_SqlCode   = This.SQLCode
al_SqldbCode = This.SQLDBCode
as_SqlText   = This.SQLErrText

// Perform processing based on error code found
CHOOSE CASE al_SQLCode
  CASE 0 no error
    li_Return = 1

  CASE 100 // not found condition
    CHOOSE CASE ai_HandleCode
      CASE 0
        // condition was not expected
        li_Return = -1
        MessageBox("Error","Unexpected not found condition encountered")
      CASE 1
        // condition was expected ... just indicate it occurred to caller
        li_Return = 0
    END CHOOSE

  // Other type of error (other than OK or not found), so check the
  // DB-specific SQLDBCode
  CASE ELSE
    CHOOSE CASE al_SQLDBCode
      CASE 3113, 3114 // Oracle database connection lost
        // give a specific error message
        li_Return = -1
        MessageBox("Error","Database connection was lost")

      CASE ... other special conditions (e.g. 20000-level errors in Oracle)

      CASE ELSE
        li_Return = -1
        MessageBox("Error","Serious database error encountered with SQL Code = " + &
                  string(al_SqlCode) + " SQL DB Code = " string(al_SqldbCode) + &
                  " and SQL Text = " as_SqlText)
    END CHOOSE
END CHOOSE

Return li_Return

Sample use of this function is provided below. First, it checks the results of a stored procedure:

p_GetCustDetails(al_CustId, as_CustName, as_CustGroup) // stored procedure call

// any non-zero code is a serious error
IF SQLCA.f_CheckDBError() = 1 THEN // call overloaded version with no parms
  ... all OK
ELSE
  ... serious error handling (message already given)
END IF

The next example checks the results of inline SQL and handles "not found":

SELECT Cust_Name
INTO :ls_Name
FROM CUST
WHERE Cust_Id = as_CustId;

CHOOSE CASE f_CheckDbError()
  CASE 1
    // all OK

  CASE 0
    // not found condition
    ls_Name = "" // set default value

  CASE ELSE
    // serious error handling (message already provided)
    li_Return = -1 // exit routine
END IF

Gen Tip 6: Setting Focus to a Window (handling minimized)

When setting focus a window, it is useful to make sure the window is not minimized. Standard CloseQuery processing (see tip) is one place where this logic might be needed. The window-level function below sets focus to the current window and returns minimized window to Normal state.

Function:  f_ShowWindow (no args)

integer li_Return = 1

 // make the current window normal size, if it was minimized
IF This.WindowState = Minimized! THEN
   This.WindowState = Normal!
END IF

// give the window focus
This.SetFocus()

Return li_Return

Gen Tip 7: Numeric Values for Standard Windows Colours

The numeric values for the standard Windows Colours, are as follows:

Standard Colour Numeric Value Value formatted for readability
Windows Background 1090519039 1 090 519 039
Button Face 79741120 79 741 120
Windows Text 33554432 33 554 432
Application Workspace 276856960 276 856 960

Gen Tip 8: The Invisible MessageBox

The MessageBox may not display in the example below:

SELECT cust_name
INTO :ls_CustName
FROM Customer
WHERE cust_id = :ls_custid;

MessageBox( "Warning!", "Customer: " + ls_CustName + &
	" does not have sufficient credit." )

If the customer is not found or if the customer name is Null, then no MessageBox will display. The reason: MessageBox displays nothing if an input parameter is Null (or evaluates to Null when concatenated).

Having no MessageBox display could clearly result in both logic and business problems. Since the problem is essentially hidden, it might not be discovered for a long time.

The solution: if you are using a framework (like PFC) and you should consistently use its Message (or "Error") service which should handle such a situation. Otherwise, create your own function like f_MessageBox (see sample code). As shown in the sample code, f_MessageBox explicitly checks for Null input. It can display an error and/or log the error when it receives Null input.

---------------------------------------------------------- back to tips menu


Copyright © Woodger Computing Inc.