DB Scalar

DB Scalar

Type: db-scalar  ·  Package: Database Activities v1.0.3  ·  Output: object

Runs a SELECT that returns a single value and gives it back directly. It is the easy way to get one piece of data from the database: a count (COUNT(*)), a maximum (MAX(Date)), a total (SUM(Amount)), or the value of a specific cell — without handling a DataTable.

How it works

Runs the query and returns the value of the first column of the first row; the rest of the result is discarded. Use @name parameters and the params field for variable values (see parameterized queries).

• Session: if you don’t set session, it uses the one from the enclosing DB Connect body.
• Parameters: same as in DB Query: each params key is sent as @key; with odbc they become positional ? markers.
• It also works for an INSERT that returns the generated key (for example INSERT … OUTPUT INSERTED.Id in SQL Server or INSERT … RETURNING id in PostgreSQL).

Required parameters

Parameter	Type	Default	Description
sql	string	—	A SQL statement that returns a single value. Use @name for variable values. Supports multiple lines and expressions.

Optional parameters

Parameter	Type	Default	Description
session	ZoanDbSession	—	Session from DB Connect. Inherited automatically inside a body; outside, pass db.
params	Dictionary	—	Parameter values, as an object { "name": value }. Values can be expressions; text, numbers, true/false and null are sent with their type.

Output

Returns an object with the value, typed as the driver provides it: integer or decimal number, text, date, boolean… The exact type depends on the engine (for example, COUNT(*) is a 32-bit integer in SQL Server and a 64-bit integer in PostgreSQL and MySQL).

• No rows or a NULL value: returns null. Check it with isNull(value) or convert it with num(value) (gives 0) or str(value) (gives "").

Example

Count a customer’s pending orders:

DB Scalar
  sql    = "SELECT COUNT(*) FROM Orders WHERE CustomerId = @id AND Status = 'pending'"
  params = { "id": customerId }
  → output: pending

If   condition = num(pending) > 0
 └─ then:
      Log   message = "The customer has " + str(pending) + " pending orders"

Scalar vs Query

• DB Scalar: when you expect a single value (a number, one field). Simpler.
• DB Query: when you expect several rows/columns (returns a DataTable).

Common errors

Message	Cause and fix
'session' is required and must be a database session.	There is no session: the activity is outside a DB Connect body and got no session.
'sql' is required.	The query is missing or the expression returned nothing.
Engine error: syntax, missing table or column, undeclared parameter	The message comes from the database. Check the SQL and the params names.
Conversion error in a later expression	The query returned no rows and the value is null. Use num(…) or str(…), which accept null, instead of toNumber(…).

Related activities

• DB Query — for multi-row results.
• DB Execute — to change data.