mORMot Open Source

larand54

Member

Registered: 2018-12-25

Posts: 104

Checking against NULL data from DB

Using ORM I want to check if a field is null or not like this:

function TInfraRepoTicket.SelectUO: TCQRSResult;
begin
  result := ORMSelectAll('DateOpened is ?',[null]);
end;

This doesn't seem to work as I never get a hit.
How can I solve this problem?

---

Delphi-11, WIN10

pvn0

Member

From: Slovenia

Registered: 2018-02-12

Posts: 211

Re: Checking against NULL data from DB

Did you check the documentation? This should help you solve your problem : 5.1.14. TNullable* fields for NULL storage

larand54

Member

Registered: 2018-12-25

Posts: 104

Re: Checking against NULL data from DB

Yes I have read that and made all TDateTime fields of type TNullable. But it didn't help.
You see, I'm new to mORMot and not so familiar with variants. I have not so easy to understand how I should use it here.

I doubt that I can write the code like I did in my example above even though it feels natural for me as I'm more used to SQL.

I just test mORMot occasionally when I have time. I'm looking for a way to use it in a larger project and try to find as many pitfalls as possible before I proceeds with a larger project.
I've found a lot of them already and some of them are solved but this problem are still unsolved.

---

Delphi-11, WIN10

macfly

Member

From: Brasil

Registered: 2016-08-20

Posts: 374

Re: Checking against NULL data from DB

Activate the logs and see which SQL was generated in this query.

I avoid using null fields but I imagine that it is generating an empty string
DateOpened is ""

Why not use a direct form:
'DateOpened is NULL'

larand54

Member

Registered: 2018-12-25

Posts: 104

Re: Checking against NULL data from DB

That was a good idea, that worked! Normally I like to use standard ORM-style instead of plain SQL but sometimes it seems to be necessary to breake that rule.
If someone have a better idea using ORM you're welcome, but otherwise I would say this is now solved.
Thank's

---

Delphi-11, WIN10

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,672

Website

Re: Checking against NULL data from DB

Writing 'DateOpened is null' could be the way I did it, even when using a nullable type.