7.3 Aqua Commands (Revision #1)

Query analyzer scripts have support for 9 client side commands which allow you to execute a stored procedure with local variables bound to the parameters.

This allows for the execution of procedures with OUT parameters. The commands include print, println, variable, executeCallableQuery, sendMail, saveResults, saveLastResult, setResultSettings and setPivotResultSettings.

Client side commands are identified by a dot at the beginning of the command line, and multiple commands can be executed within one batch statement. The schema browser contains object scripting for procedures and functions that include "EXECUTE" and "EXECUTE BIND". The script EXECUTE will generate the appropriate code needed to execute the object according to the database procedural language (ie. PL/SQL, T-SQL). The EXECUTE BIND will generate the appropriate Aqua Data Studio client side commands to execute the object. To easily generate the needed client commands to execute a procedure or function, you may browse in the schema browser to locate the procedure or function and right-click on the object to select "EXECUTE BIND" from the Script Object menus.

Command support

Query Analyzer scripts have support for client side commands ".sendMail", ".saveResults", ".saveLastResult", "setResultSettings", "setPivotResultSettings" which allow users to either save query results in an EXCEL, HTML, XML, or CSV document, or insert them into a table.

The ’sendMail’ command sends emails using the settings you specify and allows attachments.

The ’saveResults’ command saves the results of all of the queries in the window, while the ’saveLastResult’ command only saves the result for the last query.

The ’setResultSettings’ command lays out how grid data will be presented.

The ’setPivotResultSettings’ command lays out how pivot grid data will be presented.

Here is a combination example script using .setPivotResultSettings, .saveResults and .sendMail which runs a query, takes data from that query, laysout and generates a pivot grid result, saves the results to two different Excel files, then emails a message with both Excel files as attachments.

.println [.print]

Syntax:
.println { <variable_name> | ’text value’ }[, ... n]

Example:
.println myvar
.println ’ Results:’, OUTVAR

.variable

Syntax:
.variable <variable_name>, datatype [, value]

Example:
.variable myvar, varchar, ’this is a test’
.variable INVAR, NUMBER, 0
.variable OUTVAR, NUMBER, 0
.variable INOUTVAR, DATE, ’2005-01-01 12:00:00 AM’

.executeCallableQuery

Syntax:
.executeCallableQuery ’parameter_name<datatype,{in | out | inout}>[, ... n]’, <LINE_BREAK>
{ CALL <PROCEDURE_NAME>( ? { , ? ... } ) }
or ...
{ ? = CALL <PROCEDURE_NAME>( ? { , ? ... } ) }

Example:
.executeCallableQuery ’INVAR<NUMBER,in>,OUTVAR<NUMBER,out>,INOUTVAR<DATE,inout>’,
BEGIN
    SCOTT.FULL_PROCEDURE(?, ?, ?);
END;

.sendMail

Syntax:
.sendMail ’host=mail.aquafold.com,
port=25,
ssl=TRUE,
authentication=TRUE,
user=myuser,
password=mypass,
[email protected],
to="[email protected],[email protected], [email protected]",
cc="[email protected],[email protected]",
bcc="[email protected],[email protected]",
subject="New Software Release",
message="Get the latest version of ADS!\nReady for download at www.aquafold.com",
attachments="c:/my directory/abc.xls, D:/another/def.txt"’

.saveResults and .saveLastResult
(all of the .saveResults commands work for both .saveResults and .saveLastResult)

.saveResults

Syntax:
.saveResults ’Format=CSV,
File="<filename>",
Delimiter=<delim>,
QuoteIdentifier=<quote_identifier>,
IncludeHeader=<True/False include_header>,
IncludeRowCount=<True/False include_row_count>,
IncludeNullText=<True/False include_null_text>,
NewLine=<plat_new_line>,
Encoding=<encode_full_name>,
Overwrite=<True/False overwrite>’

Syntax:
.saveResults ’Format=XML,
File="<filename>",
QuoteIdentifier=<quote_identifier>,
IncludeHeader=<True/False include_header>,
IncludeRowCount=<True/False include_row_count>,
IncludeNullText=<True/False include_null_text>,
NewLine=<plat_new_line>,
Encoding=<encode_full_name>,
Overwrite=<True/False overwrite>’

Syntax:
.saveResults ’Format=HTML,
File="<filename>",
IncludeSQL=<True/False include_sql_stmt>,
QuoteIdentifier=<quote_identifier>,
IncludeHeader=<True/False include_header>,
IncludeRowCount=<True/False include_row_count>,
IncludeNullText=<True/False include_null_text>,
NewLine=<plat_new_line>,
Encoding=<encode_full_name>,
AlternateRow=<True/False should_alternate_row_color>,
AlternateRowForeground=<alt_row_foreground_color>,
AlternateRowBackground=<alt_row_background_color>,
Overwrite=<True/False overwrite>’

Syntax:
.saveResults ’Format=INSERT,
File="<filename>",
QuoteIdentifier=<quote_identifier>,
IncludeHeader=<True/False include_header>,
IncludeRowCount=<True/False include_row_count>,
IncludeNullText=<True/False include_null_text>,
NewLine=<plat_new_line>,
Encoding=<encode_full_name>,
Overwrite=<True/False overwrite>’

Syntax:
.saveResults ’Format=EXCEL,
Source=<GRID or PIVOT>,
File="<filename>",
IncludeSQL=<True/False include_sql_stmt>,
IncludeHeader=<True/False include_header>,
IncludeRowCount=<True/False include_row_count>,
IncludeNullText=<True/False include_null_text>,
AlternateRow=<True/False should_alternate_row_color>,
AlternateRowForeground=<alt_row_foreground_color>,
AlternateRowBackground=<alt_row_background_color>,
Overwrite=<True/False overwrite>’

Example:
.saveResults ’format=excel,
source=pivot,
file=C:/abc.xls,
overwrite=true’

.setResultSettings

Syntax:

"CategoryFields" is now synonymous with "RowFields"
and
"SeriesFields" is now synonymous with "ColFields"

The List of Aggregate Functions can contain one or more of the following:
Sum, Max, Min, Mean, Var, Stddev, Count

.setResultSettings ’Index=<Index #>,
Title=<Title>,
CategoryFields=<Category Field List>,where a field looks like "[Field Name] {List of Aggregate Functions}
SeriesFields=<Series Field List>,
SortBy=<row | col | data>,
SortOrder=<asc | desc>,
ShowTitle=<True or False Show Title>,
TopCount=<Top Count>,
ShowTopCount=<True or False Show Top Count>,
ShowLegend=<True or False Show Legend>,
ShowChart=<True or False Show Chart>,
Chart=<area | bar | column | line | pie | surface | stacked>,
ScaleData=<True or False Scale Data>,
RotX=<Rotation X in degrees>,
RotY=<Rotation Y in degrees>,
RotZ=<Rotation Z in degrees>,
Zoom=<Zoom [ 0.0-100.0]>,
Split=<Split Divider Location [0.0-1.0]>,
Paging=<True or False Paging>,
Paging=<RowsPerPage>’

Example:
.setResultSettings ’Index=1,
Chart=column,
RotX=325,
RotY=53,
RotZ=0,
Zoom=50.0,
ScaleData=true,
ShowChart=true,
ShowLegend=true,
ShowTitle=false,
ColFields="[ShipCountry]",
DataFields="[Freight]",
Paging=true,
ShowTopCount=false,
Split=0.35460994’

.setPivotResultSettings

Syntax:

"CategoryFields" is now synonymous with "RowFields"
and
"SeriesFields" is now synonymous with "ColFields"

The List of Aggregate Functions can contain one or more of the following:
Sum, Max, Min, Mean, Var, Stddev, Count

.setPivotResultSettings ’Index=<Index #>,
ShowFields=<True or False to show list of unselected fields>,
ShowTotal=<True or False to Show Total Columns>,
ShowSubTotal=<True or False to Show SubTotal Columns>,
Title=<Title>,
RowFields=<Row Field List>,where a field looks like "[Field Name] {List of Aggregate Functions}
ColFields=<Column Field List>,
DataFields=<Data Field List>,
SortBy=<row | col | data>,
SortOrder=<asc | desc>,
ShowTitle=<True or False Show Title>,
TopCount=<Top Count>,
ShowTopCount=<True or False Show Top Count>,
ShowLegend=<True or False Show Legend>,
ShowChart=<True or False Show Chart>,
Chart=<area | bar | column | line | pie | surface | stacked>,
ScaleData=<True or False Scale Data>,
RotX=<Rotation X in degrees>,
RotY=<Rotation Y in degrees>,
RotZ=<Rotation Z in degrees>,
Zoom=<Zoom [0.0-100.0]>,
Split=<Split Divider Location [0.0-1.0]>,
Paging=<True or False Paging>,
Paging=<RowsPerPage>’

Example:
.setPivotResultSettings ’Index=1,
Chart=column,
RotX=324,
RotY=12,
RotZ=0,
Zoom=50.0,
ScaleData=true,
ShowChart=false,
ShowLegend=true,
ShowTitle=false,
RowFields="ShipCountry",
ColFields="ShipVia",
DataFields="Freight",
Paging=true,
ShowTopCount=false,
Split= 0.5,
ShowFields=true,
ShowTotal=true,
ShowSubTotal=true’

COMBINATION EXAMPLE SCRIPT

select * from orders

GO

.setPivotResultSettings ’Index=1,
Chart=column,
RotX=324,
RotY=12,
RotZ=0,
Zoom=50.0,
ScaleData=true,
ShowChart=false,
ShowLegend=true,
ShowTitle=false,
RowFields="ShipCountry",
ColFields="ShipVia",
DataFields="Freight",
Paging=true,
ShowTopCount=false,
Split= 0.5,
ShowFields=true,
ShowTotal=true,
ShowSubTotal=true’

GO

.saveResults ’format=excel,
source=pivot,
file=C:/abc.xls,
overwrite=true’

GO

.saveResults ’format=excel,
source=pivot,
file=C:/abc2.xls,
overwrite=true’

GO

.sendMail ’host=smtp.gmail.com,
port=465,
ssl=true,
authentication=true,
user=<user_name>,
password=<mypassword>,
[email protected],
[email protected],
subject="Nice document attached",
message="This will blow your sockets off\ncheck it out!",
attachments="c:/abc.xls,c:/abc2.xls"’