Problem with Firebird trigger

Discussion of open issues, suggestions and bugs regarding usage of dbExpress drivers for InterBase & Firebird in Delphi and C++Builder
Post Reply
PurChemist
Posts: 5
Joined: Fri 01 Nov 2013 22:29

Problem with Firebird trigger

Post by PurChemist » Tue 18 Mar 2014 18:55

Under Delphi 2010 Professional I access a Firebird database (version 2.5.1) with the Devart dbExpress driver (4.3.4). Everything works fine but I am not able to work with triggers.

In Delphi I want to automatically set a new incremented "RecordID" during each insert procedure of the related ClientDataSet.

The trigger and generator have been generated in Firebird directly. In Firebird the trigger works fine - during each insert procedure the "RecordID" is incremented by 1 as it was defined.

When I insert a new record via Delphi then a new record with empty datafields is generated and displayed. After finishing the datainput I tried to post the dataInput with the Post method of the ClientDataset but then I get the error message "Field 'RECORDID' must have a value".

How can I achieve that the incremented 'RecordId' from Firebird is transferred to my Delphi form that the post command will work without problems. I do not know which settings have to be done in which component to realize this data refresh.
Please help.
Peter

ZEuS
Devart Team
Posts: 240
Joined: Thu 05 Apr 2012 07:32

Re: Problem with Firebird trigger

Post by ZEuS » Fri 21 Mar 2014 10:45

To prevent the "Field must have a value" error, you should set the "RequiredFields" driver option to False. You can find more information in the Readme.html file that is supplied with dbExpress driver for InterBase & Firebird.
You can assign the option in the client dataset BeforeOpen event handler in the following way:

Code: Select all

procedure TForm1.ClientDataSet1BeforeOpen(DataSet: TDataSet);
begin
 SQLConnection1.Params.Values['RequiredFields'] := 'False';
end;
After posting the record, you should execute ApplyUpdates and Refresh methods of the client dataset in order to retrieve the incremented field value.

PurChemist
Posts: 5
Joined: Fri 01 Nov 2013 22:29

Re: Problem with Firebird trigger

Post by PurChemist » Fri 21 Mar 2014 15:35

Thank you for your message.

I followed your advise but unfortunately the error message still remains the same.

It is not clear to me what is required in the BeforeOpen Event of the ClientDataSet:
'RequiredFields': is this the term that must be used within the brackets or should 'RequiredFields' be replaced with the name of the Database field where the 'RecordID' is generated by the trigger.

Nevertheless I tried both options but after inserting a new record in the Delphi Form and adding data to some fields (I left 'RecordID' empty) the post command generated still the "Field must have a value" error. What is my mistake?

ZEuS
Devart Team
Posts: 240
Joined: Thu 05 Apr 2012 07:32

Re: Problem with Firebird trigger

Post by ZEuS » Wed 26 Mar 2014 06:58

"RequiredFields" is the driver option which manages the TField.Required property assignment. When it is set to False, the dataset fields like "RecordID" will not be automatically marked with TField.Required = True, and this will prevent from the "Field must have a value" error when inserting a record. Please, refer to the Readme.html file that is supplied with dbExpress driver for InterBase & Firebird to learn more about the driver options.
If the error remains, please make a small sample that demonstrates the problem, including a script to create the test database, and send it to eugeniyz*devart*com.

PurChemist
Posts: 5
Joined: Fri 01 Nov 2013 22:29

Re: Problem with Firebird trigger

Post by PurChemist » Thu 02 Oct 2014 13:10

Sorry for the long delay to answer your message but I was ill. Since I am back again I have to state that my problem still maintains after following your advise. I already prepared the requested small sample that demonstrate the problem but I do not know to whom I should send my sample because the mail address that you mentioned "eugeniyz*devart*com" is not available. I have never seen the sign "*" within mail adresses. Please help

Peter

ViktorV
Devart Team
Posts: 3168
Joined: Wed 30 Jul 2014 07:16

Re: Problem with Firebird trigger

Post by ViktorV » Fri 03 Oct 2014 08:21

We mask email addresses to protect them from spam. Replace the first '*' with '@', the second - with '.' and send your sample to viktorv*devart*com

ViktorV
Devart Team
Posts: 3168
Joined: Wed 30 Jul 2014 07:16

Re: Problem with Firebird trigger

Post by ViktorV » Wed 15 Oct 2014 09:55

Please, try to assign the option value in the TSQLConnection BeforeConnect event handler in the following way:

Code: Select all

procedure TForm1.SQLConnection1BeforeConnect(Sender: TObject);
begin
  SQLConnection1.Params.Values['RequiredFields'] := 'False';
end;
and check, if the problem is solved.
After posting the record, you should execute ApplyUpdates and Refresh methods of the client dataset in order to retrieve the incremented field value.

PurChemist
Posts: 5
Joined: Fri 01 Nov 2013 22:29

Re: Problem with Firebird trigger

Post by PurChemist » Thu 16 Oct 2014 22:56

Perfect - Now everything works fine without any problem !
Thanks a lot for this solution and best regards

PurChemist

ViktorV
Devart Team
Posts: 3168
Joined: Wed 30 Jul 2014 07:16

Re: Problem with Firebird trigger

Post by ViktorV » Fri 17 Oct 2014 05:37

It is good to see that the problem has been solved. Feel free to contact us if you have any further questions.

Post Reply