SQL Query

The SQL Query plugin in the Administrative Console (Database Admin > Utilities) allows users to execute SQL statements directly against the System Connection (i.e., BusinessPlus Database) or the Catalog Connection (i.e., System Catalog Database). To allow multiple screens operating at the same time, each new instance of the SQL Query screen will create a new connection to the database.

There is no security or restrictions applied to the SQL Query screen. Therefore, any user who has access to this screen can execute any statement against the database that is granted based on the user from the Database tab of the connection manager. In most cases, these are DBA-level permissions. Access to this screen should be extremely limited at any organization.

	Creates a new SQL Query in the current window.
	Prompts to open an existing SQL Document (*.sql) on the local machine.
	Prompts to save the current SQL statement to a file.
	Executes the SQL statement currently in the Editor window.
	Select System or Catalog Connection from the drop-down.

Database Schema

The Database Schema panel on the left of the screen allows browsing information about the System and Catalog connections to assist in the creation of SQL Statements. Each connection provides groupings for User Tables, System Tables, Views, and Stored Procedures.

Show Properties

The Show Properties

button in the Schema Panel toolbar displays/hides the Properties window for the panel. When an item (table, column, etc.) is selected in the panel, its properties become visible in the Properties window at the bottom of the panel.

User Tables

Contains the non-system tables in the database instance. Within each table, the Columns and Indexes can also be expanded. Each index can be expanded to display the columns referenced in that index.

System Tables

The System Tables folder contains the system tables in the database instance.

Views

Displays information about the views in that instance.

Stored Procedures

Displays information about the Stored Procedures in that instance.

Query Editor

The query editor consists of two parts:

The top part (Editor) is used to create and edit SQL statements.
The bottom portion displays database response messages in the "Messages" tab and SQL statement query results in "Results" tabs.

For those statements returning rows (e.g., SELECT statements), the rows are shown in a grid on the Results tab. If there are multiple queries with SELECT, there will be multiple "Results" tabs showing data in a grid for each SELECT query. For those statements that alter the database, the response from the DBMS instance is placed in the Messages tab.

If there are multiple queries, or a combination of comments and a query, a semi-colon must be used at the end of the query. If there is only one query and no comments, then a semi-colon is optional.

Query Results

After a SQL statement is executed, the results appear in one or more Results tabs in the bottom portion of the screen. The horizontal divider in the middle of the screen allows the results panel to be increased by clicking and dragging it vertically.

Copy Selection

The information from the results grid can be copied and pasted into other application by selecting one or more cells or rows and copying them (Ctrl+C or right-click "Copy Selection") from the grid and pasting into an application such as Notepad or Microsoft Excel. The data in the results grid cannot be edited in place; there is no mechanism for altering the data in the database from the results grid.

Viewing Blobs

The SQL Query screen offers limited support for viewing BLOB columns. BusinessPlus stores information such as User Settings and Workflow Instance records in different tables in a binary data type. Querying these tables will return the column in the Results tab. However, due to their size, the BLOB column content is not shown in the cell. Instead, a link to the Blob Viewer is made available to the user to open a new window showing the BLOB contents. Not all BLOB data is viewable in the SQL Query plugin due to compression and storage differences, but many types should be viewable by simply clicking the "View Data" link. The content is displayed in a "Blob Viewer" tab and, if needed, can be saved to a file. Currently, the Blob Viewer does not support Documents Online BLOB types. Users should use the Document Viewer to view that information.