Screens: SQL editor

Bind parameters

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.NameDescription
1Parameter columnA 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.
2Column columnThe 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 —.
3Type columnThe value type. It is preselected from the DB type of the column found, or String when no column is found. See the table below.
4Value columnThe value to use. For Date and Timestamp, pick a date with the calendar icon.
5PreviewThe SQL with the current values filled in. This is not the SQL that is executed (see 'Preview versus actual execution').
6Execute buttonConfirms the values and runs the SQL. You can also press Enter in the dialog.

Supported syntax

SyntaxExampleHow the value is used
?WHERE customer_id = ?Bound
:nameWHERE status = :statusBound. The name starts with a letter or _
? and :name mixedWHERE customer_id = ? AND status = :statusBound. 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 :name inside a single-quoted string, a comment, or a PostgreSQL $$ body is not a placeholder.
  • PostgreSQL casts such as ::int and 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.column and :OLD.column in an Oracle trigger body are not placeholders.

Types

TypeValue
StringA string
NumberA number
BooleanChoose TRUE or FALSE
DateYYYY-MM-DD
TimestampYYYY-MM-DD HH:mm:ss
NULLUses NULL; there is no value field
ExpressionInserts 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

  1. Write SQL with placeholders in a SQL tab. For example: SELECT * FROM orders WHERE customer_id = :customerId AND status = :status
  2. Click the Execute button or the Run Script button. The Bind Parameters dialog opens.
  3. Fill in the Type and Value columns on each row.
  4. 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.).

PreviewActual execution
?, :name, #{} placeholdersShows the values as SQL literals. Strings and dates are wrapped in single quotesReplaces every placeholder with ? and binds the values in order. If the same :name appears twice, the same value is bound twice
${} placeholders, Expression typeShows the text you typed as isInserts the text you typed into the SQL as is and runs it
Long or multi-line valuesShows 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 :name appears 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. :name is 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.