DB Query

DB Query

Type: db-query  ·  Package: Database Activities v1.0.3  ·  Output: DataTable

Runs a SELECT query and returns the results as a DataTable, ready to loop over, filter or write to Excel. It is the most common read activity.

How it works

Runs the SELECT on the connection and loads all its rows into memory as a DataTable, with one column per result column. 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: each params key is sent as @key (you can write it with or without @). With odbc, the @name markers in the SQL are replaced by ? in order of appearance; a repeated name sends its value again and a name with no value in params is sent as NULL.
• Volume: the whole result stays in memory. For large tables, filter and page in the SQL (WHERE, TOP/LIMIT) instead of reading everything.
• Timeout: the driver’s command timeout applies (there is no parameter of its own).

Required parameters

Parameter	Type	Default	Description
sql	string	—	The SELECT statement. 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 (customerId). Text, numbers, true/false and null are sent with their type; a value that is not an object is ignored.

Output

Returns a DataTable with the first result set of the query:

• Columns: the names returned by the SELECT (use AS aliases for clear names), with the data type provided by the driver (number, text, date, boolean…).
• Rows: one per record, in result order (use ORDER BY if order matters).
• Null values: arrive as a database null (DBNull); str(row["Column"]) turns them into empty text.
• No matches: a DataTable with the columns and 0 rows (not an error). Check it with rowCount(table) == 0.

Loop over it with For Each and read cells with str(row["Column"]).

Example

DB Query
  sql    = "SELECT Id, Name, Balance FROM Customers WHERE City = @city"
  params = { "city": targetCity }
  → output: customers

Log   message = "Customers found: " + rowCount(customers)
For Each   items = customers   itemVariable = row
 └─ activities:
      Log   message = str(row["Name"]) + ": " + num(row["Balance"])

Use parameters, not concatenation

Write WHERE City = @city with params, not "WHERE City = '" + city + "'". Parameters avoid quoting errors and protect against SQL injection. Table or column names cannot be parameterized: if they come from a variable, validate them against a list of allowed values.

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, or the variable does not hold a 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 that every @name has its key in params.
Driver error about a closed connection	The session was already closed with DB Close or when its body ended. Run the query before closing.

For transient errors (network, locks) wrap the query in a Retry; for everything else, in a Try / Catch.

Related activities

• DB Scalar — when the SELECT returns a single value.
• DB Execute — for INSERT/UPDATE/DELETE.
• DataTable — filter, sort and summarize the result.