TFDUpdateSQL Problem onInsert

[Osama Ghazal 3]

Hi All,

I am using MySQL Database and I create a query called qryProducts like this:

SELECT  p.ProductID,  p.Barcode,  p.ProductName,  p.CategoryID,  p.LevelNo,  p.ProductType,  p.UserID,  p.IsService,  pl.SalePrice,  pl.MinSalePrice,  pl.Service
FROM products p
  LEFT JOIN pricelists pl  ON p.ProductID = pl.ProductID

 

pricelists table primarykey is ProductID and it is related with products by the ProductID field

 

I connect the query with a datasource and with dbEdits to fill the values

I am using also FDUpdateSQL and i fill InsertSQL with this sentence:

Insert INTO pricelists (ProductID,  SalePrice,  MinSalePrice,  Service)
VALUES (:ProductID,  :SalePrice,  :MinSalePrice,  :Service)

 

the DFM file for qryProducts is like this

object qryProducts: TFDQuery
    BeforePost = qryProductsBeforePost
    Connection = FDConnection1
    UpdateOptions.KeyFields = 'ProductID'
    UpdateObject = FDUpdateSQL2
    SQL.Strings = (
      'SELECT'
      '  p.ProductID,' ...

 

qryProductsBeforePost : qryProductsProductID.Value := fdConnection1.ExecSQLScalar('SELECT IFNULL(MAX(ProductID) + 1, 1) AS NewID FROM products');

 

and the result is ok = 151 this productid is not exists

 

I write qryProducts.Append;

and fill the values by dbEdits

then I write qryProducts.Post;

 

it gives me this error:

[FireDAC][Phys][MySQL] Cannot add or update a child row: a forign key contraint fails(pricelist', Constraint 'pricelists_prod...'foreign key ('productid'' references 'products'(productID'on delete cascade on update cascade.

 

i tried many solutions as I put a    UpdateOptions.KeyFields = 'ProductID' and remove it later, i put the update table pricelists and so on, with no result

[programmerdelphi2k 237]

@Osama Ghazal

 

I think there is a basic error here!

You cannot insert a new product in the price list if it does not exist in the products table, however, to insert a new product in the products table, you must inform all required fields (id, name, price, etc...) in this table.
At the end, then, you could get the "ID" of this new product to use in the price list table!
Thus "ID" is the primary key in the product table, and it is the foreign key in the price list table!

Typical Master-Details usage!

Edited May 31, 2023 by programmerdelphi2k

[Osama Ghazal 3]

I put it already in this event

qryProductsBeforePost : qryProductsProductID.Value := fdConnection1.ExecSQLScalar('SELECT IFNULL(MAX(ProductID) + 1, 1) AS NewID FROM products');

and it gives me a new id 151

[Hans J. Ellingsgaard 21]

The problem is your join in the query. Sometimes FireDAC is smart enough to write an update script that's ignoring the join fields, but if not, then you have to write the update query yourself. If I remember correctly there is a description of how to do it in Cary Jensen's book. ( I saw you mentioned it in an another post)

[Osama Ghazal 3]

12 hours ago, Hans J. Ellingsgaard said:

The problem is your join in the query. Sometimes FireDAC is smart enough to write an update script that's ignoring the join fields, but if not, then you have to write the update query yourself. If I remember correctly there is a description of how to do it in Cary Jensen's book. ( I saw you mentioned it in an another post)

Thanks,

I have many solutions for this but I am trying to optimize my code and using less code as I can.

I will look for other solution.

[Osama Ghazal 3]

22 hours ago, programmerdelphi2k said:

@Osama Ghazal

 

I think there is a basic error here!

You cannot insert a new product in the price list if it does not exist in the products table, however, to insert a new product in the products table, you must inform all required fields (id, name, price, etc...) in this table.
At the end, then, you could get the "ID" of this new product to use in the price list table!
Thus "ID" is the primary key in the product table, and it is the foreign key in the price list table!

Typical Master-Details usage!

@programmerdelphi2k

 

Ok, I Update the InsertSQL as the following:

 

INSERT HIGH_PRIORITY INTO products
(ProductID, Barcode, ProductName, CategoryID, LevelNo, ProductType, QtyHasDigits, IsService, MinQty, UserID)
  VALUES
(:ProductID, :Barcode, :ProductName, :CategoryID, 4, 0, 0, 0, 0, 1);

Insert INTO pricelists
(ProductID,
  SalePrice,
  MinSalePrice,
  Service)
VALUES
(:ProductID,
  :SalePrice,
  :MinSalePrice,
  :Service)

 and it is success.

 

Thanks you

 

[programmerdelphi2k 237]

@Osama Ghazal