MySQL Reporting using AutoSQL Tool

Three ways to schedule a MySQL query

If you walk through any office you see people working in Excel. With MySQL for Excel (https://www.mysql.com/why-mysql/windows/excel/) you can already let them pull information from Excel themselves. However, in some cases it saves a lot of time if they don’t have to pull the information, but it’s pushed automatically.
Exception lists are the best examples of queries you want to push to users. If you have a query with occasional results, you don’t want to check for this every day. In this case you just want to receive a mail if there are any results. Eg a list of stuck invoices which can’t be processed automatically.
How can we do this?

1. Using the MySQL Event Scheduler

The MySQL Event scheduler can be used to run a query on a predefined schedule and output the results in CSV format.
Let’s write a simple query to output stuck invoices to a CSV file (which can be imported with Excel).


SELECT * FROM erp.invoice WHERE status = 'stuck'
INTO OUTFILE 'c:\\DailyReports\\stuck_invoices.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

Note that you can specify some formatting options of the file like the separator and the line terminator. Choosing the options above will allow Excel to open the file without problems.
Now let’s create a new MySQL Event to have it run every day at 9 AM:

CREATE
EVENT
erp.daily_invoice_stock
ON SCHEDULE AT '2017-01-29 09:00:00' + INTERVAL 1 day
DO
SELECT * FROM erp.invoice WHERE status = 'stuck'
INTO OUTFILE 'c:\\DailyReports\\stuck_invoices.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

Creating events in MySQL does requires the ‘EVENT’ privilege on the schema in which the event is created.

2. Scripting

Using command line tools you can do a lot. The main benefit from using the events is that you don’t need specific database permissions to create them. Instead you could create a simple batch script and schedule it using the Windows Task Scheduler.
Create a file C:\invoice_stuck.sql with the query you want to schedule:


SELECT * FROM erp.invoice WHERE status = 'stuck'
INTO OUTFILE 'c:\\DailyReports\\stuck_invoices.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

Create a batch script c:\execute_query.cmd with the following contents:

mysql.exe –u query_exec –p my_password <c:\invoice_stuck.sql

Now schedule this batch file with Windows Task Scheduler to run at any interval.

3. Using a commercial application like AutoSQL

There are several 3rd party tools to do the same and more. The major benefits are:
 No special MySQL database privileges needed to create events (like with scripting)
 Outputting to genuine Excel format (dates, numbers and columns nicely formatted)
 Option to email the results
 Easy for any user to setup
Here we will use AutoSQL (http://www.autosql.net), a simple single purpose tool to schedule queries and specify the output. It actually creates action lists which can be scheduled by Windows Task Scheduler.
AutoSQL uses an ODBC connection to connect to a data source. Therefore you need to have the MySQL ODBC driver installed (https://dev.mysql.com/downloads/connector/odbc/) and setup an ODBC connection (https://www.youtube.com/watch?v=K3GZidOwGmM).
Setup the query
Now choose the MySQL ODBC connection in the ‘Get DSN’ Window and copy the SQL query in the query window:
Specifying the

autosql1

Specifying the output
On the ‘Output’ tab we can specify that we want to output it to an Excel and send an email with the file attached. Here you can also specify you only want to send it if there are more than 0 results.

autosql2

After you tested it, you can click on ‘Get Background Command’. This copies the background execution command to the clipboard. You can use this in Windows Task Scheduler to actually execute the command on a schedule.

Conclusion

If you need to schedule a query and have no specific needs on the output format using the Event scheduler from MySQLor a simple script is perfect. However the output options are limited (CSV) and it is not really user friendly for the average user.
If you need better output and you are not into heavy scripting, take a look at the commercial options available.