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 message 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

Prerequisites
  • Obtain the basic information of the external database, including server address, port, database name, username and password.
  • Make sure the PBX can access the external database.
Procedure
  1. After adding Database Access component to a message 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.
    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.

      For example, insert SELECT email_addr FROM contant WHERE number='$Session.contactNumber'; to return the email address corresponding to the customer's number.

      For more information about functions and variables, 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.

    The configured information appears on the component.

  6. Optional: To route the message session or trigger other actions when the component's branch conditions are met, hover over the desired branch and click to add the next component.

    For more information about components that support to be connected, see Component connections.

Component variables

When a Database Access component is added to a message 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 message flow, an index is appended to the unique ID of 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 comes with two built-in branches Default Destination and Timeout Destination. Each branch can be connected on one component, which can be any of the components listed below.

Note: If a component is already connected to the branch, and you need to insert a new component between them, hover over the line, click and select the target component. The selected component is inserted between them with connections auto-established on both ends, without the need to delete the connection, add the component and reconnect manually.

End components Transfer and Close are unavailable for this insertion method.

Component Description
Send Message Send Message component enables the system to automatically send messages to customers.

For more information, see Send Message.
Send WhatsApp Template Message Send WhatsApp Template Message component enables the system to automatically send messages to customers using specified WhatsApp message templates.
Important: This component is available only when the message flow is only applicable to WhatsApp channel type.

For more information, see Send WhatsApp Template Message.
Wait for Reply Wait for Reply component allows you to set the reply timeout and route the message session depending on whether a reply is received.

For more information, see Wait for Reply.
Menu Menu component allows you to present the customer with a set of menu options, and route the message session based on the value that they reply.

For more information, see Menu.
Business Hours

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

For more information, see Business Hours.
Wait Wait component enables the system to automatically route the message session to the connected component upon reply timeout.

For more information, see Wait.
Condition Condition component allows routing the message session based on logical expressions.

For more information, see Condition.
Loop Loop component allows a group of components to be executed repeatedly based on the loop strategy.

For more information, see Loop.
Internal Data Ops Internal Data Ops component allows you to query data from PBX-native database.
For more information, see the following topics:
Email Sender Email Sender component enables the system to automatically send emails to a recipient via configured email server and record the send logs, enabling real-time notifications.

For more information, see Email Sender.
Database Access Database Access component allows you to interact with database during a message 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.
Transfer

Transfer component allows you to transfer the message session to a specific destination to terminate the current message flow.

For more information, see Transfer.
Close Close component enables the system to automatically disconnect the message session to terminate the current message flow.

For more information, see Close.