Building a Database Lookup in NICE CXone Studio with DBConnector

How-To-Articles
1

Building a Database Lookup in NICE CXone Studio with DBConnector

What You Will Build

  • A CXone Studio workflow that intercepts an inbound interaction, extracts a unique identifier, and queries an external PostgreSQL database to retrieve customer loyalty points.
  • This tutorial utilizes the NICE CXone Studio visual workflow engine and the specific DBConnector action node.
  • The implementation covers Studio JSON configuration, SQL parameterization, and error handling within the workflow context.

Prerequisites

  • NICE CXone Access: You must have access to the CXone Studio environment with permissions to create and publish workflows.
  • Database Connectivity: A PostgreSQL, MySQL, or SQL Server instance that is accessible from the NICE CXone network. If the database is on-premise, you must have the NICE CXone On-Premise Connector installed and configured.
  • Studio Skills: Familiarity with the CXone Studio interface, specifically the “Actions” palette and the “Data” panel.
  • SQL Knowledge: Basic understanding of SELECT statements and parameterized queries to prevent injection attacks.

Authentication Setup

CXone Studio workflows run in a serverless execution context. You do not write code to authenticate with CXone; the platform handles the session. However, the DBConnector action requires credentials to connect to your external database.

  1. Navigate to Integrations > Connectors in the CXone Admin Console.
  2. Create a new Database Connector.
  3. Select your database type (e.g., PostgreSQL).
  4. Enter the Host, Port, Database Name, Username, and Password.
  5. Test the connection.
  6. Note the Connector ID. You will need this ID in the Studio workflow configuration.

Note: Never hardcode database credentials in Studio variables. Always use the Connector object.

Implementation

Step 1: Configure the Studio Workflow and Input Variables

Before adding the database action, you must define the input data that will be sent to the database. In this example, we assume an inbound call where the caller provides their Customer ID via DTMF or IVR.

  1. Open CXone Studio.
  2. Create a new Workflow.
  3. Add an Input node or use the existing Interaction context.
  4. Create a variable named customer_id of type String.
  5. Map this variable to the relevant source (e.g., Interaction.Caller.PhoneNumber or a custom IVR variable).

In Studio, variables are accessible in the JSON configuration using the syntax {{variable_name}}. For the DBConnector, we will map this variable to a SQL parameter.

Step 2: Add and Configure the DBConnector Action

This is the core step. We will add the action that performs the actual database query.

  1. Drag the DBConnector action from the Actions palette onto the canvas.
  2. Connect it to the previous node (e.g., the Input node or a Set Variable node).
  3. Click on the DBConnector node to open the Properties panel.

Configuring the Connection

  • Connector: Select the Database Connector you created in the Prerequisites section.
  • Operation: Select Query (for SELECT statements). Do not use Execute for read-only lookups unless necessary, as Query returns result sets more efficiently.

Configuring the SQL Statement

You must write a parameterized SQL query. Never use string concatenation (e.g., SELECT * FROM users WHERE id = '{{customer_id}}') in the SQL field. This is a security risk and will fail if the ID contains special characters.

In the SQL field, enter:

SELECT points, tier_name 
FROM customer_loyalty 
WHERE customer_id = ?
LIMIT 1

Note: The syntax for parameters (?) depends on the database driver. PostgreSQL and MySQL typically use ? or $1. SQL Server uses @param. The CXone DBConnector abstracts this slightly, but using ? is the safest standard for the visual config.

Mapping Parameters

In the Parameters section of the Properties panel:

  1. Click Add Parameter.
  2. Set Parameter Name to param_1 (or match the driver expectation).
  3. Set Value to {{customer_id}}.
  4. Set Data Type to String or Integer depending on your schema.

Configuring Output

The DBConnector returns a result set. By default, it may return a JSON array.

  1. In the Output section, set the Result Variable to db_result.
  2. Ensure Return Type is set to JSON or List.

Step 3: Process the Result and Handle Empty Sets

The database query might return no rows if the customer ID does not exist. You must handle this gracefully to prevent the workflow from breaking.

  1. Add a Condition node after the DBConnector.
  2. Configure the condition:
    • If {{db_result}} is not empty (or {{db_result.count}} > 0).
  3. In the True branch:
    • Add a Set Variable node.
    • Create a variable loyalty_points.
    • Set Value to {{db_result[0].points}}.
    • Create a variable tier_name.
    • Set Value to {{db_result[0].tier_name}}.
  4. In the False branch:
    • Add a Set Variable node.
    • Set loyalty_points to 0.
    • Set tier_name to Unknown.

Step 4: Validate the Data Structure

CXone Studio variables are dynamic. If the database column points is an integer, Studio will treat it as a number. If it is NULL in the database, Studio may treat it as null or an empty string.

Add a Script node (JavaScript) after the Condition node to sanitize the data. This ensures downstream nodes (like TTS or Email) receive valid strings.

Script Code:

// Input: loyalty_points (number/null), tier_name (string/null)
// Output: safe_points (string), safe_tier (string)

let rawPoints = input.loyalty_points;
let rawTier = input.tier_name;

// Handle null or undefined from database
let safePoints = (rawPoints !== null && rawPoints !== undefined) ? rawPoints.toString() : "0";
let safeTier = (rawTier !== null && rawTier !== undefined) ? rawTier.toString() : "Valued Member";

output.safe_points = safePoints;
output.safe_tier = safeTier;

Complete Working Example

Below is the conceptual JSON representation of the Studio Workflow configuration. While you configure this visually, understanding the underlying JSON helps when debugging complex mappings.

{
  "workflow": {
    "id": "wf_db_lookup_example",
    "name": "Customer Loyalty Lookup",
    "nodes": [
      {
        "id": "node_input",
        "type": "Input",
        "properties": {
          "variables": [
            {
              "name": "customer_id",
              "type": "string",
              "source": "interaction.caller.phoneNumber"
            }
          ]
        },
        "outgoing": ["node_db_lookup"]
      },
      {
        "id": "node_db_lookup",
        "type": "DBConnector",
        "properties": {
          "connectorId": "conn_postgres_01",
          "operation": "Query",
          "sql": "SELECT points, tier_name FROM customer_loyalty WHERE customer_id = ?",
          "parameters": [
            {
              "name": "param_1",
              "value": "{{customer_id}}",
              "dataType": "String"
            }
          ],
          "resultVariable": "db_result",
          "timeout": 5000
        },
        "outgoing": ["node_check_result"]
      },
      {
        "id": "node_check_result",
        "type": "Condition",
        "properties": {
          "conditions": [
            {
              "operator": "IsNotEmpty",
              "value": "{{db_result}}"
            }
          ]
        },
        "outgoing": {
          "true": "node_set_found",
          "false": "node_set_not_found"
        }
      },
      {
        "id": "node_set_found",
        "type": "SetVariable",
        "properties": {
          "assignments": [
            {
              "variable": "loyalty_points",
              "value": "{{db_result[0].points}}"
            },
            {
              "variable": "tier_name",
              "value": "{{db_result[0].tier_name}}"
            }
          ]
        },
        "outgoing": ["node_finalize"]
      },
      {
        "id": "node_set_not_found",
        "type": "SetVariable",
        "properties": {
          "assignments": [
            {
              "variable": "loyalty_points",
              "value": "0"
            },
            {
              "variable": "tier_name",
              "value": "New Customer"
            }
          ]
        },
        "outgoing": ["node_finalize"]
      },
      {
        "id": "node_finalize",
        "type": "Script",
        "properties": {
          "language": "javascript",
          "code": "output.final_points = input.loyalty_points.toString();"
        },
        "outgoing": ["node_end"]
      },
      {
        "id": "node_end",
        "type": "End",
        "properties": {}
      }
    ]
  }
}

Common Errors & Debugging

Error: DBConnector Timeout

  • What causes it: The database query takes longer than the configured timeout (default is often 5-10 seconds). This happens with unindexed columns or large result sets.
  • How to fix it:
    1. Check the SQL execution plan in your database. Ensure customer_id is indexed.
    2. Increase the timeout in the DBConnector properties (e.g., to 15000 ms).
    3. Add LIMIT 1 to your SQL if you only expect one row.

Error: “Parameter type mismatch”

  • What causes it: The data type defined in the Studio Parameter mapping does not match the database column type. For example, sending a String ? to an Integer column.
  • How to fix it:
    1. In the DBConnector properties, change the Data Type of the parameter to match the database schema (e.g., change from String to Integer).
    2. Alternatively, cast the variable in a preceding Script node: output.int_id = parseInt(input.customer_id); and pass {{int_id}} to the DBConnector.

Error: “Result set is empty” when data exists

  • What causes it: The customer_id variable contains leading/trailing whitespace or case sensitivity issues.
  • How to fix it:
    1. Add a Set Variable node before the DBConnector to clean the input: output.clean_id = trim(input.customer_id).
    2. Use LOWER() in your SQL: WHERE LOWER(customer_id) = LOWER(?).

Error: “Connector not found”

  • What causes it: The Connector ID referenced in the workflow does not exist or is not published.
  • How to fix it:
    1. Go to Integrations > Connectors.
    2. Verify the connector status is Active.
    3. Ensure you are selecting the correct connector from the dropdown in Studio.

Official References