When you run SQL that contains placeholders, the Bind Parameters dialog opens and asks for a value for each one. Use it to run the same SQL many times with different values. How values reach the SQL (binding versus direct substitution) is explained in SQL execution and transactions.
Where you can use itWebDesktopWithout signing inSigned inLocal projectsAccount projects
What's on the screen
| No. | Name | Description |
|---|---|---|
| 1 | Parameter column | A placeholder in the SQL. A ? carries its position number; :name and #{name} show the name. A name that appears several times has only one row. |
| 2 | Column column | The column the placeholder is compared with or assigned to, and its DB type. NeoSQL looks in WHERE, the column list of INSERT, and the SET of UPDATE. If none is found, it shows —. |
| 3 | Type column | The value type. It is preselected from the DB type of the column found, or String when no column is found. See the table below. |
| 4 | Value column | The value to use. For Date and Timestamp, pick a date with the calendar icon. |
| 5 | Preview | The SQL with the current values filled in. This is not the SQL that is executed (see 'Preview versus actual execution'). |
| 6 | Execute button | Confirms the values and runs the SQL. You can also press Enter in the dialog. |
Supported syntax
| Syntax | Example | How the value is used |
|---|---|---|
? | WHERE customer_id = ? | Bound |
:name | WHERE status = :status | Bound. The name starts with a letter or _ |
? and :name mixed | WHERE customer_id = ? AND status = :status | Bound. The dialog lists them in the order they appear |
MyBatis #{name} | WHERE order_id = #{orderId} | Bound. In #{id, jdbcType=INTEGER} only the name before the comma is used |
MyBatis ${name} | ORDER BY ${column} | Inserted into the SQL text as is. The type column shows the Inline tag |
- A
?or:nameinside a single-quoted string, a comment, or a PostgreSQL$$body is not a placeholder. - PostgreSQL casts such as
::intand the JSON operators?|and?&are not placeholders. A?right after a column or a closing parenthesis and followed by a string (data ? 'key') is also treated as the JSON key-exists operator, not a placeholder. :NEW.columnand:OLD.columnin an Oracle trigger body are not placeholders.
Types
| Type | Value |
|---|---|
| String | A string |
| Number | A number |
| Boolean | Choose TRUE or FALSE |
| Date | YYYY-MM-DD |
| Timestamp | YYYY-MM-DD HH:mm:ss |
| NULL | Uses NULL; there is no value field |
| Expression | Inserts the text you type into the SQL as is. Use it for expressions the DB evaluates, such as now() or the next value of a sequence. Available only for ? and :name |
Entering values and running
- Write SQL with placeholders in a SQL tab. For example:
SELECT * FROM orders WHERE customer_id = :customerId AND status = :status - Click the Execute button or the Run Script button. The Bind Parameters dialog opens.
- Fill in the Type and Value columns on each row.
- Check where the values go in the Preview area and click the Execute button.
- SQL without placeholders runs right away, without this dialog.
- The connection profile's protection mode judges the SQL with the values filled in. When you run with the Execute button, the dangerous SQL check also uses the SQL with the values filled in (Dangerous SQL confirmation).
- The result tab shows the SQL in its preview form, with the values filled in.
- The values you enter are remembered within the same SQL tab and prefilled the next time the dialog opens. Named placeholders are remembered by name and filled in other statements too.
?placeholders have only a position, so they are filled only when you run the same SQL again. They are lost when you close the tab.
Preview versus actual execution
The preview in the dialog only shows the values spliced into the SQL text. The actual execution is a PreparedStatement binding that sends the SQL and the values separately (Preview only — actual execution uses PreparedStatement binding.).
| Preview | Actual execution | |
|---|---|---|
?, :name, #{} placeholders | Shows the values as SQL literals. Strings and dates are wrapped in single quotes | Replaces every placeholder with ? and binds the values in order. If the same :name appears twice, the same value is bound twice |
${} placeholders, Expression type | Shows the text you typed as is | Inserts the text you typed into the SQL as is and runs it |
| Long or multi-line values | Shows only the first 60 characters of the first line, followed by … | Uses the whole value |
- A bound value never becomes SQL text, so a single quote inside the value cannot break the SQL.
- If there is at least one
${}placeholder or Expression type, the dialog shows the notice $ substitution and Expression-type parameters are inserted into the SQL as-is, not bound via PreparedStatement.. While such a value is empty, you cannot click the Execute button.
Values in Run Script
When you run several statements with the Run Script button, one dialog asks for the placeholders of all statements at once. The preview shows the statements one after another.
?positions are numbered across the whole script. If the first statement has two?and the second has one, the?in the second statement is number 3.- If the same
:nameappears in several statements, it is asked once and the value is used in every statement. - If any statement uses MyBatis syntax, the Expression type is not available.
Things to keep in mind
- In a statement that uses MyBatis syntax (
#{},${}),?is asked for as a placeholder too.:nameis not treated as a placeholder and stays in the SQL as written, so do not mix it in one statement. - If a
?used as an operator does not match the rule above and is read as a placeholder, choose the Expression type and type the original text?. The SQL then stays unchanged. - Values of
${}placeholders and of the Expression type become SQL without binding. Check the SQL in the preview before using values you received from someone else. - The SQL shown in the result tab is in its preview form, so long values may be shortened to
…. If you copy that SQL and run it again, the values may differ.
Related screens
- SQL editor: the screen where you write and run SQL.
- SQL execution and transactions: what gets executed, and binding versus direct substitution.
- Dangerous SQL confirmation: the confirmation applied to SQL with the values filled in.
- SQL history and saved SQL: where executed SQL is kept.