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 clicking on Play button

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.

4. Review Query Results

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

Sharing in Notebook Cells

• 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

1. Using ’&’:

SELECT * FROM users WHERE username = &user

2. Using ’$’ with a default value:

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

3. Using ’:’ with a default value and type:

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

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

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