The following article shows you how to setup a DbVisualizer export to run at certain intervals and send the export result as an email attachment.

 

The examples are in two flavours

  • For a Windows environment using VBScript and scheduling through the Windows Task Scheduler
  • For a Linux environment using shell script and scheduling through cron jobs.
Although the script examples can be used as a base for designing your own reports, the scripts lack proper error handling/logging and should be improved to handle this.

Exporting in Windows

The following files export the result of a simple select to a CSV file. The CSV file is sent as an attachment in an email message. The files are attached to this article.

  • runReport.vbs  Main VBScript which calls the runDbVis.vbs and sendMail.vbs scripts.
  • export.sql  Contains the SQL needed do the export.  
  • runDbVis.vbs  Runs DbVisualizer using the command line interface to run the sql file report.sql.
The following line of runDbVis.vbs does call the DbVisualizer Command Line interface dbviscmd.bat using the named connection H2 and running the sql file report.sql. Note the "0" at the end represents the intWindowStyle parameter and means that no window will be shown when running the script. Set it to "1" when debugging. For more information about the Run method see Microsoft Documentation

strReturn = oShell.Run ("""C:\Program Files\DbVisualizer\dbviscmd.bat"" -connection H2 -sqlfile C:\Users\ulf\Desktop\vbs\report.sql", 0, true)
  • sendMail.vbs Sends an email with the exported CSV file as an attachment.  The script needs to be adjusted to define the correct email server, accounts etc.

Using the Windows Task Scheduler

To make windows running the runReport.vbs once every 24 hours do the following 

  • Start the Task Scheduler and choose the action "Create Basic Task".
  • Name the task (E.g MyDbVisualizer Report) and optionally enter a description. Press next
  • Enter trigger type (E.g daily). In the next screen enter start time and value 1 for recurring every day. Press next
  • Specify the action to perform. Choose the runReport.vbs script. Press next and then finished

Exporting in Linux

The attached bash script runReport.sh runs a DbVisualizer export using the command line interface to run the sql file export_linux.sql and sends the exported file as an email attachment. 


Scheduling using crontab
The following shows how to schedule the report once every day at 10pm using crontab.
  • open a terminal and run "crontab -e"
  • Add the following line in the editor and save the file.
0 22 * * * /path_to_the_file/runReport.sh