Cannot query for a null value in a datetime field from an external MySQL DB

Cannot query for a null value in a datetime field from an external MySQL DB

  

Hi all,

We have an external MySQL DB, imported through an extension. One of the fields is datetime. The extension is currently set to "Convert to/from NULL values in Database"

In ServiceStudio, I create an aggregate, I need to get records where CompletedOn is null. There is no filter syntax that will retrieve that record. Things that i've tried:

  • Table.CompletedOn = NullDate()
  • Table.CompletedOn <> NullDate()
  • Table.CompletedOn = TextToDateTime("1900-01-01 00:00:00")
  • Table.CompletedOn = #1900-01-01 00:00:00#
  • Table.CompletedOn < CurrDateTime()
  • others like comparing to empty string or NullIdentifier() give a syntax error

I am positive that there exists a record with a null value on the date field, I put it there myself. Also, an Advanced SQL Query works fine : "SELECT {Table}.* FROM {Table} WHERE  {Table}.[CompletedOn] is null"

When I remove the filter from the aggregate, I can see the record, but the date is shown as 1900-01-01 00:00:00.

Is there some other query that I haven't thought of?

Hello,

I think you will not be able to retrieve this record with an aggregate filtering, because OutSystems does not have the NULL concept.

NullDate is not NULL, but 1900-01-01 00:00:00

When you does not give a value on creating or updating a record, OutSystems give that value automatically.

So, if you put a NULL value in that field using other means that OutSystems, only through an SQL you will be able to retrieve it.

Cheers

Eduardo Jauch

Eduardo,

I'm not familiar with the "Convert to/from NULL values in Database", but I would assume that this would implictely convert NULL dates to NullDate() and vice versa?

Hello Kilian,

While, on retrieving a record that has a field that is not a foreign key (ex: a date time field) and that is NULL in database, the system will convert implicitly from NULL to NullDate() (in this case), when executing the query in database, thus conversion will not be made.

A filter of type table.field = NullDate() will be executed as table.field = 1900-01-01 00:00:00, as far as I know.

I didn't know about an OutSystems function that convert or return a database NULL value... Could you point me it? If it exists would be very interesting.

@eduardo it exists only in integration studio afaik?


J.

That makes sense, as in the integration you are dealing with external tables and code (C#, Java).
But as soon as you bring something to OutSystems, there isn't NULL anymore.

So, if, for some reason, you have NULL values in records in OutSystems and entities (the only way to do it is inserting/updating using SQL), you will not be able to retrieve those records filtering by this record agains NULL outside a SQL.

What is funny is that if you are dealing with an "Id" (primary or foreign keys), the platform will translate a table.id = NullIdentifier() into table.id IS NULL, because, in fact, if you don't provide values for it, the platform "stores" NULL, instead of 0 (zero).

Cheers,
Eduardo Jauch

Eduardo,

For Ids it makes perfect sense to use NULL, as they must be able to be used as foreign keys, and you can't have a foreign key with 0 (unless that record exists).

Kilian Hekhuis wrote:

Eduardo,

For Ids it makes perfect sense to use NULL, as they must be able to be used as foreign keys, and you can't have a foreign key with 0 (unless that record exists).

Exactly! :)

So, if I'm using an SQL, I have to bear in mind that NULLs will exist for id's, but not for the other types of data.
I'm ok with that. I know it and I use it. Still, it is a behavior difference.

:)

P.S.

And in fact, if you don't use Autonumber in an Identifier, you can have a "zero" as id. than the need to allow the "NULL" in identifiers.

Hi all,

Check here the documentation for that External Entity configuration in Integration Studio.

Relevant part is:

Indicates how the default values of the entity's attributes are stored in the database and retrieved from the database:

No conversion to/from Database: all the entity's attributes that have the default value set are stored in and retrieved from the database without any conversion, i.e., it is the default value that is stored in and retrieved from the database.

Convert to/from Null value in Database: all the entity's attributes that have the default value set are stored in the database with the null value. When retrieving data from the database, the null value is converted to the attribute's default value. If no default value is defined for the attribute,  the null value is then converted to the Platform default value for the data type.

Thanks Jorge. The question now is, whether the platform converts a query like Date = NullDate() with Date IS NULL when the Convert to/from Null value in Database is set.

Kilian Hekhuis wrote:

Thanks Jorge. The question now is, whether the platform converts a query like Date = NullDate() with Date IS NULL when the Convert to/from Null value in Database is set.

Where is this option, kilian?


I don't know, I just repeated what the OP said on the topic.

Hum... This seems to be something specific to Extensions.
From the documentation:

Default Value behavior
Indicates how the default values of the entity's attributes are stored in the database and retrieved from the database:
No conversion to/from Database: all the entity's attributes that have the default value set are stored in and retrieved from the database without any conversion, i.e., it is the default value that is stored in and retrieved from the database.
Convert to/from Null value in Database: all the entity's attributes that have the default value set are stored in the database with the null value. When retrieving data from the database, the null value is converted to the attribute's default value. If no default value is defined for the attribute,  the null value is then converted to the Platform default value for the data type.


While the option set by the OP will make NULLs being send to database instead of default values, when retrieving, they will be converted to default values set by the user or platform default values.

The problem is that I don't think the aggregates "know" that the entity is an external entity that have NULL's. To the aggregate, its just another entity, and will not do the comparison differently than it would with an OutSystems entity.

At least is what I think would happen, and the behavior described by the OP says the same thing.

But... Probably is something that should've be take into account...

Eduardo,

The documentation was also posted by Jorge above :).

The aggregate can't know about the conversion behaviour, as it's inside the extension. However, I would think the extension should know, and convert whatever is needed in the query itself. But I have zero technical knowledge on this topic, so I would't know whether this could work.

Kilian Hekhuis wrote:

Eduardo,

The documentation was also posted by Jorge above :).

The aggregate can't know about the conversion behaviour, as it's inside the extension. However, I would think the extension should know, and convert whatever is needed in the query itself. But I have zero technical knowledge on this topic, so I would't know whether this could work.

lol

Sorry, I skipped Jorge's post. :)

The SQL that will be executed by the database is generated by the Aggregate.To the query to take this into account, it would have to know about the conversion option defined by the option. 

While I don't know how this works "under the hood", because you need to define the connection used by the extension in the Service Center, I would say that the platform knows about this, and use it to be able to correctly define the values of the fields depending on the setting. I think the extension does not "execute the query", it just provides information to the platform, that creates the query using this info and send to the database, changing data accordingly when saving / retrieving.

It seems however that in the Aggregate, the comparisons are not affected by this option, being interpreted as if the entity was not an external entity. 

But I'll have to test this to be sure.

And I agree that, being this the case, the Aggregate could change the SQL generated accordingly this option.
I really think the platform has access to it.

Cheers,
Eduardo Jauch