SQL Connectors

The SQL Connectors editor is where named SQL queries are declared. A SQL connector is a reusable "talks to one database" definition — a JDBC connection plus a list of named queries — that the rest of NomaUBL references by name. It mirrors the structure of API Connectors: the same picker drives Action Bindings and Notification Rules, with a · SQL suffix in the dropdown so the kind is visible.

Typical targets are:

• A second NomaUBL database for cross-environment lookups.
• A source ERP database — JD Edwards, SAP, NetSuite or a custom schema — when an action needs to read a customer email, a payment date, a status code that the HTTP API does not expose.
• An operational database that an action must update — for example flagging an invoice as sent in a downstream system, archiving a row, recording an audit entry.

The page applies to documents from any source system, since the SQL connector can point at any reachable JDBC database.

The editor has three tabs:

1. Connection — database type, JDBC URL, credentials, schema and query limits.
2. Queries — collapsible per-query cards: name, label, description, SQL, parameters, Writable flag.
3. Test — runs a query against the live database and shows columns + rows or affected count.

---

Opening the editor

• Settings → + Add SQL at the top right of the resource sidebar to create a new SQL connector.
• Existing SQL connectors live under the SQL group of the resource sidebar with an sql badge — click any of them to open the editor.

---

At a glance

---

Tab 1 — Connection

Connection

Field	Values	Description
Database Type	Oracle / PostgreSQL / Microsoft SQL Server / Custom JDBC (driver class below)	Backend type. Drives the JDBC URL placeholder and the dialect-specific parsing in the runtime. Microsoft SQL Server needs the mssql-jdbc jar in lib/. Custom JDBC points at any other database (DB2, MariaDB, Snowflake, …) by naming its driver class — no new NomaUBL release is required to add one.
Driver class	text (Custom JDBC only)	Fully-qualified JDBC driver class name, e.g. com.example.jdbc.MyDriver. Shown only when Database Type is Custom JDBC; drop the matching driver jar in lib/.
JDBC URL	text	Full JDBC connection string — jdbc:oracle:thin:@host:1521/service_name (Oracle), jdbc:postgresql://host:5432/database_name (PostgreSQL), jdbc:sqlserver://host:port;databaseName=name (SQL Server), or the format your custom driver expects.
Schema	text (optional)	Data schema, written as {{schema}} in a query (e.g. PRODDTA). No automatic prefixing happens otherwise: unqualified table names resolve against the JDBC user's current_schema.
Control schema	text (optional)	Control-table schema, written as {{ctlSchema}} in a query (e.g. PRODCTL, for the JDE control tables such as F0005).

{{schema}} and {{ctlSchema}} are filled from these fields when the query runs, never from the caller, and must be plain identifiers. A query that uses one of them while the field is empty fails with Set the Schema / Control schema of connector …. That way the same query runs unchanged on every environment.

Credentials

Field	Description
DB User	Database account name with the privileges required by the queries declared on this connector. Read-only access is enough for SELECT-only queries.
DB Password	Password for the DB account. Stored Base64-encoded in the config file, the same way as db-nomaubl and db-jde.

Limits

Field	Default	Description
Query timeout (ms)	30000	Per-statement JDBC timeout. Long-running queries are cancelled past this.
Max rows	1000	Hard cap on rows returned per SELECT call. A runaway query cannot exhaust the JVM heap.

---

Tab 2 — Queries

The Queries tab is a list of collapsible cards. The card header shows a READ / WRITE badge, the query name, and the description as a secondary line so a connector with several queries reads as a punch-list at a glance. Click a header to expand the editor; click + Add query to append a new one.

Per-query fields

Field	Description
Name	Identifier of the query. Used as the endpoint value when wiring the query into an action binding or a notification rule (connector / name).
Label	Short human-readable title shown next to the name in the dropdowns (name — Label).
Description	Free-form sentence shown as the collapsed-card secondary line. Reading the rule list out loud should make sense.
Writable	Yes / No. Required to be Yes when the SQL is anything other than SELECT — otherwise the runtime rejects the call before the connection opens.
SQL	The SQL itself, with :name placeholders for parameters. The first non-comment, non-string keyword decides the statement type (SELECT / INSERT / UPDATE / DELETE / MERGE).
Parameters	Spec of :placeholder tokens used in the SQL. Format: `name

Statement-type allow-list

Only SELECT, INSERT, UPDATE, DELETE and MERGE are accepted. Anything else — DROP, TRUNCATE, ALTER, GRANT, CREATE, etc. — is rejected before the connection even opens. A query that begins with one of those keywords cannot be saved as Writable=No either: the runtime always re-parses the first keyword on call, so the gate is not bypassable by editing the resource by hand.

Parameter binding (:name → ?)

Parameter binding goes through PreparedStatement. The runtime parses the SQL, rewrites every :name token to a positional ?, and binds the values positionally — values are never string-substituted into the SQL. The parser respects:

• single-quote string literals ('O''Brien'),
• double-quoted identifiers ("customer.name"),
• line comments (-- …) and block comments (/* … */),
• the PostgreSQL ::type cast operator ('foo'::text).

Tokens inside any of those constructs are passed through unchanged.

The parameter spec drives the Test tab and the call-site editor: each declared :name becomes a labelled input pre-filled with its default value. Spec rows are semicolon-separated; each row has up to three pipe-delimited fields. Empty fields are accepted (a parameter with no default just renders an empty input).

Surrounding quotes stripped on string params

The runtime strips one matching pair of surrounding single or double quotes from each bound string param before the JDBC setString. Typing '01' in a default or in the test panel — the way a SQL literal is written — binds 01, not the 4-character string, so either convention works.

JDE CHAR comparison

Stripping the quotes is not a substitute for the standard Oracle non-blank-padded comparison rule. Comparing against a JDE CHAR(n) column still needs WHERE TRIM(col) = :p in the query when one side of the comparison is bound as VARCHAR2. That is an Oracle rule, not a framework choice.

Writable flag

Writable=No is the safe default. Setting it to Yes is required for any non-SELECT statement and is what the runtime checks at call time:

• A SELECT query with Writable=Yes runs fine — the flag widens the allow-list, it does not narrow it.
• An UPDATE query with Writable=No is rejected with an error message in the test panel and STOP in the audit trail at runtime.
• A typo in a notification rule that points to a non-Writable query but tries to fire DELETE FROM … cannot succeed — the gate runs before the connection opens.

---

Tab 3 — Test

A built-in runner that executes the selected query against the live database and shows the result.

Element	Description
Query	Dropdown of every query declared on the Queries tab, with name — Label rendering. Selecting a query pre-fills the parameter rows from the spec.
Parameters	One row per declared :placeholder, plus an Add param button for ad-hoc parameters not in the spec. Each row has a name input and a value input.
Run	Sends the call to /api/sql-connectors/{connector}/{query} with the parameter values. The result is rendered next to the button.

Result panel

Statement type	What's shown
SELECT	Statement label + row count + duration on the green-on-success line; below it, a compact table of columns × rows. The runtime caps the number of rows to Max rows on the Connection tab.
INSERT / UPDATE / DELETE / MERGE	Statement label + affected count + duration on the green-on-success line. No table — the runtime returns only the update count for non-SELECT calls.
Error	Red error box with the JDBC / parser error message (SQLException, allow-list violation, missing :placeholder value, etc.).

The Test tab calls the live database — Save the connector first if you have just edited it; otherwise the test runs against the previously-saved version of the query.

---

Shipped connector: jde-sql

JDE-specific

NomaUBL ships a jde-sql connector with ready-made queries on JD Edwards EnterpriseOne tables. Set its connection (database type, JDBC URL, account) and its two schemas, and the queries work as they are.

Query	Writable	What it does
getUDC — UDC values	No	Values of a user-defined code (F0005) for a product code (drsy) and a code type (drrt), as CODE, LABEL_FR, LABEL_EN — ready to feed a custom list. Uses {{ctlSchema}}.
updateInvoiceStatus — Update invoice status	Yes	Puts the receivable lines of an invoice (F03B11, by doc, dct, kco) on pay status H and stops dunning letters for them (RPDNLT = 'N'), except lines already paid (P). Meant for an action binding on a disputed invoice. Uses {{schema}}.
getCompanies — Companies	No	JDE companies (F0010) with their names, as CODE, LABEL_FR, LABEL_EN. It feeds the shipped list-companies custom list, which drives the company pickers (VAT declaration, audit report, extraction, status retrieval). Uses {{schema}}.

An upgrade adds the queries the connector lacks and keeps the ones already there; see Upgrade.

---

How SQL connectors are used

SQL connectors plug into the same call sites as API connectors:

• Action Bindings — the regulatory buttons in the invoice detail modal can call any SQL query, alone or in a chain, via the multi-call list.
• Notification Rules — same multi-call list, fires automatically whenever the rule matches a status change.

In both cases, the connector dropdown lists API connectors and SQL connectors merged with · API / · SQL suffixes; the target dropdown loads endpoints or queries depending on the picked connector kind.

Outputs and response chaining

When a SQL call returns, its outputs land in the dispatch context under call.N.* keys, where N is the 1-based index of the call within the rule or binding. Subsequent calls reference them with {call.N.fieldName} placeholders in their parameter values.

Field	Source
call.N.success	true when the call ran without error.
call.N.statementType	SELECT / INSERT / UPDATE / DELETE / MERGE.
call.N.rowCount	For SELECT — number of rows returned.
call.N.updateCount	For non-SELECT — number of rows affected.
call.N.error	Error message when success is false.
call.N.<column>	For SELECT — every column of the first row, by name.

Example: a rule that first looks up the customer's email via a SQL query, then sends a follow-up HTTP webhook, would set the webhook payload's to parameter to {call.1.EMAIL} (the EMAIL column of the first row of call #1).

---

Tips & best practices

• Keep one connector per database, not per query. A crm connector with five named queries is easier to read than five connectors each holding one query. The dropdown groups queries under the parent connector.
• Name queries by intent, not by SQL shape. findCustomerEmail reads better than selectKcoFromF0101. The body of the SQL is one click away in the editor — the name is what the rule list shows.
• Start every connector with Writable=No. Flip the flag to Yes only on the queries that actually need to write. A SELECT-only connector cannot be coerced into running a DELETE even if a typo points the rule at the wrong query.
• Use :name placeholders for everything user-supplied. Concatenating values into the SQL string defeats the parameter-binding safety net. The parser deliberately ignores tokens inside literals and comments so : characters inside strings are safe.
• Set Max rows tighter than the default for dashboard-style queries. A widget that reads the top 10 rejected invoices does not need 1000 rows back; capping at 50 keeps the UI snappy and the JDBC fetch cheap.
• Test before saving when changing an existing query. The Test tab uses the in-memory edit of the query when you have unsaved changes, but the Run button calls the live database — there is no rollback. A Writable=Yes query under test will commit if the SQL says so.
• Pair SQL connectors with API connectors via response chaining. A notification rule can read a value from a SQL query and forward it as a parameter to an HTTP webhook, without code on either side. See the Notification Rules — Actions tab for details.
• Keep credentials in a dedicated DB account. Even on a read-only connector, give the JDBC user just the privileges the queries need — SELECT on a small set of tables, no broad schema access.