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:

    if (IsNull(as_text)) then
        MessageBox("", "null value received!");
        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;
    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;
    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";
        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.

2 thoughts on “Handling NULL values in PowerBuilder”
  • I’ll come back again to find out your upcoming post! Excellent job done!

    June 17, 2010 at 8:41 am
  • Good post and this enter helped me alot in my college assignement. Say thank you you for your information.

    September 22, 2010 at 5:08 pm

Comments are closed.

By continuing to use the site, you agree to the use of cookies. More information

The cookie settings on this website are set to "allow cookies" to give you the best browsing experience possible. If you continue to use this website without changing your cookie settings or you click "Accept" below then you are consenting to this.