CXone Studio DBConnector: Real-Time Database Lookup Pattern

How-To-Articles
1

CXone Studio DBConnector: Real-Time Database Lookup Pattern

What You Will Build

  • A CXone Studio script that pauses execution to query an external SQL database using the DBConnector action.
  • The script retrieves customer details based on a Caller ID passed from the telephony layer.
  • This tutorial covers the configuration of the database connection object and the JavaScript logic required to map query parameters and handle the asynchronous response within the Studio flow.

Prerequisites

  • CXone Studio Access: You must have the “Studio Developer” or “Studio Admin” role to create and publish scripts.
  • Database Connector Configuration: A pre-configured Database Connection object in CXone. This requires the database URL, username, and password to be entered in the CXone Admin console under Integrations > Database Connectors.
  • SQL Permissions: The database user must have SELECT permissions on the target table.
  • Studio Runtime Environment: Understanding of the CXone Studio JavaScript execution context. Note that Studio scripts run in a sandboxed Node.js-like environment. You cannot use standard fetch or axios libraries. You must use the built-in cxone namespace methods.

Authentication Setup

CXone Studio scripts do not require manual OAuth implementation in the code. The platform injects the authenticated session context automatically. However, the script execution context must be associated with a valid CXone tenant.

When testing locally or in the Studio preview mode, ensure you are logged into the CXone Portal with a user account that has permission to execute scripts. The DBConnector action uses the credentials stored in the Database Connection object you configured in the Admin console, not the user’s personal API token.

Implementation

Step 1: Define the Database Connection and Query Parameters

The first step is to identify the Database Connection ID. In CXone Admin, navigate to Integrations > Database Connectors. Locate your connection and copy the Connection ID (a string like db-conn-12345).

In Studio, you do not write the SQL query directly in the JavaScript code for security reasons. Instead, you define a parameterized query in the DBConnector action configuration and pass variables from your script.

Create a new Studio Script. Add a DBConnector action node.

  1. Select Connection: Choose the Database Connection created in the prerequisites.
  2. Query Type: Select SELECT.
  3. SQL Statement: Enter the parameterized SQL. Use ? as placeholders for parameters.
    SELECT customer_name, account_status, tier_level 
FROM customers 
WHERE phone_number = ?
  4. Parameter Mapping: You will map the ? placeholder to a Studio variable in the next step.

Step 2: Configure the Studio Flow and Variable Mapping

You need a source for the phone number. Typically, this comes from the IVR or the initial call event.

  1. Add a Start node.
  2. Add a DBConnector node. Connect the Start node to the DBConnector node.
  3. In the DBConnector node properties:
    • Connection ID: Paste the ID from Step 1.
    • SQL: SELECT customer_name, account_status, tier_level FROM customers WHERE phone_number = ?
    • Parameters: Click “Add Parameter”.
      • Value: $call.caller.phoneNumber (This is a common CXone context variable for the caller’s ID).
      • Type: STRING.

The DBConnector action returns a result set. By default, CXone Studio stores the result in a variable named dbResult. You must configure the node to handle success and failure paths.

  • On Success: Connect to a subsequent node (e.g., a Speak node or a Logic node).
  • On Failure: Connect to an error handling node (e.g., a Play Audio node stating “We could not retrieve your account information”).

Step 3: Process the Database Result in JavaScript

While simple lookups can be handled entirely in the visual flow, complex logic requires a Script node. This is where you validate the data returned by the DBConnector.

Add a Script node after the successful path of the DBConnector node.

// cxone-studio-script.js

/**
 * CXone Studio Script Node
 * Context: Runs after DBConnector action
 * Input: dbResult (Array of objects)
 */

function main() {
    // 1. Access the result from the DBConnector action
    // The variable name 'dbResult' is the default output variable name 
    // configured in the DBConnector node properties.
    var results = cxone.variables.dbResult;

    // 2. Validate if results exist
    if (!results || results.length === 0) {
        // Set a flag for the flow to handle "No Data Found"
        cxone.variables.customerFound = false;
        cxone.variables.errorMessage = "No customer record found for this number.";
        return;
    }

    // 3. Extract the first record (assuming unique phone number)
    var customer = results[0];

    // 4. Map database columns to Studio variables
    // Note: Column names are case-sensitive as defined in your SQL query
    cxone.variables.customerName = customer.customer_name;
    cxone.variables.accountStatus = customer.account_status;
    cxone.variables.tierLevel = customer.tier_level;
    cxone.variables.customerFound = true;

    // 5. Log for debugging (visible in CXone Logs)
    cxone.log.info("Customer Lookup Success: " + customer.customer_name);
}

Step 4: Handle Edge Cases and Data Types

Database drivers often return data in types that differ from JavaScript expectations. For example, a DATE column might return a string or a timestamp. A BOOLEAN might return 1 or 0.

Update the Script node to handle type coercion explicitly.

function main() {
    var results = cxone.variables.dbResult;

    if (!results || results.length === 0) {
        cxone.variables.customerFound = false;
        return;
    }

    var customer = results[0];

    // Safe extraction with defaults
    cxone.variables.customerName = customer.customer_name || "Unknown";
    
    // Handle potential nulls from DB
    cxone.variables.accountStatus = customer.account_status ? customer.account_status : "Inactive";
    
    // Convert tier to a string if it comes as an integer or blob
    cxone.variables.tierLevel = String(customer.tier_level || "Standard");
    
    cxone.variables.customerFound = true;
}

Complete Working Example

This example demonstrates a complete flow: Receive Call → Lookup Customer → Personalize Greeting → Route to Agent.

Studio Flow Configuration

  1. Start Node:

    • Default outgoing connection to DBConnector.

  2. DBConnector Node (Name: LookupCustomer):

    • Connection ID: db-conn-prod-sql-01
    • SQL: SELECT first_name, vip_status FROM subscribers WHERE mobile = ?
    • Parameter 1: $call.caller.phoneNumber
    • Output Variable Name: lookupResult
    • Success Path: To Script Node.
    • Failure Path: To “Error” Audio Node.

  3. Script Node (Name: ProcessLookup):

    function main() {
    var lookupResult = cxone.variables.lookupResult;

    if (!lookupResult || lookupResult.length === 0) {
        cxone.variables.greetingMessage = "Hello. Thank you for calling.";
        cxone.variables.routeToQueue = "General_Support";
        cxone.variables.isVip = false;
        return;
    }

    var record = lookupResult[0];
    
    // Sanitize input to prevent injection in downstream nodes if used in SQL again
    var firstName = record.first_name || "Guest";
    
    // Determine routing based on VIP status
    // Assume vip_status is 1 for true, 0 for false
    var isVip = (record.vip_status == 1 || record.vip_status === true);
    
    cxone.variables.isVip = isVip;
    cxone.variables.greetingMessage = "Hello " + firstName + ".";
    
    if (isVip) {
        cxone.variables.routeToQueue = "VIP_Support";
    } else {
        cxone.variables.routeToQueue = "General_Support";
    }
}

  4. Speak Node (Name: PersonalGreeting):

    • Text to Speech: cxone.variables.greetingMessage
    • Voice: Standard Female
    • Success Path: To Route Node.

  5. Route Node (Name: AgentRouting):

    • Queue: cxone.variables.routeToQueue
    • Success Path: To End/Transfer.

  6. Audio Node (Name: ErrorHandling):

    • Audio File: system_error.mp3
    • Success Path: To End/Transfer to General Queue.

Common Errors & Debugging

Error: DBConnector Timeout (HTTP 504 or Internal Timeout)

What causes it:
The SQL query takes longer than the configured timeout in the DBConnector action (default is often 5-10 seconds). This happens with unindexed columns or large result sets.

How to fix it:

  1. Index your database: Ensure the column used in the WHERE clause (e.g., phone_number) has a database index.
  2. Limit Results: Add LIMIT 1 to your SQL query if you only expect one match.
  3. Increase Timeout: In the DBConnector node properties, increase the timeout value if supported by your CXone version.

Code/Config Fix:
Change SQL from:

SELECT * FROM customers WHERE phone_number = ?

To:

SELECT first_name, last_name FROM customers WHERE phone_number = ? LIMIT 1

Error: “Column Name Not Found” or undefined in Script

What causes it:
The SQL query uses aliases or the column names in the result object do not match the keys you are accessing in JavaScript. JavaScript object keys are case-sensitive.

How to fix it:

  1. Check the exact casing of the columns in your SQL SELECT statement.
  2. If you use aliases, use the alias in the JavaScript code.

Example:
SQL: SELECT customer_name AS name FROM customers
JavaScript: cxone.variables.lookupResult[0].name (Correct)
JavaScript: cxone.variables.lookupResult[0].customer_name (Incorrect/Undefined)

Error: SQL Injection Vulnerability Warning

What causes it:
Attempting to concatenate user input directly into the SQL string in the DBConnector configuration.

How to fix it:
Never use string interpolation in the DBConnector SQL field. Always use the ? placeholder and map the variable via the Parameter mapping feature.

Incorrect:

SELECT * FROM users WHERE id = "$call.caller.phoneNumber"

Correct:

SELECT * FROM users WHERE id = ?

And map $call.caller.phoneNumber to the parameter in the UI.

Error: Connection Refused (DBConnector Status: Failed)

What causes it:
The credentials stored in the CXone Admin Database Connector object are invalid, or the database server is not reachable from the CXone cloud environment.

How to fix it:

  1. Go to CXone Admin > Integrations > Database Connectors.
  2. Click “Test Connection” on your configured connector.
  3. If it fails, verify:
    • Username/Password are correct.
    • The database server allows inbound connections from CXone IP ranges (check NICE CXone IP whitelist documentation).
    • SSL/TLS requirements are met (some databases require SSL, and CXone may need to trust the CA certificate).

Official References