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:

Parameter	Default	Valid Values
AppendFile	false	true,false, clear
BinaryFileDir		Directory path for data files when BinaryFormatis set to File
BinaryFormat	Don'tExport	Don'tExport, Size, Value, Hex, Base64, File
BooleanFalseFormat	false	false, no, 0, off
BooleanTrueFormat	true	true, yes, 1, on
CLOBFileDir		Directory path for data files when CLOBFormat isset to File
CLOBFormat	Value	Don't Export, Size, Value, File
CsvColumnDelimiter	\t(TAB)
CsvIncludeColumnHeader	true	true,false
CsvIncludeSQLCommand	false	true,false
CvsRemoveNewlines	false	true, false
CsvRowCommentIdentifier
CsvRowDelimiter	\n	\n(UNIX/Linux/Mac OS X), \r\n (Windows)
DateFormat	yyyy-MM-dd	Seevalid formats in ToolProperties document
DecimalNumberFormat	Unformatted	Seevalid formats in ToolProperties document
Destination	File	File
Encoding	UTF-8
ExcelFileFormat	xls	xls (Binary Excel) or xlsx(Excel 2007)
ExcelIncludeColumnHeader	true	true, false
ExcelIncludeSQLCommand	false
ExcelIntroText		Any description
ExcelTextOnly	false	true, false
ExcelTitle		Any title
Filename	REQUIRED	** **
Format	CSV	CSV,HTML, XML, SQL, XLS
HtmlIncludeSQLCommand	false	true,false
HtmlIntroText		Any description
HtmlTitle		Any title
NumberFormat	Unformatted	Seevalid formats in ToolProperties document
QuoteDuplicateEmbedded	true	true, false (quote char is the same asQuoteTextData)
QuoteTextData	None	None,Single, Double
Settings
ShowNullAs	(null)
SqlIncludeCreateDDL	false	true, false
SqlIncludeSQLCommand	false	true,false
SqlRowCommentIdentifier	--
SqlSeparator	;
TableName		Can be set if DbVisualizer cannot determine the value for the${dbvis-object} variable
TimeFormat	HH:mm:ss	Seevalid formats in ToolProperties document
TimeStampFormat	yyyy-MM-dd HH:mm:ss.SSSSSS	See valid formats in Tool Propertiesdocument
XmlIncludeSQLCommand	false	true, false
XmlIntroText
XmlStyle	DbVisualizer	DbVisualizer, XmlDataSet, FlatXmlDataSet

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.