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 Developer component to a call flow, click Developer component to proceed with the configuration.

  2. In the Component Type drop-down list, select Database Access.

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

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

  5. 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.
    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.
  6. 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 or Developer, 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 component, 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.
Developer Developer component allows you to query and update data from PBX-native database or third-party database.
For more information, see the following topics: