error when updating record

[dany40]

Hello;

(DBX4 for firebird -last version- and Delphi XE5).

I added a few lines of code in the DataSetProviderBeforeUpdateRecord event to save a timestamp in a field before updating the record. It works very well with your DBX4 for PostgreSQL driver but when I use it with your DBX4 for Firebird driver an update error is raised, telling me that some field in the table must have a value (this field is the primary key and must have a value, but it has one!). In the example code (link) you will see I am not changing that field value.

The example includes all the files of Firebird 2.5.2 installation for using it embedded, and my example code (Project6.dpr). It also includes a FDB database.

https://onedrive.live.com/redir?resid=C ... file%2cZIP

I am sorry for sending this again, I sent it by email, but I never received an answer.

Best regards,

Dany

[ricolebo]

Hello,

You have error because all fields are required in your dataSet
So, all fields must be set when you post data

I made some changes in your demo and it works like this :
Hope it will help you
Rico

Code: Select all

procedure TForm8.Button1Click(Sender: TObject);
var
  s: string;
begin
  SQLDataSet1.CommandText := 'select * from "PROC"';
  ClientDataSet1.Active := True;
  s := ClientDataset1.FieldByName('INST').AsString;
  s := IntToStr(StrToIntDef(s, 0) + 1);
  ClientDataSet1.Edit;
  ClientDataset1.FieldByName('INST').Value := s;
  ClientDataset1.Post;
  if ClientDataset1.ApplyUpdates(0) = 0 then
    Label1.Caption := ClientDataset1.FieldByName('INST').AsString
  else Label1.Caption := 'Error';
end;

procedure TForm8.SQLDataSet1AfterOpen(DataSet: TDataSet);
var
  i :integer;
begin
  for i := 0 to DataSet.Fields.Count -1 do
    DataSet.Fields[i].Required := False;
end;

[PavloP]

To solve the problem, set the RequiredFields option of the TSQLConnection component to False. For your case, this can be done as follows:

Code: Select all

 Conn.Params.Values['RequiredFields'] := 'False';

More details about using RequiredFields can be found in the ReadMe.html distributed with dbExpress driver for InterBase and Firebird.

[dany40]

Hello;

Thank you but, I think the problem I am reporting has no relation with RequiredFields. In fact, the error say "field PROC must have a value" and it is very clear that it has a value, because I am editing just one field of an existing (an validated jet) record.

Best regards,

Dany

[PavloP]

The problem occurs in the BeforeUpdateRecord event handler of the DataSetProvider1 component, in which you are trying to edit the row of the DeltaDS dataset. In this handler, the DeltaDS dataset contains 2 rows. In the first one, fields contain field values of the row, for which the ClientDataSet1.Edit method is called; and all the fields of the second row are Null. The second row of DeltaDS is current. And you are trying to edit it by calling the DeltaDS.Edit method without setting required fields, the "PROC" field in particular.
To solve the problem, follow the advice given to you in the previous response.

[dany40]

I am sorry, but I do not understand what you mean. You are wrong when you say that "all the fields in the second row are "Null". In the example code you will see that, in the second row, field "INST" has a value, and this works very well if I remove my event code.

Also, it is not clear to me what is "RequiredFields" parameter for. I do this stuff in the BeforeUpdateRecord with other drivers without problems. And your README.HTML does not say to much:

"RequiredFields Manages of TField.Required property assignment. If True, all fields are marked as they are declared on server. Default value of this option is True."

What does it mean?, in my case always all the fields are declared on the server, regardless the database server (oracle, postgresql, firebird, ...) I use.

Best regards.

[PavloP]

Excuse me for my inaccuracy in the previous post. I meant "all the unmodified fields has the Null value". For your sample, in the second row of the DeltaDS dataset, all the fields except the "INST" field have the Null value.
The RequiredFields parameter of the TSQLConnection component is used for setting the Required property of TField objects. If the value of the RequiredFields parameter is True, then the Required property will be set to True for the TField objects, which fields are defined as NOT NULL, and otherwise - to False. If the RequiredFields is False, then all the TField.Required's will be set to False.
You can read about the RequiredFields parameter at http://www.devart.com/ibdac/docs/index. ... fields.htm

[dany40]

Thank you. Now you say you meant "all the unmodified fields has the Null value". And this is the point!!! The second row is only for setting the changes, and because of that it does not matter for this case if "PROC" or the other is required or not, as I am not changing its value. Requiredfields must be as it is, I don't have to change it. Please!!! I think you have to study the case a little more, I think it is a bug in the driver.

[dany40]

Also .... my database is the same in PostgreSQL and in Oracle, I mean, I has always NOT NULL columns, and the project works very well without setting this RequiredFields parameters. Can you explain why this is needed in the Firebird driver but not in the others?.

[PavloP]

Please send the PostgreSQL table creating script and the TSQLConnection parameters for the PostgreSQL driver to pavelp*devart*com, in order for us to be able to test driver behavior on the test application you have provided earlier.

[dany40]

Please let me know if you received my example for PostgreSQL. To clarify:

Maybe, you can finish considering that the PostgreSQL driver is the incorrect (as it is not setting required to true for fields that are defined as NOT NULL in the database). This can be fine, I guess.

But what I am trying to show you is that setting a value in one field in the second record of DeltaDS does not mean I am changing all the orders. So, for example, "PROC" has a value I a am not changing it. Second row is only for setting changes, and if I do not change "PROC" I can´t understand why it is considered as empty.

[PavloP]

Yes, you are right. The dbExpress Driver for PostgreSQL contains an error related to TField.Required. We will fix the error in the next build.
As for the data that are in the DeltaDS records — it refers to DataSnap operation, and not our driver. We can not affect to this behaviour. We can not change TField.Required for specific DeltaDS fields.
So DeltaDS behaves as a trivial TDataSet and generates an error when you POST a record that contains NULL for fields that were created as NOT NULL.