DB Execute

DB Execute

Type: db-execute  ·  Package: Database Activities v1.0.3  ·  Output: int

Runs a statement that changes data —INSERT, UPDATE or DELETE— and returns the number of affected rows. It is the database write activity: insert records, update statuses, delete data.

How it works

Runs the statement on the connection and returns how many rows it changed. 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 (with or without @). With odbc, @name markers become ? in order of appearance and a name with no value is sent as NULL.
• Immediate commit: the package does not open a transaction; each statement is committed when it finishes. If several statements must apply all or nothing, send them in the same sql inside the engine’s transaction (for example BEGIN TRAN … COMMIT in SQL Server) or use a stored procedure.
• Other statements: it also runs stored procedures (EXEC/CALL) and definition statements (CREATE, ALTER); the count then depends on what the engine reports.
• Retries: a repeated INSERT creates duplicate rows. If you wrap it in a Retry, write the statement so that repeating it does not duplicate data (for example, checking first whether the record exists).

Required parameters

Parameter	Type	Default	Description
sql	string	—	The INSERT, UPDATE or DELETE 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; text, numbers, true/false and null are sent with their type.

Output

Returns an int with the number of affected rows: 0 if the condition matched no rows. With several statements in the same sql, the value adds up what the engine reports. If the engine reports no count (definition statements, SET NOCOUNT ON in SQL Server), the value is -1.

Example

Mark an order as shipped:

DB Execute
  sql    = "UPDATE Orders SET Status = @status WHERE Id = @id"
  params = { "status": "shipped", "id": orderId }
  → output: affectedRows

If   condition = affectedRows == 0
 └─ then:
      Log   level = warning   message = "No order with id " + orderId

Use parameters, not concatenation

Same as in DB Query: pass values with params, never concatenated into the SQL. It protects against errors and SQL injection.

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 statement is missing or the expression returned nothing.
Engine error: duplicate key, constraint violation, missing column, undeclared parameter	The message comes from the database. Check the data and that every @name has its key in params. Catch it with Try / Catch to log the case and move on.
Engine error for a timeout or lock	The statement took longer than the driver’s timeout or waited on a lock. Narrow the WHERE, split the work into batches or retry with Retry.

Related activities

• DB Query — read data with SELECT.
• DB Scalar — read a single value.