Introduction
In the articles Creating a phone support portal with the VAD - Part 1, Part 2 and Part 3, we have shown how to create an application to validate whether a customer has a support contract, and then transfer the call to the appropriate department.
In the first part we have shown how to create the application callflow, while in the second part we have shown how to validate the information using a web service, and in the third part how to perform the validation using XML and CSV text files.
In this article we will show how to perform the validation using the information available in databases and Microsoft Excel files:
- Microsoft Excel Spreadsheet
- SQL Server Database
- Microsoft Access Database File
- Another database via ODBC
SQL Server and ODBC databases
The VAD offers out of the box integration with databases using the "Database Access" component. This component may be configured to directly access an SQL Server database, or any other data source that can be accessed via ODBC. For example, we can access a MySQL or Postgres database via ODBC, or also a Microsoft Access database file or a Microsoft Excel Spreadsheet file, because Microsoft provides ODBC drivers for them.
So, in this example we'll use the "Database Access" component to access all the data sources mentioned at the beginning of the article, simply updating the ODBC "data source".
Validating the customer using an SQL Server database
In first place we'll validate the customer by querying an SQL Server database. To do this, drag a "Database Access" component from the toolbox and drop it into the design surface. Then rename it to "validateCustomer". Supposing that we have a "Customers" table with "id" and "pin" fields, the component should be configured as follows:
Please note that the statement type is "Scalar" which means that the query will return a single value. In this case it will return the quantity of records with the specified id and pin. So, if the value returned by the query is 0, that means that the customer could not be validated, while if the value returned is 1 then the validation was successful.
Now we need to assign the validation result to the output property "ValidationResult". To do that we'll use a "Variable Assignment" component to assign the value resulting from the following expression to the variable "callflow$.ValidationResult":
GREAT_THAN(validateCustomer.ScalarResult,0)
The resulting diagram will look like this
Validating the customer using ODBC
The following article may be useful to create an application that accesses data using ODBC: https://www.3cx.com/blog/docs/database-vad-3cx/
Validation using a Microsoft Excel Spreadsheet file
First we will show how to perform the validation using a Microsoft Excel file.
IMPORTANT: Microsoft only provides ODBC drivers for 32 bits Office applications. If your 3CX Phone System is running on a 32 bits operating system, you will be able to query the Microsoft Excel file without problems. But if it is running on a 64 bits operating system, then we do not recommend using this method. It is preferable to export the data to a CSV text file and access it as we have shown in the previous article of this series. While it is possible to access the 32 bits ODBC driver from a 64 bits operating system, in order to properly execute the VAD application in that scenario, a web server (IIS or Abyss) reconfiguration is required, to make it run as a 32 bits application, and that is not supported by 3CX.
The file format used in this example is shown in the following figure:
In order to create the "System DSN" to access the Microsoft Excel Spreadsheet file, installing additional drivers may be required. Depending on the Office version used, the required drivers may be downloaded from here:
- For other Office versions just search the Internet for ODBC drivers for the specific Office version you need.
To create the “System DSN” entry, go to Start -> Administrative Tools -> Data Sources (ODBC) and select the “System DSN” tab. Click on the “Add” button, and select the driver “Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)”. Then press “Finalize”, enter the name for the “DataSource” and select the Microsoft Excel file to use.
Note: if your 3CX Phone System is running on a 64 bits operating system, then you will have to open the 32 bits version of the ODBC control panel, which can be accessed from "%WINDIR%\sysWOW64\odbcad32.exe". ALTHOUGH WE DO NOT RECOMMEND USING THESE DRIVERS IN 64-BIT OPERATING SYSTEMS.
Once created the System DSN, we can proceed to create the component that performs the validation. Drag a "Database Access" component from the toolbox and drop it into the design surface. Rename it to "validateCustomer". Assuming that the file format is the one shown above (spreadsheet with name "CustomersSheet" and columns with name "id" and "pin"), the SQL statement to use will be:
SELECT count(*) FROM [CustomersSheet$] WHERE id={0} AND pin={1}
So the component will be configured as follows:
From here the validation component is completed in the same way as we did when accessing an SQL Server database, i.e. we have to get the result of the query and set the validation result depending on that.
Validation using a Microsoft Access database
In order to query a Microsoft Access database, the procedure is almost identical to the validation using a Microsoft Excel file. The only difference is when creating the “System DSN” entry, because here you have to select the driver “Microsoft Access Driver (*.mdb)”, as shown in the following figure.
This driver has some additional configuration options in the last page of the wizard, where you can select the database file, among other parameters.
Then the SQL statement to use will be similar to the one we used when querying the SQL Server database, i.e. we'll query the table and the fields that contain the information we need.
Validation using and arbitrary ODBC driver
From the point of view of creating the VAD application, using any ODBC driver is the same. The difference lies in the creation of the corresponding "System DSN". For this reason we will not delve into details, as the examples of the preceding paragraphs and this article described in sufficient detail this process.
Conclusion
In this article we have shown how to validate the data entered by the customer using different databases. This way we complete this series of articles describing the creation of a telephone support portal.
Phone System 
