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.

   

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

   

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

   

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 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	Play an audio file or a text-to-speech message to the caller.
Business Hours	Route the call to different destinations depending on the time of day.
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	Collect DTMF digits from the caller, usually paired with Condition to act on what was entered.
Language	Change the system prompt language for the rest of the flow.
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	Let callers dial a number directly to reach their destination.
Dial by Name	An end component that lets callers reach a user by typing the first three letters of their name.
Transfer	An end component that sends the caller to a chosen destination, with optional prompts announcing the transfer.
Hang Up Call	An end component that disconnects the call.
Condition	Route the call based on logical expressions.
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, Set Extension Presence Status, Get Queue Agent, Get Queue Info, Get Agent Status, Set Agent Status, Get System Info, Get System Capacity, Get Extension Info, and Get Company Contact Info.
Email Sender	Send an email for real-time notifications, alerts, or user-specific information.
Database Access	Run another SQL operation against a database during the call flow.
HTTP Request	Send an HTTP request to an external server to exchange data with a third-party service.