Automating MySQL reports using Command Line

Learn about how to automatically generate MySQL data reports using command-line interface and automate data report routines with dbForge Studio for MySQL.

You can automate the report creation process using command line. Such possibility can be very useful if you need to create a report using the same report file. For this, you will need to create a report template to use it afterwards in the command line. Note that some parameters can be specified directly in the command line. In this case, values of the parameters specified in your template file will be overridden.

The walkthrough described further in this topic will show you how to use command line in order to automate the following data report routines: saving a report on disk in the HTML format, sending it by email and FTP.

Creating a Report Template

To create a report template, you should perform the following actions:

1. Click Design New Report on the Data Analysis tab of the product Start page.

2. The Data Report Wizard opens. Follow the wizard pages to create a report template file with .rdb extension.

3. Compose the report template.

4. Save the file for future usage.

Prerequisites

To complete the following walkthrough, you need to have an already created report file with *.rdb extension. Refer to the corresponding tutorials to create and master-detail report files that can be used in command line as input files.

In this walkthrough Report1.rdb file, created in the Creating a Simple Data-Aware Report, will be used.

How to launch auto-email data reports process (with command-line)

Suppose you need to send a data report containing customers’ address and phone number list in HTML format to your authorities via e-mail and FTP every day. For this, you have a report template Report1.rdb (the file is located in C:\dbForge Studio\Reports), the folder on disk to which the report should be saved is C:\dbForge Studio for MySQL\Reports\Output\, the e-mail address of your authorities is [email protected], the address of your FTP server is ftp://myftphost.net/reports/, login is send-report, and password is report.

To accomplish such task through command line, you need to perform the following steps:

1. Open Command Prompt by pressing Win+R and type cmd in the Open line.

Note

To run Command Line in Windows 8:

From the Desktop Mode

  • Move the mouse cursor to the exact lower left corner until the desktop shortcuts menu appears
  • Right-click to see the shortcut menu and then click Run

From the Charms Bar

  • Move your mouse to the upper right corner until the Charms Bar appears
  • Select Apps from the list and type run in the search box
  • Click Run from the search results

Use Windows + R Shortcut Keys

In the desktop mode, press Windows key + R to show the Run command line.

2. In Command Prompt, navigate to the path where the dbforgemysql.com file is located (by default it’s C:\Program Files\Devart\dbForge Studio for MySQL). For this, use the cd operator:

C:\Users\User>cd C:\Program Files\Devart\dbForge Studio for MySQL\

Note

The path to the executable file will differ depending on how you have installed the Studio - as a standalone tool or as part of the dbForge Edge bundle. Make sure that you have specified a correct path. If you have installed the Studio as part of dbForge Edge, change the path accordingly:

C:\Program Files\Devart\dbForge Edge\dbForge Studio for MySQL\

Press Enter. You can see that the path has changed:

C:\Program Files\Devart\dbForge Studio for MySQL>

3. Now, call the dbforgemysql.com file:

C:\Program Files\Devart\dbForge Studio for MySQL> dbforgemysql.com

4. Specify the /datareport operation switch:

C:\Program Files\Devart\dbForge Studio for MySQL> dbforgemysql.com /datareport

5. Specify the path to your Report1.rdb input file and the name of the file itself:

C:\Program Files\Devart\dbForge Studio for MySQL> dbforgemysql.com /datareport /reportfile:"C:\dbForge Studio for MySQL\Reports\Report1.rdb"

6. Specify the format you want to save your report in. In this case, it’s HTML:

C:\Program Files\Devart\dbForge Studio for MySQL> dbforgemysql.com /datareport /reportfile:"C:\dbForge Studio for MySQL\Reports\Report1.rdb" /format:HTML

7. (Optional) To include parameters declared in a query script to command line, you can use the /parameters operation. For example, the following query script:

SELECT
first_name, 
last_name,
email
FROM customer
WHERE store_id = :store_id and active = :active

will look as follows:

C:\Program Files\Devart\dbForge Studio for MySQL> dbforgemysql.com /datareport /reportfile:"D:\Projects\Report1.rdb" /parameters store_id:2 active:0

8. To start working with the result file, type the /result operation.

  • To save your report in the specified folder on disk, type the path to this folder:
C:\Program Files\Devart\dbForge Studio for MySQL> dbforgemysql.com /datareport /reportfile:"C:\dbForge Studio for MySQL\Reports\Report1.rdb" /format:HTML /result folder:"C:\dbForge Studio\Reports\Output\"
  • To send your report by email, add the email address:
C:\Program Files\Devart\dbForge Studio for MySQL> dbforgemysql.com /datareport /reportfile:"C:\dbForge Studio for MySQL\Reports\Report1.rdb" /format:HTML /result email:"[email protected]"
  • To send your report to multiple emails, add the email addresses as follows:
C:\Program Files\Devart\dbForge Studio for MySQL> dbforgemysql.com /datareport /reportfile:"C:\dbForge Studio for MySQL\Reports\DataReport1.rdb" /format:HTML /result email:"[email protected], [email protected]"
  • To send your report by FTP, specify the host, login, and password:
C:\Program Files\Devart\dbForge Studio for MySQL> dbforgemysql.com /datareport /reportfile:"C:\dbForge Studio for MySQL\Reports\Report1.rdb" /format:HTML /result folder:"ftp://myftphost.net/reports/ " ftplogin:send-report ftppassword:report

Note

If your report is in the HTML format and consists of several files or folders, it is not possible to send such areport by email or FTP using command line interface.

Note

You can perform two actions at a time with the result: save it on disk and send by e-mail, or send it by FTP and e-mail.

Note

If you need to create the same report and perform the same action with the result frequently, you can save the needed command line in a *.bat file and run it each time you need to create a report. In this case, you will not have to type all the necessary operators manually in Command Prompt any more.

Note

To see quick help on the arguments available in the command line, you can type /datareport /?

How to automatically email comparison report

dbForge Studio for MySQL allows you to create automatic MySQL email reports by entering code into the command line utility. There is a simple way to send comparison reports automatically via email.

To accomplish this task, we will use the Collaboration Data Objects (CDO), previously known as OLE Messaging or Active Messaging. CDO is an application programming interface included with Microsoft Windows.

Below is the simple BAT file that automatically saves a comparison report in the HTML format on the “D:\” drive and runs the send.vbs script file.

call “C:\Program Files\Devart\dbForge Studio for MySQL\dbforgemysql.com” /datacompare /compfile:d:\project.dcomp /reportformat:HTML /report:d:\report.html start send.vbs

The send.vbs script contains the essential settings that are important for the automatic email delivery:

  1. Const mAttachment - report file name.
  2. Const mFrom - sender email address
  3. Const mTo - recipient email address
  4. Const mSubject - email subject
  5. Const mTextBody - email text
  6. Const mSMTPServer - SMTP server name
  7. Const mSMTPport - SMTP server port
  8. oEmail - contains the CDO object
  9. mReportDir - a directory that contains the report file

The entire end.vbs script looks as follows:

' Script Name: Send.vbs
' Date: 05.09.2014
' Author: Devart Team www.devart.com
' Description:
Option Explicit
On Error Resume Next
Dim mReportDir
Dim oShell, oEmail
Const mAttachment = "report.html"
Const mFrom = "[email protected]"
Const mTo = "[email protected]"
Const mSubject = "Data Compare Report"
Const mTextBody = "See report..."
Const mSMTPServer = "SMTPserverIP_NAME"
Const mSMTPport = 25
Set oShell = CreateObject("WScript.Shell")
Set oEmail = CreateObject("CDO.Message")
mReportDir = "d:\"
oEmail.From = mFrom
oEmail.To = mTo
oEmail.Subject = mSubject
oEmail.Textbody = mTextBody
Call oEmail.AddAttachment(mReportDir & mAttachment)
oEmail.Configuration.Fields.Item ("http://schemas.microsoft.com/" & _
"cdo/configuration/sendusing") = 2
oEmail.Configuration.Fields.Item ("http://schemas.microsoft.com/" & _
"cdo/configuration/smtpserver") = mSMTPServer
oEmail.Configuration.Fields.Item ("http://schemas.microsoft.com/" & _
"cdo/configuration/smtpserverport") = mSMTPport
oEmail.Configuration.Fields.Update
' Sending email
oEmail.Send

In order to send an email you need to edit the send.vbs script to fit your needs.

In case you need to implement automatic scheduled email reports for MySQL databases, you can use Windows Task Scheduler. It allows you to create and manage the emailing tasks that your computer will carry out at the specified time.

Want to Find out More?

Overview

Overview

Take a quick tour to learn all about the key benefits delivered by dbForge Studio for MySQL.
All Features

All features

Get acquainted with the rich features and capabilities of the Studio in less than 5 minutes.
Request a demo

Request a demo

If you consider employing the Studio for your business, request a demo to see it in action.
Ready to start using dbForge Studio for MySQL?
Download a free 30-day trial