dataset.getAsText issue

Classic Servoy
1

We get narrative reports via a backend hospital database that parses the reports out as one line per record. In recreating the report in ‘readable’ fashion I’m using the ‘.getasText’ function which works very nicely. The only issue is that each ‘record/line’ of course is faithful to the report so any time there is an ‘empty’ line in the report that also creates a record with the ‘report’ column empty. The .getAsText function in Servoy though puts the word ‘null’ on that line instead of leaving it ‘simply empty/blank/null’. I am using “dataset.getAsText(‘’,‘\n’,‘’,false)”.

I can obviously remove it using regex in Servoy or in Oracle after the fact but does anyone know if there is a way to prevent that in the first place?

Thanks for any help…

2

Hi John,

In Visual FoxPro there is a function called NVL which tests for this. It is simulated by the following function you could put in globals to get the same result:

// Function : NVL()
/**
* Returns eExpression2 if eExpression1 evaluates to a null value.
* Returns eExpression1 if eExpresion1 is not a null value.
* Returns null if both, eExpression1 and eExpression2, evaluates to a null value.
* 
* @param {Object} eExpression1 - Main expression to evaluate
* @param {Object} eExpression2 - Alternative value to eExpresssion1
*/
function NVL(eExpression1, eExpression2){
    var eReturn=null;
    if(eExpression1!=null){
        eReturn=eExpression1;
    }else{
        if(eExpression2!=null){
            eReturn=eExpression2;
        }
    }
    return eReturn;
}

Then you can do something like this:

globals.NVL(dataset.getAsText(etc.),"\n");

Btw. this function is part of the VFP2Servoy Toolkit and was contributed by Juan Antonio Santana Medina.

3

Thanks Omar but what I was looking/hoping for was simply a setting on .getAsText itself to handle this. Otherwise since I’m dealing with a dataset anyway I can probably just add a CASE statement in the SQL itself to put a space character on those columns that are otherwise empty/null and presumably .getAsText will then leave it alone. That should be as quick or quicker and less coding.

4

Hi John,

No need to use a CASE statement which is pretty verbose. You can simply use the COALESCE statement.

SELECT COALESCE(column1,''), COALESCE(column2,''), COALESCE(column3,'') FROM myTable

Hope this helps.

5

I actually didn’t express that last reply correctly. I need to put a space character in those ‘records’ that have a null value, not the ‘columns’. I am only ever dealing with one column. But otherwise you are correct Robert about the CASE statement. I’ve just always been rather fond of it because it reads better than the COALESCE function! (The NVL function also would work fine here and is equally terse! Perhaps if my code gets more pithy it will spill over into my emails :D ).