Accessing a Database from a Call Flow

Introduction

This guide describes how to use the “Database Access” component to query MS SQL Server, PostgreSQL and MySQL databases from a Call Flow created with the 3CX Call Flow Designer.

💡 Tip: The project for this example application is available via the CFD Demos GitHub page, and is installed along with the 3CX Call Flow Designer in your Windows user documents folder, i.e. “C:\Users\YourUsername\Documents\3CX Call Flow Designer Demos”.

To customize this example project you just need to change the database access settings and credentials. This application asks for a PIN from the user, validates the data entered against the database, and transfers the call to a specific extension only when the PIN is valid.

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, e.g. “DatabaseAccessDemo”.

Step 2: Ask the User for the PIN

We can ask for a PIN by adding a “User Input” 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”.

User Input component configuration in 3CX CFD

  1. Double-click on the “User Input” component to configure these properties:
  • “Initial Prompts” - configure with a WAV file inviting the user to enter a Personal Identification Number, e.g. “Please enter your personal identification number.”.
  • “Subsequent Prompts” - set a WAV file that gives more detailed information on what the user needs to enter, as this message is only played when the user entered invalid or no digits. E.g. “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.”.
  • “Timeout Prompts” - set a WAV file that explains that no digit was detected, e.g. “Sorry, we didn’t receive any digit.”.
  • “Invalid Digit Prompts” - use a WAV file that explains that the digits entered are invalid, e.g. “Sorry, your input is not valid.”.
  • Configure the remaining options as in the above example.
  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 configure:

Validate the PIN Against the Database

  • 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 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 to enter a constant value you need to use quotes.
  • For the “Statement Type” field we 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.
  • For the “SQL Statement” field, we will build an expression using the Expression Editor, concatenating static text with the variable requestPIN.Buffer which contains the PIN entered by the user. The resulting expression will be:

CONCATENATE("SELECT count(*) FROM customers WHERE id=",requestPIN.Buffer)

Step 4: Check Database Result and Set Call Flow Path

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.

requestPIN example call flow in 3CX CFD

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. Consider the example call flow above.

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), e.g.:

EQUAL(validatePIN.ScalarResult,1)

requestPIN example call flow in 3CX CFD

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. See example call flow above.

Step 5: Build and Deploy to 3CX Phone System

The project is ready to build and upload to our 3CX Phone System server, with these steps:

  1. Select “Build” > “Build All” and the CFD generates the file “DatabaseAccessDemo.zip”.
  2. Go to the “3CX Management Console” > “Advanced” > “Call Flow Apps” > “Add/Update”, and upload the ZIP file generated by the CFD in the previous step.
  3. The Call Flow app is ready to use. Make a call to the application, enter your PIN to validate and transfer your call to the operator extension.

See Also

Last Updated

This document was last updated on 29th April 2021

https://www.3cx.com/docs/cfd-accessing-database/