Identical joined tables between client and server

Jez_Poppleton · February 5, 2024, 3:15pm

Hi
I am having an issue where I have joined tables that exist on both the client and server database.

The entities, shortened for brevity.

  TShipments = class
  private
    [ManyValuedAssociation([], CascadeTypeAllRemoveOrphan)]
    [ForeignJoinColumn('CONS_NO', [TColumnProp.Required])]
    FPieces: TList<TShip_Pieces>;
    [Column('CONS_NO', [TColumnProp.Required], 35)]
    FCONS_NO: string;
  property Pieces: TList<TShip_Pieces> read FPieces write FPieces;

  TShip_Pieces = class
  private
    [Association([TAssociationProp.Required], CascadeTypeAll [TCascadeType.Remove])]
    [JoinColumn('CONS_NO', [], 'CONS_NO')]
    FCONS_NO: Proxy<TShipments>;
    function GetCONS_NO: TShipments;
    procedure SetCONS_NO(const Value: TShipments);
    property CONS_NO: TShipments read GetCONS_NO write SetCONS_NO;
end;

The client service call:
procedure TUploadService.UploadShipments;
var
  Result: boolean;
  Shipment: TList<TShipments>;
begin
  Manager.EnableFilter('Status');
  try
    Shipment:= Manager.Find<TShipments>.OrderBy('CONS_NO').List;
    FClient := TXDataClient.Create;
    FClient.Uri := 'localhost:2024/UploadServer'; 
    Result := FClient.Service<IIShipmentsService>.UpdateShipments(Shipment);
  finally
    FreeAndNil(FClient);
  end;
end;

The server implementation.

function TShipmentsService.UpdateShipments(Shipments: TList<TShipments>): boolean;
var
  ACarrier: ICarrier;
  CustSite: TCust_Sites;
  Shipment: TShipments;
begin
  try
    for Shipment in Shipments do
    begin
        //Shipment.Pieces is nil for all Shipments !
        Manager.Replicate(Shipment);
      end;
    end;
    Manager.Flush;
    Result := true;
  finally

  end;
end;

As the tables are joined, when I make the client call, it has the correct Pieces joined on Shipments cons_no.

However when it gets to the server, I have all the shipments data but all of the Pieces data is nil.

I am assuming the join on the server side is somehow "wiping" out the pieces data? But it retains the shipment data. In theory it should retain both? Any suggestions on how to get around this rather niche issue? I can of course send the entities separately but then there's no point in the tables being joined.

Thanks in advance

wlandgraf · February 6, 2024, 1:05pm

Hi @Jez_Poppleton, welcome to TMS Support Center.

Your mapping is wrong. You have two sides of the bidirectional association, but in each side you declare a different CONS_NO foreign key field, so it gets duplicated. You should point to the other side of the association so Aurelius knows they belong to the same one.

This is how you should map FPieces:

    [ManyValuedAssociation([], CascadeTypeAllRemoveOrphan, 'FCONS_NO')]
    FPieces: TList<TShip_Pieces>;

Jez_Poppleton · February 8, 2024, 10:45am

Hi Wagner

Unfortunately that had no effect.

This:

    //[ManyValuedAssociation([], CascadeTypeAllRemoveOrphan)]
    //[ForeignJoinColumn('CONS_NO', [TColumnProp.Required])]
    //FPieces: TList<TShip_Pieces>;

Or this:

[ManyValuedAssociation([], CascadeTypeAllRemoveOrphan, 'FCONS_NO')]
    FPieces: TList<TShip_Pieces>;

Gives me exactly the same problem. The client has data in the joined FPieces in both cases but FPieces is still nil at the server.

wlandgraf · February 8, 2024, 12:54pm

Have you completely recreated the database in client and generated the data again? With the old mapping, the existing data would remain incorrect.

Jez_Poppleton · February 8, 2024, 1:14pm

Hi Wagner
Not sure what you mean? Both server and client databases already exist. The shipments and pieces tables are just a small part of a 60GB database. Both tables already contain data on both server and client. When the client shipment record is inserted with a new record it has a status that indicates it needs to be sent to the server shipment table and a new shipment record is inserted there. There are 5 other tables (Pieces being one of them) that are joined to the shipments table that should also be sent up to the server. So far, only the shipment entity contains data when it hits the server and any other joined entities are nil.

wlandgraf · February 8, 2024, 1:57pm

Inspect your database and check if the fields in both relevant tables are the way you expect them to be, and with the data you expect them to be.

Check the entity structure client side and check if the Ship_Pieces.CONS_NO is filled the way it should be, before it's being sent to the server.

Jez_Poppleton · February 8, 2024, 2:16pm

Hi
Yes I can confirm that everything on the Client side is as it should be. Forgot to mention this is a Firebird 3 database, there are some subtle differences with triggers on the server end and some fields that exist on the server but not the client, but I have used directives for those eg {$IFDEF SERVERAPP} 'entity field' {$ENDIF}. I am going to test sending from client database to another client database to see if the issue is indeed on the server database. Will update shortly

Jez_Poppleton · February 8, 2024, 2:24pm

Can confirm that the same issue exists when attempting to populate client to client as when trying to populate client to server.
As shown in the above screen shots, cons_no "0000006193" has 2 pieces records before being sent to the server. At the server end cons_no "0000006193" has no pieces records...

wlandgraf · February 8, 2024, 2:39pm

What about this:

Are you able to create a minimal project group reproducing the issue, using SQLite?

Jez_Poppleton · February 8, 2024, 4:15pm

I will give it a go

Jez_Poppleton · February 12, 2024, 1:58pm

@wlandgraf Drop box link here. Transfer - Dropbox

I have used the firedac demo to reproduce the issue. You only need the server and the client not the web app.
Cheers

wlandgraf · February 14, 2024, 2:12am

I'm not sure what I'm supposed to do with the apps? I ran the server. I ran the client. I clicked the only button in client "Send", nothing happens.

Jez_Poppleton · February 14, 2024, 9:37am

Hi Wagner
Sorry for the lack of instruction. This is taken from the firedac sql demo in the TMS Demos. There isn't any real visual feedback in terms of grid data etc.

Double click the XDataFireDacSqlServer.exe from the debug folder.
Run the XDataFireDacSqlClient in debug in Delphi and set a breakpoint on:

    Shipment:= Manager.Find<TShipments>.OrderBy('CONS_NO').List;
    Client := TXDataClient.Create;

3. Press Send.
Analyse the shipment entity in the local variables window and you will see the pieces in the shipment entity.

Next step is to reverse this process:

Double clicking the XDataFireDacSqlClient.exe in the debug folder.
Run the XDataFireDacSqlServer in debug in Delphi.
Set a breakpoint in the XDataFireDacSqlServer :

    for Shipment in Shipments do
    begin
    //breakpoint here. Shipments Pieces sent from client is now nil;
    end;

Analyse the shipment entity and you will discover that all the Pieces in the shipment are nil.

Hope this clarifies it a little?

wlandgraf · February 14, 2024, 11:32am

You must have you class to create and destroy and list objects:

{ TShipments }

constructor TShipments.Create;
begin
  FPieces := TList<TShip_Pieces>.Create;
end;

destructor TShipments.Destroy;
begin
  FPieces.Free;
  inherited;
end;

And your ManyValuedAssociation is wrong, I already hinted that but your code didn't change:

    [ManyValuedAssociation([], CascadeTypeAllRemoveOrphan, 'FCONS_NO')]
//    [ForeignJoinColumn('CONS_NO', [TColumnProp.Required])]
//    [ForeignJoinColumn('CARRIER', [TColumnProp.Required])]
    FPieces: TList<TShip_Pieces>;

Jez_Poppleton · February 14, 2024, 11:43am

Hi Wagner I will give that a try.
"ManyValuedAssociation is wrong"
I had also explained that changing this had no effect whatsoever.

Jez_Poppleton · February 14, 2024, 11:51am

Hi Wagner
Thank you so much that works with a constructor.

Just a note I had created these entities with the right click of the Aurelius Connection and selecting the menu "Generate Entities from Database" No constructor was added to the unit.

However when I do this with TMS data modeller it does add the constructor to the unit.

This works with either ManyValueAssociation declaration

wlandgraf · February 14, 2024, 11:56am

"Generate entities from database" does not create many valued associations. You probably added those manually.

Jez_Poppleton · February 14, 2024, 12:07pm

It created them in ship pieces but not in shipments, so yes I had to add them as foreign keys myself.
Either way. Many thanks your speedy assistance, I am happy to mark this as closed

Cheers Wagner

Jez_Poppleton · February 14, 2024, 12:26pm

Er..no it doesn't. On inserting my original associations

[ManyValuedAssociation([], CascadeTypeAllRemoveOrphan)]
[ForeignJoinColumn('CONS_NO', [TColumnProp.Required])]
FPieces: TList<TShip_Pieces>;

..create an SQL insert error with FCONS_NO being declared twice in the insert statement.

For any other readers this is the only association mapping that works:

    [ManyValuedAssociation([], CascadeTypeAllRemoveOrphan, 'FCONS_NO')]
    [ForeignJoinColumn('CONS_NO', [TColumnProp.Required])]
    FPieces: TList<TShip_Pieces>;

Apologies Wagner !

wlandgraf · February 14, 2024, 7:31pm

Still wrong, you don't need the ForeignJoinColumn attribute. Just the ManyValuedAssociation one, as I indicated several times in this topic. Again:

    [ManyValuedAssociation([], CascadeTypeAllRemoveOrphan, 'FCONS_NO')]
    FPieces: TList<TShip_Pieces>;