UniDAC and MySQL autoinc

[mirkocutri]

Hi,
I'm using UniDAC 6.2.10 and try to post a new dataset using TUniQuery with DBEditor components. Confirming the entry with the post button on dbNavigator (not using the component SQL query) I get the error message that the "Id" field requires a value message. I'm using a MySQL database. The field is of type integer and set as primary key
Is there any property on the TUniQuery component that I have to set to commit and generate the autoinc value? Or do I miss something?
Second question, how to get the committed dataset with the autoinc value?
Thanks for any help

[ViktorV]

In order automatically generate a primary key value, use the AUTO_INCREMENT attribute at field declaration. For example:
id int NOT NULL AUTO_INCREMENT PRIMARY KEY
UniDAC returns the auto-incremental field value automatically on inserting a new record.

[mirkocutri]

Right! I supposed this too.
In the db I have this declaration but when the Post event is called, I get the id value requiried message.
In addition I tried to use a DBedit field connected to the id Field and leave it blank. No way. Also the direct setting uniquery.fieldbyname('id').asinteger to null doesn't work.
Any further ideas?

[FCS]

Hello,

Try
uniquery.fieldbyname('id').value:=null;

Regards
Michal

[ViktorV]

Unfortunately, we couldn't reproduce the issue. In order to investigate the issue, please compose and send to viktorv*devart*com a small sample demonstrating the issue, including scripts for creating database objects. Also, specify the exact version of your MySQL server.

[mirkocutri]

Hello FCS and Viktor.

Thank you very much for your replies.

FCS, my first thought was this but I it didn't work but I found the answer after checking again all possible properties. The answer for all those who need it in future.

UniQuery.FieldByName('ID').Required := False;
UniQuery.FieldByName('ID').AutoGenerateValue := arAutoInc;

The problem is that loading the field into the query component is setting automatically the required property to yes on NOT NULL and AUTO_INCREMENT database fields. So before the execution of the post on the database, the UniQuery checks this parameter and wait for a value.

Viktor, just for doublecheck, can you confirm or did I find the solution just randomly?

Next small issue connected to this... after post and commit, how can I get the inserted value on "ID"?

I need this value immediately after the post process in order to insert this value in a master dataset.

In the master OnBeforePost procedure after filling out the form I execute:

UniQueryPerson.Post;
UniQueryPerson.ApplyUpdates();
UniQueryPerson.CommitUpdates;

UniQueryCompany.FieldByName('C_ID').Required := False;
UniQueryCompany.FieldByName('C_ID').AutoGenerateValue := arAutoInc;
UniQueryCompany.FieldByName('COMPANY_OWNER').Value := (here I need the person ID just posted and committed);

Thank you guys!

[FCS]

Hello,

I use PostgreSQL database.

In my case I do like this:

Code: Select all

var
  UQ: TUniQuery;
  ID : longint;
begin
  UQ:=TUniQuery.Create(DM_01);    //here is definition of connection 
  UQ.Connection:=DM_01.UniConnection1;
  UQ.SQL.Add('SELECT * FROM my_table WHERE id > 0;' );  // here may be more detail query condition
  UQ.Options.DefaultValues:=true;
  UQ.Open;
  UQ.Append;
  UQ.FieldByName('id').value:=null;
  UQ.FieldByName('....').As.......:= .......
  UQ.Post;
  ID:=UQ.FieldByName('id').AsInteger;  //in my_table id is serial type (autoinc) 
  UQ.RefreshRecord;  // I use this when I want to get the others record values ie: generated by triggers 
                                   // but this leads to an error when added record is out of the query condition

  UQ.Close;
  UQ.Free;
end;

Regards
Michal

[ViktorV]

Hello FCS and Viktor.

Thank you very much for your replies.

FCS, my first thought was this but I it didn't work but I found the answer after checking again all possible properties. The answer for all those who need it in future.

UniQuery.FieldByName('ID').Required := False;
UniQuery.FieldByName('ID').AutoGenerateValue := arAutoInc;

The problem is that loading the field into the query component is setting automatically the required property to yes on NOT NULL and AUTO_INCREMENT database fields. So before the execution of the post on the database, the UniQuery checks this parameter and wait for a value.

Viktor, just for doublecheck, can you confirm or did I find the solution just randomly?

Next small issue connected to this... after post and commit, how can I get the inserted value on "ID"?

I need this value immediately after the post process in order to insert this value in a master dataset.

In the master OnBeforePost procedure after filling out the form I execute:

UniQueryPerson.Post;
UniQueryPerson.ApplyUpdates();
UniQueryPerson.CommitUpdates;

UniQueryCompany.FieldByName('C_ID').Required := False;
UniQueryCompany.FieldByName('C_ID').AutoGenerateValue := arAutoInc;
UniQueryCompany.FieldByName('COMPANY_OWNER').Value := (here I need the person ID just posted and committed);

Thank you guys!

UniDAC automatically returns the value of the auto-incremental field on calling the TCustomDADataSet.Post method if the TCustomDADataSet.CachedUpdates property is set to False. If TCustomDADataSet.CachedUpdates is set to True, the auto-incremental field value is returned on calling the TCustomDADataSet.ApplyUpdates method. Therefore, to prevent "Field must have a value" error on inserting a record containing an auto-incremental field in the Cached Updates mode, you should set the TField.Required property to False. In your sample, after calling the UniQueryPerson.ApplyUpdates method, the value of the UniQueryPerson.FieldByName('ID').Value property will contain the correct value of the auto-incremental field.

[mirkocutri]

Thanks Victor.

Your suggestion was pretty useful and the records are stored in the database now. But I have a strange reaction of the component that I cannot solve.

Code: Select all

procedure TFormCompany.UniQueryCompanyBeforePost(DataSet: TDataSet);
begin
  if UniQueryCompany.State = dsInsert then
  begin
    UniQueryPerson.FieldByName('PERSON_ID').Required := False;
    UniQueryPerson.FieldByName('PERSON_ID').AutoGenerateValue := arAutoInc;

    UniQueryPerson.Post;
    UniQueryPerson.ApplyUpdates();

    UniQueryCompany.FieldByName('COMPANY_ID').Required := False;
    UniQueryCompany.FieldByName('COMPANY_ID').AutoGenerateValue := arAutoInc;
    UniQueryCompany.FieldByName('COMPANY_OWNER').Value := UniQueryPerson.FieldByName('PERSON_ID').Value;
  end;

end;

Calling this procedure, I get from the DB the PERSON_ID autoinc field and after the ApplyUpdates call the UniQueryPerson.FieldByName('PERSON_ID').Value contains the assigned value.

But doing the same in the AfterPost procedure for the COMPANY_ID field doesn't react same, although the settings of the two UniQuery are identic.

Code: Select all

procedure TFormCompany.UniQueryCompanyAfterPost(DataSet: TDataSet);
begin
  try
    UniQueryCompany.ApplyUpdates();
    DBCompanyID_Lab.EditValue := UniQueryCompany.FieldByName('COMPANY_ID').Value; // this value is null after ApplyUpdates execution although it should contain the autoinc value of company table
    UniQueryCompany.CommitUpdates;
    UniQueryPerson.CommitUpdates;
    UniConnection.Commit;
  except
    UniConnection.Rollback;
  end;
end;

Does the UniQuery loose field values after POST?

The strange thing is, that in the physical DB i find both rows, the person table and the company table.

But I get the full row information only after closing and open the UniQuery. Refreshing the QueryPerson by UniQueryPerson.RefreshRecord works but not UniQueryCompany.RefreshRecord that raises the error message "Refresh failed. Found 0 records".

I looked also on the MySQL parameter settings where the parameters sql_auto_is_null is set to ON and the delay_key_writes is set to ON.

Do you have any ideas?

Thanks a lot...

[FCS]

Hello,

You may get this error when the new refreshed record is out of the SQL condition defined by WHERE ....

Regards
Michal

[ViktorV]

Thanks Victor.

Your suggestion was pretty useful and the records are stored in the database now. But I have a strange reaction of the component that I cannot solve.

Code: Select all

procedure TFormCompany.UniQueryCompanyBeforePost(DataSet: TDataSet);
begin
  if UniQueryCompany.State = dsInsert then
  begin
    UniQueryPerson.FieldByName('PERSON_ID').Required := False;
    UniQueryPerson.FieldByName('PERSON_ID').AutoGenerateValue := arAutoInc;

    UniQueryPerson.Post;
    UniQueryPerson.ApplyUpdates();

    UniQueryCompany.FieldByName('COMPANY_ID').Required := False;
    UniQueryCompany.FieldByName('COMPANY_ID').AutoGenerateValue := arAutoInc;
    UniQueryCompany.FieldByName('COMPANY_OWNER').Value := UniQueryPerson.FieldByName('PERSON_ID').Value;
  end;

end;

Calling this procedure, I get from the DB the PERSON_ID autoinc field and after the ApplyUpdates call the UniQueryPerson.FieldByName('PERSON_ID').Value contains the assigned value.

But doing the same in the AfterPost procedure for the COMPANY_ID field doesn't react same, although the settings of the two UniQuery are identic.

Code: Select all

procedure TFormCompany.UniQueryCompanyAfterPost(DataSet: TDataSet);
begin
  try
    UniQueryCompany.ApplyUpdates();
    DBCompanyID_Lab.EditValue := UniQueryCompany.FieldByName('COMPANY_ID').Value; // this value is null after ApplyUpdates execution although it should contain the autoinc value of company table
    UniQueryCompany.CommitUpdates;
    UniQueryPerson.CommitUpdates;
    UniConnection.Commit;
  except
    UniConnection.Rollback;
  end;
end;

Does the UniQuery loose field values after POST?

The strange thing is, that in the physical DB i find both rows, the person table and the company table.

But I get the full row information only after closing and open the UniQuery. Refreshing the QueryPerson by UniQueryPerson.RefreshRecord works but not UniQueryCompany.RefreshRecord that raises the error message "Refresh failed. Found 0 records".

I looked also on the MySQL parameter settings where the parameters sql_auto_is_null is set to ON and the delay_key_writes is set to ON.

Do you have any ideas?

Thanks a lot...

Unfortunately, we couldn't reproduce the issue. In your sample, after calling the UniQueryCompany.ApplyUpdates method, the value of the UniQueryCompany.FieldByName('COMPANY_ID').Value property contains the correct value of the auto-incremental field. In order to investigate the issue, please compose and send to viktorv*devart*com a full sample demonstrating the issue, including scripts for creating database objects. Also, specify the exact version of your MySQL server.

[FCS]

Hello Victor,

At this point, I would like to ask you, what is the proper way to append new record ?

The simple answer is by using the UQ.Append method.
But this topic tells this is not so simple, because before using Append it is necessary to define the select condition.
For example:
1. UQ.SQL.Add('Select * from aTable <WHERE condition> ');
2. UQ.Open;
3. UQ.Append;
4. UQ.FieldByName('xxx').As...... := a_value;
5. UQ.Post;
6. UQ.RefreshRecord;

And now:
If <WHERE condition> is empty, the query returns all records.
If <WHERE condition> is more detailed, UQ.RefreshRecord may raise an exception, because added record is outside of the selection condition.

Regards
Michal

[ViktorV]

The WHERE clause from the query in SQL property will be added to the query for RefreshRecord only in case if the TUniQuery.Options.FullRefresh option is set to True. Or if you set the SQLRefresh property by yourself. If the TUniQuery.Options.FullRefresh property is not set, then any record will be added in your sample, and then updated with no errors, not depending on the WHERE condition in the SQL property.

[FCS]

Hello,

Thanks,

Where is described the FullRefresh option ? I have documentation to 6.1.6 and the search engine not recognized this keyword.

if I change in my example the where condition:

0. UQ.Options.DefaultValues:=true;
1. UQ.SQL.Add('Select * from aTable WHERE id=0 ');
2. UQ.Open;
3. UQ.Append;
4. // UQ.FieldByName('id') not filled = id is serial in PostgreSQL
4a. UQ.FieldByName('xxx').As...... := a_value_xxx;
5. UQ.Post;
6. UQ.RefreshRecord;

The RefreshRecord command raises an exception (0 rows fetched), because ID is great then 0 and outside the where condition,

Regards
Michal

[mirkocutri]

Michal, thanks for your suggestion.

In fact in the UniQueryCompany.SQL I had one wrong WHERE clause responsible for no records on the refresh record action. After optimizing the WHERE and keeping it much more simple, the UniQueryCompany refreshes and gives back correctly the autoinc value like the UniQueryPerson.

Thx a lot!!