Introduction

The DBQuery block in the Sequencer module is an interface for no-code SQLite database interactions within CDP applications. It serves as a bridge between the application and its underlying database, streamlining data operations and enhancing data management efficiency. DBQuery's capability to execute various SQL statements seamlessly integrates database functionalities into the application, enabling real-time data access and manipulation. This makes it indispensable in data-centric CDP applications where database interaction is frequent and critical for decision-making processes.

Usage

DBQuery is employed within CDP applications to handle a range of SQL operations:

• Query Execution: It executes SQL queries, including SELECT, INSERT, UPDATE, and DELETE, facilitating both data retrieval and manipulation.
• Prepared Statements: The block manages prepared statements with bound parameters, enhancing security and efficiency in query execution.
• Result Processing: It processes query results row by row, ensuring accurate and orderly data retrieval.
• Error Handling: DBQuery effectively handles database connection errors, maintaining the stability and reliability of database interactions.

These functionalities enable dynamic database interactions, ensuring data integrity and responsiveness in the application's data handling.

Configuration

Configuring the DBQuery block involves setting up the database connection, specifying the SQL query, and defining the query arguments. The block can be dynamically configured at runtime via its properties.

Properties

Arguments

DBQuery allows dynamic addition of arguments for use as bound parameters in SQL queries.

Events

The DBQuery block handles several key events integral to its operation. These events enable SQL query execution, process each result set row, and notify the query's completion status.

Input Events

The following table summarizes the input events for the DBQuery block.

Output Events

The following table summarizes the output events for the DBQuery block:

Event Data Mapping

The Data member in an Event is crucial for mapping DBQuery arguments to and from the messages. For input events like Execute, Data lists arguments to update from incoming messages. For output events like Row, QueryDone, and QueryFailed, Data lists arguments relevant to the event, such as row data or error messages.

QueryArguments and Query Parameter Binding

The QueryArguments property and '?' binding in the Query play a crucial role in SQL statement execution within the DBQuery block. They enable dynamic data binding and enhance SQL query execution flexibility and security.

QueryArguments

QueryArguments specifies arguments for the SQL query. It is a semicolon-separated list matching each '?' placeholder in Query. These arguments safely pass values into the query, preventing SQL injection.

QueryArguments: "id;name"

This example shows 'id' and 'name' as the expected arguments by the SQL query.

Query Parameter Binding

The Query property defines the SQL statement. Parameters are represented as '?' placeholders in the query string. These placeholders are replaced by values from QueryArguments at runtime.

Query: "SELECT * FROM users WHERE id = ? AND name = ?;"

This query has two '?' placeholders for 'id' and 'name'. The QueryArguments values are provided in the listed order.

This parameter binding method ensures safe user input handling, preventing SQL injection attacks. It also enhances query readability and maintainability, as values are not hardcoded but provided dynamically during execution.

The DBQuery block internally prepares the SQL statement with these arguments and executes it securely against the database.

Mapping Output Values to Arguments

DBQuery can map output values from a SQL query result row into output arguments. This mapping is based on column names in the result set or aliases defined using the AS keyword in the SQL query.

Column Name Mapping

Each column in the SQL query result set can be automatically mapped to an output argument of the DBQuery block, provided the column name matches an output argument's name.

Query: "SELECT email FROM users WHERE id = ?;"

Alias Mapping Using AS

The SQL AS keyword creates a column alias in the result set. This alias can be mapped to an output argument with the same name. Alias mapping is useful when output argument names do not match database column names or in expressions.

Query: "SELECT user_email AS email FROM users WHERE id = ?;"

This query aliases 'user_email' as 'email'. If an output argument named 'email' exists, the 'user_email' value from the result set is assigned to it.

This mapping approach using column names or aliases allows flexibility in handling data from SQL query results. It ensures dynamic population of output arguments in the DBQuery block with relevant data from the query's result set.

Example

An example configuration in YAML format demonstrates DBQuery usage for executing a SQL query and handling input and output arguments:

configure:
  - uri: .
    add:
      - name: ExampleDBQuery
        model: Sequencer.DBQuery
        values:
          Database: "example.db"
          Query: "SELECT name, email FROM my_table WHERE id = ?;"
          QueryArguments: "id"
        add:
          - name: id
            model: Sequencer.Argument<int>
            values:
              Value: 1
          - name: name
            model: Sequencer.Argument<string>
          - name: email
            model: Sequencer.Argument<string>

In this example, ExampleDBQuery executes a SQL SELECT query on example.db , selecting name and email from my_table where id matches the specified value (1 in this case). It includes input argument id for the query and output arguments name and email for capturing fields from the result set.