Updating MySQL result set from RESULT tab


When the MySQL server sends a resultset to a client, it does not only send data but also various types of 'metadata'. One such important piece of 'metadata' is that for every column in the result set the client has information about the database from which the data originates. SQLyog uses this information and adds the database name to the INSERT, UPDATE and DELETE queries generated.

However in two situations such 'metadata' is not available. The two situations are:
1) With MySQL servers before 4.1. Those early servers do not have this information in the resultset returned to the client.
2) When using HTTP-tunnel. PHP does not return this 'metadata' information - also not with recent MySQL servers. PHP only returns data.

In those two cases SQLyog will execute INSERT, UPDATE or DELETE queries against the database that is currently selected by user (typically in the Object Browser). For every new RESULT tab opened it will remind you once when you try to INSERT, UPDATE or DELETE data if either of the two conditions listed above apply. It is user's responsibility to manage the database context in those mentioned cases. If user fails to do this, the statements generated by SQLyog will most probably be invalid (and an error will be returned), but there also is a rare chance that the statement is valid (if table names and column names are the same in two databases) and in that case executing statements against the wrong database may result in damage to data stored.

This is however only a risk if
1) user executes a script with 'fully qualified tablenames' (`database`,`table`) and another database than ?database` is currently selected .
2) users executes a statement without 'fully qualified tablenames' and changes the database context from object browser after execution and next tries to INSERT, UDPATE or DELETE queires from RESULT tab.

3) result has data from more than one database. In that case you will need to switch the database context manually depending on the origin of the columns in the RESULT tab where you INSERT, UPDATE or DELETE queries.

Still need help? Contact Us Contact Us