Database Activities

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

The Database package allows your automation to 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. Supports SQL Server, PostgreSQL, MySQL and any source via ODBC.

Do I need to know SQL?

Yes, a little. These activities execute 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 execute. If you do not have access to the database, consider integrating through their API or their desktop/web interface.

The model: connect → consult → 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 within body inherit the connection automatically (you don’t configure session), and the connection closes itself upon completion.

DB Connect   provider = sqlserver   connectionString = = credential("db-ventas").ToPlainText()
 └─ body:
      DB Query   sql = "SELECT * FROM Clientes WHERE Activo = 1"   → output: clientes
      DB Execute sql = "UPDATE Procesos SET UltimaCorrida = GETDATE()"

Option B — Session in a variable

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

DB Connect   provider = postgres   connectionString = = credential("db").ToPlainText()   → output: db
DB Query   session = = db   sql = "SELECT * FROM pedidos"   → output: pedidos
DB Close   session = = db

Providers and connection string

provider	Motor
sqlserver	Microsoft SQL Server
postgres	PostgreSQL
mysql	MySQL / MariaDB
odbc	Any source with ODBC driver

The connection string (connectionString) is the ADO.NET boilerplate that indicates server, database, and credentials:

Server=db.empresa.com;Database=ventas;User Id=bot;Password=...;

The connection string carries the password: protect it

The connection string contains credentials. Do not write it in plain text in the playbook. Save it complete to a credential and use it with = credential("db-ventas").ToPlainText().

Consultas parametrizadas: @nombre + params

Never put variable values directly into the SQL text by concatenating. Instead, use parameters: put @nombre in the query and pass the values ​​in the params field (a { nombre: valor } object).

DB Query
  sql    = "SELECT * FROM Clientes WHERE Ciudad = @ciudad AND Activo = @activo"
  params = { "ciudad": = ciudadBuscada, "activo": true }
  → output: resultado

Security: prevent SQL injection

Concatenating values in the SQL ("... WHERE Nombre = '" + nombre + "'") is dangerous: a piece of data with quotes can break the query or, worse, allow a SQL injection attack. The @nombre parameters avoid this completely and also handle quotes, dates, and nulls well. Always use them when a value comes from a variable.

Tres formas de consultar

Activity	Purpose	Returns
DB Query	SELECT returning multiple rows	DataTable
DB Scalar	SELECT which returns a single value (a COUNT, a MAX)	the value
DB Execute	INSERT / UPDATE / DELETE	number of rows affected

Activities

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

A typical flow

Read pending orders and mark them as processed:

DB Connect   provider = sqlserver   connectionString = = credential("db-erp").ToPlainText()
 └─ body:
      DB Query   sql = "SELECT * FROM Pedidos WHERE Estado = @e"   params = { "e": "pendiente" }   → output: pedidos
      For Each   items = = pedidos   itemVariable = fila
       └─ activities:
            ... procesar ...
            DB Execute   sql = "UPDATE Pedidos SET Estado = 'procesado' WHERE Id = @id"   params = { "id": = fila["Id"] }

Next steps

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