Handling NULL values in PowerBuilder
When it comes to NULL values, PowerBuilder treats them a little different than other languages. Not only that, when null is added, compared to or concatenated with any variable the entire result will become null, but also when given a null parameter most PowerBuilder functions will simply return NULL and perform no action.
To avoid generating possible errors, the solution is to check for the null values and proceed accordingly.
A common example of such case is passing a null text value to the MessageBox function. The function will do simply nothing and the flow will continue with the next line of code.
So instead of:
one can wrapp the function like this:
f_MessageBox(as_text) if (IsNull(as_text)) then MessageBox("", "null value received!"); else MessageBox("", as_text); end if
On the other hand, in PowerBuilder unassigned variables/columns can take different values depending on their source. Table below shows how an unassigned PowerScript variable will be initialized:
|string||EmptyString (zero length string)|
When external stored procedures or functions need to be called with explicit null value parameters or for any other particular reason when a null value is needed for a variable leaving the value unassigned will generate wrong unexpected results. To proper specify a NULL value, use the SetNull function.
int ln_location_id = 1; string ls_location_name; SetNull(ls_location_name); prepare sqlsa from "INSERT INTO locations values (?,?)"; execute sqlsa using :ln_location_id, :ls_location_name;
Most SQL databases (eg PostgreSQL) do not use null in arithmetic comparisons.
For example following queries will return 0 records.
SELECT * FROM locations WHERE "name" = null SELECT * FROM locations WHERE "name" IN (null)
To get the desired result must use:
SELECT * FROM locations WHERE "name" IS NULL
A common situation here is where null parameters are supplied to a stored procedure and the results are not what should expect. In this case must evaluate such variables and use a dynamic cursor to provide specific parameters to the function:
string ls_query_where_clause, ls_query; DECLARE crs_stock DYNAMIC CURSOR FOR SQLSA; ls_query_where_clause = " WHERE article_id = '" + string(ll_article_id) + "'"; if (isnull(ll_location_id)) then ls_query_where_clause += " AND location_id ISNULL"; else ls_query_where_clause += " AND location_id = '" + string(ll_location_id) + "'"; end if ls_query = "SELECT quantity, cost FROM inv_stock" + ls_query_where_clause; PREPARE SQLSA FROM :ls_query; sqlca.checksql(true, ""); OPEN DYNAMIC crs_stock; sqlca.checksql(true, ""); FETCH crs_stock INTO :ldbl_qty, :ldbl_cost; // read lines CLOSE crs_stock;
Finally, there’s another very important peculiarity of what does Cialis that brings it so high above its alternatives. It is the only med that is available in two versions – one intended for use on as-needed basis and one intended for daily use. As you might know, Viagra and Levitra only come in the latter of these two forms and should be consumed shortly before expected sexual activity to ensure best effect. Daily Cialis, in its turn, contains low doses of Tadalafil, which allows to build its concentration up in your system gradually over time and maintain it on acceptable levels, which, consequently, makes it possible for you to enjoy sex at any moment without having to time it.