SQL output of Outsystems is null (DB is " " or 0, not null)

Hello everybody,

When I fetching data from the database using SQL statement, I have a problem:

In DB is " " (1 space) or 0 (number) and it is not primary key (can null),

After fetched, its is null in output Outsystems (In fact it is " " or 0, not null).

Of course its displays undefined on Data Grid (nothing for display in cell).

Has anyone had the same problem and solved it?

Thanks.

Hi Huyen,

I had that issue making Math functions in the advanced query. So, if you're trying to sum or calculate something it was returning me wrong values. I guess the solution pass with making conditions in the advanced query and when it's NULL or something just put the result you want. Where it's a generic example:

CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    WHEN conditionN THEN resultN
    ELSE result
END;

Hope it helps! 

Hi Huyen,

A single database column (and hence, OutSystems Entity Attribute) cannot contain both a string (Text) or a number (Integer, Decimal), and OutSystems doesn't allow NULL values for anything other than a foreign key. NULL values in foreign keys (of type Integer or Long Integer) will have the numeric value 0 but should be tested with NullIdentifier().

The Data Grid component is fed via a REST service, if I'm not mistaken, which means that after fetching the data from the database, it is converted to JSON and back, so any "default" values are not sent.

So to sum up, I don't understand what your problem exactly is, let alone how you can solve it.

Tiago Gomes wrote:

Hi Huyen,

I had that issue making Math functions in the advanced query. So, if you're trying to sum or calculate something it was returning me wrong values. I guess the solution pass with making conditions in the advanced query and when it's NULL or something just put the result you want. Where it's a generic example:

CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    WHEN conditionN THEN resultN
    ELSE result
END;

Hope it helps! 

Thanks Tiago Gomes,

I tried it but the result was the same.

CASE TextField
    WHEN ' ' THEN ' ' ELSE TextField END        => '' (nothing)

CASE NumberField
    WHEN 0 THEN 0 ELSE NumberField END        => '' (nothing)

Regards.

Kilian Hekhuis wrote:

Hi Huyen,

A single database column (and hence, OutSystems Entity Attribute) cannot contain both a string (Text) or a number (Integer, Decimal), and OutSystems doesn't allow NULL values for anything other than a foreign key. NULL values in foreign keys (of type Integer or Long Integer) will have the numeric value 0 but should be tested with NullIdentifier().

The Data Grid component is fed via a REST service, if I'm not mistaken, which means that after fetching the data from the database, it is converted to JSON and back, so any "default" values are not sent.

So to sum up, I don't understand what your problem exactly is, let alone how you can solve it.

Thanks Kilian Hekhuis,

Exactly my problem is "...it is converted to JSON and back, so any "default" values are not sent."

My cell has process related to "cell-dirty" like validation, calculation, dynamic input, dynamic cell style, dynamic button...

In DataBase it is " " (1 space), but on the screen, cell is undefined and when double-click in it, it become cell changed (cell-dirty) even though I didn't input anything.

It must be " " (1 space) and when double clicked it still " " (1 space) and nothing changed (not become cell dirty).

I've actually handled this problem manually, but it exist in OSP so I hope it should improve in next versions.

------ in DB is " " but SQL output null ------

Thanks & Best Regards.

Hi Huyen,

Do you use an Oracle database? Because iirc, one space is used to indicate a "null" string (since on Oracel, you cannot have a 0-length string).

Kilian Hekhuis wrote:

Hi Huyen,

Do you use an Oracle database? Because iirc, one space is used to indicate a "null" string (since on Oracel, you cannot have a 0-length string).

Hi Kilian Hekhuis,

Exactly, I am using Oracle database, so there is no other way than to handle it manually? ^^


If there's a space " " in a text column (Attribute) in an OutSystems Oracle database, OutSystems will automatically convert it to an empty string (""). You cannot have a single space in a Text Attribute when using an Oracle database and expect it to be read as a space.

Thanks Kilian Hekhuis,

>> You cannot have a single space in a Text Attribute when using an Oracle database and expect it to be read as a space.

If it is primary key, 1 space can be set and get. ^^


So there will be cases of data loss(" " space =>  "" null), or incorrect data processing ("" null => " " space)

I will manual handling.

Regards.