Accessing a MySQL database from the VAD

The 3CX Voice Application Designer (VAD) allows access to databases such as the SQL Server, Oracle and ODBC enabled database like MySQL.

Pre-requisites

In order to access a MySQL database through ODBC you need to install the ODBC driver for MySQL. This can be downloaded from the MySQL site.

Once it is installed, you can continue with the creation of the “System DSN”.

Creating the “System DSN”

The web server (IIS or Abyss) that is used with 3CX Phone System will access the database when required by an application created with the VAD. That is why the ODBC entry must be “System DSN”, so it is visible to these services.

To create the “System DSN” entry, go to “Start” > “Administrative Tools” > “Data Sources (ODBC)” and select the “System DSN” tab. Click “Add” and select the “MySQL ODBC 5.1 Driver”. Press “Finish”, and enter the name of the “DataSource” and information to connect to the database.

Creating the application with the VAD

First you must create a new project. Open the VAD and go to “File” > “New” > “Project”, select the folder where you want to store the new project and choose a name. In our case we will be calling it “MySqlTest”.

The application will request us to enter a customer code which will be validated against the MySQL database. In order to do this, we need to add an element of type “User Input” by dragging it from the toolbox, dropping it into the Callflow and rename it to “requestPIN”. Set the message that will be played by the component to request the PIN, using the properties window. We will assume that the customer code has 10 digits. Set the properties MaxDigits and MinDigits to the value of 10.

When the user enters a valid 10 digit value, the execution will continue in the “Valid Input” branch of the component requestPIN. We now need to validate the customer code.

To do that, add a component type “Database Access” by dragging it from the toolbox and dropping it into the “Valid Input” branch mentioned above. Rename the component to “validatePIN” and double click on it to open the component configuration dialog. Fill in the fields as follows:

  • In the “Database Type” field select “ODBC”.
  • In the “DataSource” field enter the name that you used when creating the “System DSN” entry. This field is an expression, so in order to enter a constant value you need to use single quotes.
  • In the “User Name” and “Password” fields enter the user name and password to connect to the database. These fields are expressions, so in order to enter a constant value you need to use single 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 “CustomerPIN” for the name and 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 “CustomerPIN” parameter in the right place. The SQL statement will be:

select count(*) from customers where id={0}

Now that the database access component is properly configured, we must verify the value returned and decide to accept the customer code or not. This requires us to use a “Conditional” component with two branches, one for the case of successful validation and one for the case of validation error.

Add the Conditional component by dragging it from the toolbox, and change its name to “validateDatabaseResult”. Rename the two branches as “success” and “error” in the case of success and failure in the validation respectively. The main flow should look like in the picture below:

In order to execute the “success” branch, the database should return a value of 1 indicating that there is a record with the code entered by the customer, i.e. the following condition must be fulfilled, which should be set for that branch using the Expression Editor:

EQUAL(validatePIN.ScalarResult,1)

Once this condition is configured, add a success and an error audio prompt, and transfer the call to an operator in the case of a successful validation.


Liked this article?


Get notified of new articles
or share
You might also be interested in: