Building a CXone Studio Script with DBConnector Database Lookup

How-To-Articles
1

Building a CXone Studio Script with DBConnector Database Lookup

What You Will Build

  • A NICE CXone Studio script that intercepts an inbound call, extracts a customer identifier, and queries an external SQL database to retrieve account status.
  • This tutorial utilizes the NICE CXone Studio visual scripting environment and the DBConnector action node for real-time data integration.
  • The implementation covers the Studio Canvas configuration, SQL query parameterization, and error handling logic within the visual workflow.

Prerequisites

  • NICE CXone Studio Access: You must have administrative or developer access to the CXone Studio environment.
  • Database Connector Configuration: A configured DBConnector instance in the CXone Control Center. This requires a valid connection string to a supported SQL database (MySQL, PostgreSQL, SQL Server, or Oracle).
  • SQL Permissions: The database user associated with the connector must have SELECT permissions on the target table.
  • Studio Canvas Access: Access to the Studio Designer interface to create and publish scripts.

Authentication Setup

Unlike REST API integrations that require OAuth token management in code, CXone Studio scripts run within the NICE CXone execution environment. Authentication is handled implicitly by the platform. However, the DBConnector action relies on the connection credentials configured in the Control Center.

  1. Navigate to the CXone Control Center.
  2. Go to Integrations > Connectors > Database Connectors.
  3. Ensure a connector exists with the status Active.
  4. Note the Connector ID or Name. This identifier is required in the Studio DBConnector action configuration.
  5. Verify that the connection test passes. If it fails, the Studio script will fail at runtime with a connection error, not an authentication error.

Implementation

Step 1: Configure the Inbound Trigger and Variable Extraction

The first step is to define when the script executes and what data is available. For a database lookup, we typically need a unique key, such as a Phone Number or Account ID.

  1. Open the Studio Designer.
  2. Create a new Script or open an existing Inbound Call Flow.
  3. Add a Start node.
  4. Connect the Start node to a Get Data node (or use the built-in call object if available in your specific Studio version).
  5. Configure the Get Data node to extract the Caller ID or a specific DTMF input if the caller provides an account number.

In this example, we assume the caller provides a 10-digit Account ID via DTMF.

Studio Node Configuration:

  • Node Type: DTMF Collect
  • Variable Name: dtmf_account_id
  • Max Digits: 10
  • Timeout: 10 seconds

If the caller hangs up or provides no input, the flow should branch to a goodbye message. If input is provided, it flows to the Database Lookup.

Step 2: Configure the DBConnector Action

This is the core of the integration. The DBConnector action allows you to execute SQL queries against the configured database.

  1. Drag the DBConnector action node onto the Canvas.
  2. Connect the DTMF Collect node’s “Success” output to the DBConnector’s input.
  3. Configure the DBConnector node properties:

Node Properties:

  • Connector Name: Select the active DBConnector created in Prerequisites.
  • Action: Select Execute Query.
  • SQL Query: Enter the parameterized SQL statement.
  • Output Variable: Define a variable to store the result, e.g., db_result.

SQL Query Construction:
Never concatenate user input directly into the SQL string. Use parameter placeholders to prevent SQL injection. The placeholder syntax depends on the database type, but CXone Studio generally supports standard ? or @param syntax. For this example, we use ?.

SELECT account_status, customer_name, loyalty_points 
FROM customer_accounts 
WHERE account_id = ?

Parameter Mapping:
In the DBConnector configuration panel, you will see a section for Parameters.

  • Index 0: Map this to the variable dtmf_account_id.

Expected Response Structure:
The db_result variable will contain a JSON-like structure or a list of objects, depending on the Studio version and connector configuration. A typical successful response for a single record looks like this:

{
  "records": [
    {
      "account_status": "ACTIVE",
      "customer_name": "John Doe",
      "loyalty_points": 1500
    }
  ],
  "rowCount": 1
}

If no records are found, rowCount will be 0, and records will be an empty array.

Step 3: Process the Database Result

Raw database results must be parsed to drive the conversation flow. You cannot directly speak a JSON object. Use a Set Variable or Script node to extract specific fields.

  1. Add a Set Variable node after the DBConnector.
  2. Configure the node to check if data exists.

Logic Branching:
Use a Condition node to branch based on the result.

Condition Node Configuration:

  • Condition: db_result.rowCount > 0
  • True Branch: Proceed to personalized greeting.
  • False Branch: Proceed to “Account not found” error handling.

Extracting Data on the True Branch:
Add a Set Variable node to extract the customer name.

  • Variable Name: customer_name
  • Value: db_result.records[0].customer_name

Similarly, extract account_status and loyalty_points.

Personalized Greeting:
Connect the “True” branch to a Say node.

  • Text: Hello, {{customer_name}}. Your account status is {{account_status}}. You have {{loyalty_points}} loyalty points.

Error Handling on the False Branch:
Connect the “False” branch to a Say node.

  • Text: We could not find an account with that number. Please check your account ID and try again, or press 0 to speak to an agent.

Step 4: Handle Database Errors

Database lookups can fail due to network issues, timeouts, or invalid queries. The DBConnector node typically has an “Error” output port.

  1. Connect the Error output of the DBConnector node to a Set Variable node.
  2. Set a variable error_message to db_result.error or a generic message.
  3. Connect this to a Say node.
  • Text: We are experiencing technical difficulties. Please try calling back later.
  1. Optionally, log the error using a Log node or send an alert via a Webhook to your monitoring system.

Complete Working Example

Below is a representation of the Studio Canvas flow in a structured format. Since Studio is visual, this text-based representation maps the nodes and connections.

Node 1: Start

  • Output: flow

Node 2: DTMF Collect

  • Input: flow from Start
  • Variable: dtmf_account_id
  • Max Digits: 10
  • Output Success: has_input
  • Output Timeout/Cancel: goodbye

Node 3: DBConnector

  • Input: has_input from DTMF Collect
  • Connector: My_SQL_Connector
  • Action: Execute Query
  • SQL: SELECT account_status, customer_name, loyalty_points FROM customer_accounts WHERE account_id = ?
  • Parameter 0: {{dtmf_account_id}}
  • Output Variable: db_result
  • Output Success: db_success
  • Output Error: db_error

Node 4: Condition (Check Result)

  • Input: db_success from DBConnector
  • Condition: {{db_result.rowCount}} > 0
  • Output True: found_account
  • Output False: not_found

Node 5: Set Variable (Extract Name)

  • Input: found_account from Condition
  • Variable: customer_name
  • Value: {{db_result.records[0].customer_name}}
  • Output: greet

Node 6: Say (Greeting)

  • Input: greet from Set Variable
  • Text: Hello, {{customer_name}}. Your account is active.
  • Output: end

Node 7: Say (Not Found)

  • Input: not_found from Condition
  • Text: Account not found. Please verify your ID.
  • Output: end

Node 8: Say (Error)

  • Input: db_error from DBConnector
  • Text: System error. Please try again later.
  • Output: end

Node 9: Goodbye

  • Input: goodbye from DTMF Collect
  • Text: Goodbye.
  • Output: end

Node 10: End

  • Inputs: end from Say (Greeting), Say (Not Found), Say (Error), Goodbye

Common Errors & Debugging

Error: DBConnector Timeout

  • What causes it: The SQL query takes longer than the configured timeout (default is often 5-10 seconds) to return results. This happens with unindexed columns or large table scans.
  • How to fix it:
    1. Check the database execution plan for the SQL query.
    2. Add an index on the account_id column.
    3. Increase the timeout setting in the DBConnector configuration in the Control Center if appropriate.
    4. Optimize the query to select only necessary columns.

Error: SQL Injection Risk

  • What causes it: Concatenating the dtmf_account_id directly into the SQL string instead of using parameters.
  • How to fix it: Always use parameterized queries. In the DBConnector configuration, use the parameter mapping feature. Do not construct the SQL string dynamically in a script node before passing it to the connector.

Error: “Connector Not Found” or “Invalid Connector”

  • What causes it: The DBConnector name in the Studio node does not exactly match the name in the Control Center, or the connector is disabled.
  • How to fix it:
    1. Verify the exact name of the connector in the Control Center.
    2. Ensure the connector status is Active.
    3. Refresh the Studio environment cache if necessary.

Error: Null Pointer or Index Out of Bounds

  • What causes it: Accessing db_result.records[0] when rowCount is 0.
  • How to fix it: Always check rowCount > 0 in a Condition node before accessing array elements. Never assume a query returns results.

Error: Data Type Mismatch

  • What causes it: Passing a string variable to a numeric database column without proper casting, or vice versa.
  • How to fix it: Ensure the DTMF input is treated as a string if the database column is VARCHAR, or cast it to an integer in the SQL query if the column is INT.
    • Example: WHERE account_id = CAST(? AS INT)

Official References