User Parameters

Depending on the database vendor, the question mark ? is treated as a parameter in SQL statements. On this page, you can specify what other characters and their sequences should be treated as parameters, and in which places.

The patterns for SQL parameters are specified by means of regular expressions.

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 on the toolbar and enter values in the second column. Alternatively, to open the Parameters dialog, click the View Parameters button .

Configure settings for user parameters

Open settings by pressing Control+Alt+S and navigate to Database | Query Execution | User Parameters.

Examples

Array as a parameter

In DataGrip, the replacement of a parameter with a value is straightforward. To use an array as the value, specify your array in the corresponding field.

In the following example, the ? parameter can be replaced with 206, 'John', 'Smith' value:

INSERT INTO actor (actor_id, first_name, last_name, last_update)
VALUES (?, '01.02.2023 13:34');

For further information on parameters, refer to the database documentation.

Parameter pattern

If you have parameters with a specific parameter syntax in your scripts, specify the pattern using a regular expression.

For the following example, the %{2}(\w+_\d+) pattern must be set:

SELECT title, description, release_year FROM actor
    JOIN film_actor fa ON actor.actor_id = fa.actor_id
    JOIN film f ON f.film_id = fa.film_id
WHERE last_name = '%%winnerpl_2';

User parameters

Item	Description
Enable in query consoles and SQL files	Apply parameter patterns to SQL in SQL files and database consoles. You can limit the usage scope at the level of individual patterns. If this checkbox is cleared, the patterns are not used in SQL files and consoles irrespective of the usage scope that is specified for individual patterns.
Enable in string literals with SQL injection	Apply parameter patterns to string literals injected with SQL. If necessary, you can limit the usage scope at the level of individual patterns. If this checkbox is cleared, the patterns are not used in string literals irrespective of the usage scope that is specified for individual patterns.
Substitute inside SQL strings	Apply parameter patterns to string literals in the SQL code. For example, consider the following code. SELECT ${column_name} FROM actor WHERE actor_id='${actor_id}' If the checkbox is cleared, DataGrip will find only the `column_name` parameter in it. The `actor_id` parameter is treated as a string. But if you select the Substitute inside SQL strings option, the `actor_id` parameter is treated as a user parameter.
Parameter patterns	List of parameter patterns and their usage scopes. The patterns are specified using regular expressions. Values that start with a colon (`:`) or located in parentheses are treated as parameter names. The patterns available initially have the following meanings: `\?(\d+)` - a question mark followed by one or more digits, for example, `?69` in which case `69` would be the parameter name. `%\w+` - `%` followed by one or more word characters, for example, `%xyz`. `\$\{([^$\{\}])\}` - `$`, then `{`, then any character except `$`, `{` or `}` zero or more times, then `}`, for example, `${}`, `${value}`. `\$$([^$]+)\)` - `$`, then `(`, then any character except `)` one or more times, then `)`, for example, $(x). `\$(\w+)\$` - `$`, then one or more word characters, then `$` again, for example, $x1$ . `\#(\w+)\#` - `#`, then one or more word characters, then `#` again, for example, `#field_3#`. `(?<=\W\|\A)\$(\d+\|[a-zA-Z_](?:\w\|\.))\$?(?=\W\|\z)` - various sequences that start with `$` with additional conditions for symbols that precede and follow those sequences. For example, `$ACCESS` if preceded by a space matches the pattern but `V$ACCESS` does not. In Oracle, there are system views whose names start with `V$` that should be filtered out. `(?<=\W\|\A)#(\d+\|[a-zA-Z_](?:\w\|\.)*)#?(?=\W\|\z)` - similar to the previous pattern but for the sequences that start with `#`. Use `Alt+Insert`, `Alt+Delete`, `Alt+ArrowUp` and `Alt+ArrowDown` to add, delete and reorder the patterns. To edit a pattern or its usage scope, click the pattern and use the following controls: In scripts: clear this checkbox if the pattern must not be used in SQL files and database consoles. In literals: clear this checkbox if the pattern must not be used in string literals injected with SQL. All languages: click the link and clear language checkboxes where you do not want to use the pattern.

Last modified: 21 June 2023