Databases
All four supported SQL databases follow the same design: a Collection runs a query on a schedule and turns the result into messages; a Delivery runs a write command (or a procedure) with the message data. This page covers what holds for all of them.
What changes between them — the connection fields, what the engine offers and the quirks of each dialect — lives on its own page:
| Database | Collection | Delivery |
|---|---|---|
| SQL Server | SQL_SERVER | SQL_SERVER_EXEC |
| Oracle | ORACLE | ORACLE_EXEC |
| PostgreSQL (includes TimescaleDB) | POSTGRES | POSTGRES_EXEC |
| SQLite | SQLITE | SQLITE_EXEC |
| InfluxDB | INFLUXDB | INFLUXDB_WRITE |
| MongoDB | MONGODB | MONGODB_WRITE |
For all of them the connection is registered under External Connections. Passwords are stored encrypted and never come back to the screen — when editing, leaving the field blank keeps the current password.
InfluxDB also appears in the list above, but it does not follow this design: it has no Key Column nor Post-Collection Command (incremental sweeping is done through the query’s time window) and its delivery writes line protocol instead of running a command. See its page.
MongoDB also has a design of its own: the query is a JSON filter or pipeline (not SQL) and the Delivery writes the document directly, with no command. See its page.
Collection — SQL_SERVER / ORACLE / POSTGRES / SQLITE
CMS runs the SELECT Query on the Collection schedule and turns the result into messages. The Result Mode decides the granularity:
| Mode | Result |
|---|---|
LINHA_UNICA_PAYLOAD_UNICO | The whole result becomes one message (a JSON array with every row) |
UMA_LINHA_POR_PAYLOAD | Each row becomes an independent message (a JSON object) |
Every run opens the connection, queries and closes it — no pool is kept between cycles. The Collection Timeout limits the connection time on SQL Server and Oracle; on SQLite it has no effect, because what makes a run wait there is the file lock.
Marking what has been read
To avoid collecting the same records on the next run, configure the Key Column + Post-Collection Command pair:
- Key Column — name of the column returned by the SELECT Query that identifies each row (e.g.
id). - Post-Collection Command — a write command run right after the query, with the
:idsplaceholder replaced by the list of values of that column for the rows just collected.
-- SELECT Query
SELECT id, ordem, quantidade FROM fila_producao WHERE processado = 0
-- Post-Collection Command
UPDATE fila_producao SET processado = 1 WHERE id IN (:ids):ids is a variable-length list, so it is concatenated into the command (text values are
escaped); every other value still goes in as a bind. The post-collection command only runs if the
query returned rows and the Key Column returned a value.
Without that pair — or without a query that filters what has already been read — the next run collects the same records again. CMS does not keep a read cursor for you.
Parameters in the query
Both the SELECT Query and the Post-Collection Command accept :name for Variables (global and
per Application) and for the Collection’s Input Parameters:
SELECT * FROM apontamento WHERE planta = :PLANTA_PADRAO AND turno = :turnoOnly the names that actually appear in the text are bound, and the value always goes in as a driver parameter — never concatenated. On a name clash the Variable wins over the Input Parameter, the same as in every other field.
Do not quote the placeholder (':turno'): inside a literal it stops being a bind and becomes
text — a comparison that is always false on SQL Server, a bind error on Oracle and PostgreSQL. CMS
warns you when saving.
Delivery — SQL_SERVER_EXEC / ORACLE_EXEC / POSTGRES_EXEC / SQLITE_EXEC
Runs the Message Definition’s SQL Command against the destination database with the message
data. The command runs with auto-commit, and the result (row(s) affected) goes into the message
history, where it can be evaluated by business error rules. When the database rejects the command,
the recorded error carries the executed command with the values in place of the placeholders — which
is what lets you reproduce the failure straight in a SQL client.
| Placeholder | Value |
|---|---|
:payload | Message body as text |
:datetime | Processing timestamp |
:transformer | Output of the Transformer configured on the Delivery |
:NAME | Global or Application variable |
:alias | Input Parameter |
INSERT INTO recebimento (payload, recebido_em, planta)
VALUES (:payload, :datetime, :PLANTA_PADRAO)Procedures and dynamic SQL
| Dialect | How to call |
|---|---|
| SQL Server | EXEC my_procedure :payload, :datetime — a named procedure; EXEC(@variable) and EXEC('text') are blocked |
| SQL Server | EXEC (:transformer) — runs the text produced by the Transformer as SQL |
| Oracle | DML only (INSERT/UPDATE/DELETE). To run PL/SQL produced by the Transformer, use exactly BEGIN EXECUTE IMMEDIATE :transformer; END; |
| PostgreSQL | DML only (INSERT/UPDATE/DELETE/MERGE), including INSERT ... ON CONFLICT. Procedure CALL and DO $$...$$ blocks are not accepted |
| All | A command that is just :transformer — the text produced by the Transformer is the command that runs |
Calling an Oracle procedure by name (a PL/SQL block or {call proc(...)}) is not supported yet:
validating that syntax as safely as the T-SQL path is a design of its own. Until then, generate the
block from the Transformer.
What CMS blocks in SQL fields
Every command field — SELECT Query, Post-Collection Command, the Delivery’s SQL Command and the Test
Query — goes through a guard before being saved and again before running. It requires the command
to start with a verb compatible with the mode (read: SELECT/WITH; write:
INSERT/UPDATE/DELETE, plus MERGE on PostgreSQL and REPLACE on SQLite), rejects several
statements chained by ; and bars the vocabulary below:
| Scope | Blocked |
|---|---|
| Every dialect | CREATE, ALTER, DROP, TRUNCATE, GRANT, REVOKE, DENY, BACKUP, RESTORE |
| SQL Server | OPENROWSET, OPENQUERY, OPENDATASOURCE, SHUTDOWN, DBCC, sp_/xp_ procedures, EXECUTE AS |
| Oracle | UTL_HTTP, UTL_TCP, UTL_SMTP, UTL_FILE, DBMS_SCHEDULER, DBMS_JOB, DBMS_SQL, EXECUTE IMMEDIATE (outside the Transformer block) |
| PostgreSQL | COPY (including COPY ... FROM PROGRAM, which runs a shell command on the server), dblink, pg_read_file, pg_write_file, pg_ls_dir, lo_import, lo_export, pg_sleep, pg_terminate_backend |
| SQLite | ATTACH, DETACH, PRAGMA, VACUUM, REINDEX, LOAD_EXTENSION, READFILE, WRITEFILE |
Comments and string literals are neutralized before the check, so hiding a forbidden word inside a
comment (CRE/**/ATE) or a string does not get through. Every blocked attempt is recorded in the
audit log, with the field and the reason.
The guard is defense in depth, not the main protection. What really limits the damage is the
permission of the user configured on the connection: no DDL/DCL, and GRANT EXECUTE only on the
procedures you actually need.