# Database Access

The Database Access component connects a call flow to an external database so you can read or change data while a call is in progress. It runs a SQL (Structured Query Language) operation, saves the result in a variable, and makes that value available to the components that follow it, for example, to look up a caller's record and then branch on what you find.

## What it does

You can point the component at any of these database engines:

- Microsoft SQL
- PostgreSQL
- MySQL
- Oracle

Each execution runs one of three statement kinds:

- **Query**: returns one or more rows (a `SELECT`, for instance).
- **NonQuery**: changes data without returning rows (`INSERT`, `UPDATE`, `DELETE`).
- **Scalar**: returns a single value, such as a `COUNT` or `SUM`.

:::caution
A **NonQuery** statement changes real data in the target database, and those changes cannot be undone from the call flow. Test the statement against a non-production database first, and connect using a database account that has only the permissions this flow needs (for example, read-only access when you run only **Query** or **Scalar** statements). Least-privilege access limits the damage if a statement is wrong or if caller-supplied input reaches the database.
:::

## Configure the component

1. Drag the **Database Access** component into your call flow, then click it to open its settings.

   ![The Database Access block placed on the call flow canvas](/images/pbx/database-component-cfd.png)

2. Choose your database engine from the **Database Type** drop-down list.

   ![Selecting a database engine from the Database Type list](/images/pbx/database-type-cfd.png)

3. Enter the connection details for that database so the component can reach it.

   ![Connection and authentication fields for the target database](/images/pbx/database-authentication-info.png)

4. Set up the SQL operation and how long it may run.

   | Setting | Description |
   |---------|-------------|
   | **Statement Type** | The kind of SQL operation to run: **Query** (returns rows, e.g. `SELECT`), **NonQuery** (modifies data without returning rows, e.g. `INSERT`, `UPDATE`, `DELETE`), or **Scalar** (returns a single value, e.g. `COUNT`, `SUM`). |
   | **Timeout(s)** | How many seconds to wait for the statement to finish before giving up. |
   | **Timeout Destination** | Optional. Turn this on to route the call somewhere specific when the statement times out. After you finish configuring the component, add the follow-on components to its **Default Destination** and **Timeout Destination** branches. |
   | **SQL Statement** | The SQL to execute. |

   :::note
   Keep the following in mind when writing the **SQL Statement**:

   - For **Microsoft SQL Server** and **PostgreSQL**, include the schema name. If the schema is `pbx`, write `SELECT * FROM pbx.user;`.
   - You can embed expressions to insert dynamic values. For example, `SELECT email_addr FROM extension WHERE number='$Session.ani';` looks up the email address tied to the caller's number. See [Variables and Functions in Cloud Voice Expression](/pbx/call-flow-designer-guide/constants-and-variables-in-cloud-voice-cfd-expression/) for the full list of supported variables and functions.
   - If you enter more than one statement, only the first is run.
   :::

5. Click **Confirm** in the bottom-right corner.

## Variables

Every time the component runs, it writes its result into variables. You can read these variables from any expression-aware component, **Condition**, for example, to decide how the call should proceed.

:::note
Because a call flow can contain several Database Access components, each one is numbered in the order you add it (Database Access 1, Database Access 2, and so on). Include the matching index in the variable name so you read the result from the right component.
:::

| Variable | Type | Description | Example |
|----------|------|-------------|---------|
| `$DatabaseAccess{index}.queryResult` | Object | The table returned by a Query statement. | `"demo%40cloudvoice.com"` |
| `$DatabaseAccess{index}.nonQueryResult` | Integer | The number of rows a NonQuery statement affected. | `99` |
| `$DatabaseAccess{index}.scalarResult` | String | The single value a Scalar statement returned. | `"10"` |

:::tip
To pull values out of a query result table, use these functions:

- `GET_TABLE_ROW_COUNT(table)`, the total number of rows.
- `GET_TABLE_CELL_VALUE(table,row,column)`, the value in a specific row and column.
:::

## What it can connect to

A Database Access component leads to **one or two** downstream components, two when **Timeout Destination** is enabled, so you can handle the timeout branch separately. Any of the following can follow it:

| Component | Purpose |
|-----------|---------|
| [Prompt](/pbx/call-flow-designer-guide/prompt/) | Play an audio file or a text-to-speech message to the caller. |
| [Business Hours](/pbx/call-flow-designer-guide/business-hours/) | Route the call to different destinations depending on the time of day. |
| [Menu](/pbx/call-flow-designer-guide/menu/) | Offer callers a set of options and route them by the DTMF (Dual-Tone Multi-Frequency, the tone a phone keypad key sends) key they press. |
| [User Input](/pbx/call-flow-designer-guide/user-input/) | Collect DTMF digits from the caller, usually paired with **Condition** to act on what was entered. |
| [Language](/pbx/call-flow-designer-guide/language/) | Change the system prompt language for the rest of the flow. |
| [Record](/pbx/call-flow-designer-guide/record/) | Start recording once the caller connects to another party, with optional notification prompts, or turn recording off for a call that would otherwise be recorded. |
| [Dial by Number](/pbx/call-flow-designer-guide/dial-by-number/) | Let callers dial a number directly to reach their destination. |
| [Dial by Name](/pbx/call-flow-designer-guide/dial-by-name/) | An end component that lets callers reach a user by typing the first three letters of their name. |
| [Transfer](/pbx/call-flow-designer-guide/transfer/) | An end component that sends the caller to a chosen destination, with optional prompts announcing the transfer. |
| [Hang Up Call](/pbx/call-flow-designer-guide/hang-up-call/) | An end component that disconnects the call. |
| [Condition](/pbx/call-flow-designer-guide/condition/) | Route the call based on logical expressions. |
| [Loop](/pbx/call-flow-designer-guide/loop/) | Repeat a group of components a set number of times or until a condition is met. |
| Internal Data Ops | Read from and write to the Cloud Voice system's own database. See [Get Extension Presence Status](/pbx/call-flow-designer-guide/get-extension-presence-status/), [Set Extension Presence Status](/pbx/call-flow-designer-guide/set-extension-presence-status/), [Get Queue Agent](/pbx/call-flow-designer-guide/get-queue-agent/), [Get Queue Info](/pbx/call-flow-designer-guide/get-queue-info/), [Get Agent Status](/pbx/call-flow-designer-guide/get-agent-status/), [Set Agent Status](/pbx/call-flow-designer-guide/set-agent-status/), [Get System Info](/pbx/call-flow-designer-guide/get-system-info/), [Get System Capacity](/pbx/call-flow-designer-guide/get-system-capacity/), [Get Extension Info](/pbx/call-flow-designer-guide/get-extension-info/), and [Get Company Contact Info](/pbx/call-flow-designer-guide/get-company-contact-info/). |
| [Email Sender](/pbx/call-flow-designer-guide/email-sender/) | Send an email for real-time notifications, alerts, or user-specific information. |
| [Database Access](/pbx/call-flow-designer-guide/database-access/) | Run another SQL operation against a database during the call flow. |
| [HTTP Request](/pbx/call-flow-designer-guide/http-request/) | Send an HTTP request to an external server to exchange data with a third-party service. |