Michael Zuskin

Contents (click on a topic for quick jump):

  1. iif()
  2. nvl()
  3. iin()
  4. Convert DataWindow to... zebra!
  5. Save DataStore from Debugger
  6. Ensure SetItemStatus() is always successful
  7. How to overcome long-circuit evaluation
  8. Oracle packaged procedure as DW data source
  9. DataStore by dynamic SQL


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 "IIf" function of Visual Basic (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"
	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):

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

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

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

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

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).

Convert DataWindow to... zebra!

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)


Save DataStore from Debugger

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.

Ensure SetItemStatus() is always successful

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:

New! NewModified! DataModified! NotModified!
New! -Yes Yes No
NewModified! No -Yes New!
DataModified! NewModified! Yes - Yes
NotModified! YesYes 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!)

How to overcome long-circuit evaluation

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()


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

Oracle packaged procedure as DW data source

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:

  1. Create a temporary standalone procedure with exactly the same name, arguments and records set (returned via REF_CURSOR).
  2. Select that proc in the dropdown to be the data source of your DW (reconnect to the database if it doesn't appear).
  3. Save the DW.
  4. Open the DW's source (right click -> "Edit Source").
  5. Find my_proc and add my_package. (with a dot!) just before it, so the result is my_package.my_proc.
  6. Save the DW. Good job, you've done that!
  7. Drop the temporary standalone proc.

DataStore by dynamic SQL

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:

  1. To prevent overloading of PBLs with a large number of one-off DataObjects.
  2. To avoid client-side cursors (including ones in the formats 3 and 4 of dynamic SQL).

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

blog comments powered by Disqus

Keywords for search engines: POWERBUILDER TRICKS TIPS

Copyright © zuskin.com

Join me in LinkedIn!

free counters