Executing SQL

FluidShell provides a powerful and unique feature which allows execution of SQL statements directly from within a single shell window

$:1 select count(*) from dbo.categories;                                                  
$:2 go                                                                                   

 column1   
 ----------
 8         

 1 record(s) selected [Fetch MetaData: 114ms] [Fetch Data: 32ms] 

 [Executed: 10/30/2012 3:02:17 PM] [Execution: 70ms] 

$:1

For sake of clarity, longer SQL statements can be broken down into smaller statements and then executed by typing go on a separate line. For instance:

$:1 select count(*)                                                                       
$:2 from dbo.products                                                                     
$:3 where productid=1                                                                     
$:4 go                                                                                   

 column1   
 ----------
 1         

 1 record(s) selected [Fetch MetaData: 0ms] [Fetch Data: 1ms] 

 [Executed: 10/30/2012 3:08:40 PM] [Execution: 184ms] 

$:1  

Note: You need to type go on a separate line for executing the SQL command.

If the FluidShell has been opened in "shell" mode (see CLI for more details), then SQL command(s) need to be prefixed with a ";". For instance:

$:1 ;select count(*)                                                                       
$:2 ;from dbo.categories                                                                     
$:3 go                                                                                   

 column1   
 ----------
 10         

 10 record(s) selected [Fetch MetaData: 0ms] [Fetch Data: 1ms] 

 [Executed: 10/31/2012 5:28:40 PM] [Execution: 184ms] 

You can also directly execute SQL statements without the need for writing "go" by appending a "/" or "@" at the end of each statement. For example:

$:1 select count(*) from dbo.suppliers@                                                                              

 column1   
 ----------
 29        

 1 record(s) selected [Fetch MetaData: 0ms] [Fetch Data: 1ms] 

 [Executed: 11/1/2012 1:40:59 PM] [Execution: 56ms] 

$:1 select * from dbo.testdata/                                                                                      

 col_id    col_datetime2    col_datetimeoffset    col_time   
 ---------    -----------------------    -----------------------------    -------------
 1         9/18/2012 10:50:44 PM 9/18/2012 10:50:44 PM 10:50:44 PM

 1 record(s) selected [Fetch MetaData: 0ms] [Fetch Data: 1ms] 

 [Executed: 11/1/2012 1:41:02 PM] [Execution: 60ms] 

$:1  

Note: Both these delimiters are individually controlled by CLI_SHELL_LINE_INTERPRETER_IS_AT_SIGN_GO and CLI_SHELL_LINE_INTERPRETER_IS_FORWARDSLASH_GO CLI options, respectively.

If you would direct your attention to the $ prompt, you would notice that it has an index which increments once per statement.

For instance, here it has incremented three times

$:1 ;select count(*)                                                                       
$:2 ;from dbo.categories                                                                     
$:3 go << Index is at 3                             

This index represents the current position of the SQL Buffer.

SQL Buffer

Each instance of FluidShell has its own SQL Buffer. The SQL Buffer stores the most recently entered SQL command (but not FluidShell commands). The command remains in the buffer until it's executed. To execute a SQL command, type go on a separate line and press enter. You can bring up an editor to view, modify and execute the contents of the SQL Buffer by clicking the "SQL Buffer" icon in the FluidShell UI (see red box in image below).

The number next to the SQL Buffer icon represents the number of lines stored in the buffer. 

SQL Buffer Command Line Options

FluidShell also provides a command sqlbuffer to manipulate the SQL Buffer. This command comes with a number of useful options, including:

1. To clear the contents of SQL Buffer, execute sqlbuffer -c from FluidShell.

2. To dump the contents of SQL Buffer to standard output, execute sqlbuffer -p from FluidShell.

3. To edit the contents of SQL Buffer, execute sqlbuffer -e from FluidShell. This will open the SQL Buffer command window (as shown in the image below) and allow you to edit the contents directly.

4. To load content from an external file into the SQL Buffer, you may execute sqlbuffer -l from FluidShell. For example, to load SQL commands from a file called "cmd.txt" you can do the following:

:$ sqlbuffer -p                                                                                               

:$ sqlbuffer -l "c:\temp\cmd.txt"                                                                             
:$ sqlbuffer -p                                                                                               
 select count(*) from dbo.employees; 
:$  

(For the sake of this example "cmd.txt" contains only one SQL command.)

5. To dump the contents of SQL Buffer to an external, you may execute sqlbuffer -s from FluidShell. For example, to save the SQL Buffer to a file called "cmd.txt", you can do the following:

:$ sqlbuffer -p                                                                                               
 select count(*) from dbo.employees; 
:$ sqlbuffer -s "c:\temp\cmd.txt"                                                                             
:$  

If the file "cmd.txt" exists, then the contents of SQL Buffer are appended to "cmd.txt". If the file does not exist, a new file called "cmd.txt" will be created in the specified location.

Note: Saving a SQL Buffer like this will not clear nor execute the contents of SQL buffer.

If you would like to customize which characters can be used to execute SQL statements and further control how SQL statements get parsed, please refer to "CLI Settings" section on the Shell Variables page.