TryLearn More

Use SIP trunks, WebRTC & Apps

Slash your Phone Bill by 80%

Accessing a database from a CFD voice app

On this topic

Accessing a database from a CFD voice app

Introduction

Step 1: Create the project

Step 2: Ask the PIN from the user

Step 3: Validate the PIN against the database

Step 4: Check the result from the database and decide the call flow path depending on that

Step 5: Build and Deploy to 3CX Phone System

Introduction

In this article we’ll explain how to use the Database Access component to query an SQL Server database from a voice app created with the 3CX Call Flow Designer (formerly VAD). The CFD supports PostgreSQL databases as well, and this article can also be applied to that case, with minimal changes.

The project for this application is installed along with the 3CX Call Flow Designer, in folder “Documents\3CX Call Flow Designer Demos”. You just need to change the database access location and credentials. This application will ask for a PIN from the user, validate the data entered against the database, and transfer the call to a specific extension only when the PIN is valid. Let’s start.

Step 1: Create the project

First, we need to create a new project. Open the CFD and go to File > New > Project,  select the folder where you want to save it, and enter a name for the project. In this case we’ll name it DatabaseAccessDemo.

Step 2: Ask the PIN from the user

We can ask for a PIN using a User Input component. In order to add this component to our application:

  1. Drag a User Input component from the toolbox, and drop it into the design surface of the “Main” callflow. Then select the component added, go to the Properties window and rename it to “requestPIN”.

  1. Double click on the User Input component to open the configuration dialog. Set the following properties:
  • Configure the “Initial Prompts” with a WAV file inviting the user to enter a Personal Identification Number. For example, “Please enter your personal identification number.”.
  • Configure the “Subsequent Prompts”, with a WAV file that gives more detailed information on what the user needs to enter, because this message will only be played when the user entered invalid digits or no digits at all. For example, “Please enter your personal identification number. Remember that only digits between 0 and 9 are valid, and it must have between 3 and 6 digits.”.
  • Configure the “Timeout Prompts”, with a WAV file that explains that no digit was detected. For example, “Sorry, we didn’t receive any digit.”.
  • Configure the “Invalid Digit Prompts” with a WAV file that explains that the digits entered are invalid. For example, “Sorry, your input is not valid.”
  • Configure the other options as shown in the following screenshot.

  1. Press OK to save the changes.

Step 3: Validate the PIN against the database

Now we need to validate the PIN entered by the user against the database. To do this, we need to add a new Database Access component to the “Valid Input” branch of the User Input component, rename it to “validatePIN” and double click on the component to open the configuration dialog. Fill in the fields as follows:

  • For the “Database Type” field select “SqlServer”.
  • Select “Configure each property separately”, so we can enter the server IP address, port, database, username and password. It’s also possible to configure a connection string instead.
  • For the “Server” field enter the server name or IP address. Please note that this field is an expression, so in order to enter a constant value you need to use quotes.
  • For the “Port” field enter the port number where the database is listening for requests, or you can leave it empty to use the default value.
  • For the “User Name” and “Password” fields enter the credentials to connect to the database. These fields are expressions, so in order to enter a constant value you need to use quotes.
  • For the “Statement Type” field we will use the value “Scalar” as we want to get a single value from the database: the quantity of customer records with the entered code.
  • The 30 seconds value for the Timeout can be left or changed if you consider it convenient.
  • Before filling the “SQL Statement” field, we need to add a parameter to the Parameters list: the PIN code entered by the user. This parameter will be used later to build the SQL statement. Enter “id” for the name and the expression “requestPIN.Buffer” for the value, i.e. the digits entered by the user.
  • Now we have to enter the SQL command to retrieve the required information from the database, in the “SQL Statement” field. We’ll use the button on the right of that field to insert the value of the “id” parameter in the right place. The SQL statement will be:

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

Step 4: Check the result from the database and decide the call flow path depending on that

Now that the Database Access component is properly configured, we need to verify the value returned by the database, and decide to accept the customer PIN or not. To do this we need a “Create a Condition” component with two branches, one for the case of successful validation and one for the case of validation error.

Add the Create a Condition component by dragging it from the toolbox to the designer, and change its name to “validateDatabaseResult”. Rename the two branches to “success” and “error”, for the cases of success and failure in the validation respectively. The main flow should look like in the screenshot below.

In order to execute the “success” branch, the database must return the value 1, indicating that there is a record with the PIN entered by the user. That means that the following condition must be fulfilled, and we need to set it for the “success” (this condition can be easily created using the Expression Editor):

EQUAL(validatePIN.ScalarResult,1)

Once this condition is configured for the “success” branch, add Prompt Playback components to all the possible call paths (validation succeeded, validation error, and invalid input when entering the PIN). Finally transfer the call to an operator in the case of a successful validation, using a Transfer component. The call flow will look like in the following screenshot.

Step 5: Build and Deploy to 3CX Phone System

The project is ready. We just need to build and upload it to our 3CX Phone System server. To do this:

  1. Go to Build > Build All. The CFD will create the file “DatabaseAccessDemo.tcxvoiceapp”.
  2. Go to the 3CX Management Console > Call Queues, create a new queue, configure it with name and extension, check the Voice apps option, and upload the file created by the CFD in the previous step.

  1. Save the changes to the queue. The voice app is ready to use. Make a call to the configured extension, enter your PIN and if it’s validated you should be transferred to the operator extension.

Free for up to 1 year! Select preferred deployment:

On-Premise

for Linux on a $200 appliance or as a VM

Get the ISO

On-Premise

for Windows as a VM

Download the setup file

On the cloud

In your Google, Amazon, Azure account

Take the PBX Express