7.0.4 possible bug in ApplyUpdates with auto increment field

[lsrzj]

I'm trying to persist two tables with master-detail relationship in MySQL 5.6 and, when I do ApplyUpdates on the master, the auto increment field stays with 0 as value. I need the auto increment value, so I can link the detail table with the master table's ID field coming from ApplyUpdates. I'm using ZConnection with AutoCommit = FALSE and TransactionIsolationLevel = tiReadCommitted, ZQuery with CachedUpdates = TRUE.

Code snippet:

Code: Select all

ZQPerson.Append;
ZQEmployee.Append;
try
  ZQPersonName.Value := Edit1.Text;
  ZQPerson.ApplyUpdates; //Here I expected to have the auto increment value on the Id field of ZQPerson, but it returns always 0
  ZQEmployeePersonID.Value := ZQPersonId.Value; //Here I'd link Employee to it's Person record
  ZQEmployeeRegNo.Value := StrToInt(Edit2.Text);
  ZQEmployee.ApplyUpdates;
  ZConnection1.Commit; //Here I would persist both tables in a single transaction to avoid master table without details
except
  ZQPerson.CancelUpdates;
  ZQEmployee.CancelUpdates;
  ZConnection1.Rollback; //In case of exceptions rollback everything
  raise;
end;
ZQPerson.CommitUpdates;
ZQEmployee.CommitUpdates;

[lsrzj]

During ApplyUpdates it's generating the SQL and executing it, but it's not returning the auto increment value to the master query ID field. Here is my SQL trace coming from ZSQLMonitor

Code: Select all

2013-08-29 00:01:23 cat: Execute, proto: mysql-5, msg: INSERT INTO person (Id, name) VALUES (NULL, 'Edit1') --> This is just after ZQPerson.ApplyUpdates
2013-08-29 00:01:50 cat: Execute, proto: mysql-5, msg: INSERT INTO employee (Id, RegNo, ProductId) VALUES (NULL, 1000, 0), errcode: 1452, error: Cannot add or update a child row: a foreign key constraint fails (`test`.`employee`, CONSTRAINT `FK_A6085E0491BDF8EE` FOREIGN KEY (`PersonId`) REFERENCES `person` (`Id`) --> This is just after ZQEmployee.ApplyUpdates
2013-08-29 00:02:05 cat: Execute, proto: mysql-5, msg: Native Rollback call --> Rollback after Exception on the ZQEmployee.ApplyUpdates

[mdaems]

Can you check if after the error the auto increment field has been filled? Eventually put a debug point in, the zeoslib source code where the autoinc value should be retrieved. (Some code in the mysql resultset unit at the dbc level, I think)

Mark

[lsrzj]

well, the value is not being filled even after the error. I don't know anything about zeos code, I have absolutely no idea where in the code it should be taking the next auto increment value from the database. I asked this, because I saw in one of the changelogs that zeos supports MySQL auto increment feature, so the only thing I need to know is how to use it in zeos. I tried to debug zeos code during ApplyUpdates of my code snippet and didn't figured out, really, if zeos was trying to get the auto increment value in some point of it's code. What I noticed is that the ApplyUpdates, when reaches this point

ZDatabaseUtils line 514

Code: Select all

ResultSet.UpdateInt(ColumnIndex, RowAccessor.GetInt(FieldIndex, WasNull));

function TZRowAccessor.GetInt(ColumnIndex: Integer; var IsNull: Boolean): Integer;
begin
{$IFNDEF DISABLE_CHECKING}
  CheckColumnConvertion(ColumnIndex, stInteger);
{$ENDIF}
  Result := 0;
  if FBuffer.Columns[FColumnOffsets[ColumnIndex - 1]] = 0 then ...

the condition is false, so it puts IsNull := True. But it's an AutoIncrement field, that's why it's returning always 0 as result during the ApplyUpdates, it should get the Last_Insert_ID from the database and fill the value automatically. That's only a guess, I don't know anything about zeos code!

My workaround to make it work was this one below, but it not satisfies me, because it's not transparent for the programmer the use of the auto increment feature.

Code: Select all

function LastInsertID(ATableName: string): Integer;
var DBQuery: TZQuery;
begin
  DBQuery := TZQuery.Create(Self);
  with DBQuery do
  begin
    Connection := ZConnection1;
    SQL.Clear;
    SQL.Add('Select Last_Insert_ID() as Last_Insert_ID from ' + ATableName);
    Open;
    Result := FieldByName('Last_Insert_ID').Value;
    Free;
  end;
end;

procedure Persist;
var LastID: Integer;
begin
  ZQPerson.Append;
  ZQEmployee.Append;
  try
    ZQPersonName.Value := Edit1.Text;
    ZQPerson.ApplyUpdates; // Here I expected to have the auto increment value on the Id field of ZQPerson, but it returns always 0
    LastID := LastInsertID('Person'); //Getting the Last_Insert_ID(), even on the uncommitted transction, works
    ZQEmployeePersonId.Value := LastID; //Link the two tables using the Last_Insert_ID() result
    ZQEmployeeRegNo.Value := StrToInt(Edit2.Text);
    ZQEmployee.ApplyUpdates;
    ZConnection1.Commit; // Here I persist both tables in a single transaction to avoid master table without details
  except
    ZQPerson.CancelUpdates;
    ZQEmployee.CancelUpdates;
    ZConnection1.Rollback; // In case of exceptions rollback everything
    raise;
  end;
  ZQPerson.CommitUpdates;
  ZQEmployee.CommitUpdates;

[EgonHugeist]

Didn't i already point you to the TZDataSet.Options := [..., doUpdateMasterFirst] option? This should resolve this issue too.

[lsrzj]

Yes, you pointed it, but I couldn't make it work because, even with your snippet, I didn't understand how to use this option, it would be better to understand if your snippet was a real use case as I made mine. But, what I noticed is that, even after ZQuery.ApplyUpdates, ZConnection.Commit and ZQuery.CommitUpdates, ZQuery seems to be not refreshing the record so all fields of the ZQuery object are blank just after insertion, so I have to make a manual call to Refresh to fill ZQuery fields of every inserted record. Is this the expected behaviour? I expected that, just after ApplyUpdates all ZQuery fields would be automatically filled with the values of the newly applied, but yet uncommitted, record without the need of a refresh that will point to the first record of the recordset. But, in case of any kind of errors on the transaction and a call to Rollback and CancelUpdates, it would point to the record that was active just before the insertion.

[EgonHugeist]

Can you prepare a little test case? Just to see you're rigth or wrong(than i'll fix your code)?

[lsrzj]

I made a very small test case to show you what I'm pointing. If you go, line by line, executing the code and put a watch to ZQuery1Id.Value you'll see that the field is not refreshed staying with a value of 0 after ApplyUpdates. All fields still filled normally after ApplyUpdates but Auto Increment fields are not so I'm having to create a dynamic ZQuery object to query the last_insert_id() returned from MySQL for the opened transaction. Shouldn't Zeos get the value and fill the ZQuery1Id.Value automatically after ApplyUpdates with the value returned by Last_Insert_ID()? Here is the code snippet that I made as test case

test.rar

Best Regards!

[EgonHugeist]

Did test your app after convert it back to my IDE.

Well you're using the TZUpdateSQL component for your statements. Didn't look deeper into it. But i think you're missing the RefreshSQL to refresh your Data of the first query.

Also couldn't i see any Master-Detail relation between your TZQueries.
May i ask why you're using ApplyUpdates in this way?

[lsrzj]

Well, the Person table is the master, as person could be an employee, a visitor visiting the company and doesn't have a RegNo as an employee does, but have other fields that Person doesn't have and employee neither. I made something like specialization(something like class inheritance, but in database). Well, first I do need to persist the person table first, and then I have to persist the other one putting the person table PK in the FK field of the second one. So first I have to ApplyUpdates to the first one to apply the updates to the database, but without committing it to the database(AutoCommit = false), then I get the value of the PK of the last insertion of the first table to put it to the FK field of the second one fill all other fields and do a ApplyUpdates to apply the updates to the database. If both ApplyUpdates are successful, then I commit the transaction to the database to be persisted, but if the RDBMS system detects any error on this transaction, both tables aren't really persisted to the database as it wasn't Committed yet, so I can do a Rollback to cancel the failed attempt to put the record in the database. If I permitted to persist the first table first and than the second table failed it's commit, I'd have a dangling master without it's child table; an inconsistent state for the database. I wanted to have a way that the data would really go to the database if and only if all tables involved in opened transaction could be persisted by the RDBMS in the current transaction, if one table failed, all transaction must be rolled back. The way I did, I achieved this successfully. ApplyUpdates, with AutoCommit = false, only tests if data is good to go to the database, and if it's not RDBMS indicates errors, Zeos raises exceptions, so you can catch them and Rollback the failed transaction and your database is always consistent.

Is there a better way to achieve what I need? I thought that this would be the best way, but as you never seen the ApplyUpdates being used the way I used, I feel like doing something wrong.

Best regards!

[mdaems]

Why not just posting to the database, row by row? Which means no autocommit, no cached updates. This sends the data of the master to the db and retrieves the autoincrement value on the master. When connecting the master to the details, this should also update the linking keys in the detail table.

An alternative is basing your zquery on a join and linking a zupdatesql component that executes a stored procedure to do the updates.

Mark

[lsrzj]

Thanks for your answer. What you mean with row by row? Can you give me a snippet of both examples you gave me? It's just because I never did the way you are proposing, so I don't know how to accomplish this.

Best regards!

[mdaems]

Thanks for your answer. What you mean with row by row? Can you give me a snippet of both examples you gave me? It's just because I never did the way you are proposing, so I don't know how to accomplish this.

Best regards!

Sorry, but I have no snippets available. And unfortunately at the moment I'm rarely in the occasion of spending some time with my zeoslib development machine.

The first solution is more or less this:
- Set up the connection->no autocommit
- Set up a master ZQuery -> no CachedUpdates
- Set up the 2 detail zqueries and link them using the linkfields properties (don't know the exact name) -> don't think cachedupdates hurt here (but why mix ?)
- Append a row to the master and post -> this should write an insert to the db and retrieve the autoinc value on mysql and FB
- Append a row to the childs and post -> there the link field values should be automatically entered by the linkfields properties
- Connection.Commit.
If there's no join in the zquery.sql properties it's not necessary to use ZUpdateSQL betcause the ZQUeries can consrtruct the update statements automatically.

The second solution:
- Set up the connection->no autocommit
- Set up one ZQuery, retrieving all the fields of master and both details
- Write a database procedure insert_person with input parameters containing all columns of your query
- Set up an ZUpdateSQL component linked to the ZQuery and set insertsql to 'call insertperson(:NEW.Name,:NEW.firstname...)' (Eventually you can also use this for an update function, a delete function and a refreshsql statement)
- Open the ZQuery
- ZQuery.appendRow
- ZQuery.post --> this should call the database procedure
- Commit
The disadvantage of this procedure is that it does not retrieve the autoinc values, unless you can use the refreshsql property using a sql statement with another unique key to locate the master record that's just inserted.

I hope this can put you on the right track...

Mark

[lsrzj]

mdaems, Thank you!

I tried your first solution and it worked perfectly. And the second one was simply the best! I have no code in my forms anymore to persist data as zeos makes many data conversions automatically, for example date formats. I created three procedures per table one for insertion, another for updates and one for deletion. Finally all i had to do was to fill ZUpdateQuery with a CALL to one of those depending on the case filling passing the correct parameters to the SPs. Fantastic!