Database Access

This topic provides an overview of the Database Access component, and describes its configuration, variables, as well as supported connections.

Component introduction

Database Access component allows you to interact with database during a call flow. You can execute SQL operations to retrieve or update data as needed. The results are stored in variables, which can be used as input for subsequent components or for condition evaluation.

Supported database types
  • Microsoft SQL
  • PostgreSQL
  • MySQL
  • Oracle
Supported SQL statements
  • Query
  • NonQuery
  • Scalar

Component configuration

  1. After adding Database Access component to a call flow, click Database Access component to proceed with the configuration.

  2. In the Database Type drop-down list, select a database type.

  3. Fill in the following information to connect to a database.

  4. Configure the SQL operation and timeout settings.
    Item Description
    Statement Type Select the type of SQL operation.
    • Query: Execute a SQL statement that returns one or more rows of data (e.g. SELECT).
    • NonQuery: Execute a SQL statement that modifies data but doesn't return any rows (e.g. INSERT, DELETE, UPDATE).
    • Scalar: Execute a SQL statement that returns a single value (e.g. SUM, COUNT).
    Timeout(s) Set the time to wait in seconds before terminating the attempt to execute a SQL statement.
    Timeout Destination Optional. To customize the destination when SQL execution times out, enable this option and add subsequent components to the Default Destination and Timeout Destination branches after completing the component configuration.
    SQL Statement Enter the SQL statement.
    Note:
    • For Microsoft SQL Server and PostgreSQL, the SQL statement must include the schema name.

      For example, if the schema is pbx, you should enter SELECT * FROM pbx.user;.

    • You can use expressions in the SQL statement for dynamic values.

      Example: SELECT email_addr FROM extension WHERE number='$Session.ani';.

      In this example, the system queries the email address corresponding to the caller's number.

      Note: For more information about the expression, see Variables and Functions in Yeastar Expression.
    • If multiple SQL statements are entered, only the first one will be executed.
  5. At the bottom-right corner, click Confirm.

Component variables

When a Database Access component is added to a call flow, the system stores database information in variables. These variables can be referenced in expression-supported components (such as Condition) to retrieve the database information and respond accordingly based on the result.

Note: Since Database Access component can be added multiple times in a call flow, an index is appended to each component (e.g. Database Access 1, Database Access 2) based on the order in which the components are added. To retrieve data from the right component, the component variable must contain the corresponding index.
Variable Type Description Example Value
$DatabaseAccess{index}.queryResult Object

The table returned by a SQL query.

Tip: You can use the variable with the following functions to retrieve data from the result.
  • GET_TABLE_ROW_COUNT(table): Get the total number of rows.
  • GET_TABLE_CELL_VALUE(table,row,column): Get the value at the specified row and column.
 "demo%40yeastar.com"
$DatabaseAccess{index}.nonQueryResult Integer The number of rows affected by a non-query SQL statement. 99
$DatabaseAccess{index}.scalarResult String The single value returned by a scalar SQL query. "10"

Component connections

Database Access component can be connected to one or two components (depending on whether the Timeout Destination option is enabled), which can be any of the components listed below.

Component Description
Prompt

Prompt component allows you to play audio prompt(s) or a text-to-speech message to callers.

For more information, see Prompt.
Business Hours

Business Hours component allows you to route calls to different destinations based on the time of day.

For more information, see Business Hours.
Menu Menu component allows you to present callers with a set of menu options, and route calls based on the DTMF digit they press.

For more information, see Menu.
User Input

User Input component allows you to collect DTMF digits from callers, typically used with Condition component to evaluate user input and route calls.

For more information, see User Input.
Language

Language component allows you to change the system prompt language for subsequent components in a call flow.

For more information, see Language.
Record Record component allows you to start recording a call upon the caller being connected to another participant, and optionally configure prompts to inform call participants at the start and during the recording. Alternatively, you can use the component to disable call recording for calls that are supposed to be recorded.

For more information, see Record.
Dial by Number

Dial by Number component allows callers to directly dial a number to reach the destination.

For more information, see Dial by Number.
Dial by Name

Dial by Name component is one of the end components to terminate caller's connection to the current call flow. It allows callers to reach extension user by entering the first three letters of the user's name.

For more information, see Dial by Name.
Transfer

Transfer component is one of the end components to terminate caller's connection to the current call flow. It allows you to transfer callers to a designated destination, and optionally configure prompts to inform them of the transfer.

For more information, see Transfer.
Hang Up Call

Hang Up Call component is one of the end components to terminate caller's connection to the current call flow. When callers are routed to the component, the call will be disconnected.

For more information, see Hang Up Call.
Condition Condition component allows routing calls based on logical expressions.

For more information, see Condition.
Loop Loop component allows a group of components to be executed repeatedly, either for a specified number of times or until a condition is met.

For more information, see Loop.
Internal Data Ops Internal Data Ops component allows you to query and update data from PBX-native database.
For more information, see the following topics:
Email Sender Email Sender component allows you to send emails, enabling real-time notifications, alerts, or delivery of user-specific information.

For more information, see Email Sender.
Database Access Database Access component allows you to interact with database during a call flow. You can execute SQL operations to retrieve or update data as needed.

For more information, see Database Access.
HTTP Request HTTP Request component allows you to send HTTP requests to external web servers, enabling data exchange with third-party services.

For more information, see HTTP Request.