Database Activities

Version: 1.0.3  ·  ID: zoan-packages-database  ·  Author: Zoan Software

The Database package lets your automation read and modify databases directly with SQL. It is the most efficient way to work with large volumes of data when you have access to the database: query thousands of records, insert results, update statuses. It supports SQL Server, PostgreSQL, MySQL and any source via ODBC.

Do I need to know SQL?

Yes, a little. These activities run SQL statements that you write (SELECT, INSERT, UPDATE, DELETE). You don’t need to be an expert, but you do need to know the query you want to run. If you don’t have access to the database, consider integrating through its API or its desktop/web interface.

The model: connect → query → close

Working with a database follows the open → use → close pattern:

1. DB Connect opens a connection and returns a session (ZoanDbSession).
2. You run one or more queries with DB Query, DB Execute or DB Scalar.
3. DB Close closes the connection.

As with other session packages, there are two ways:

Option A — DB Connect with body (recommended)

Activities inside the body inherit the connection automatically (you don’t set session), and the connection closes by itself when it ends.

DB Connect   provider = sqlserver   connectionString = credential("db-sales")["value"]
 └─ body:
      DB Query   sql = "SELECT * FROM Customers WHERE Active = 1"   → output: customers
      DB Execute sql = "UPDATE Jobs SET LastRun = GETDATE()"

Option B — Session in a variable

DB Connect stores the session in a variable; each activity receives it in session, and you close it with DB Close.

DB Connect   provider = postgres   connectionString = credential("db")["value"]   → output: db
DB Query   session = db   sql = "SELECT * FROM orders"   → output: orders
DB Close   session = db

Providers and connection string

provider	Engine
sqlserver	Microsoft SQL Server
postgres	PostgreSQL
mysql	MySQL / MariaDB
odbc	Any source with an ODBC driver installed on the robot’s machine

In expressions the aliases mssql, sql server, postgresql, pg and mariadb are accepted too. Each engine uses its .NET driver (Microsoft.Data.SqlClient, Npgsql, MySqlConnector); for odbc you install the driver on the robot’s machine.

The connection string (connectionString) is the standard ADO.NET text that sets server, database and credentials:

Server=db.example.com;Database=sales;User Id=bot;Password=...;

The connection string holds the password: protect it

The connection string contains credentials. Don’t write it in plain text in the playbook. Store the whole string in a credential and use it with credential("db-sales")["value"].

Parameterized queries: @name + params

Never put variable values straight into the SQL text by concatenating. Use parameters instead: put @name in the query and pass the values in the params field (an object { name: value }).

DB Query
  sql    = "SELECT * FROM Customers WHERE City = @city AND Active = @active"
  params = { "city": targetCity, "active": true }
  → output: result

Security: prevent SQL injection

Concatenating values into the SQL ("... WHERE Name = '" + name + "'") is dangerous: a value with quotes can break the query or, worse, allow a SQL injection attack. @name parameters avoid this completely and also handle quotes, dates and nulls correctly. Always use them when a value comes from a variable.

How parameters are sent

• Each params key is sent as @key; you can write the key with or without @.
• Values can be expressions (customerId); text, numbers, true/false and null reach the engine with their type.
• With odbc, the package replaces each @name in the SQL with ? in order of appearance: a repeated name sends its value again and a name with no value in params is sent as NULL. Avoid writing @ inside SQL string literals when you use ODBC.
• Parameters are for values, not for table or column names. If a table name comes from a variable, validate it against a list of allowed values before building the SQL.

Transactions and timeouts

• Autocommit: each statement is committed as it runs; the package does not open transactions. To make several statements apply all or nothing, send them in the same DB Execute inside the engine’s transaction (BEGIN TRAN … COMMIT in SQL Server, BEGIN; … COMMIT; in PostgreSQL) or use a stored procedure.
• Timeouts: the activities have no timeout parameter; the driver’s apply. Adjust them in the connection string with the driver’s keywords if you need to.
• Errors: engine errors (syntax, constraints, locks, connection) arrive with the database’s original message. Combine them with Try / Catch and Retry.

Three ways to query

Activity	Purpose	Returns
DB Query	SELECT returning several rows	DataTable (0 rows if nothing matches)
DB Scalar	SELECT returning a single value (a COUNT, a MAX)	the value (null if there are no rows)
DB Execute	INSERT / UPDATE / DELETE	number of affected rows

Activities

Activity	Type	Output	What it does
DB Connect	db-connect	ZoanDbSession	Opens a connection and returns a session
DB Query	db-query	DataTable	Runs a SELECT and returns the rows
DB Scalar	db-scalar	object	Runs a SELECT and returns a single value
DB Execute	db-execute	int	Runs INSERT/UPDATE/DELETE; returns affected rows
DB Close	db-close	—	Closes the connection

A typical flow

Read pending orders and mark them as processed:

DB Connect   provider = sqlserver   connectionString = credential("db-erp")["value"]
 └─ body:
      DB Query   sql = "SELECT * FROM Orders WHERE Status = @s"   params = { "s": "pending" }   → output: orders
      For Each   items = orders   itemVariable = row
       └─ activities:
            ... process ...
            DB Execute   sql = "UPDATE Orders SET Status = 'processed' WHERE Id = @id"   params = { "id": row["Id"] }

Next steps

• DB Connect — open the connection.
• DB Query — the most common query.
• DataTable — process the results of a SELECT.