DBCode - Docs - Query Parameters

DBCode provides flexible query parameter formats to help you create dynamic queries, supporting use across notebook cells. This guide explains the available parameter formats, their optional components, and best practices for effective usage.

Supported Formats

DBCode supports defining query parameters using the following prefixes:

& (e.g., &user for a parameter named “user”)
$ (e.g., $amount for a parameter named “amount”)
: (e.g., :event_date for a parameter named “event_date”)
% (e.g., %(field) for a parameter named “field”) - Only supported when using {} or () to enclose the parameter name.

Components

Each format can include optional components:

Syntax: &name||value||type

Components:

value: The assigned value of the parameter.
type: The data type of the parameter value. Supported types are:
- string: Escapes the value as a text string.
- number: Treats the value as a numeric type.
- date: Handles the value as a date format.
- identifier: Leaves the value unescaped.

Note: If no type is specified, DBCode will attempt to infer the type automatically.

Handling Spaces

If a parameter name or value contains spaces, enclose it in curly braces {} or parentheses () to avoid syntax errors.

Example:

SELECT * FROM orders WHERE customer_name = &{customer name||John Doe||string}

Use Query Parameters

1. Open DBCode Query Editor

Launch DBCode within Visual Studio Code and open an SQL editor or DBCode notebook.

2. Write a Query with Parameters

Incorporate one of the supported parameter formats into your SQL query.
Then execute the query by using Ctrl+Enter or by clinking on Play button

SQL query with parameters — Write SQL queries using parameter placeholders for dynamic values

3. Confirm Values and Type for Parameters

If any parameter value is not supplied in the query, DBCode will prompt you to input the required values and types, otherwise confirm the default or previously used values.
Click the Continue button to execute the parameterized query.

Parameter input prompt — Input or confirm parameter values when executing the query

4. Review Query Results

DBCode will execute the query with the provided parameter values and display the result.

Query results with parameters — View the results of your parameterized query

Query parameters can share data across notebook cells. Once a parameter is defined in one cell, it can be reused in subsequent cells.
DBCode caches previously defined parameter values. When you execute a cell, a prompt will appear in the result panel, allowing you to continue using the same value or modify it. Click Continue if no changes are needed.

Parameter Reuse

Once you define a parameter with its default value and type, you can reuse it throughout your query by referencing just the parameter name. This is particularly useful for complex queries where the same parameter is used multiple times.

Example:

SELECT * FROM actor
WHERE id = &actorId||1||number
AND status = 'active'
AND id = &actorId

In this example, &actorId is first defined with a default value of 1 and type number. Later in the same query, you can simply use &actorId without repeating the default value and type - DBCode will use the previously defined configuration.

Examples

Using ’&’:

SELECT * FROM users WHERE username = &user

Using ’$’ with a default value:

SELECT * FROM transactions WHERE amount = $amount||100

Using ’:’ with a default value and type:

SELECT * FROM events WHERE event_date = :event_date||2024-12-25||date

Using ’%’ with a default value and the identifier type:

SELECT * FROM products WHERE %{field||category||identifier} = 'Electronics'

Query Parameters