---------------------------------------------------------- back to tips menu
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 doesnt 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 = "James" ... more work ...
// empty out Name Array ls_NameArray = ls_EmptyArray
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
- 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
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:
in_parm1 = "input value"
out_parm2 = space(50) // preallocating space for string
2.2 Invoking a Stored Function (shown using an array variable)
The invocation syntax is:
ReturnValue = TransactionObjectName.FcnName(args)
string out_parm2 // defining fixed sized array
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 = 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