RubyMine
 
2022.2
Shortcuts: Windows
Get RubyMine
You are viewing the documentation for an earlier version of RubyMine.
  1. Database tools and SQL
  2. Database code

Database code

Last modified: 09 August 2022

To run SQL code, press Ctrl+Enter

To cancel a running statement, press Ctrl+F2

warning

The following is only valid when the Database Tools and SQL plugin is installed and enabled.

You can run Run your code by using shortcuts, toolbar buttons and icons, a context menu, and run/debug configurations. You can run a query from query consoles, scratch files, and user files.

tip

A database or query console does not differ from other SQL files like user files and scratches. The only difference is that the query console is already connected to a data source, so you can type and run your code right away. You can use a query console in the same way as you use a terminal. Just type, press Ctrl+Enter, and run your code.

Running statements and files

Run statements from an open file

In RubyMine, you can open and run a file. Limitation for the file size is 20 MB. When you open a file that is larger than 20 MB, you see only first 2.5 MB of the file.

  1. Open the Project tool window (View | Tool Windows | Project) and double-click an SQL file.

  2. Click the statement that you want to execute.

    Also, you can select (highlight) the fragment of code that you want to execute. It can be a subquery or a group of statements. RubyMine executes only the selection.

  3. Press Ctrl+Enter or select Execute from the context menu.

    To customize query execution settings, click the Сustomize link. Alternatively, open settings by pressing Ctrl+Alt+S and navigate to Tools | Database | Query Execution.

  4. (Optional) If the SQL file is not connected to a data source, select or create a connection session in the <session> list. For more information about sessions, see Managing connection sessions.

  5. In the Statements window, press Enter to run the selected statement. You can switch between other entries to run another set of statements. Statements that you are going to execute are highlighted in a query editor.

    For another example of running script files, see the following video at youtube.com.

    https://resources.jetbrains.com/help/img/idea/2022.2/run-individual-statements.png
    Gif

tip

From the <session> list, you can associate a different session with the file, or remove the association between the file and the session (Detach Session). Also, you can associate a session with a file by selecting Attach Session from the context menu of the file.

tip

In the Statements window, you can click Customize to define whether you want to see the chooser or always run the statement under the caret.

Run an SQL file for several data sources

  1. In the Project tool window (View | Tool Windows | Project), select files that you want to run.

  2. Right-click the selection and navigate to Run <configuration_name> (or press Ctrl+Shift+F10). This action creates a temporary run configuration.

  3. In the Edit Configuration window, click the Add button (the Add button) and select data sources or schemes against which you want to run selected files.

    You can click the Recent button to select data sources that you used recently.

  4. Click Run.

    Run files for several data sources

Run files by using run configurations

Depending on your purposes, you can edit existing or create new configurations. For more information about editing you configurations, see Run/debug configurations.

  1. Open the Run/Debug Configuration dialog in one of the following ways:

    • Select Run | Edit Configurations from the main menu.

    • With the Navigation bar visible (View | Appearance | Navigation Bar), choose Edit Configurations from the run/debug configuration selector.

      Edit run configurations

    • Press Alt+Shift+F10 and then press 0.

  2. In the Run/Debug Configuration dialog, click the Add New Configuration icon (the Add New Configuration icon) and select Database Script.

    The fields that appear in the right-hand pane display the default settings for the selected configuration type.

    • Target data source / schema: databases or schemas against which you want to run your database scripts. This setting is dialect-dependent.

      If you select a data source as a target, RubyMine displays a schema in which the script will be run. It is the default schema.

    • Script text: script that you want to run.

    • Script files: SQL files that you want to run. To add files, click the Add button (the Add button) and navigate to files that you want to run. If a script contains schema switching, you will see a warning (Warning).

    • Data source (utPLSQL and tSQLt): a name of a data source. utPLSQL supports Oracle. tSQLt supports Microsoft SQL Server.

    • Database (tSQLt): a name of the Microsoft SQL Server database.

    • Paths (utPLSQL and tSQLt): a qualified path to the testing package.

    • Tags (utPLSQL): tags of tests that you want to run. Use semicolon (;) as separator.

  3. Apply the changes and close the dialog.

  4. Click the Run <configuration_name> button The Run button.

Running parameterized statements

If you have parameters in your statement, you must specify the values of the parameters before you execute the statement.

  • To execute a parameterized statement, click the Execute button the Execute button on the toolbar and enter values in the second column. Alternatively, to open the Parameters dialog, click the View Parameters button the View Parameters button.

    Execute a parametrized statement

Run stored procedures

A stored procedure is a set of SQL statements with an assigned name. You can execute stored procedures in PostgreSQL, Microsoft SQL Server, Oracle, and MySQL.

  1. Right-click a stored function that you want to execute.

  2. In the Execute Routine window, type all the necessary parameter values, and click OK.

    Run stored procedures

Cancel running statements

Before RubyMine 2022.1, when you clicked the stop button several times, it looked like the query was terminated, but in fact the request continued its execution in the database.

On the first click, RubyMine sent a cancellation request to the database, while on the second click RubyMine canceled the JDBC Driver process to terminate all connections to the data source (deactivate the data source). As a result, RubyMine received an error for the first cancellation request because the connection was lost, not because the cancellation was actually completed.

Starting with RubyMine 2022.1, the progress indicator on the gutter shows a red square while the request is being canceled. A canceling progress bar is shown, which clarifies the data source and the session.

https://resources.jetbrains.com/help/img/idea/2022.2/db_cancel_running_statements.png
Gif

RubyMine displays a warning to verify that you want to deactivate the data source. The warning is displayed to ensure safety as deactivation might be potentially dangerous for some processes happening simultaneously. So, if you click the Cancel Running Statements button two times, RubyMine displays a warning where you can terminate the remote process or wait a bit longer.

Also, the IDE displays a warning after 10 seconds of idle canceling.

note

If you click Deactivate, it will stop the query in RubyMine but the query will continue its execution in the database.

Canceling running statements

  • Click the Cancel Running Statements button.

    cancel_running_statements_procedure

Canceling connections

Before RubyMine 2022.1, when you attempted to abort a request at the stage of creating a connection, the data source was deactivated. Deactivation means that RubyMine canceled the JDBC Driver process to terminate all connections to the data source.

Starting with RubyMine 2022.1, you can interrupt not only the execution of a request, but also the creation of a connection. This is especially relevant for the very first query in the console, which also creates a connection.

You can now stop the process of creating a connection without deactivating the data source: if you click the Cancel Running Statements button while the connection is being created, the connection will be stopped, and you will see the Connection canceled message.

Resolve modes

In order to run SQL scripts, treat the usages of the database objects as links, and provide you with correct code completion, RubyMine must resolve the database objects in your code to a certain context. By default, IDE uses one of the following as a starting point to resolve the database objects to: <schema> selector, or the default database (if none of the above is set).

Depending on whether your code is a single script or it has multiple unconnected SQL statements, select one of the following resolve modes to resolve the database objects properly:

  • Playground: default mode for query consoles. All the database objects are resolved to the same context.

    Use this mode if your code is a set of unconnected SQL statements which are not meant to be run as a single script.

  • Script: default mode for local SQL files. The USE SQL statement (SET search_path TO for PostgreSQL) overrides the default context that the database objects are resolved to.

    Use this mode if your code is meant to be run as a single script.

    note

    By default, the <schema> selector is not attached to local SQL files. To attach the <schema> selector, right-click the file in Files tool window and select Attach Session.

View history of executed statements

Using the History dialog

RubyMine stores history of all statements that you have run.

  • To open the History dialog, click the History button or press Ctrl+Alt+E.

    In the History dialog, you can perform the following operations:

    • To filter information, start typing your search query.

    • To paste the query from the History dialog to the console, double-click the query in the left pane of the History dialog.

    • To delete a record from history, select the record and press Delete.

Viewing results

Display query results in a query console

You can relate to a query console as to a terminal where you type and run your code.

  1. In the Database tool window (View | Tool Windows | Database), click the data source.

  2. Press F4 to open a query console. For more information about working with consoles, see Working with consoles.

  3. Type or paste the statement that you want to execute.

  4. Press Ctrl+Enter. Alternatively, click the Execute icon The Execute icon on the toolbar.

    If you have several statements, select whether you want to execute all statements or a single statement. The suggestion list always contains an item for running all the statements.

    https://resources.jetbrains.com/help/img/idea/2022.2/db_run_query_mysql.png
    Gif

Disable in-editor results

  • To disable the In-Editor Results feature for all files across the IDE, open settings by pressing Ctrl+Alt+S and navigate to Database | Query Execution | Output and Results |. Clear the Show results in editor checkbox.

  • To disable the In-Editor Results feature for the current file, click the In-Editor Results icon.

    Disable in-editor results for the current file

Enable output from the DBMS_OUTPUT package (Oracle)

In Oracle, the DBMS_OUTPUT package enables you to send messages from stored procedures, packages, and triggers. You can enable or disable showing the contents of the DBMS_OUTPUT buffer in the output pane.

  • To toggle the DBMS_OUTPUT option, click the Enable SYS.DBMS_OUTPUT button the Enable SYS.DBMS_OUTPUT button on the toolbar of the Services tool window.

    Enable output from the DBMS_OUTPUT package (Oracle)

Save results of SELECT statements into files

  1. (Optional) If the file is not connected to a data source, select a data source from the list of data sources on the toolbar. Then select the connection session from the Sessions list. For more information about connection sessions, see Change a data source and a connection session.

  2. Right-click a SELECT statement.

  3. Select Execute to File and select the output format.

  4. In the Export Data dialog, specify the extractor that you want to use and other settings. For more information about the Export Data dialog, see Export data.

    Save the result of a SELECT statement into a file

Tips and tricks

Browse query history

  • To view history of all the queries that you ran in a query console, click the Browse query history button (the Browse query history button). Alternatively, press Ctrl+Alt+E.

    Browse the query history

Execute a file from the search dialog

  1. Press Shift twice.

  2. In the Search Everywhere dialog, type the filename.

  3. Select a file by using Up and Down arrows.

  4. Press Ctrl+Shift+F10.

  5. In the Edit Configuration window, click the Add button (the Add button) and select data sources or schemes against which you want to run selected files.

    https://resources.jetbrains.com/help/img/idea/2022.2/db_run_file_from_search.png
    Gif