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 

[BushAl 0]

Sorry - missed one of your points. Both Est_Id and EstDtl_Id are in the query (as a result of select b.*). And you are of course correct that the change to ARequest is useless.

 

So I have been trying to determine where I went wrong, by starting up a new minimal project, and then adding on an FDUpdateSQL and then an OnUpdateRecord. Nothing I do allows records to be inserted into the EstDtl table.

 

I think I have followed all of your suggestions, The EstDtl table has only 3 fields that are important, Est_Id which is required, EstDtl_Id which is a autogenerated field, and EsdDtlDsc which is a required field. All other can be null.

The idea of the app is that alist of all the costcentre descriptions is on the left and a set of edits is on the right allowing maintenance to the EstDtl fields. 

The EstDtl table has only 3 fields that are important, Est_Id which is required, EstDtl_Id which is a autogenerated field, and EsdDtlDsc which is a required field. All other can be null.

 

So my assumption is that if I have a stringgrid with two fields (CscDsc, and EstDtlDsc) and a memo for EstDtlDsc each linked by livebindings to the Query fields then any change to the memo would trigger either an update (if the EstDtl already exists or an insert when it does not. 

However in the OnUpdateRecord I consistently get 3 calls when any change is made to the memo and regardless of if it should be an insert or an update - they are always  (arLock arUpdate and arUnlock)

By debugging the OnUpdateRecord (when I expect an insert) I can see the old values for Est_Id, EstDtl_Id, and EstDtlDsc are null, while the new fields are 1, null and some text, so that all looks correct. 

 

The Query has an SQL string of   

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

 

The UpdateSQL has an insert string of:

INSERT INTO ESTDTL
(EST_ID, ESTDTL_ID, CSC_ID, ESTDTLDSC, CSTPLS, GSTINC, 
  GSTPCT, QTYCLC, QTY, CST, GST, 
  SLE, VAL)
VALUES (:NEW_EST_ID, :NEW_ESTDTL_ID, :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, ESTDTLMNTDTE

I also attach the project directory in case it helps (minus the exe for size). Happy to also provide the database (1mb testing only) if its any use. Its a Firebird 3.0 db.

 

One thing to note is that there is a property CountUpdatedRecords of the UpdateOptions which gets rid of this error - but still nothing is written, it just seems to cache the change and keep trying to apply them.

Thanks again for your help.

testfirebird.zip

[Olli73 6]

The database file is missing...

[BushAl 0]

here it is - sorry

BUILDER.FDB

[Olli73 6]

The modify SQL should look something like this:

 

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

 

Edited April 6 by Olli73

[Olli73 6]

5 hours ago, BushAl said:

Sorry - missed one of your points. Both Est_Id and EstDtl_Id are in the query (as a result of select b.*).

And this was my fault: You had a "b.*", not an "a.*". 

[BushAl 0]

Thank you very much Olli73 - that looks like its working bar some field validation.  I will sort that in the morning (Australia time).

 

Now why didnt I think of the Update or Insert ....?

 

Thanks again for your valuable help

[BushAl 0]

Well its clear I didnt look at Olli73's solution closely enough, as it now fails if the EdtDtlDsc is changed (adding a new record rather than changing the existing one).

 

The only way I can find to stop that is to include the ESTDTL_ID in the Matching clause, and that requires ESTDTL_ID to also be in the update list and values, and then the insert goes wrong again. showing

FireDAC][DApt]-400. Update command updated [0] instead of [1] record. Possible reasons: update table does not have PK or row identifier, record has been changed/deleted by another user.

the ESTDtl table is defined as below

CREATE TABLE ESTDTL 
(
  EST_ID                     BIGINT         NOT NULL,
  ESTDTL_ID                  BIGINT         GENERATED BY DEFAULT AS IDENTITY (START WITH 1) NOT NULL,
  CSC_ID                     BIGINT         NOT NULL,
  ESTDTLDSC                 VARCHAR(100),
  QTYCLC                    VARCHAR(1000),
  QTY                       DECIMAL(18,3),
  CST                       DECIMAL(18,3),
  GST                       DECIMAL(18,2),
  SLE                       DECIMAL(18,2),
  VAL                       DECIMAL(18,2),
  ESTDTLMNTDTE            TIMESTAMP         DEFAULT CURRENT_TIMESTAMP,
  ESTDTLSTS                    CHAR(1),
  CONSTRAINT PK_ESTDTL PRIMARY KEY (EST_ID, ESTDTL_ID)

 

There is a document at https://docwiki.embarcadero.com/RADStudio/Athens/en/Editing_Questions_(FireDAC) which relates to this (Q1), but none of the suggestions apply.

I do note that there is talk of a TFDAutoIncField but there are none in this project and I cannot see how to force its creation. This field is a TLargeIntField.

 

So if anyone has any ideas please please suggest anything - I have been going round this constantly for a week.

 

I attach a zipped up minimal project and the database (now Firebird 4.0 as I was worried the GENERATED BY DEFAULT AS IDENTITY in FB 3.0 was causing this but no.

I might add I have tried various other sequence / generators at the db level and get slightly different but similar errors.

 

testfirebird.zip

BUILDER.FDB

[Olli73 6]

Therefore I have used

MATCHING (EST_ID, ESTDTLDSC)

(And maybe additional fields), because ID is created by DB after post.

 

But maybe there is another solution using the id field...

[BushAl 0]

I understand what you used, but in my use case it fails when the Description is changed. And I cannot see what other fields can be used for this purpose unless a complete copy of the EstDtl_Id is kept on the record - that sounds very twisted to me.

My major problem is that I am Relatively new to using FireDac which is very powerful but all the different options does make it hard to see what should be used in this case. I think I have tried most of the combinations of options on the FDQuery and the TField that look as if they are related but I could easily have missed some

I would be sure there must be a solution just involving the two primary key fields as most / all of the rest of  the detail record is changeable, and cant be relied on to determine if the record should be inserted or updated.

Given the RETURNING ESTDTL_ID, ESTDTLMNTDTE clause it should not matter that the id is determined by the database.

I may post a question on the Embarcadero support page and see if I get any result.

 

Thanks again for your help