Nil references problem

ASiwon · 2015-02-03 22:06:42

Hello,

I'm newbie in mORMot. Now I'm trying to write my first mORMot powered project. I'm very impressed - this library is amazing. Thank you very much for writing this project.

I have problem with storing object to the external Oracle database. When object has only simple types there is no problem to write it to the database. But in some cases I have object with references to other objects. Here is sample code:

TUser = class(TSQLRecord)
private
  FName: RawUTF8;
published
  property Name: RawUTF8 read FName write FName;
  // and other properties
end;

TOperation = class(TSQLRecord)
  private
    FUser: TUser;
    ...
  published
    property User: TUser read FUser write FUser;
    // and other properties

When I'm trying to store instance of class TOperation to the database I'm getting an error. Here is log:

20150203 21515014 SQL   	TSQLDBOracleStatement(035B4730) update U_EKS.T_WAGONY_WAZENIA_IMPORTY set WWI_ZDW_ID=161,WWI_GRANICA_OBC=56000,WWI_NR_WAGONU='84-51-7582810-0',WWI_NR_WAGONU_BLAD=0,WWI_NR_WAGONU_FILTR='001828571548',WWI_NR_WAGONU_RODZ=0,WWI_WE_ID=0,WWI_TARA_Z_BEL=22700,WWI_UZ_ID=0,WWI_WAGOWY='',WWI_CZAS_WAZENIA='2014-01-30 23:04:54',WWI_WAZENIE_ID=407688,WWI_TOWAR_NAZWA='',WWI_TOWAR_NUMER='',WWI_MASA_BRUTTO=22750,WWI_MASA_NETTO=0,WWI_POSREDNIE=0,WWI_RODZAJ_WAZ=2,WWI_POTWIERDZ=0,WWI_SKLAD='2',WWI_STATUS=-8,WWI_TECHNOLOGICZNY=0 where WWI_ID=13795
20150203 21515014 EXC   	ESQLDBOracle ("TSQLDBOracleStatement error: ORA-02291: integrity constraint (U_EKS.FK_WWI_BTK_UZYTKOWNICY) violated - parent key not found") at 006E34E8  stack trace API 00602E68 00602E90

Property User is mapped to the wwi_uz_id field. MORMot is trying to write value 0 to this field. I have read NULL handling section in documentation and I know this is correct operation. But in my database are integrity constraint created. Its because there are many modules which are not using mORMot and for now they need this constraints to work correctly.

So the question is: Is it possible in mORMot to store to the databases fields NULL instead of zero value for references which have value nil? I found an option StoreVoidStringAsNull but I can't find nothing for nil references. If this is impossible I will write database trigger which will updating zero value to the NULL but I think this is very incorrect way.

best regards
Adam Siwon

ab · 2015-02-04 10:08:31

There is no option for this yet.
We do not use the DB for integrity references, but only at ORM level.

Since we need the 0 to be replaced into null only for foreign references, it should be implemented not in SynDB, but in mORMotDB (the unit which link SynDB to the ORM).
Possible, but not easy.

Perhaps there is an option about the constraints definition in Oracle?
Or a small PL/SQL which may take place before the integrity check, replacing the 0 with a null?

ASiwon · 2015-02-04 12:15:43

Hello,

Perhaps there is an option about the constraints definition in Oracle?

Unfortunately in Oracle there is no option for the constraint definition to solve this problem. In constraint definition it is impossible to use expression like: nullif(column, 0). Only column names must be used.

Or a small PL/SQL which may take place before the integrity check, replacing the 0 with a null?

As I wrote earlier I can just to create the trigger for the table which will replace 0 values with a null for some fields. But it must be created manually for particular tables. Neverthless I do this if it will be necessary and changes in mORMot code are too complicated to do.

best regards
Adam Siwon

ab · 2015-02-04 13:36:55

I took a deeper look at the code, and in fact, integration of this change may be complicated at mORMot level.
The main problem is that StoreVoidStringAsNull is an option for all string values, but here we need to change the integer values, only for some columns - we want it for TSQLRecord published properties, but not for integer published properties for instance...
And there are several ways of going from mORMot.pas to SynDB.pas: with CRUD operations, with direct SQL, with BATCH mode...
So it is definitively not an easy task!

ASiwon · 2015-02-05 10:01:26

Hello,

with trigger in the database everything works correctly.

best regards
Adam Siwon

mORMot Open Source

#1 2015-02-03 22:06:42

Nil references problem

#2 2015-02-04 10:08:31

Re: Nil references problem

#3 2015-02-04 12:15:43

Re: Nil references problem

#4 2015-02-04 13:36:55

Re: Nil references problem

#5 2015-02-05 10:01:26

Re: Nil references problem

Board footer