snezhko July 31, 2017 at 09:31

Working with databases in the 3CX Call Flow Designer development environment

Tutorial
Recovery mode

Introduction

In this article, we’ll show you how to query SQL Server database from 3CX Call Flow Designer using the Database Access component. Note that the Database Access component can also work with the PostgreSQL database that the 3CX Phone System uses.

The demo project of this voice application is supplied with the 3CX CFD distribution kit and is located in the Documents \ 3CX Call Flow Designer Demos folder . If you want to use it, just specify the location of your database and access credentials.

Our voice application will request the user's PIN (this can be the number of the client, insurance policy, etc.), check it in the database and transfer it to the specified extension number (it can be a service employee, a pre-prepared voice menu or another voice application ) if the PIN is found.

Project creation

To create a CFD project, go to File → New → Project , specify the project location folder and its name, for example, DatabaseAccessDemo .

PIN request from user

The user PIN is requested using the User Input component. Add the component to the voice application:

Drag the User Input component from the component set on the left to the main application screen. Select a component and in the Properties window rename it to requestPIN .
Double-click on the component to open the configuration window and set the following properties:
- Initial Prompts - specify the WAV file in which the user will be asked for a PIN, for example, “Enter your personal identification number”. WAV file format: PCM, 8 kHz, 16 bit, Mono.
- Subsequent Prompts - specify a WAV file with more detailed input explanations. It is played only if the user has entered the wrong input or entered nothing at all. For example, “Enter your personal identification number. Numbers 0 through 9 are allowed and lengths are between 3 and 6 digits. ”
- Timeout Prompts - indicate the WAV file with a warning that nothing was entered, for example, “Unfortunately, we did not receive input from you”.
- Invalid Digit Prompts - specify a WAV file with a warning about incorrect (too few digits or they are invalid), for example, "Unfortunately, you entered the PIN incorrectly."
Configure other options as shown below and click OK to save.

Checking the entered PIN in the database

After receiving the PIN from the user, it should be checked in the database. To do this, add the Database Access component to the Valid Input component branch , which assumes the correct input. Rename the component to validatePIN and double-click on it to set the properties:

Database Type - select SqlServer .
Configure each property separately - select this option to specify connection parameters individually. However, you can specify the connection string to the database .
Server - the name or IP address of the database server. This field can also be an expression, so if you specify a constant, quote it .
Port - SQL server port. When using a standard port number, this field can be left blank.
User Name and Password are database connection credentials. Values can also be variables.
Statement Type - specify Scalar , because you need to get only one value from the database - the number of user records with this PIN.
Timeout - leave the default 30 seconds. or change if necessary.
SQL Statement - this field indicates the query string to the database. But first, add the parameter to the Parameters section, which is used in the query string. The parameter is the PIN entered by the user.
id is the name of the variable, requestPIN.Buffer is the value, i.e. input buffer in the User Input component (which we previously called requestPIN )

Now enter the query string to the database. Use the small button to the right of the input window to insert the value of the id variable . Our line has the form:

SELECT count (*) FROM customers WHERE id = {0}

The properties window of the Database Access component should look something like this:

Checking the result of the SQL query and choosing further actions

Having configured the Database Access component , we will proceed to the verification of the PIN. To do this, add the Create a Condition component with two conditions (branches) - successful and unsuccessful validation.

Drag the component from the left side and rename it to validateDatabaseResult . Rename the branches to success and error . The development environment window should look something like this:

For success to branch , the database query should return 1, which means that a client with this PIN code was found (one record was found). To do this, manually or using the expression constructor, we introduce the conditional operator:

EQUAL (validatePIN.ScalarResult, 1)

After you have specified the conditions for the success branch , add the Prompt Playback components for all possible branches of the voice application: the PIN is found, the PIN is not found and the PIN is entered incorrectly the number of times (in our User Input Max Retry Count = 3 component ). In the component settings, specify the corresponding WAV notification file.

After that, add the Transfer component to the success branch , which will transfer the call to the employee. The voice application will look something like this:

Compiling and installing the application on the 3CX server

The voice application is ready! Now it should be compiled and downloaded to the 3CX server. For this:

Go to Build> Build All , and CFD will create the DatabaseAccessDemo.tcxvoiceapp file .
Go to the 3CX management interface, under Call Queues . Create a new Call Queue, specify the name and extension of the Queue, and then set the Voice applications option and download the compiled file .
Save the changes to Call Queues. The voice application is ready to use.

You can check the operation of the application by calling the extension number of the Queue and entering the PIN for verification. If it is found in the database, the call will be transferred to the agent.

Downloads and documentation

Tags: