FireDAC FDQuery Outer Join with fdUpdateSQL fails when Inserting Records

[BushAl 0]

I have a FDQuery with a left join between a cost Centre Table (CC) and an Estimate Detail Table (ED) that can have multiple records for a CC. The select is below for ref. The application should allow the ED to be updated and inserted (at simplest in a string grid). The Query will successfully update records in the ED, but fails when trying to insert, stating "update affected [0] rows, while [1] was requested". So I tried adding a FDUpdateSQL linked to the Query and an OnUpdateRecord as follows but it gives exactly the same error (also shown below)

if (ARequest = arUpdate) and (ASender.FieldByName('EstDtl_ID').IsNull) then
 begin
   ARequest := arInsert;
 end;

If anyone has any tips on how to force an insert under this condition I would be very grateful.

Project Builder.exe raised exception class EFDDBEngineException with message '[FireDAC][Phys][FB]-312. Exact update affected [0] rows, while [1] was requested'.

select b.*, a.csc_id as refcsc_id, a.cscdsc as cscdsc, a.cstpls as refcstpls, a.gstinc as refgstinc, a.gstpct as refgstpct from csc a left join estdtl b on a.csc_id = b.csc_id and b.est_id = !est_id

 

[Olli73 6]

When I understood you right, you have problems with inserting new records !?

 

How looks your "TFDUpdateSQL.InsertSQL" ?

[BushAl 0]

The FDQuery SQL is select b.*, a.csc_id as refcsc_id, a.cscdsc as cscdsc, a.cstpls as refcstpls, a.gstinc as refgstinc, a.gstpct  as refgstpct from csc a left join estdtl b on a.csc_id = b.csc_id and b.est_id = !est_id so a fairly straightforward left out join (excuse the 3letter mnemonics)

 

The FDUpdateSQL insert statement is the one generated by FireDac and is:-

 

INSERT INTO ESTDTL
(CSC_ID, ESTDTLDSC, CSTPLS, GSTINC, GSTPCT,
  QTYCLC, QTY, CST, GST, SLE, VAL)
VALUES (:NEW_CSC_ID, :NEW_ESTDTLDSC, :NEW_CSTPLS, :NEW_GSTINC, :NEW_GSTPCT,
  :NEW_QTYCLC, :NEW_QTY, :NEW_CST, :NEW_GST, :NEW_SLE, :NEW_VAL)
RETURNING ESTDTL_ID

 

By default the FDQuery would try and update a query "record" consisting of a real left part and an completely null right part (other than the Est_ID which comes from a parameter in the FDQuery set when choosing the Estimate in a seperate combobox). But it failed with the "update affected [0] rows, while [1] was requested"  and did not insert the record in EstDtl.

 

So I then added the code shown in the original post in an  OnUpdateRecord event on the FDQuery, basically changing the ARequest from arUpdate to  arInsert if the right part is null. This actually inserts the record then fails with the same message. So maybe there is some way to suppress the message (ideally just in this case) but it all happens somewhere in the depths of livebindings...

 

Thanks for looking at this - my first time combining FireDac which seems very powerful and livebindings. I have normally used IBX components. In case its useful its connecting to a Firebird Database.

Al

[Olli73 6]

In the query I would include the b.ESTDTL_ID to have a unique ID for "ESTTDL table" and in the insert I would set b.est_id to a proper value. What is the content if this est_id field? Can it be null?

[Olli73 6]

And is this condition true on insert: 

if (ARequest = arUpdate) and (ASender.FieldByName('EstDtl_ID').IsNull) then

Normally it should be insert and not update for new record and the estdtl_id is not in the query, so that should raise an ecxception!?

 

[Olli73 6]

And

ARequest := arInsert;

ARequest is no var parameter, so changing should have no affect!?

[BushAl 0]

Thanks very much for the help.

 

I will try as requested and report back.

I had not noticed the insert happening before I added the  OnUpdateRecord and FdUpdateSQL as I just cancelled the program when the exception occurred, so I will go back and test that - its possible that the insert would happen under that condition.

 

To be clear the Est_Id will always have a non null value when doing this insert, while the EstDtl_Id will be null for an insert as a value is generated by the Database