Contents (click on a topic for quick jump):
Creation of global functions is definitely not recommended, but... that is exactly what I will advise now! As we know, each rule has exceptions (except of the rule, that each rule has exceptions)... Yes, we will also break the rule of prefixing global functions' names with f_ because we will use the functions as frequently as if they would built-in operators of PowerScript (you have just begun to read this page, and there are already 2 bad recommendations... are you sure you want to keep reading?):
So, let's begin with the iif function. That is how it is used:
ls_greeting = iif(lc_sex = "M", "Mr", "Ms")
It returns the value of the second or of the third argument depending on the Boolean expression passed as the first one. It works similar to (as you can read there, "IIf" stands for "Internal If") and is very convenient - why not to have it in PB? It helps to keep scripts shorter by avoiding the if...else construction, so each use of this function removes 4 extra lines - compare with the "old-style" variant:
if lc_sex = "M" then ls_greeting = "Mr" else ls_greeting = "Ms" end if
Please pay attention: if you pass an expression as the 2nd or 3rd argument, that expression is evaluated ALWAYS (no matter what is the result of the Boolean expression), and after that the expression's value is sent to the function, so DON'T USE iif in situations like zero divide preventing (when Visual Basic's IIf and the ternary operator of C would work fine)! So, the following line will fail if ll_2 contains 0:
ll_result = iif(ll_2 <> 0, ll_1 / ll_2, 0)
The function has 3 arguments - ab_condition (boolean), aa_val_1 (any), aa_val_2 (any) - and returns any. That is the source - not extremely complicated:
if ab_condition then return aa_val_1 end if return aa_val_2
In contrast to iif, which was stolen from VB, the next function - nvl - is stolen from Oracle. It accepts 2 arguments: the value of the first argument is returned if that value is not null; otherwise the function returns the value of second argument. Works with any datatype. Examples of use:
ll_elephants_count = nvl(uf_get_elephants_count(), 0) // in case uf_get_elephants_count() can return null ls_full_name = ls_first_name + " " + nvl(ls_mid_name + " ", "") + ls_last_name // only first and last names are required ls_err_msg = "Argument as_mode contains invalid value '" + nvl(as_mode, "NULL") + "'." // prevent NULLifying of result string on concatenation
Both the arguments must have a same datatype:
ls_err_msg = "Argument ai_mode contains invalid value '" + nvl(String(ai_mode), "NULL") + "'."
Source code (save as nvl.srf file and import to your application):
$PBExportHeader$nvl.srf global type NVL from function_object end type forward prototypes global function any nvl (any aa_val_1, any aa_val_2) end prototypes global function any nvl (any aa_val_1, any aa_val_2); if not IsNull(aa_val_1) then return aa_val_1 end if return aa_val_2 end function
Writing Oracle PL/SQL code, I always enjoy the ability to use the SQL's in clause in a procedural environment:
if v_day in ('SATURDAY', 'SUNDAY') then
-- do something
end if;
Of course, we can mimic it in PowerScript using the choose case construction (the price - one extra line of code):
choose case ls_day case 'SATURDAY', 'SUNDAY' // do something end choose
If the comparison's result should be stored directly in a Boolean variable (which is impossible with choose case) then we can use a Boolean expression with or(s):
lb_weekend = (ls_day = 'SATURDAY' or ls_day = 'SUNDAY')
But the last solution is not very elegant - imagine what mess will be created if you compare a variable with 30 values, not just 2. So, I am introducing the iin function ("internal in). That's how it works (the second argument is an array where the value of the first argument is searched):
lb_weekend = iin(ls_day, {'SATURDAY', 'SUNDAY'})
Of course, you can use a previously populated array (that is totally impossible with choose case!):
string ls_days_arr[] = {'SATURDAY', 'SUNDAY'}
lb_weekend = iin(ls_day, ls_days_arr)
One more example - this time with numeric data:
if iin(ll_employee_id, {123, 456, 789}) then
You can ask: how did I overload a global function in PowerScript? It's impossible! Not for me - I have a personal permission from the president of Powersoft Sybase SAP! Ok, now I will demonstrate a simple trick. It's true that we cannot overload global functions in the Function Painter, but the source code can pass through a surgical operation! So, firstly create a normal, not-overloaded global function, save and re-open in with "Edit Source". Then manually add the needed overloaded versions! See an example in the source of iin function which appears next - it includes two overloads: one for string (also used for char) and one for long (also used for int) - you can save the code as iin.srf file and import it to your application:
$PBExportHeader$iin.srf global type iin from function_object end type forward prototypes global function boolean iin (string as_val, string as_arr[]) global function boolean iin (long al_val, long al_arr[]) end prototypes global function boolean iin (string as_val, string as_arr[]); int i int li_upper_bound li_upper_bound = UpperBound(as_arr[]) for i = 1 to li_upper_bound if as_arr[i] = as_val then return true end if next return false end function global function boolean iin (long al_val, long al_arr[]); int i int li_upper_bound li_upper_bound = UpperBound(al_arr[]) for i = 1 to li_upper_bound if al_arr[i] = al_val then return true end if next return false end function
My goal was to demonstrate overloading of global functions, but you can write iin also utilizing any as the arguments' datatype (as in nvl):
int i int li_upper_bound string ls_val_type ls_val_type = ClassName(aa_val) li_upper_bound = UpperBound(aa_arr[]) for i = 1 to li_upper_bound if ClassName(aa_arr[i]) <> ls_val_type then continue if aa_arr[i] = aa_val then return true next return false
This approach is more universal (because it works with all datatypes, not only with int/long/char/string), but, theoretically, it is a little bit less efficient, so I prefer the overloading implementation (usually we don't compare values of types other than int/long/char/string in a IN-like comparison, but if that happens then we can add an overload).
And now - small trick how to make a multi-line DW looking similar to a data control in .NET (odd rows with withe background and even rows with light grey background) - such an appearance helps users not to be lost working with many rows of data. That function (I called it uf_set_zebra_bg even though I was working in a very serious organization) gets one argument - the DW - and is looking like that:
/**********************************************************************************************************************
Acc: public
-----------------------------------------------------------------------------------------------------------------------
Dscr: Sets DW's background color so odd rows are white and even rows are light grey.
Must be called AFTER DataObject is set.
-----------------------------------------------------------------------------------------------------------------------
Arg: adw - DataWindow
-----------------------------------------------------------------------------------------------------------------------
Log: 08mar2012 Michael Zuskin Initial version
**********************************************************************************************************************/
int li_start_pos = 1
int li_tab_pos
string ls_obj_list
string ls_obj_name
boolean lb_is_field
if not IsValid(adw) then return
if adw.DataObject = '' then return
// Make zebra background:
adw.Object.DataWindow.Detail.Color = "1073741824~tif(Mod(GetRow(), 2) = 0, RGB(220, 220, 220), RGB(255, 255, 255))"
// Make fields transparent:
ls_obj_list = adw.Describe("DataWindow.Objects")
li_tab_pos = Pos(ls_obj_list, "~t", li_start_pos)
do while li_tab_pos > 0
ls_obj_name = Mid(ls_obj_list, li_start_pos, (li_tab_pos - li_start_pos))
// Check if it's a field (regular or computed):
lb_is_field = (adw.Describe(ls_obj_name + ".DBName") <> "!" or adw.Describe(ls_obj_name + ".Type") = "compute")
if lb_is_field then
adw.Modify(ls_obj_name + ".Background.Mode='1'") // transparent
end if
li_start_pos = li_tab_pos + 1
li_tab_pos = Pos(ls_obj_list, "~t", li_start_pos)
loop
return
Data, contained in a DataStore, can be saved as an Excel file when you are debugging. For that, insert a new Watch with the following expression (change ids_XXX to of your real DS):
ids_XXX.SaveAs("C:\aaa_" + String(Today(), "yyyymmdd") + "_" + String(Now(), "hhmmss") + ".xls", Excel!, true)
Data save will be performed immediately after creation of the Watch and, in addition, each time the script is executed while the application is in the debug mode. As you see, the name of the created file contains the current date and time, so you don't have to delete the existing file to prevent failure when you are making a new save. You can also save a DataStore's data as a text file:
ids_XXX.SaveAs("C:\aaa_" + String(Today(), "yyyymmdd") + "_" + String(Now(), "hhmmss") + ".txt", Text!, true)
The last, Boolean argument, passed to SaveAs() function, manages displaying of columns' headers in the first line of the created Excel/text file. Pass true ("display headers") if you want to open the file and have a look on the data, but if your intent is to import the file into a DataWindow, then pass false to prevent an import error.
Don't use SetItemStatus() function directly - it doesn't change every old status to any new status - you can find the problem combinations in this table:
| OLD STATUS | NEW STATUS | |||
| New! | NewModified! | DataModified! | NotModified! | |
| New! | - | Yes | Yes | No |
| NewModified! | No | - | Yes | New! |
| DataModified! | NewModified! | Yes | - | Yes |
| NotModified! | Yes | Yes | Yes | - |
As you see, there are some item statuses that cannot be set directly; you need a second SetItemStatus to achieve the goal. One example: changing the row status from New! to NotModified! simply doesn't work. You need to set it to DataModified! first, then you can change it to NotModified!. Some others settings are allowed, but don't work as expected; changing from NewModified! to NotModified! will change the status to New!. To encapsulate all that complexity (and forget about it forever), create a function (named, for example, uf_set_row_status) in a class of useful functions or another class in your application (maybe in the DW ancestor - in this case remove the argument "adw" and use "this" instead):
/**********************************************************************************************************************
Dscr: Changes the item status of the passed row in a DataWindow.
-----------------------------------------------------------------------------------------------------------------------
Arg: DataWindow adw
long al_row
DWItemStatus a_new_status
DWBuffer a_buf
-----------------------------------------------------------------------------------------------------------------------
Ret: none
**********************************************************************************************************************/
DWItemStatus l_old_status
l_old_status = adw.GetItemStatus(al_row, 0, a_buf)
choose case true
case a_new_status = l_old_status
return // nothing to do
case l_old_status = NewModified! and a_new_status = New!
adw.SetItemStatus(al_row, 0, a_buf, NotModified!) // that sets the row to New! status
return // nothing esle to do
case l_old_status = New! and a_new_status = NotModified!
adw.SetItemStatus(al_row, 0, a_buf, DataModified!)
case l_old_status = NewModified! and a_new_status = NotModified!
adw.SetItemStatus(al_row, 0, a_buf, DataModified!)
case l_old_status = DataModified! and a_new_status = New!
adw.SetItemStatus(al_row, 0, a_buf, NotModified!)
end choose
adw.SetItemStatus(al_row, 0, a_buf, a_new_status)
Create a similar function for DataStore too. These functions can be also overloaded with a 3-arguments version which will be called when only the Primary buffer is processed:
/********************************************************************************************************************** Dscr: Changes the item tatus of the passed row in a DataWindow. Works only on Primary! buffer; to work with other buffers, use the overloaded version. ----------------------------------------------------------------------------------------------------------------------- Arg: DataWindow adw long al_row DWItemStatus a_new_status ----------------------------------------------------------------------------------------------------------------------- Ret: none **********************************************************************************************************************/ this.uf_set_row_status(adw, al_row, a_new_status, Primary!)
Unfortunately, the PowerScript programming language doesn't support the short-circuit evaluation of Boolean expressions - the evaluation is always "long-circuit". Let's suppose we have Boolean functions uf_is_bird() and uf_is_dinosaur(); the first one is very light and quick, but the second is very heavy and slow (because so are dinosaurs it goes to the database). If you have come to PowerBuilder from a programming language which supports the short-circuit evaluation (and almost all the languages support it!), you can mistakenly write:
lb_is_bird_or_dinosaur = (uf_is_bird() or uf_is_dinosaur())
or (if you are returning a value from a Boolean function)
return (uf_is_bird() or uf_is_dinosaur())
You can suppose, that uf_is_dinosaur() will not be called if uf_is_bird() has returned true, but you are wrong: uf_is_dinosaur() will be executed ALWAYS. So, express yourself in one of the following ways:
lb_is_bird_or_dinosaur = uf_is_bird() if not lb_is_bird_or_dinosaur then lb_is_bird_or_dinosaur = uf_is_dinosaur()
or
lb_is_bird_or_dinosaur = false if uf_is_bird() then lb_is_bird_or_dinosaur = uf_is_dinosaur()
or (if you are returning a value from a Boolean function)
if uf_is_bird() then return true if uf_is_dinosaur() then return true return false
It was the OR relation, but the same problem exists with the AND. In the following example, the condition ll_row > 0 will not protect against failure when ll_row = 0 (as a Java or C# developer could think):
lb_from_usa = (ll_row > 0 and dw_addr.object.country[ll_row] = "US")
To use ll_row > 0 as a protection, write this way:
lb_from_usa = false if ll_row > 0 then lb_from_usa = (dw_addr.object.country[ll_row] = "US")
Remember about that when you are trying to make you code safer using the IsValid() function. The following example, written in the Java/C# style, is dangerous - if the object has not been created, you will get a run-time error:
if IsValid(ids_XXX) and ids_XXX.RowCount() > 0 then...
The correct solution is obvious:
if IsValid(ids_XXX) then if ids_XXX.RowCount() > 0 then... end if
This tip can be interesting only for programmers building PB applications against Oracle RDBMS. I will explain how to use not-standalone stored procedures (i.e. "living" in packages) as DataWindow's data source.
If you have selected "Stored procedure" as the data source for your DW, you see a dropdown of stored procs, available to the application. But, unfortunately, it contains only standalone procs, not packaged ones. To use a SP from a package, we will cheat PowerBuilder! Let's say, we want to use my_proc which exists in my_package. To do that, perform the following steps:
my_proc and add my_package. (with a dot!) just before it, so the result is my_package.my_proc.
The following function, named uf_create_ds_by_sql, returns DataStore created dynamically by the supplied SQL SELECT. It's not very tricky but is very useful:
ls_sql = "SELECT " + ls_list_of_fields_to_print + " FROM emp WHERE dept_id = " + String(al_dept_id) lds_emp = gn_util.uf_create_ds_by_sql(ls_sql, true /* ab_also_retrieve */, SQLCA)
The second argument, ab_also_retrieve, instructs the function to retrieve data just after the DS has been created (true) or not to retrieve (false). False can be passed, for example, when the calling script will populate the DS with InsertRow(). The third argument is the Transaction object to use. If SQLCA is used widely in the application then you can add an overload keeping only two the first arguments and always pass SQLCA.
The function has two main purposes:
The source code of the function is here (you can add it to your utilities NVO):
/********************************************************************************************************************** Acc: public ----------------------------------------------------------------------------------------------------------------------- Dscr: Returns DataStore created dynamically by the supplied SQL SELECT. ----------------------------------------------------------------------------------------------------------------------- Arg: as_sql_select: SQL SELECT to be used as the data source of the created DS. ab_also_retrieve: true = retrieve data after the DS has been created; false = only create DS, don't Retrieve() it. a_tr - Transaction object for created DS. ----------------------------------------------------------------------------------------------------------------------- Ret: DataStore **********************************************************************************************************************/ long ll_rc string ls_err string ls_err_from_pb string ls_syntax DataStore lds if IsNull(as_sql_select) or Trim(as_sql_select) = "" then ls_err = "SQL SELECT not passed." if ls_err = "" and not IsValid(a_tr) then ls_err = "Transaction object not valid." if ls_err = "" then ls_syntax = a_tr.SyntaxFromSQL(as_sql_select, "style(type=grid)", ref ls_err_from_pb) if Len(ls_err_from_pb) > 0 then ls_err = "SyntaxFromSQL() failed:~r~n" + ls_err_from_pb end if if ls_err = "" then lds = create DataStore lds.Create(ls_syntax, ref ls_err_from_pb) if Len(ls_err_from_pb) > 0 then ls_err = "Create() failed:~r~n" + ls_err_from_pb end if if ls_err = "" and not IsValid(lds) then ls_err = "DataStore create failed." if ls_err = "" then ll_rc = lds.SetTransObject(a_tr) if ll_rc = -1 then ls_err = "SetTransObject() failed." end if if ls_err = "" and ab_also_retrieve then ll_rc = lds.Retrieve() if ll_rc = -1 then ls_err = "Retrieve() failed." end if if ls_err <> "" then ls_err += "~r~n~r~nServerName = '" + a_tr.ServerName + "'~r~nLogID = '" + a_tr.LogID + "'" ls_err += "~r~n~r~n###############################################~r~n~r~nSQL SELECT:~r~n~r~n" + as_sql_select MessageBox(this.ClassName() + ".uf_create_ds_by_sql() failed", ls_err) // or throw an exception (better!) end if return lds
