SQL Action
SQL Action executes a native SQL statement against a configured DataSource. With dataUpdate = false it runs getResultList() and returns the result as JSON; with dataUpdate = true it runs executeUpdate() and returns the affected row count.
Add an SQL Action to automate database work
SQL Action lets automation rules, manual controls, and task pipelines execute database statements directly through a configured DataSource. Use it for connectivity checks, read-only lookups, audit inserts, state updates, stored procedure calls, and periodic maintenance.
Before creating an SQL Action, make sure a DataSource component is already configured and running. The action executes inside the datasource operation context, so the datasource must be reachable when the action fires.
Open the Actions section
In the Banalytics server view, navigate to Event Manager or the component that will trigger the action, then add a new action and select SQL Action from the action type list.
Select the target DataSource
Set Datasource to the DataSource component that holds the JDBC connection you want to use. The datasource must be started and connected before the action can run.
Configure the SQL statement
Enter the SQL in the Query field. Set Data update to false for SELECT queries and to true for INSERT, UPDATE, DELETE, or stored procedure calls.
Configuration parameters
| Parameter | Required | Description | Default |
|---|---|---|---|
Title | Yes | Display name for this action instance. Use a name that describes the purpose of the query, for example Audit insert on alarm or Mark device processed. | None |
Datasource | Yes | Reference to the DataSource component that provides the JDBC connection. The action executes inside the datasource operation context and respects its transaction and connection pool settings. | None |
Data update | Yes | Controls the execution mode:
| None |
Query | Yes | Native SQL statement to execute. The syntax, quoting rules, and supported statement types depend on the selected database and JDBC driver. The statement is executed as-is without parameter binding. | None |
Configure the action for the right operational role
SQL Action bridges the Banalytics automation engine with external relational databases. It is designed for simple, bounded SQL operations triggered by events, schedules, or manual invocation — not for complex multi-statement workflows or dynamic parameterization.
Action task
Runs manually from the UI, from Event Manager on a rule match, or as part of an action chain. The action result and any errors are stored in action event history.
Execution context
Runs inside the datasource operation context. The SQL statement is executed as-is; upstream event and variable values are not automatically injected into the query text.
Action events
Produces action results reviewable in history and diagnostics. SELECT results appear as JSON; UPDATE results show the affected row count. Errors are reported as action processing failures.
Query patterns and integration scenarios
Read operations
Connectivity check
Keep Data update set to false and use a minimal query such as select 1 to verify that the selected datasource is reachable and the action can be executed from the UI or Event Manager.
Read-only lookup
Use Data update set to false for diagnostic or rule-support queries that read from a local or external database. The result is serialized to JSON and stored as the action event result, which is useful for audit and troubleshooting.
Local reporting query
Use Data update set to false for small, bounded reports from the local datasource. Keep result sets short because action results are serialized into JSON and can become noisy in event history.
Write operations
Event audit insert
Use Data update set to true with an INSERT statement to write a fixed audit marker into an external table when a rule fires. This is useful for integrating Banalytics events with an existing operations database.
External state update
Use Data update set to true with UPDATE or DELETE when a Banalytics rule should mark an external record as processed, update a device status row, acknowledge an alarm, or clear a temporary flag.
Maintenance operation
Run controlled cleanup, compaction, or archive SQL from a manually executed action. Keep these actions separate from high-frequency event rules and test them on a backup database first.
Advanced patterns
Stored procedure or database-side logic
When the target database supports it, use a native call statement to delegate dynamic decisions to a stored procedure. This keeps complex SQL and validation in the database instead of trying to express it in the action configuration.
Integration with custom logic
When SQL needs values from the execution context, an event object, or user input, use a JavaAction or custom module to build a parameterized query through application code. SQL Action itself stores and executes the configured SQL text as-is.
Operational notes
Native SQL, not JPQL
SQL Action executes native SQL, not JPQL. Syntax, quoting, pagination, stored procedure calls, and DDL support depend on the selected database and JDBC driver.
Choose Data update carefully
SELECT-style queries must use Data update set to false. INSERT, UPDATE, DELETE, and most DDL and procedure calls must use true. Using the wrong mode can cause runtime errors or silently discard results.
Transactional execution
The action runs in a transaction through DataSourceThing.executeOperation. On failure the transaction is rolled back by the datasource operation wrapper.
Avoid slow queries in high-frequency rules
Long-running SQL can block the action execution path, consume connection pool slots, and delay other work that shares the same datasource. Reserve heavy queries for manually triggered or low-frequency rules.
Bound SELECT result sets
Keep SELECT result sets bounded with WHERE, LIMIT, FETCH FIRST, or database-specific pagination. Large result lists are loaded into memory and serialized as the action result.
No automatic parameter binding — avoid SQL injection
SQL Action executes the configured SQL text as-is. Do not concatenate untrusted data into the Query field. Use a JavaAction, stored procedure, or a dedicated integration module when dynamic values from user input or external sources are required.
Use minimum required permissions
Configure the DataSource with a database account that has only the permissions required for the SQL being executed. Prefer read-only credentials for SELECT actions and a narrowly scoped account for update actions.
Test before enabling in production
Errors are reported through action processing and the returned action result may contain the error message. Test SQL manually against the target datasource before enabling the action in production rules.