Integrating SQL in s-Studio

Whenever SQLstream s-Studio is connected to the server, you can issue SQL queries against the server and view their results.

To open a SQL query window, right-click an object (stream, table or view) and select New Query. (Or, after you select an object, you can simply click the New Query button (with the rocket ship icon) in the Catalog Navigator pane just above the list of objects.)

Creating a Query

Within the query editor, you can type any valid SQL query (a statement starting with SELECT). The editor can provide context-sensitive suggestions and completions if you type Ctrl+Space within the editor window.

In this illustration, the Properties utility tab has been selected, showing the properties of the ASKS stream.

To execute the query, click the Execute button in the Query Editor window (the button with the green triangle “Play” icon). The results will appear in the lower portion of the query editor window. Note: SQLstream s-Studio automatically surrounds lower or mixed-case identifiers, or those containing a dash, with double-quotes.

Import and Export Queries and Results

The Query menu appears in the menu bar whenever you launch the query editor, using either the little rocket icon or the New Query option on the right-click menu off any object in the catalog. This menu lets you import and export queries, as well as export the results of a query. The exported query contains all dependent items as well as the specific item selected.

Validating, Executing and Formatting SQL

The SQL menu (Alt-S) lets you do the following:

  • Format SQL (Alt-SF)
  • Validate SQL (Alt-SV)
  • Execute SQL (Alt-SE)

All three buttons are available in the toolbar (when the SQL window is in focus); the Execute button is also visible as the top-left button inside the SQL Query window.

The SQL formatting is affected by preferences, which you can change by selecting Window / Preferences and clicking SQL Format.

Injecting Data into Streams

Often, when testing an application, you need to provide data for a stream to test downstream objects, particularly aggregations and JOINs. Normally, to handle this you need to either run a script or use an external tool to execute INSERT SQL statements. SQLstream s-Studio provides an easy way to inject proper test data into a stream or table. To inject data into a stream, select the stream object in the Catalog Navigator and either double-click it or right-click and choose Open to open the object editor. When the editor opens, click the Inject tab. This will show all the columns by name, with fields for input data for each.

Once you set the values you want to inject, click the Inject button, and s-Studio will execute the appropriate SQL INSERT statement for you.