Easily find issues by searching: #<Issue ID>
Example: #1832
Easily find members by searching in: <username>, <first name> and <last name>.
Example: Search smith, will return results smith and adamsmith
To open a FluidShell window you click on the FluidShell button in the Server application menu or application toolbar with a key binding of Ctrl-Shift-F. When opening a FluidShell, the shell will attempt to establish a default database connection to any registered server the user may have selected in the schema browser. If no server is selected then no default database connection will be established in the newly open shell.
Once the a new FluidShell is open, it will execute any initialization script. If a FluidShell is open on a specific registered server it will execute the initialization script defined in the server registration in the FluidShell tab. If a FluidShell is open without a registered server it will execute the global initialization script defined in the File->Options->FluidShell->Script. Users may configure their shells environment per registered server or at a global level by issuing any specific shell commands.
In the FluidShell command prompt users may execute commands by typing the command name and hitting enter. Adding a "\" to the beginning of a command will give the shell an explicit request to execute the command line as a shell command. The first command to test is the <help> command which will give you a list of commands available in the shell.
:$ \help
You can also get more detailed help for a specific command by typeing \help and the command name :
:$ \help go
The shell interprets command lines based on the Command Line Interpreters (CLI) configurable options indicated by the shell variables. To see a list of the shell variables that control the CLI you may type :
:$ export | grep CLI
Which will produce the result ...
declare -x CLI_SHELL_LINE_INTERPRETER_ERROR_ON_EXPLICIT_CMD_NOT_FOUND="true"
declare -x CLI_SHELL_LINE_INTERPRETER_EVAL_COMMENT="sql"
declare -x CLI_SHELL_LINE_INTERPRETER_EVAL_EXPLICIT_CMD="true"
declare -x CLI_SHELL_LINE_INTERPRETER_EVAL_EXPLICIT_SQL="false"
declare -x CLI_SHELL_LINE_INTERPRETER_EVAL_HISTORY="true"
declare -x CLI_SHELL_LINE_INTERPRETER_EVAL_IMPLICIT_CMD="true"
declare -x CLI_SHELL_LINE_INTERPRETER_EXPLICIT_CMD_CHAR="\\"
declare -x CLI_SHELL_LINE_INTERPRETER_EXPLICIT_SQL_CHAR=";"
declare -x CLI_SHELL_LINE_INTERPRETER_IMPLICIT_BEHAVIOR="sql"
declare -x CLI_SHELL_LINE_INTERPRETER_IS_AT_SIGN_GO="true"
declare -x CLI_SHELL_LINE_INTERPRETER_IS_FORWARDSLASH_GO="true"
declare -x CLI_SHELL_LINE_INTERPRETER_IS_SEMICOLON_GO="false"
declare -x CLI_SHELL_LINE_INTERPRETER_PERFORM_ALIAS_EXPANSION="true"
To learn more about each CLI shell variables visit the Shell Variable documentation : LINK
The shell provides a command called <cli> that makes it easier to control the behavior of the CLI. You can learn more about the command with :
\help cli
A user can change the behavior of the CLI to 3 predefined profiles : fluid, shell & sql. The default behavior is fluid. The fluid behavior tells the CLI to interpret all command lines as shell commands unless there is no matching command, in which case it falls back to an SQL buffer command which places the command line into the SQL buffer. As an example, the following command line :
:$ ls
is interpreted as a shell command becuase there is a matching command called "ls". While the following command line :
:$ select * from orders
is interpreted as a SQL buffer command because there is no matching command called "select". This behavior provides a user a fluid command line which allows the user to type and enter commands just like any other Unix and SQL shell without having to alternate between different interfaces.
The shell also provides alias commands similar to Unix shells. Use the <alias> command to list or create aliases :
List aliases :
:$ alias
alias go='\go'
alias GO='\go'
alias ls='ls -l'
alias man='help'
alias set='declare'
Create new alias :
:$ alias ls='ls -lh'
:$ ls
drwx- 4.0K 2012-08-06 12:45 directory1
drwx- 4.0K 2012-08-06 12:45 directory2
drwx- 4.0K 2012-07-18 13:13 directory3
-rw-- 5.7M 2012-08-04 07:37 orders.csv
To view the contents of the SQL buffer you may click on the toolbars SQL buffer button which includes the line number the buffer is at to the right. This will popup an editor where you may modify the SQL buffer. You can also use the <sqlbuffer> command to view or clear the buffer. Below is an example of how to enter, print and clear the SQL buffer.
:$ sqlbuffer -p
:$ select * from orders
:$ limit 5
:$ sqlbuffer -p
select * from orders
limit 5
:$ sqlbuffer -c
:$ sqlbuffer -p
:$
Once the SQL buffer contains the SQL you want to execute, you can use the <go> command. Below is an example of how to enter an SQL statement into the SQL buffer and then execute it.
:$ select order_id, product_name, quantity
:$ from orders
:$ limit 5
:$ go
order_id product_name quantity
----------- --------------- -----------
01012007-72207-847 Aqua Data Studio v6.0 [w/ One Year Subscription] 1
01012007-72207-ROWBOAT Aqua Data Studio v6.0 [w/ One Year Subscription] 1
01012008-72207-14343 Aqua Data Studio v6.5 [w/ One Year Subscription] 1
01012008-72207-1529 Aqua Data Studio v6.5 [w/ One Year Subscription] 1
01012008-72207-BLACKDUCK Aqua Data Studio v6.5 [w/ One Year Subscription] 1
5 record(s) selected [Fetch MetaData: 1ms] [Fetch Data: 0ms]
[Executed: 9/11/2012 8:45:15 AM] [Execution: 1ms]
As you work in the shell you may want to execute previously execute shell commands or SQL commands. The shell maintains a list of previously executed shell commands. The history behavior is controled by the shell variables HIST* which can be viewed as follows :
:$ export | grep HIST
declare -x CLI_SHELL_LINE_INTERPRETER_EVAL_HISTORY="true"
declare -x HISTCONTROL="ignoredups:ignorespace"
declare -x HISTFILE="/home/dbuser/.datastudio/.datastudio/connections/MySQL-Server.history"
declare -x HISTFILESIZE="500"
declare -x HISTSIZE="500"
These variables may be configured to control the behavior of the history.
You may view a list of your history with the <history> command. You may use history expansion with an "!" just as in any Unix shell. And the UP and DOWN arrow allow you to cycle the current command line to any command in the history. Below is an example of how to list your history and execute a previous command with history expansion.
:$ history
...
58 export
59 clear
60 export | grep HIST
61 history
62 export | grep history
63 clear
64 history
:$ !60
export | grep HIST
declare -x CLI_SHELL_LINE_INTERPRETER_EVAL_HISTORY="true"
declare -x HISTCONTROL="ignoredups:ignorespace"
declare -x HISTFILE="/home/dbuser/.datastudio/.datastudio/connections/MySQL-Server.history"
declare -x HISTFILESIZE="500"
declare -x HISTSIZE="500"
:$
To learn more about each history shell variables visit the Shell Variable documentation : LINK
-> try out some autocompletion on shell commands and SQL.
-> notice the execution time at top right to tell you how long a command takes to execute.
-> manage db connections. use \connect, \reconnect, \disconnect and \session to manage your connections. You may also use the reconnect button on the toolbar.
-> Understanding pipelines, redirection, variable sub & sql variable sub.
a. redirection
b. pipeline
c. variable sub
d. sql variable sub
D. Exporting data to Excel using pipelines and redirection. :
:$ select * from orders
:$ sqlexport | csv2excel > orders.xlsx
E. Writing and Excuting Scripts
Edit a file called myscript.sql and add the lines :
select * from orders
sqlexport | csv2excel > orders.xlsx
Then execute the file with :
:$ source myscript.sql
**** End Outline by Niels *******
Starting a Shell
To open a SQL Shell window (tab), click on the "SQL Shell" toolbar button:
If a server has been selected in the schema tree prior to that, the shell will automatically establish a connection to that server. There is also a "SQL Shell" popup menu on each registered server in the schema or the project tree.
|
|
|
|
|
SQL Shell Application Toolbar Button |
SQL Shell Right Click Menu |
Server Properties SQL Shell tab |
When starting, the shell executes a script defined in the "SQL Shell" section of the Server Properties dialog. This can be used to load aliases and variables specific to that server.
The shell operates with SQL queries as well as shell commands. To distinguish between the two, all built-in commands start with backslash character (Example: \help). It is possible to define a command alias that does not require a backslash character, ls for example.
The shell UI permits entering complex multi-line queries via its work buffer. Any text which is not recognized as a built-in command gets appended to the query work buffer, which can be opened via \sqlbuffer edit command. A \go command (and its equivalents - a trailing (/) or (@) symbols) sends the contents of the work buffer to the query in the background, showing the result immediately. The query is also stored in the SQL history buffer (available via \sqlhistory command).
|
|
|
|
SQL Shell Query Executed |
SQL Shell Multiline Query |
The shell window provides context-aware auto completion function: just press TAB or Ctrl-SPACE key on the keyboard.
|
|
|
SQL Shell Autocompletion |
Scripting
The FluidShell command line tries to follow the same rules as a common Bourne shell, allowing for redirection, pipelining, command aliasing. Working with the FluidShell is complicated by the fact that it has to understand two "languages" at the same time: command language and SQL syntax. Some rules are modified to make this possible.
When a line is read by the shell, the first thing checked is whether it's a comment. Then the command line is analyzed to see if it contains a built-in command or an SQL statement.
In case of a built-in command, the variables are found and expanded, followed by command aliasing. The shell then checks for pipelining or input/output redirection.
If the command contains an SQL statement, it's appended to an off-screen SQL buffer. The buffer is sent to the server as a result of the \go command. A trailing (/) or (@) character can be used as an inline \go. Keep in mind that the variable substitution will be performed when the \go command is issued, the SQL buffer contains unmodified text.
A running command can be cancelled with Ctrl-C (which is also used for copy to clipboard. Only when a command is running does Ctrl-C keystroke function as command cancellation).
Comments
Any line beginning with a (#) symbol followed by a non-alphanumeric character is interpreted as a comment. This is to make sure temporary table names are not confused with comments. Notice that below the initial line 2 is ignored and the initial line 3 is ignored because of the #, and yet the statement executes successfully
sa:[SQL Server 2012 - 80]:1> select * from Orders where
sa:[SQL Server 2012 - 80]:2> # getting orders table list of orders...
sa:[SQL Server 2012 - 80]:2> Freight > 890
sa:[SQL Server 2012 - 80]:3> # for all orders where freight is larger than 890 lbs
sa:[SQL Server 2012 - 80]:3> go
>[SQL] Script lines: 1-3 ----------------------------
select * from Orders where
Freight > 890
Variables can be referenced using the following syntax:
${variable}
Within quoted strings, variable definitions can be defined with a backslash character such as:
"\${variable}"
A variable can be declared by the \declare command:
\declare name=value
Name can only be a combination of alphanumeric character, underscore or period and may or may not contain any special characters such as ( ' ) or ( " ). Value may contain anything, however if it is to include special characters, then it must be quoted.
|
|
|
|
SQL Shell Declaring a Variable |
Note that variables are expanded prior to breaking command line into words. To illustrate how the parser treats variables If it contains whitespaces, see the example below:
\echo "-n foo"
prints
-n foo
while
\echo -n foo
prints
foo1>
as it takes -n as the option not to output trailing newline in the echo command.
Now let us declare a variable which contains whitespaces and see how it is interpreted:
\declare a="-n foo"
\echo ${a}
prints
foo1>
|
|
|
|
SQL Shell Variable Containing Whitespace |
To ensure the maximum amount of compatibility between FluidShell and various database servers, variables are not expanded in SQL statements by default. To enable variable substitution in SQL statements, use the following command:
\declare SQL_VARIABLES=1
Quoting
Quoting is used to prevent the interpretation of special characters, such as whitespace, variable expansion symbols and quotes by the shell. There are several types of quoting: escape, single quotes, double quotes. The escape symbol ( \ ) is used to prevent the interpretation of the immediately following special character(quote, double quote, or another escape symbol) - but only within a shell command. The ( \ ) symbol is treated as text in an SQL statement.
For example:
\echo Can\'t find the file
outputs
Can't find the file
Enclosing text in single or double quotes disables an interpretation of special characters. A variable within a quoted string will not be expanded, unless it is escaped with the ( \ ) character.
Enclosing characters in single-quotes ( '' ) shall preserve the literal value of each character within the single-quotes.
Enclosing characters in double-quotes ( "" ) shall preserve the literal value of all characters within the double-quotes.
No escape is needed when characters are in quotes.
For example:
\echo 'Cant find the file'
prints
Cant find the file
while
\echo "find the file"
prints
find the file
|
|
|
|
SQL Shell Quoting |
The rules for Quoting SQL commands in FluidShell, follow the same as the Query Analyzer. Make sure you execute the below command before you try out variable substitution in SQL statements
\declare SQL_VARIABLES=1
Some examples:
To quote a string inside a SQL WHERE clause, double the symbol. If you need to find the occurences of shipname Alfred's in a table, use Alfred''s inside the WHERE clause
1> SELECT Shipname FROM orders WHERE ShipName like 'Alfred''s%'
Shipname
-----------
Alfred's Kerala
Example 2:
\declare myvar='ShipName'
| <column_name> | SQL statement to execute | Output |
| ${myvar} | 1> SELECT ${myvar} FROM orders where ${myvar} is not null / | displays all the shipname from the table |
| "\${myvar}" | 1> SELECT "\${myvar}" FROM orders where ${myvar} is not null / | displays all the shipname from the table |
| '"\${myvar}' | 1> SELECT '"\${myvar}' from orders where ${myvar} is not null / | "ShipName |
|
|
|
quote a string inside a WHERE clause |
Command Aliasing
Command aliases can be created by the \alias command.
Input/Output Redirection
FluidShell supports redirection of command input and output via standard notation. Unlike most shells however, redirection of stderr is not supported.
Examples:
Redirect (>) and append (>>) symbols are used to redirect command output to a file:
\ls -l > ListOfFiles.txt
\ls -l >> MoreFiles.txt
|
|
|
Input and Output Redirection |
A few commands that expect stream can use input redirection via (<) symbol:
\cat < text.txt
In addition, \ask command can be used to set a shell variable directly.
Pipelining
Commands can be chained by using a pipe character (|). The output of preceding command would be redirected to the input of subsequent command. The commands are run in parallel, with each command blocking if there is no input yet available.
Example:
\cat filename | \more
|
|
|
SQL Shell Pipelining Commands |
Wildcards
A wildcard is a character that can stand for all members of some class of characters. The examples below will make this clearer. We will use the command ls for illustration.
The * wildcard
The character * is a wildcard and matches zero or more character(s) in a file (or directory) name. For example, you might type:
ls prog*
This will list all files in the current directory starting with list.
You could type
ls *prog
This will list all files in the current directory ending with list.
The ? wildcard
The character ? will match exactly one character.
So ?ram will match files like gram
An example would be typing
ls list?
Default Directory
When a SQL Shell is opened, the current directory for the shell is set. The default directory depends on the context of the Server and the application:
Server with a mounted scripts folder: Default directory is set to the mounted scripts folder.
Projects Tab, Server with a mounted scripts folder: Default directory is set to the mounted scripts folder.
Projects Tab, Server without a mounted scripts folder: Default directory is set to the "User Files" folder of the project.
In any other case the default directory is defined by the JVM user.home property.
The current directory is displayed by the \pwd command. By default, the current working directory is part of the prompt for both \cli fluid and \cli shell modes.
|
|
|
|
SQL Shell Default Directory |
History Buffers
Two buffers are used to keep previously entered commands: the SQL buffer ( \sqlbuffer ) and the command history buffer ( \history ).
The SQL buffer accumulates the query to be sent by the \go command. This buffer can be opened in a built-in text editor with the \sqlbuffer edit command. A list view of all executed SQL statements can be seen by issuing \sqlhistory command. See the \sqlhistory page for details on its GUI.
|
|
|
sqlhistory command results |
The command history buffer keeps track of FluidShell commands. A number of shorthand combinations allow to re-issue the past commands without typing them again:
! Repeat the last command
!n Repeat the n-th command
!prefix Repeats the last command which starts with prefix.
The \history command displays the command history buffer.
If the variable ${histsave} is set to 1, the history buffer will be stored on disk. The file name of the history file can be obtained from a read-only ${histfile} variable.
Secure Storage
The FluidShell integrates Aqua Data Studio's secure storage facility. The information stored in the secure storage cannot be revealed after it has been entered, but can be indirectly supplied to several FluidShell commands in place of user credentials.
The following commands provide command line options to obtain the information from the secure storage: \connect, \reconnect, \ssh.
Database Access Commands
Connect to a Database Server, Reconnect and Disconnect:
In the FluidShell prompt, use the \connect command to establish connection to a RDBMS server using the command \connect -s SERVER [-d DATABASE] [-au] [-su NAME] [-u USER] [-ap] [-sp NAME] [-p PASSWORD] [-auth AUTH] [-ca AS] [-ct TO]
You can reconnect to the database server using \reconnect command and end the current transaction using the \disconnect command. For example,
1> \connect -s 'SQL Server 2000' - d Northwind -u dbo -p password
2> \reconnect
3> \disconnect
Another feature in FluidShell is that the \connect command automatically picks up the servername variable if the user doesn't include a server name in the parameters. Other options included in the \connect command for specific databases are
-auth Authentication [SQL Server Only] = { SQLSERVER, WINDOWS, SINGLESIGNON }>
-ca Connect As [Oracle Only] = { NORMAL, SYSOPER, SYSDBA, SYSASM }
-ct Connect To [Apache Derby Only] = { LOCAL, SERVER }
-ap Asks for the password
-su Reads the username from the secure storage
-sp Reads the password from the secure storage
List Databases, List Schemas, List Tables, List Views and Change to a different database or Schema:
\list displays the list of databases, schemas, tables or views defined in the connected RDBMS server. The connection to the RDBMS server needs to be established before the \list command is executed.
1> \list database
2> \list schemas
3> \list tables
\change [database DATABASE] changes the current connection database and \change [schema SCHEMA] changes the current schema. \change prints the current database or schema name if no name is provided.
Commit a Transaction, Rollback and AutoCommit Options:
Use the FluidShell prompt to connect to a database server, execute SQL commands and commit transactions. \commit ends the current transaction and makes permanent all changes performed in the transaction. Use \rollback command to rollback the current transaction to the beginning and use \autocommit to control the current database connection's autocommit mode. The options for this command are autocommit [on, off]. When autocommit is invoked with no arguments, it prints the current auto-commit status.
Go command and sqlhistory:
\go command sends the contents of the work buffer to the database.
1> select top 25 * from orders
2> \go
The command \sqlhistory displays the SQL statement history table with the time the sql commands were executed in the FluidShell prompt. You can select any of the previously executed commands and hit CTRL+E to execute. The Esc key exits the \sqlhistory and gets the control back to FluidShell prompt.
FluidShell application from Command Line
FluidShell scripts may be executed from the commandline without launching the Aqua Data Studio GUI. This allows scheduling the execution of scripts from an OS scheduler such as a Unix cron job or with Windows Task Scheduler. There are two files in the root install directory called "runfluidscript.sh/bat" and "runfluidscript-bundled.sh/bat".
An example of what you can do with this:
Every morning a cron job runs a script that queries for a set of data, lays out pivot or graph information, saves it to file and then emails the file to a set of people. These are just examples of how to run a fluidscript from the command line. If you type "runfluidscript" from within the root install directory the usage will display.
Windows Usage
runfluidscript-bundled.bat "-cMy Connection" "-fc:\myscript.sql" "-oc:\myscript-out.txt" "-uusername"
| connection [-c] | - [required] Identifies the connection name. |
| filename [-f] | - [required] File name of the script to execute. |
| output file [-o] | - [optional] File name for the text output. Standard out is used if not defined. |
| username [-u] | - [optional] Username to use for login. If not indicated, then the saved username will be used. |
| password [-p] | - [optional] Password to use for login. If not indicated, then the saved password will be used. |
Example:
runfluidscript-bundled.bat "-cSQL Server 2000" "-fc:\\scheduled-pivotgrid.sql"