AppCode 2018.3 Help

Writing and Executing SQL Statements

Selecting the default schema or database

You can select the default schema or database by using the list in the right-hand part of the toolbar. If you do so, you'll be able to omit the name of that schema or database in your statements.

DBConsoleSchemaSelector

See also, Controlling the schema search path for PostgreSQL and Redshift.

Controlling the schema search path for PostgreSQL and Redshift

When working with a PostgreSQL or Redshift data source, the default search path (one set in a database) is used unless you specify a different search path.

To control the search path, use the popup in the right-hand part of the toolbar. The popup also lets you switch your databases.

DBConsoleSchemaSearchPath

To select a database or set the default search path for it, click the database or press .

To change the search path for the current database, open the schema list.

If the search path should include only one schema, click the necessary schema. In the same way you can replace a schema with another one in a single-schema search path.

To form a search path that includes two or more schemas, use:

  • Space to add a highlighted schema to the search path and also to remove a schema from the search path.

  • ⌃↑ and ⌃↓ to reorder the schemas within the search path.

  • OK to apply the changes.

More instructions and usage hints are available right in the popup.

Composing SQL statements

When composing your SQL statements, use:

  • Predefined patterns (⌘J or Code | Insert Live Template).

    sqlConsoleLiveTemplates

  • Auto-completion and highlighting of SQL keywords, and table and column names.

    sqlConsoleAutocompletion

  • Data type prompts for columns (⌘P or View | Parameter Info).

    sqlConsoleParameterInfoTooltip

  • Advanced find and replace capabilities (⌘F or Edit | Find | Find, and ⌘R or Edit | Find | Replace).

    sqlConsoleFindAndReplace

  • Quick evaluations (⌥⌘F8). They are available for table and column names, and SQL keywords, and give you hints about the data and potential result when you compose your statements.

    DBQuickEvaluation

  • The console history (icons vcs history or ⌥⌘E). See Executing auto-memorized statements.

    sqlConsoleHistory

See also, Navigating to a table or column view in the Database tool window.

Editing data for INSERT statements in table format

  1. Select the INSERT statement of interest.

  2. Select Edit as Table from the context menu.

    sqlConsoleTableEditorForInsert

  3. Use context menu commands and associated shortcuts for working with the data.

When composing a statement, it's sometimes useful to take a look at the structure of a table, or to see the info about a column (field) in the context of the table to which it belongs. For such purposes, AppCode provides the ability to switch from the name of a table or column in the input pane to its view in the Database tool window.

The following ways are available for using this feature:

  • Place the cursor within the name of the table or column of interest. Then use⌘B. (Alternatively, you can use Navigate | Declaration from the main menu or Go To | Declaration from the context menu.)

  • Press and hold the Ctrl key, and point to the name of interest. When the text turns into a hyperlink, click the hyperlink.

    sqlConsole1

Configuring the Execute command

The Execute command (icons toolwindows toolWindowRun svg on the toolbar, ⌘⏎ or Execute from the context menu) is used to run your statements.

AppCode provides many options for the Execute command depending on the cursor position and on whether there is a selection.

The options are specified on the Tools | Database page in the Settings / Preferences dialog (File | Settings | Tools | Database on Windows and Linux; AppCode | Preferences | Tools | Database on macOS). For more information, see Execute in Console.

Executing SQL statements

Execute an SQL statement

  1. Place the cursor within the statement.

  2. Press ⌘⏎. Alternatively, click the Execute icon The Execute icon on the toolbar.

  3. 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.

Executing parameterized statements

Your statements can contain parameters, however, by the time you execute such statements the values of the parameters must be specified. There are the following ways of specifying the parameter values:

  • Click icons toolwindows toolWindowRun svg on the toolbar or press ⌘⏎ to execute the statement. In the dialog that opens, specify the parameter values and click OK.
    sqlConsoleParams

    ( To start editing a value, switch to the corresponding table cell and start typing. To indicate that you have finished editing a value, press or switch to a different cell. To quit the editing mode and restore an initial value, press. )

  • Alternatively, you can open the Parameters pane in the Database Console tool window ( property yellow on the toolbar) and specify the corresponding values there. (The values are edited in the same way as in the corresponding dialog.) Then execute the statement ( icons toolwindows toolWindowRun svg on the toolbar or ⌘⏎).
    sqlConsoleParamsPane

    For more information, seeParameters pane.

See also, User Parameters and Always review parameters before execution.

Executing a group of statements

To execute a group of statements that follow one another in the console, select (highlight) the statements (to select all the statements, use ⌘A) and do one of the following:

Press ⌘⏎. Alternatively, click the Execute icon The Execute icon on the toolbar.

See also, Using the error notification bar and Execute in Console.

Executing all statements

To execute all the statements contained in a console, as an alternative to the Execute command, you can use the Run console.sql command.

This command is available in the context menu, and its keyboard equivalent is ⌃⇧R.

The Run console.sql command, generally, runs faster but:

  • The statements with parameters don't run.

  • Retrieved data for the SELECT statements are not shown.

Executing a part of a statement (e.g. a subquery)

To execute a part of a statement (e. g. a subquery), select (highlight) the fragment that you want to execute and do one of the following:

Press ⌘⏎. Alternatively, click the Execute icon The Execute icon on the toolbar.

See also, Execute in Console.

Executing auto-memorized statements

As you run SQL statements in the consoles, AppCode memorizes them. So, at a later time, you can view the statements you have already run and, if necessary, run them again.

To open the dialog where the auto-memorized statements are shown (the History dialog), do one of the following:

  • Click icons vcs history on the toolbar.

  • Press⌥⌘E.

There are two panes in the History dialog. The left-hand pane shows the list of the statements that you have run. For "long" statements, only their beginnings are shown. When you select a statement in this pane, the overall statement is shown in the pane to the right.

You can filter the information: just start typing. As a result, only the statements that contain the typed text will be shown.

You can copy the statements from the History dialog into the input pane of the console. To copy a statement, do one of the following:

  • Double-click the statement to be copied.

  • Select the statement of interest and press.

  • Select the statement and click OK.

(Once the statement is in the input pane, you can run it straight away.)

You can delete unnecessary memorized statements. To delete a statement, select the statement in the History dialog and press.

Outputting the result of a SELECT statement into a file

Instead of the Result pane of the Database Console tool window, you can output the result of a SELECT statement into a file.

  1. Right-click the SELECT statement of interest.

  2. Point to Execute to File and select the output format.

  3. Specify the output file location and name.

Using the error notification bar

If when running a statement an error occurs, an error notification bar appears in the lower part of the input pane.

This bar may be particularly useful when executing a sequence of statements (see Executing a group of statements) because in such a case it lets you select how to react.

DBConsoleErrorNotificationBar

The options are:

  • Retry. Execute the sequence of statements starting from the one that caused the error.

  • Ignore. Skip the erroneous statement and execute the sequence starting from the next statement. If another error occurs, the error notification bar will appear again.

  • Ignore All. Skip the erroneous statement and execute the sequence starting from the next statement. If other errors occur, all the erroneous statements will be skipped and the error notification bar won't appear for these statements.

  • Stop. Stop the execution of the sequence.

Showing the error notification bar in the input pane is enabled or disabled in the Preferences dialog (the Show error notifications in editor checkbox on the Database page).

Canceling running statements

To terminate execution of the current statement or statements, do one of the following:

  • Click icons actions suspend svg on the toolbar of the input pane, or on the toolbar of the Dababase Console tool window.

  • Press ⌘F2.

Managing database transactions

You can select to commit transactions automatically or manually. To change the commit mode, use the Tx switch buttonTransactionControlDB on the toolbar.

TransactionControlDB

If the commit mode is set to Auto, each SQL statement is executed in its own transaction that is implicitly committed. Consequently, the SQL statements executed in this mode cannot be rolled back.

If the commit mode is set to Manual, transactions are committed or rolled back explicitly by means of iconDBCommitTransaction or iconDBRollbackTransaction on the toolbar.

The Tx switch can also be used for selecting the isolation level for the transactions.

Run migrations

Run stored procedures

  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.

db run stored functions

Showing execution plans

Execution plan is a set of steps that were used to access data in a database. AppCode supports two types of execution plans:

  • Explain Plan: the result is shown in a mixed tree and table format on a dedicated Plan tab. You can click the Show Visualisation icon (the Show Visualization icon) to create a diagram that visualizes the query execution.

  • Explain Plan (Raw): the result is shown in a table format.

To create an execution plan, right-click a query in the database console and select Explain Plan. If you want to create a diagram for the query, click the Show Visualisation icon (the Show Visualization icon).

Showing DBMS_OUTPUT for Oracle

For Oracle, you can enable or disable showing the contents of the DBMS_OUTPUT buffer in the output pane. To do that, use DBConsoleEnableDBMSOutputIcon on the toolbar of the Database Console tool window (⌘F8).

DBConsoleDBMSOutput
Last modified: 28 March 2019