Using Structured Query Language (SQL)

Using Scripts, SQL and Extensions > Using Structured Query Language (SQL)

The scripting language offers the ability to use Structured Query Language (SQL) syntax to query your data. This can be done via a script or, alternatively, by entering SQL statements directly in the Console Panel.

Console Panel

To open the Console Panel, select View > Console Panel or select CTRL+K:

console_panel

The console can be used for both input and output:

•	Input SQL statements to query a database (internal or external)

•	Input console commands

•	Show output from a script

Using SQL Statements

SQL is the standard language for querying databases. Many of these same processes can be performed in Strata via the interface. However, for those who know SQL syntax, it is often useful to be able to just query directly from a prompt. To do so, open the Console Panel and enter in a SQL statement.

It is important to use the full path of the table name for both internal and external databases as seen in the Project Panel. If the SQL statement references an internal table, the SQL will use the software's internal SQL syntax. If the SQL statement references an externally connected table, the SQL statement is passed through to the database containing that table and must use the SQL syntax of the database in question.

So, if we had a folder called "data" with a table called "sample", we could type in:

select * from /data/sample;

Likewise, if we had an external database connection called "connection" with a table called "sample", we could type in:

select * from /connection/sample;

If the table name contained a space and was called "sample table", we would use the following syntax

select * from [/connection/sample table];

An example of a more advanced SQL statement might be:

select myfield, count(*) from [/data/sample table] group by myfield;

NOTE: The Console Panel accepts single line SQL Statements only.

Using Console Commands

In addition to SQL, the console enables you to enter various functions to help speed up your processing from the command-line:

•	OPEN <filename>: Opens a given file

•	CLOSE <filename>: Closes a given file

•	USE <directory path>: Changes the default directory used to run SQL Statements

•	PWD: Prints the current directory that's being used for SQL Statements

•	CLEAR: Clears the Console of all text

•	EXIT: Exit the software

So, if we wanted to open a table called "data", we could type in:

open data

Likewise, if we had a folder called "data" with a table called "sample", we could type in:

open /data/sample;

If we wanted to use a SQL Select statement on a table called "data" in an external database called "connection", we could either write the SQL Statement in full:

select * from /connection/data;

or we could use the commands to switch directories for us, enter the sql command and go back to our root folder:

pwd; [shows what the current directory is]

use connection; [sets directory to "connection"]

select * from data; [executes a SQL statement on the table "data" in the "connection" directory]

use; [reverts back to root project folder]

pwd; [shows that the current directory is the root project folder]

Outputting Script Results

The Console Panel can also be used for outputting information from a script. There are several functions for this, including:

Console.write();

Console.writeLine();

Console.clear();

For example:

// count to three on the same line

Console.write("Count to Three: ");

for (var i = 1; i <= 3; ++i)

{

var text = "";

if (i > 1)

text += ", ";

text += i;

Console.write(text);

}

This would output the following to the Console:

Count to Three: 1, 2, 3