mORMot Open Source

sonadorje

Member

Registered: 2016-12-20

Posts: 13

null value is forced convert to 0 in json

in my REST server, I'v defined a function,it will return a T*ObjArray.
My Database is AdventureWorks2008R2, the mapping table is Sales.Customer

type
TSQLCustomer = class(TSQLRecord)
  protected
    fCustomerID: Int64;
    fPersonID: Int64;
    fStoreID: Int64;
    fTerritoryID: Int64;
    fAccountNumber: RawUTF8;
    frowguid: RawUTF8;
    fModifiedDate: TDateTime;
...
end

function TInfraRepoCustomer.GetCustomerByID(aID: int64): TCustomerObjArray;
begin
  //result := ORMGetAllAggregates(aAggregates);
  ServiceContext.Factory.Rest.RetrieveListObjArray(Result, TSQLCustomer, 'CustomerID = ?', [aID]);
end;

on the client ,I call the function,and use ObjectToJSON retrieve a JSON data:

{"CustomerID":11,"PersonID":0,"StoreID":1022,"TerritoryID":6,"AccountNumber":"AW00000011","rowguid":"{750F3495-59C4-48A0-80E1-E37EC60E77D9}","ModifiedDate":"2008-10-13T11:15:07"}

PersonID is null in database,now it is forced convert to 0.
but NULL is diffrent from 0,How to get real value "PersonID":null?

Last edited by sonadorje (2017-02-15 09:31:16)

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,659

Website

Re: null value is forced convert to 0 in json

Yes, because Int64 is not nullable, by definition.

You may try to use TNullableInteger instead of Int64.
See https://synopse.info/files/html/Synopse … l#TITL_177

sonadorje

Member

Registered: 2016-12-20

Posts: 13

Re: null value is forced convert to 0 in json

@AB Thank master!
now its json data is null.

sonadorje

Member

Registered: 2016-12-20

Posts: 13

Re: null value is forced convert to 0 in json

Hi AB Master:
    I have another question.I'v defined a function "Add"  by following dddDomUser***.pas sample

function TInfraRepoCustomer.Add(const aAggregate: TCustomer): TCQRSResult;
begin
  result := ORMAdd(aAggregate);
end;

In the table [Sales.Customer],  CustomerID Field is Auto-increment
I write the code to test

   vCustomer := TCustomer.Create;
    try
      vCustomer.PersonID := 888;
      vCustomer.StoreID := 888;
      vCustomer.TerritoryID := 999;
      vCustomer.AccountNumber := 'sonadorje';
      vCustomer.rowguid := '{BC106C0A-964F-4FC1-BD77-6C978B70A531}';
      vCustomer.ModifiedDate := Now;
      if vCmd.Add(vCustomer) = cqrsSuccess then
         Caption := 'S_OK';
    finally
      vCustomer.Free;
    end;

on the server side, log is:
IDomCustomerCommand.Add[{CustomerID:0,PersonID:888,StoreID:888,TerritoryID:999,AccountNumber:"sonadorje",rowguid:"{BC106C0A-964F-4FC1-BD77-6C978B70A531}",ModifiedDate:"2017-02-17T10:32:14"}]

and the data not really be inserted into the database.
Have I lost some mainly process?

Last edited by sonadorje (2017-02-17 05:08:41)

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,659

Website

Re: null value is forced convert to 0 in json

Why is nothing inserted?
Use the debugger to find out.

sonadorje

Member

Registered: 2016-12-20

Posts: 13

Re: null value is forced convert to 0 in json

Hi AB Master!

   I have traced into "ORMAdd" function by debugger.
I found that SQL is generated by framework:

insert into dbo.Customer (CustomerID,PersonID,StoreID,TerritoryID,AccountNumber,rowguid,ModifiedDate) values (?,?,?,?,?,?,?)

How to generated properly sql? i.e
insert into dbo.Customer (PersonID,StoreID,TerritoryID,AccountNumber,rowguid,ModifiedDate) values (?,?,?,?,?,?)

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,659

Website

Re: null value is forced convert to 0 in json

I guess the CustomerID is the primary key of the table (TSQLRecord.ID), so it is mandatory here.
It is generated before insertion, by the framework ORM.

You may have some problems with your field mapping definition with the external table.

sonadorje

Member

Registered: 2016-12-20

Posts: 13

Re: null value is forced convert to 0 in json

Hi ab master!
    I have modified uian2000\DDDPersistence sample for test.
I added a member to TSomeEntity.

type
  TSomeEntity = class(TSynPersistent)
  protected
    fSomeID: int64;
    fCaption: RawUTF8;
  published
    property SomeID: int64 read fSomeID write fSomeID;
    property Caption: RawUTF8 read fCaption write fCaption;
  end; 

  //then mapped field as:
  aModel := TSQLModel.Create([TSQLRecordSomeEntity],'ROOT');
  vProperties := TOleDBMSSQL2012ConnectionProperties.Create('.','3MIS','','');
  VirtualTableExternalRegister(aModel,TSQLRecordSomeEntity,vProperties,'SomeEntity');
  aModel.Props[TSQLRecordSomeEntity].ExternalDB.MapField('ID', 'SomeID');
  RestServer := TSQLRestServerDB.Create(aModel, ':memory:', false);
  RestServer.CreateMissingTables;

when run the project, it create a table named SomeEntity, and everything is ok.
but where I alter SomeID to a auto-increment field :
"CREATE TABLE [dbo].[SomeEntity](
    [SomeID] [bigint] IDENTITY(1,1) NOT NULL,
    [Caption] [nvarchar](50) NULL,
CONSTRAINT [PK_SomeEntity] PRIMARY KEY CLUSTERED
(
    [SomeID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]"
the above same problem occur.
I traced into ORMAdd,at the code
soInsert: // mPost=INSERT with the supplied fields and computed ID
              Decode.Decode(P,...);
It seem that the framework don't know the field is a auto-increment,so the function return a SQL:
SQL := JSONDecodedPrepareToSQL(Decode,ExternalFields,Types,Occasion,[]);
'insert into dbo.SomeEntity (SomeID,Caption) values (?,?)'

In many database, a table has a auto-increment field,how to resolve the issue?

Last edited by sonadorje (2017-02-21 06:31:05)

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,659

Website

Re: null value is forced convert to 0 in json

Auto-incremented primary key are not identified by the ORM, nor should they be used.
The root cause is that RDBMS are not consistent about creating auto-incremented IDs - some don't even implement them.
And that a NoSQL DB like MongoDB don't have something similar to sequences.
As documented, and as I wrote above, the primary key is expected to be computed by the ORM, not auto-incremented by the DB.

To disable this behavior, define a fake non 0 ID value in TSQLRestStorageExternal.EngineAddForcedID.
See https://synopse.info/files/html/api-1.1 … C_45B30F67

sonadorje

Member

Registered: 2016-12-20

Posts: 13

Re: null value is forced convert to 0 in json

Hi ab master!
    I traced into the procedure "Check(cqrsSuccess = cmd.Commit)"  which in the DDDPersistenceMain.pas once again,
Its traceflow is:
TDDDRepositoryRestCommand.Commit->InternalCommit->TSQLRest.BatchSend->TSQLRestServer.EngineBatchSend
->TSQLRestStorageExternal.InternalBatchStop

in the procedure, I add the code after

case Occasion of
            soInsert: // mPost=INSERT with the supplied fields and computed ID

if (fEngineAddForcedID > 0)  then
                  Decode.Decode(P,nil,pQuoted,0,true) else
...

Now the issue is resolved!

I found that fEngineAddForcedID is only in the function TSQLRestStorageExternal.ExecuteFromJSON.