Query Engine

The Query Engine is a utility I’ve developed that can connect to and perform queries against SQL Server or Oracle databases.

Connecting to  a Data Source

The Query Engine connects to an Oracle or a SQL Server data source via the File->Connect.

This brings up the Data Link properties dialog where connection parameters for either an Oracle or a SQL Server data source may be specified.

The Data Link properties dialog may also be invoked by clicking on the “Connect” icon in the Query Engine toolbar.

Querying Data Objects

Once connected to a database instance, the Query Engine will display all tables found in the specified data source in the table list located at the left.

Double clicking any of these entries will create an SQL select query in the command window and display the corresponding results in the query results grid.

The command window may also be used to specify more advanced SQL Queries.

These queries get executed once the Run command is clicked in the Query menu.

An alternative to this is the F5 button in the keyboard or the Run icon in the toolbar menu.

The Query results are retrieved asynchronously using a background thread.

This operation can be stopped by clicking on the stop icon in the toolbar.

Selecting Columns in Database Objects

Shift Double Clicking on an item in the table list will display the column names that can be selected prior to generating a query in the command window.

The select all button checks all columns for the selected table.

The de-select all button unchecks all columns for the selected table.

The select specified columns will create an SQL select statement which contains all the checked columns in the select columns dialog.

Search values inside Data Objects

Click on the magnifier icon to bring up the search dialog.

The search box accepts an alphanumeric string which is used to evaluate columns of its occurrence.

The search button initiates the search using the specified search string.

The close button closes the search dialog.

The search is asynchronous.

When a search is in progress, the table name and the column where it is found are dumped in the results grid.

Double click on any of the entries in the results grid to issue an automatic query in the command window of the Query Engine form.

This can be done even if a search is currently in progress.

The next button skips the current table and jumps to the next available data object to scan.

There can only be one active search at a time.

The current search needs to be cancelled via the abort button for a new search to be made.

You can also invoke the search dialog via Ctrl+F or via the Tools menu of Query Engine.

Viewing in Card View

Card view allows viewing of records with a large number of columns vertically.

To view records in card view, right click on the grid and then select Card View.

Grouping Records

The Query Engine can group records using a particular column.

This can be enabled via the tools menu.

Once enabled, a band will appear in the grid where columns can be dragged for grouping.

Dragging a column into this band will group the records using that column.

Freezing Columns

A column can be frozen by clicking on the freeze icon. This is the right-most icon beside the column name.

Filtering Rows

Query results can be filtered by specifying the filter condition in a column. To specify a filter condition, click on the filter icon which is the second icon to the right of the column name.

A dropdown appears with a list of choices in which the records may be filtered.

To specify a more complex filter condition, click on the Custom item in the filter dropdown.

Adding Summaries

To add a summary, click on the summary icon for the column that needs to be summarized.

From the dialog, select one or more methods on how to summarize the column.

Clicking the ok button will apply the selection on the column.

Exporting to Excel

To export a query result to excel, click on the Export to excel button in the Query Engine toolbar.
Specify a file name and click on save.

Configuring Query Engine

Clicking on the Tools->Configure menu will bring up the Query Engine configuration screen.

The working directory points to the location that will be used by Query Engine for its processes.

The displayed rows setting controls the maximum rows displayed per query.

This prevents massive datasets from being accidentally loaded to Query Engine.

This can be turned off by specifying a value of 0.

The recommended value is 200,000 rows.

The font setting controls the font that is used for the query editor and the Query Engine grid.

To modify the font, click on the Change button beside the font field.

Clicking Ok will apply the settings to the query editor and the grid.