Implementing Dynamic Data Lookups in NICE CXone Studio Using DBConnector

How-To-Articles
1

Implementing Dynamic Data Lookups in NICE CXone Studio Using DBConnector

What You Will Build

  • You will build a NICE CXone Studio script that queries an external SQL database to retrieve customer information based on a caller ID.
  • This tutorial uses the NICE CXone Studio DBConnector action and the NICE CXone Integration API for database configuration.
  • The programming languages covered are JavaScript (for the Studio Script logic) and Python (for the API setup and testing).

Prerequisites

  • NICE CXone Admin Access: You must have administrative rights to configure Database Connectors and manage Studio scripts.
  • SQL Database: A reachable SQL Server, MySQL, or PostgreSQL instance with a table containing customer data.
  • OAuth Client: A NICE CXone OAuth client with the following scopes:
    • databaseconnector:read
    • databaseconnector:write
    • integrations:read
    • integrations:write
  • Python Environment: Python 3.8+ with requests and nice-cxone-sdk installed.
  • Studio Access: Access to the NICE CXone Studio designer to create and deploy scripts.

Authentication Setup

Before writing the Studio script, you must configure the database connection via the API. Studio scripts cannot authenticate directly to the external database; they rely on a pre-configured Database Connector resource in NICE CXone.

The following Python script creates a Database Connector using the NICE CXone SDK. This connector stores the database credentials securely within the NICE CXone platform.

import os
from nice_cxone_sdk import Configuration, ApiClient, DatabaseConnectorApi
from nice_cxone_sdk.models import DatabaseConnectorEntity, DatabaseConnectorConnectionInfo

def setup_db_connector():
    # 1. Initialize the SDK Configuration
    configuration = Configuration()
    configuration.host = "api.nice.incontact.com"
    configuration.access_token = os.getenv("CXONE_ACCESS_TOKEN")
    
    # 2. Create the API Client
    api_client = ApiClient(configuration)
    database_api = DatabaseConnectorApi(api_client)

    # 3. Define Connection Details
    # Replace these with your actual database credentials
    connection_info = DatabaseConnectorConnectionInfo(
        connection_string="Server=mydbserver.database.windows.net;Database=CXoneDB;User Id=admin;Password=MySecurePassword123;",
        connection_type="SQLSERVER"
    )

    # 4. Define the Connector Entity
    connector_entity = DatabaseConnectorEntity(
        name="CustomerDB_Lookup",
        description="Connector for customer profile lookups",
        connection_info=connection_info,
        is_active=True
    )

    try:
        # 5. Create the Connector
        response = database_api.post_integration_databaseconnectors(body=connector_entity)
        print(f"Connector created successfully. ID: {response.id}")
        print(f"Connector Name: {response.name}")
        return response.id

    except Exception as e:
        print(f"Error creating connector: {e}")
        raise e

if __name__ == "__main__":
    # Ensure CXONE_ACCESS_TOKEN is set in environment variables
    if not os.getenv("CXONE_ACCESS_TOKEN"):
        raise ValueError("CXONE_ACCESS_TOKEN environment variable is not set.")
    
    connector_id = setup_db_connector()
    print(f"Use this ID in your Studio Script: {connector_id}")

OAuth Scope Required: databaseconnector:write

Expected Response:

{
  "id": "a1b2c3d4-5678-90ab-cdef-1234567890ab",
  "name": "CustomerDB_Lookup",
  "description": "Connector for customer profile lookups",
  "connectionInfo": {
    "connectionType": "SQLSERVER"
  },
  "isActive": true
}

Error Handling:

  • 401 Unauthorized: The access token is expired or invalid. Regenerate the token.
  • 403 Forbidden: The OAuth client lacks the databaseconnector:write scope.
  • 400 Bad Request: The connection string is malformed. Verify the syntax for your specific database type.

Implementation

Step 1: Designing the SQL Query

The DBConnector action in Studio executes a SQL query against the configured database. The query must be parameterized to prevent SQL injection and to allow dynamic input from the call flow.

Create a table in your database with the following structure for this tutorial:

CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    FirstName NVARCHAR(50),
    LastName NVARCHAR(50),
    PhoneNumber NVARCHAR(20),
    LoyaltyTier NVARCHAR(20)
);

INSERT INTO Customers (CustomerID, FirstName, LastName, PhoneNumber, LoyaltyTier)
VALUES (12345, 'John', 'Doe', '5551234567', 'Gold');

The SQL query you will use in Studio is:

SELECT FirstName, LastName, LoyaltyTier FROM Customers WHERE PhoneNumber = ?

Note: The ? placeholder is replaced by a Studio variable at runtime. Do not use string concatenation in the query field.

Step 2: Configuring the DBConnector Action in Studio

Navigate to the NICE CXone Studio designer. Create a new script or open an existing one.

  1. Add a DBConnector action to the canvas.
  2. Select the Database Connector you created in Step 1 (CustomerDB_Lookup).
  3. In the Query field, paste the SQL query from Step 1.
  4. Map the input parameter:
    • Click the ? parameter in the query editor.
    • Select the variable that holds the caller’s phone number (e.g., {{System.Caller.PhoneNumber}} or a custom variable {{Caller.Phone}}).
  5. Define the output variables:
    • Add a new variable for each column returned by the SELECT statement.
    • FirstName: String
    • LastName: String
    • LoyaltyTier: String
  6. Map the query results to these variables. Studio automatically detects the column names if you use a SELECT statement with distinct column names.

Studio Configuration Example:

  • Connector: CustomerDB_Lookup
  • Query: SELECT FirstName, LastName, LoyaltyTier FROM Customers WHERE PhoneNumber = {{Caller.Phone}}
  • Input Parameters: {{Caller.Phone}}
  • Output Variables: {{Output.FirstName}}, {{Output.LastName}}, {{Output.LoyaltyTier}}

Step 3: Handling Results and Errors

The DBConnector action has two output branches: Success and Failure.

Success Branch:

  • The variables {{Output.FirstName}}, {{Output.LastName}}, and {{Output.LoyaltyTier}} are populated.
  • Proceed to a Speak or Play action to greet the customer by name.
  • Use the {{Output.LoyaltyTier}} variable to route the call to a specific queue (e.g., Gold agents for Gold customers).

Failure Branch:

  • The query returned no results, or a database error occurred.
  • Check the {{DBConnector.Error.Message}} variable for details.
  • Route to a default queue or prompt the agent to manually look up the customer.

Studio Script Logic Flow:

  1. Start
  2. Gather Input: Get Caller’s Phone Number (if not already available).
  3. DBConnector: Execute lookup.
  4. If Success:
    • Speak: “Hello, {{Output.FirstName}} {{Output.LastName}}.”
    • If {{Output.LoyaltyTier}} equals “Gold”:
      • Route to: Gold Support Queue
    • Else:
      • Route to: Standard Support Queue
  5. If Failure:
    • Speak: “We are unable to retrieve your profile.”
    • Route to: Standard Support Queue

Complete Working Example

The following is a complete Studio Script definition in JSON format. This JSON can be imported into NICE CXone Studio via the API or manually configured. This example assumes the caller’s phone number is available in {{System.Caller.PhoneNumber}}.

{
  "name": "CustomerDBLookupScript",
  "version": 1,
  "status": "ACTIVE",
  "description": "Looks up customer details from SQL DB and routes based on loyalty tier.",
  "elements": [
    {
      "id": "start",
      "type": "START",
      "next": "db_lookup"
    },
    {
      "id": "db_lookup",
      "type": "DBCONNECTOR",
      "connectorId": "a1b2c3d4-5678-90ab-cdef-1234567890ab",
      "query": "SELECT FirstName, LastName, LoyaltyTier FROM Customers WHERE PhoneNumber = {{System.Caller.PhoneNumber}}",
      "outputVariables": [
        {
          "name": "FirstName",
          "type": "STRING"
        },
        {
          "name": "LastName",
          "type": "STRING"
        },
        {
          "name": "LoyaltyTier",
          "type": "STRING"
        }
      ],
      "success": "check_tier",
      "failure": "handle_error"
    },
    {
      "id": "check_tier",
      "type": "CONDITION",
      "conditions": [
        {
          "expression": "{{Output.LoyaltyTier}} == 'Gold'",
          "next": "route_gold"
        },
        {
          "expression": "true",
          "next": "route_standard"
        }
      ]
    },
    {
      "id": "route_gold",
      "type": "ROUTE",
      "queueId": "gold-support-queue-id",
      "next": "end"
    },
    {
      "id": "route_standard",
      "type": "ROUTE",
      "queueId": "standard-support-queue-id",
      "next": "end"
    },
    {
      "id": "handle_error",
      "type": "SPEAK",
      "text": "We are experiencing technical difficulties retrieving your profile. Please hold while we connect you to an agent.",
      "next": "route_standard"
    },
    {
      "id": "end",
      "type": "END"
    }
  ]
}

Python Script to Deploy the Studio Script:

import os
from nice_cxone_sdk import Configuration, ApiClient, StudioApi
from nice_cxone_sdk.models import Script

def deploy_studio_script():
    configuration = Configuration()
    configuration.host = "api.nice.incontact.com"
    configuration.access_token = os.getenv("CXONE_ACCESS_TOKEN")
    
    api_client = ApiClient(configuration)
    studio_api = StudioApi(api_client)

    # Load the JSON script definition
    with open('customer_db_lookup_script.json', 'r') as f:
        script_data = f.read()
    
    script_entity = Script.parse_raw(script_data)

    try:
        response = studio_api.post_studio_scripts(body=script_entity)
        print(f"Script deployed successfully. ID: {response.id}")
        print(f"Script Name: {response.name}")
        return response.id
    except Exception as e:
        print(f"Error deploying script: {e}")
        raise e

if __name__ == "__main__":
    if not os.getenv("CXONE_ACCESS_TOKEN"):
        raise ValueError("CXONE_ACCESS_TOKEN environment variable is not set.")
    
    deploy_studio_script()

OAuth Scope Required: studio:write

Common Errors & Debugging

Error: DBConnector Timeout

What causes it: The external database is unreachable, or the query takes longer than the default timeout (usually 5-10 seconds).
How to fix it:

  1. Verify network connectivity between NICE CXone and your database.
  2. Check if the database firewall allows inbound connections from NICE CXone IP ranges.
  3. Optimize the SQL query. Ensure the PhoneNumber column is indexed.
  4. Increase the timeout setting in the DBConnector action if supported by your NICE CXone version.

Code Check:

-- Ensure an index exists on the lookup column
CREATE INDEX IX_Customers_PhoneNumber ON Customers (PhoneNumber);

Error: No Data Returned

What causes it: The phone number format in the database does not match the format in {{System.Caller.PhoneNumber}}.
How to fix it:

  1. Log the {{System.Caller.PhoneNumber}} value to see the exact format (e.g., +15551234567 vs 5551234567).
  2. Standardize the phone number format in the SQL query using database functions.

SQL Fix:

-- MySQL Example
SELECT FirstName, LastName, LoyaltyTier FROM Customers 
WHERE REPLACE(PhoneNumber, '-', '') = REPLACE({{System.Caller.PhoneNumber}}, '-', '');

-- SQL Server Example
SELECT FirstName, LastName, LoyaltyTier FROM Customers 
WHERE REPLACE(PhoneNumber, '-', '') = REPLACE({{System.Caller.PhoneNumber}}, '-', '');

Error: 403 Forbidden on DBConnector

What causes it: The Studio script is not authorized to use the Database Connector.
How to fix it:

  1. Ensure the Database Connector is active (isActive: true).
  2. Verify that the Studio script is associated with the correct tenant.
  3. Check if the Database Connector has specific IP restrictions that block NICE CXone’s execution environment.

Error: Variable Mapping Mismatch

What causes it: The number of columns in the SELECT statement does not match the number of output variables defined in Studio.
How to fix it:

  1. Count the columns in your SQL SELECT clause.
  2. Ensure the same number of output variables are defined in the DBConnector action.
  3. Verify that the variable names in Studio match the column aliases if used.

Official References