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 Microsoft 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.


Generating Scripts

You can of course create the script by hand, but a convenient way is to create a SQL script in the SQL Commander, and - when you are satisfied that it works as expected - generate the command line version for scripting it, including all connection details (see Generating a Command From SQL Commander for more info).


Exporting in Windows

The following shows how to export data to a CSV file and then send that data to an e-mail address. The following description assumes the scripts are located on the host/computer running the Microsoft Windows Task Scheduler. The CSV file is sent as an attachment in an email message. The script files referred to in this section are attached to this article.


To adopt the example scripts you would need to

  1. Edit the file export.sql to export the data of your choice. Edit the file in the DbVisualizer GUI. There you can also do a test run.
  2. Edit the runDbVis.vbs.
    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\export.sql", 0, true)


    Specifically, you will need to
    • Change the path pointing out dbviscmd.bat to the DbVisualizer installation on the computer you are running the scripts (I.e change the C:\Program Files\DbVisua....... part)
    • Change part "-connection H2" to "-connection YourConnection" where "YourConnection" denotes the Database name defined in DbVisualizer of the database connection you want to connect to
    • Change the -sqlfile argument to specify the path to the export.sql file
    • 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
    • The path to the SQL file (-sqlfile parameter). If the path contains spaces you may need to add some extra double-quotes. Eg. -sqlfile ""C:\Users\ulf\test\folder with space\export.sql"""
  3. Edit the sendMail.vbs script to your needs. E.g setting password mail subject etc.
  4. Edit the runReport.vbs and change the paths to pinpoint the runDbVis.vbs and sendMail.vbs.
  5. To run this on a periodic basis please see the section "Using the Microsoft Task Scheduler" below 

Note: The supplied examples does not represent production quality scripts. They would need to be adopted to implement proper error handling etc.


Using the Microsoft Windows Task Scheduler

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

  • Start the Windows 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 the start time and value 1 for recurring every day. Press next
  • Specify the action to perform. Choose the report.vbs script. Press next and then finished

Attached scripts for Windows:

  • runReport.vbs  Main VBScript which calls the runDbVis.vbs and sendMail.vbs scripts.
  • export.sql  Contains the SQL needed to do the export.  
  • runDbVis.vbs  Runs DbVisualizer using the command line interface to run the SQL file report.sql.
  • 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.

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. To make this work you need to edit the script to reflect your environment, the receiver of the email, and the mail server account to use.


Scheduling using crontab

The following shows how to schedule the report once every day at 10 pm 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