SQL queries preview before sent to server in MySQL for Excel
By Javier Treviño on Jun 17, 2014
In this blog post we are going to cover a pretty useful piece of functionality introduced since MySQL for Excel 1.2.0. Remember you can install the latest GA or maintenance version using the MySQL Installer or optionally you can download directly any GA or non-GA version from the MySQL Developer Zone.
MySQL for Excel makes the data handling in and out of a MySQL server as friendly and simple as possible, for this reason the generation of the SQL queries needed to perform the data operations is transparent for users. There could be times when you may want to see exactly what SQL queries are being generated either before they are sent to the server, so corrections can be made if the query does not look as intended, or after they are sent to the server to obtain additional information or for debugging purposes.
Starting with MySQL for Excel 1.2.0 new global options were added to modify the behavior of the moment when SQL queries that affect a MySQL database are shown, or to choose not to show SQL queries at all. The Global Options dialog can be accessed from the Options button located in the Schema Selection panel or the DB Objects Selection one as shown below.
After clicking on Options the Global Advanced Options dialog will appear, under the SQL Queries Options section the 3 new options to show SQL queries will appear as shown below. These options affect SQL queries generated by the Create Schema, Export Data, Append Data and Edit Data operations.
The Do not show SQL statements sent to the server option is selected by default, with it SQL queries will never be shown and after their execution just their results will be displayed within an information dialog.
The Preview SQL statements before they are sent to the server option adds an extra step to all of the four aforementioned data operations before any query is committed to the server. It will display the Review SQL Script dialog shown below (in this particular example, an Export Data operation was performed).
Note the dialog shows some information about the SQL script it contains, in this case it reflects it is creating the table actor_copy and inserting 200 rows of data to it. A text box will contain the SQL statements related to the data operation being performed, the SQL script is fully editable at this point, meaning that you are able not only of reviewing the script but also to make alterations to it before it is applied against the connected MySQL server. The intention of this text box is to provide a way to make a few and easy editions to the SQL statements (if any are needed) and not to provide a full and rich SQL editor; if you happen to need it you can always copy&paste the SQL script into a powerful editor such as the one provided in MySQL Workbench or any other of your preference.
In case the SQL statements are modified, the Original Query button will be enabled, if clicked it will revert back the whole script to the way it originally was when the dialog was opened. Take a look at the screenshot below as an example of how the query in the image above was altered, notice the places where changes occurred are highlighted.
Clicking Apply will execute the SQL script contained in the dialog and then an information dialog with the results sent back from the server will be displayed (to indicate whether the query was successful, warnings were found or an error was thrown and nothing was committed).
Finally, the Show executed SQL statements along with their results option will first execute the SQL statements and then show an information dialog with the results of the query execution along with the executed SQL statements. This last option is useful to review what was just executed and you can see an example of the information dialog containing the SQL queries and their result below.
If you ever wondered what exact SQL statements were issued by MySQL for Excel or needed extended information about the executed data operations, now you know how you can get that information right away and straight from the product. We hope you give this and the other new features in the 1.2.x version family a try!
Remember that your feedback is very important to us, so drop us a message and follow us:
- MySQL on Windows (this) Blog: https://blogs.oracle.com/MySqlOnWindows/
- MySQL for Excel forum: http://forums.mysql.com/list.php?172
- Facebook: http://www.facebook.com/mysql
- YouTube channel: https://www.youtube.com/user/MySQLChannel