Serious bugs on SQLInsert

[dinko]

It looks like that two serious bugs exist on SQLInsert. I am on MySQL:

FIRST BUG

SQL Text is

SELECT account_group_items.description, account_groups.expire_status, account_groups.expire_date FROM account_groups
left join account_group_items on account_group_items.id_account_group_item = account_groups.id_account_group_item
where account_groups.id_account = :id_account
order by account_group_items.`order` asc

SQL Insert Text is:

INSERT account_groups
(`id_account_group_item`, `id_account`, `expire_status`, `expire_date`)
VALUES
(:id_account_group_item, :id_account, :expire_status, :expire_date)

If you try to .Append and FieldByName('id_account') you will receive an error message that id_account not found:

EDatabaseError: MemberSelectGroups: Field 'id_account' not found

If we add the id_account on the original select:

SELECT account_groups.id_account, account_group_items.description, account_groups.expire_status, ...

It still complains and returns error that cannot modify the field (!) :

EDatabaseError: Field 'id_account_group_item' cannot be modified

So its impossible to insert record.

SECOND BUG

Looks like that SQLInsert is fully connected with the SQL query and you cannot insert a record to other table. Also you cannot insert in two tables. Unidac complains and cannot add record.

Solution for all bugs is to use separate UniQuery for insert statement, but my question is why to have 100 queries for select + 100 queries for insert? Why? Please fix these issues so we can use those features.

[ViktorV]

1. The field specified in the SQLInsert query must be present in the SELECT query, as UniDAC uses the FieldByName method to set a parameter value that is assigned to a table field.
2. UniDAC uses the first table specified after "SELECT" or the first table pointed after "FROM" as default updating table, depending on the current data provider.
If your query contains several tables, it is recommended to always set the UpdatingTable property to the table you want to edit.
To edit all data in your query perform the following steps:
- set the TUniQuery.Options.SetFieldsReadOnly property to False
- in TUniQuery.SQLUpdate add queries for updating all the tables used in select query
To insert data in several tables you should add queries for data adding to all required tables used in select query in TUniQuery.SQLInsert .

[dinko]

I still can't understand why selected fields and tables in the Insert statement *MUST* exist in the select statement? Why it "must"? Why this limitation?

[ViktorV]

To fill SQLInsert query parameters UniDAC takes values from SELECT query fields, therefore these fields must be present in SELECT query.