mORMot Open Source

ASiwon

Member

From: Poland

Registered: 2015-01-30

Posts: 82

Differences in ORM code for SQLIte and Oracle.

Hello,

I have problem with using in mORMot one code for two databases (sqlite and Oracle) My model looks like:

  TBkTowar = class(TSQLRecord)
  private
    FTextId: RawUTF8;
  published
    property TextId: RawUTF8 index 18 read FTextId write FTextId;
  end;

I want to retrieve list of the objects where TextId has not empty value. So I wrote following code:

  towaryBk := TBkTowar.CreateAndFillPrepare(FBkClient, '% <> ?', ['TextId'], ['']);

For sqlite (memory file) it works correctly but not for Oracle. To retrieve objects with not empty values from Oracle database I must to change code to the following:

  towaryBk := TBkTowar.CreateAndFillPrepare(FBkClient, '% is not null', ['TextId'], []);

Its because Oracle treats empty string as null. Now it works correctly for Oracle. But now objects from sqlite are not retrieved correctly. I could to change code to the following way:

  towaryBk := TBkTowar.CreateAndFillPrepare(FBkClient, '% is not null or % <> ?', ['TextId', 'TextId'], ['']);

Last time I wrote: It will works correctly for both databases but it is not really clean code. But I made mistake and this will also not works correctly for both databases.

Is it any way in mORMot to use one condition which will be correctly works for both or even for other databases?

Last edited by ASiwon (2015-09-10 09:01:31)

---

best regards
Adam Siwon

hnb

Member

Registered: 2015-06-15

Posts: 290

Re: Differences in ORM code for SQLIte and Oracle.

You can try to use TNullableUTF8Text instead of RawUTF8.

best regards,
Maciej Izak

Last edited by hnb (2015-09-10 09:57:37)

---

best regards,
Maciej Izak

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,660

Website

Re: Differences in ORM code for SQLIte and Oracle.

In the current state of the ORM, I do not see any workaround than writing '% is not null or % <> ?'.
I doubt TNullableUTF8 type, as proposed by hnb, would change anything here, since it is a low-level internal Oracle behavior.

Sounds like if Oracle is pretty weird, and non standard, for this.
AFAIK from SQL, there should be a difference between NULL and a value.

Sounds like a reminiscence from ancient history.
See http://stackoverflow.com/questions/203493

We may implement something better at ORM.
We may translate a field <> '' where condition as a field is not null for Oracle.

A workaround, at business logic level, may be to store a space (' ') instead of a void string ('') in the field.
It would let the '% <> ?' condition work as expected...

ASiwon

Member

From: Poland

Registered: 2015-01-30

Posts: 82

Re: Differences in ORM code for SQLIte and Oracle.

For me it was also strange behaviour in Oracle. Empty string is empty string not a null. For this particular case I have changed my code and I'm retrieving all records from table and I'm testing values after retrieving them. In this case I can do that because there are not much objects in the table. But in other cases it could be not so easy.

The similar problem exists with using functions in where clauses in mORMot. Some time ago I have tried to use following condition: upper(fieldName) = ?. For sqlite it is possible and it's working, but for Oracle it's not working - an exception is raised while retrieving objects. But in this case I'm afraid it is mORMot related problem because in Oracle SQL exists function upper.

---

best regards
Adam Siwon

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,660

Website

Re: Differences in ORM code for SQLIte and Oracle.

If the WHERE clause is complex, current implementation of TSQLRestStorageExternal.AdaptSQLForEngineList won't handle it.
Put a breakpoint in this method, and see what's happen.
AFAIK in current implementation, functions (and expressions via parenthesis) are not handled in the WHERE clause.
There is roof for improvement here.

If TSQLRestStorageExternal.AdaptSQLForEngineList returns false, SQLite3 virtual tables would be used, which are much slower than direct SQL execution on Oracle...

So for working with a legacy or external database, in such case there is no efficient solution but tune the SQL and directly execute it on the server, at SynDB level, by-passing the ORM.