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:
MessageBox("", ls_text);
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) |
| number | zero |
| date | 01/01/1900 |
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;
Tags: cursors null nullable PGSql powerbuilder PowerScript
Posted in: Power Builder
February 2, 2010
| 
Daniel Bozdoc | 
2 Comments
2 Responses
June 17, 2010 1
I’ll come back again to find out your upcoming post! Excellent job done!
September 22, 2010 2
Good post and this enter helped me alot in my college assignement. Say thank you you for your information.
Leave a Reply