3CX Call Flow Designer (CFD): Implementing Custom Database Queries for Dynamic Call Routing

Overview

Key takeaways

• Connect 3CX CFD applications to PostgreSQL, MySQL, MS SQL, or ODBC database sources.
• Execute parameterized SQL queries using caller properties like session.ani to retrieve routing targets.
• Parse scalar and multi-column query recordsets safely using CFD variables and C# expressions.
• Implement comprehensive error handling to guarantee caller fallback during database outages.
• Optimize database connection pooling and indexing to maintain sub-second call setup latencies.

Prerequisites

• 3CX Phone System Enterprise Edition (v18 or v20).
• 3CX Call Flow Designer installed on a Windows workstation.
• Accessible relational database (MS SQL, PostgreSQL, MySQL/MariaDB) with read access.
• Basic knowledge of SQL queries and C# programming constructs.

Guide

1. section #1

   Architectural Overview of 3CX CFD and Database Access

   CFD Application Runtime Architecture

   The 3CX Call Flow Designer compiles graphical call flow diagrams into .NET C# code executed directly by the 3CX Call Flow service on the PBX host. When an incoming call enters a CFD application, the engine executes routing logic, network calls, and database transactions sequentially before instructing the 3CX PBX core where to transfer the call.

   Database Interaction Mechanics

   Database access in CFD relies on standard ADO.NET providers or 64-bit ODBC drivers installed on the PBX host. The engine opens pooled connections, sends parameterized queries using real-time call metrics, and binds returned recordsets into internal application variables.

   Key CFD Components for Routing

   • Database Interaction Component: Executes SELECT, INSERT, or UPDATE statements and returns results.
   • Variable Assigner: Stores single values or extracted dataset properties into session-scoped variables.
   • Conditional Component: Evaluates database variables to direct the call down specific branches.
2. section #2

   Configuring Database Connection Strings and Parameterized Queries

   Connection String Configuration

   Connecting a CFD app to an external database requires a standard ADO.NET connection string. You must set explicit timeout limits to prevent database connection delays from stalling the PBX media engine during network outages.

   Parameterized SQL Queries

   To prevent SQL Injection and formatting bugs caused by unexpected caller ID characters, always use parameterized queries rather than string concatenation. CFD parameter mapping binds native call variables—such as session.ani (Caller ID)—directly to SQL execution parameters.

   Database Driver Considerations

   • MS SQL Server: Built natively into .NET; requires no external host drivers.
   • PostgreSQL / MySQL: Requires installing official 64-bit ADO.NET data providers or 64-bit ODBC drivers on the 3CX server.
   sqlcopy

   SELECT assigned_agent_extension, customer_tier 
   FROM customers 
   WHERE phone_number = @CallerID 
     AND account_status = 'ACTIVE';

3. section #3

   Executing Query Logic and Mapping Output Variables

   Selecting Execution Types

   The Database Interaction component supports two primary execution modes: Execute Scalar (returns a single value from the first row/column) and Execute NonQuery / Execute Reader (returns complete DataSets). For call routing, returning a DataSet provides access to multiple fields in a single query round-trip.

   Mapping Query Datasets to CFD Variables

   Returned columns are stored in component variables, such as DatabaseAccessComponent1.DataSet. You can map specific fields to local or global variables using zero-based indexing syntax: DatabaseAccessComponent1.DataSet.Tables[0].Rows[0]["assigned_agent_extension"].

   Safe Variable Initialization

   • Initialize call destination variables with a standard default extension (e.g., Main Queue 800) before running the query.
   • Ensure downstream components check variable population before triggering transfers.
4. section #4

   Parsing Query Results with Custom C# Scripting

   Advanced Result Processing

   When routing logic involves complex business rules—such as fallback logic based on account age or priority scores—you can process the returned DataSet using an Execute C# Code component.

   Null and Empty Recordset Handling

   Unrecognized caller IDs will yield empty recordsets. Your code must check that the DataSet is non-null and contains at least one row before accessing column values; failure to do so will cause an unhandled NullReferenceException.

   C# Decision Logic Example

   • Verify record existence using Rows.Count > 0.
   • Read data fields into typed C# variables.
   • Evaluate customer tier and return the target extension string.
   csharpcopy

   if (DatabaseAccessComponent1.DataSet != null && 
       DatabaseAccessComponent1.DataSet.Tables[0].Rows.Count > 0)
   {
       DataRow row = DatabaseAccessComponent1.DataSet.Tables[0].Rows[0];
       string tier = row["customer_tier"].ToString();
       string extension = row["assigned_agent_extension"].ToString();
       
       if (tier == "VIP" && !string.IsNullOrEmpty(extension))
       {
           return extension; // Route directly to dedicated account manager
       }
   }
   
   return "800"; // Fallback to main sales queue

5. section #5

   Implementing Fault Tolerance and Exception Fallback Routing

   Preventing Call Drops During Database Outages

   If the database host is offline, unresponsive, or rejects credentials, the CFD engine encounters an exception. Without explicit exception handling, 3CX will terminate the call or play a generic error prompt.

   Using Try-Catch and Error Handler Components

   Wrap database activities within a CFD Error Handler component or an explicit C# try-catch block. Catching exceptions guarantees that the caller is gracefully redirected to a backup call destination.

   Resilience Best Practices

   • Set aggressive connection timeouts (e.g., 3 seconds) so callers are not stranded in silence.
   • Hardcode reliable fallback destinations (e.g., IVR or Receptionist) in default variable definitions.
   • Write exception logs to local system files for operational auditing.
6. section #6

   Security Practices and Performance Optimization

   Database Connection Pooling

   Establishing new TCP and TLS database sessions per call adds unnecessary setup latency. Enable Connection Pooling in your ADO.NET connection string (e.g., Pooling=true;Min Pool Size=5;Max Pool Size=50;) to reuse active connection sockets across calls.

   Least Privilege Security Controls

   Do not use high-privilege credentials (such as sa or postgres) in CFD applications. Create dedicated, read-only database accounts restricted to executing specific queries or stored procedures.

   Query Optimization Techniques

   • Index Lookup Keys: Ensure database columns mapped to session.ani (e.g., phone_number) are indexed.
   • Use Stored Procedures: Encapsulate complex SQL in database stored procedures to reduce parsing overhead and network payload sizes.
   • Read Isolation: Use NOLOCK hints (MS SQL) or read-uncommitted isolation levels where appropriate to prevent routing queries from blocking transactional databases.

Further reading

• 3CX Call Flow Designer Official Documentation
• ADO.NET Connection String Syntax Reference
• 3CX CFD Database Access Component Manual