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”)

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

Query Parameter

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 Prompt

4. Review Query Results

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

Query 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.

Examples

  1. Using ’&’:
SELECT * FROM users WHERE username = &user
  1. Using ’$’ with a default value:
SELECT * FROM transactions WHERE amount = $amount||100
  1. Using ’:’ with a default value and type:
SELECT * FROM events WHERE event_date = :event_date||2024-12-25||date
  1. Using ’%’ with a default value and the identifier type:
SELECT * FROM products WHERE %field||category||identifier = 'Electronics'