The purpose of this article is to describe the features and behaviour of the SQL Editor in SolveXia Designer View. The following sections include how to access the SQL editor, a few of the user interface (UI) and user experience (UX) functionalities' descriptions, and some expected limitations for the current version.
The SQL Editor can only be accessed in the following "Manipulate Files" instruction steps.
- Run Query
- Select to Access
- Select to Excel
- Select to Text
The screenshot below describes how the editor is accessed via the "Run Query" instruction. Select the link "View/Edit SQL" - the same link can be found in the instructions specified above:
The link will prompt a new tab to open as follow:
UI Basic Features
The following are a few descriptions of the basic UI features:
1. The process and step name where the query exists.
2. The search bar is to filter through existing tables or views. Note that "Tables" or "Views" tabs must be selected when filtering through these lists.
3. The list of tables for which the user has access. The column names for each table can be expanded by selecting the ' ⯈ ' icon beside the table name. The icon "<>" allows the user to add a table or a column into the query:
4. A list of view names existing in the database and to which the user has permission to access. Similar features as for the table list (see 3) exists for the view list.
5. The editor's text box, where the query is written.
6. The section where errors, warnings and previews appear when the play button is pressed.
UX Basic Features
The following is a few descriptions of the basic UX features :
1. Intellisense guides a user to complete their query by filtering through the database's available table/view names.
2. The minimap (situated on the right of the text editor) is a condensed representation of the written query. Its purpose is to help the user orient through the code faster and with more ease.
3. The SQL Editor can handle multiple windows at a time. This means that the editor can be opened for different steps and apply changes individually. For example, SQL Editor window can be open for step A and step B at the same time and queries can be edited independently. Using SQL Editor window for step A is independent of using the SQL Editor for step B.
NOTE If two editors are open for the same step, only the latest opened window will be able to apply changes to the query.
4. It is possible to write queries linked to data steps. The following steps show how this is accomplished for a column value:
- Place the cursor where the parameter is required (a) and click the “Link” (b) icon to link the SQL query to a data step property:
5. The find and replace functionality let's the user search for keyword in the query and replace with another keyword. To access the "find" tab, press "CTRL+F" and to access the "Find and Replace tab, press "CTRL+H" :
Type in the keyword to search in the "Find" space and the item that it will be replaced with in the "Replace" space. Press "CTRL + ENTER" to apply the changes:
6. After 20 minutes of inactivity, the user's session is locked in designer view. This same locking of the session will not occur in the SQL Editor. Instead, if there's an attempt to use the SQL Editor whilst SolveXia Designer is locked, the user is taken back to the login screen and the applied changes are lost. To properly save the work, the user must unlock SolveXia's Designer window session and press " Save and Close" in the SQL Editor window.
Here are a few of the limitations expected for version 1. We are currently working on improving these in coming releases.
SQL Editor Preview
1. SQL Editor by default shows up to 50 results
2. SQL Editor produces a timeout error if it takes longer than 10 seconds to produce an output. The following warning message appears in the preview box if that is the case:
3. SQL Editor preview only runs if the statements are appropriate for preview. That is, statements that are meant to modify the data or structure of the table (SET, UPDATE, DELETE, etc) will not run. However, it can still verify if the statement is correct.
IMPORTANT The query can be executed in the SolveXia run step function even though it cannot be previewed in the SQL editor. That is, as long as the query is valid.
4. SQL Editor preview only displays the results of the first SELECT statement in your query.
5. F2 and "Play" button in SQL Editor runs the whole query and not what is highlighted in the editor.
Link to Data Step Property
1. When changing the text within a linked data, the highlighting disappears and thus removes the link and turns into a regular text.
2. SQL Editor runs the statement as per the underlying value of linked step at the moment the editor was opened.
e.g. If the link is a data step property and the value is "DOG", the word "DOG" will appear in the query. If the value of the link is changed to "CAT" while the editor is open, the SQL editor's window needs to be reloaded for the change to appear in the written query.
3. If a linked data step property in a SQL query is deleted from SolveXia, the following is expected to appear :
4. If there is an attempt to link a data step property which has "Value is Empty", the link functionality will not work. These data properties are typical "Rectangles", "Lines" or ListBoxes (where there is no valid value available for the control) :
5. SQL Editor Undo and Redo buttons do not perform well when undoing action on a linked data step properties.
6. Copy and paste of a linked data step property only copies the text and not the link.