Switch to: V9V8V7V6V5

SQL Editor - Editor Area Help

The editor area allows you to effectively type a new SQL query or choose one of the recent queries or snippets.

You can use the query field to enter one SQL query or a few queries separated by a semicolon. After you type a SQL query, the “Execute” button becomes enabled. Click it to execute the query.

In v8.7 or newer, if you have a few SQL commands in the Editor, you can click the button “Execute Current” to execute only one, where the text cursor is located.

SQL Editor

Toolbar

At the top of this area you can see an own toolbar with a set of controls:

  • “Recent Queries” button – shows the list of recent queries
  • “Editor” button – hides recent queries list and returns back to the editing
  • “Side Panel” button – hides/shows the side panel on the left
  • “Parameters” button – hides/shows the parameters panel on the right
  • “Active Connection” button – allows you to choose a connection to which send queries
  • “Active Database” menu – allows you to choose a database to which send queries
  • “Default Schemas” menu – allows you to view and choose schemas that will be the default and thus their names can be omitted when writing the query. It is shown only if a source connection supports this feature
  • “Execute” button – runs the query that is present in the query field. Default shortcut is Ctrl+E (Cmd+E for macOS). In a case of success you will see the result tab, otherwise, an error message will be appended in the console
  • “Execute Current” button – runs only one query where the text cursor is located
  • “Cancel” button – stops the currently running query. It is shown only if a source connection supports this feature
  • “Functions” button – opens the “Function Browser” to help you to choose one of the SQL functions
  • “Options” menu – allows you to specify the options of query execution

Recent Queries

If you click the Recent Queries button, you will see the list of recent queries:

  • To choose a query from the list, just double click. You will be moved back to the Editor, and the query will replace anything that was in the editor, so you can click Execute.
  • Contextual menu for each recent query contains following items:
    • Execute - execute the query and show its result. Note: the text of the query field is not affected
    • Copy - copy the query to clipboard
    • Delete - delete the query from this list
    • Append to the End - append the query to the end of the query input field
    • Insert at Cursor Position - insert the query at the current cursor position in the query field
    • Replace Current Query - replace the current query in the query field

  • To return to the editing mode, click the “Editor” button of the toolbar.
  • To clear the list of recent queries, click the button “Clear All Recent”.
  • To view all queries including failed ones use “Show Failed Queries” option.
  • To show all executed recent queries switch query scope select list from “Current Database” to “All Databases”
  • You can filter the recent queries using the data search field on the right.

Default Schemas Menu

Depending on the type of source database, the default schemas menu has different options and capabilities.

PostgreSQL

This menu allows you to add or remove schemas from the list:

  • all schemas at once
  • individual schemas by name
  • username-matched schema ($user)

The button of this menu contains the name of the schema or, in case of multiple selections, the number of selected schemas.

Tooltip contains a complete list of the selected schemas:

MS SQL

For SQL Server, this menu is in read-only mode and shows the current default schema in the menu button, without an ability to change it (requires to edit the user account by administrator).

Functions

Function Browser has three main areas:

  • List of categories;
  • List of functions in the select category;
  • Description of a selected function. For each system function, you can see its declaration and description.

When you have found a required function just click INSERT button to insert its prototype into SQL Editor’s query.

Options Menu

On the right side of the toolbar of SQL Editor, you can see the menu with the switch icon. This menu collects a set of options for SQL query execution. These options differ for database engines.

Cursor Location

  • Client Side - Defines the cursor location to be client side, i.e. all its records come into RAM of the client computer. It is good for small enough queries.
  • Server Side - Defines the cursor location to be server side. Records come on demand to the RAM of a client computer. It is good for queries with huge results.
Database Client-Side Cursor Server-Side Cursor
Valentina yes yes
PostgreSQL yes yes
mySQL yes no
SQLite yes no

Locking

Defines the kind of record locks for server-side cursors. Client-side cursor applies read-only locks on short time while loading result records from the database.

  • No Lock (Dirty Read) - No access locking.
  • Read-Only (Uncommitted Read) - Allows other users to read data (in its old state) but not to change it.
  • Read/Write (Repeatable Read) - Other users are neither allowed to read nor change records of the table(s) you are working on.

Output Options

  • Outputs - If enabled, then Valentina Server can return output from the PRINT SQL command.
  • Tunes - If enabled, then Valentina Server can return tune information about query execution.
  • Warnings - If enabled, then Valentina Server can return warnings, if any, produced during query execution.

Text Editor Toolbar

At the top of the text editor you can see the toolbar with the set of the following buttons:

  • Undo
  • Redo
  • Duplicate Line
  • Move Line Down
  • Move Line Up
  • Move Element Left
  • Move Element Right
  • Move Statement Down
  • Move Statement Up
  • From CODE
  • Indent
  • Unindent
  • Comment/Uncomment
  • Format SQL
  • А - button on the right, allows to ON/OFF mode “Underline Wrong Indents” by the static analyzer.

Advanced Editing

[Valentina Studio Pro feature]

With this feature SQL Editor is powered with the following advanced query editing actions:

  • Duplicate Line – Duplicate the current line
  • Move Line Down – Move the current line down
  • Move Line Up – Move the current line up
  • Move Element Left – Move the current element of the list to the left
  • Move Element Right – Move the current element of the list to the right
  • Move Statement Down – Move the current statement lower
  • Move Statement Up – Move the current statement upper
  • From CODE – Converts C++/C#/etc code into SQL command, removing quotes and escapes.

They are accessible either from the application menu SQL or in the text editor toolbar.

SQL Formatting

This feature allows you to format the text of SQL query. [Valentina Studio Pro feature]