Edit Query is about how to use TZUpdateSQL to make a non-editable database query completely or partially editable. The underlying principle is the same as QueryQuery, but here the link to the database table where the data is to be changed takes place using the TZUpdateSQL component. By the way, the BDE by Delphi has a similar component.
With TZUpdateSQL it is just as possible to execute several SQL statements with TZQuery. In our query, which is about ADDRESS and CONTACT, we will use TZUpdateSQL to change the CONTACT contact data.
The data of combined queries can not be edited because the database server does not know to which tables it should assign the changed field contents. This is fixed by TZUpdateSQL by telling the database in which table the corresponding field has to be restored. It may well be that there are situations where TZUpdateSQL is beneficial. As a rule, one finds another query, which only reads in the data record to be changed and writes back to it. The advantage of TZUpdateSQL is that it comes with a small SQL generator which can be used to generate the SQL commands for deleting, inserting and changing. But now for practice.
First we pull a TZUpdateSQL component onto our database form DataDMTutor2. The instructions here are fictitious, since everything was already present in the tutorial and done. But it is good to use, analogous to their own program. Next, we need to tell our ZQuery component that we want to provide a ZUpdateSQL for them. Each ZQuery component has the penultimate property in the Object Inspector UpdateObject. If we have already created our ZUpdateSQL, we can select it there. In our case this happens in ZQueryUpdate and the name of the ZUpdateSQL component is ZUpdateSQLUpdate. Now we should set to True in ZConnection1.Connected and set ZQueryUpdate.Active to true, so that the database fields will be available to generate the necessary arguments and SQL commands in ZUpdateSQLUpdate. There must be a corresponding SQL command in ZQueryUpdate.SQL, otherwise an error message will appear. This is already done in the tutorial. If everything worked fine, you can now see the data of the query in DBGrid.
Now we click on ZUpdateSQLUpdate. In the Object Inspector, the SQL commands for deleting (DeleteSQL), Inserting (InsertSQL) and Modyfying (ModifySQL) the corresponding SQL commands for these actions are contained in the first three entries. Params contain the necessary parameters for transfer to the SQL commands.
In DeleteSQL, the SQL delete command:
DELETE FROM CONTACT WHERE
CONTACT.CONTACTID = :OLD_CONTACTID
Colloquially: Delete a record from the table CONTACT where the key used has the value from Params with OLD_CONTACTID the value was retrieved at the beginning. If CONTACTID has been changed but not yet written and the delete command is executed with this VALUE, the wrong record will be deleted.
In InsertSQL, the SQL insert command:
INSERT INTO CONTACT
(TITLE, CONTENT1, CONTENT2)
VALUES (:TITLE, :CONTENT1, :CONTENT2)
Colloquially: Insert a data record in the CONTACT table using the values passed in the parameters.
In ModifySQL, the SQL command for change:
UPDATE CONTACT SET TITLE = :TITLE,
CONTENT1 = :CONTENT1,
CONTENT2 = :CONTENT2
CONTACT.CONTACTID = :OLD_CONTACTID
Colloquially: Change the data in the current record, whereby the current (changed) data from Params are passed as well as the ID with OLD_CONTACTID so that changes can not cause any errors. More about this see DeletSQL.
And in params the required parameters, which are passed to the SQL commands:
Now, what is happening? We heard above that in a composite dataset, the database server loses its reference to the source of the field values. However, we know this relation and, on the way to ZUpdateSQL, we can ask the database server to delete, insert and change using the above mentioned SQL commands. This is now the similarity of the solution with linked additional ZQueries.
The parameters TITLE to OLD_CONTACTID are used to pass the current data to the SQL commands, ie contain the current data, with the exception of OLD_CONTACTID. OLD_CONTACTID contains the value of CONTACTID before any of the three options were called with the SQL commands. This is our "link" that allows us to identify the current record.
Next we will look at the SQL generator of ZUpdateSQL. We call him by double-clicking ZUpdateSQLUpdate. If no data field labels are displayed, we click on "Get Table Fields". Next we select "CONTACT" for Table Name. In the box "Key Fields" we choose "CONTACTID" because we want to change the data in CONTACT. In the box "Update Fields" we select TITLE, CONTENT1 and CONTENT2. The ID tells the database which record in the CONTACTID table it needs to access. Next we click on "Generate SQL". If there are already SQL texts, you will be asked if you want to empty the contents. We confirm with "yes". The SQL commands are created automatically and the required parameters are created in Params. Now, changes, deletions and pastes should not be in the way.
In addition to the DBGrid, input fields are also available for the contact data on the right to also present this variant.
With the button "Open Edit Query" the database query is opened, closed with "Close Edit Query". "Toggle Grid" is used to make the database grid invisible and to display the amount of data that would normally be presented to a user.
A conceivable reason for this query would be to search for a specific phone number to change.
Description ZQuery ZUpdateSQL