DB Connect

DB Connect

Type: db-connect  ·  Package: Database Activities v1.0.3  ·  Output: ZoanDbSession

Opens a connection to a database and returns a session (ZoanDbSession) shared by the other database activities. It is the starting point of any work with SQL.

How it works

Creates the connection with the driver for the engine set in provider and opens it with the connectionString. You have two modes (see the session model):

• With body: the activities inside inherit the connection automatically (you don’t set session), and the connection closes by itself when the body ends, even if an inner activity fails. In this mode the activity returns no output.
• Without body: store the session in an output variable, pass it as session to the other activities and close it with DB Close.

Good to know:

• Engines and drivers: sqlserver uses Microsoft.Data.SqlClient, postgres uses Npgsql, mysql uses MySqlConnector (it also works with MariaDB) and odbc uses the ODBC driver installed on the robot’s machine. In expressions the aliases mssql, sql server, postgresql, pg and mariadb are accepted too, case-insensitively.
• Timeouts: the activity sets no timeouts of its own; the driver’s apply. If you need different ones, set them in the connection string with that driver’s keywords.
• Transactions: the connection works in autocommit mode: each statement is committed as it runs. To make several statements apply all or nothing, send them together in a single DB Execute using the engine’s transaction syntax, or use a stored procedure.
• Nesting: if you open another DB Connect with body inside a body, the inner activities use the closest connection; the previous one is restored on exit.

Required parameters

Parameter	Type	Default	Description
provider	string	—	Database engine: sqlserver · postgres · mysql · odbc.
connectionString	string	—	ADO.NET connection string for the chosen driver (server, database, user, password and options). Supports expressions, for example credential("db-sales")["value"] (string stored as a single-value credential) or built from fields: "Server=db01;Database=sales;User Id=" + credential("db")["user"] + ";Password=" + credential("db")["password"]. Passing credential("x") without ["…"] does not work: the string would arrive as [credential].

Optional parameters

None.

Sub-flows

body optional

Activities to run with this connection. They inherit the session without setting session, and the connection closes on exit. If omitted (or left empty), the activity returns the session as output and you must close it with DB Close.

Output

• Without body: a ZoanDbSession with the open connection. Pass it as session to DB Query, DB Execute, DB Scalar and DB Close.
• With body: this activity does not return a value; the session only exists inside the body.

Example

DB Connect   provider = sqlserver   connectionString = credential("db-sales")["value"]
 └─ body:
      DB Query   sql = "SELECT TOP 10 Id, Name FROM Customers ORDER BY CreatedAt DESC"   → output: customers
      Log        message = "Customers read: " + rowCount(customers)

With the session in a variable:

DB Connect   provider = postgres   connectionString = credential("db-reports")["value"]   → output: db
DB Scalar    session = db   sql = "SELECT COUNT(*) FROM invoices WHERE paid = false"   → output: pending
DB Close     session = db

Protect the connection string

The connectionString contains the database password. Store it in a credential and use it with credential("db-sales")["value"], never in plain text. See connection string.

Common errors

Message	Cause and fix
Unsupported provider '…'. Use: sqlserver, postgres, mysql, odbc.	The provider value is not a known engine. Pick one from the list or a valid alias.
'provider' is required. / 'connectionString' is required.	The parameter is missing or the expression returned nothing (for example, a credential that does not exist).
Blocked expression: contains a disallowed pattern (credential extraction in expressions is not allowed — use credential() in a param of type 'credential': '.ToPlainText(')…	The expression uses .ToPlainText(), which is blocked in expressions. Read the value with credential("x")["value"] or the matching field (["password"]).
Driver error: login failed, server unreachable, database does not exist, keyword not supported	The message comes from the engine or the driver. Check server, port, user, password, firewall and the connection string syntax for that driver. If the network is unstable, wrap the connection in a Retry.

Related activities

• DB Query / DB Execute / DB Scalar — work with the session.
• DB Close — close the connection (mode without body).
• Try / Catch — handle connection errors.