DbVisualizer - SQL Commander

DbVisualizer SQL

Reference

DbVisualizer - SQL Commander

@export- exportresult sets to file

The @export commands are used to declare that any result sets from the SQL statements that follows should be written to a file instead of being presented in the DbVisualizer tool. This is really useful, since it enables dumping very large tables to a file for later processing or, for example, to perform backups. The following commands are used to control the export:

• @export on
  Defines that the SQL statements that follows will be exported rather then being presented in DbVisualizer
• @export set parm1="value1" parm2="value2"
  The set command is used to customize the export process. Check the table below for the complete set of parameters.
• @export off
  Defines that SQL statements that follows will be handled the normal way, i.e., the result sets are presented in the DbVisualizer tool

These parameters are supported:

Example1: @export withminimum setup

The following example shows the minimum commands to export a result set.

The result set produced by the select * from Orders will be exported to the C:\Backups\Orders.csv file, using the default settings.

@export on;
@export set filename="c:\Backups\Orders.csv";
select * from Orders;

Example2: @export withautomatic table name to file name mapping

This example shows how to make the filename the same as the table name in the select statement. The example also shows several select statements. Each will be exported in the SQL format. Since the filename is defined to be automatically set, this means that there will be one file per result set and each file is named by the name of its table.

There must be only onetable name in a select statement in order to automatically set thefilename with the ${dbvis-object}$ variable, i.e if the select joinsfrom several tables or pseudo tablesare used, you must explicitly name the file.

The ${dbvis-object}$ variable is not substituted with atable name if using the AppendFile="true/clear" parameter.

@export on;
@export set filename="c:\Backups\${dbvis-object}$" format="sql";
select * from Orders;
select * from Products;
select * from Transactions;

Example3: @export all resultsets into a single file

This example shows how all result sets can be exported to a single file. The AppendFile parameter supports the following values.

• true
     The following result sets will all be exported to
  a single file
• false
     Turn off the append processing
• clear
     Same as the true
  value but this will in addition clear the file before the first result set is exported

@export on;
@export set filename="c:\Backups\alltables.sql" appendfile="clear" format="sql";
select * from Orders;
select * from Products;
select * from Transactions;

Example4: @export usingpredefined settings

The Export dialogs let you save export settings to a file for later use. Such an export settings file can be referenced in the @export set command.

@export on;
@export set settings="c:\tmp\htmlsettings.xml" filename="c:\Backups\${dbvis-object}$";
select * from Orders;
select * from Products;
select * from Transactions;

The example shows that all settings will be read from the c:\tmp\htmlsettings.xml file.