Building a Dynamic Database Lookup in NICE CXone Studio

What You Will Build

• You will build a CXone Studio script that accepts caller input, queries an external SQL database, and returns specific record fields into Studio variables.
• This implementation uses the NICE CXone Studio DBConnector action and the REST API for configuration validation.
• The tutorial covers JavaScript for API integration and the specific JSON structure required for the Studio DBConnector action configuration.

Prerequisites

• NICE CXone Account: Access to the Studio editor and the ability to create Database Connections in the Admin console.
• Database Access: A reachable SQL database (MySQL, PostgreSQL, or SQL Server) with a user account having SELECT permissions on the target table.
• Studio Script ID: An existing Studio script or a new blank script to attach the action to.
• Node.js Environment: For the API validation steps, Node.js v16+ with axios installed (npm install axios).
• API Credentials: A CXone API client ID and secret with Studio:Read and Admin:DatabaseConnections:Read scopes.

Authentication Setup

To interact with the CXone API for debugging connection strings or validating the DBConnector configuration, you must first obtain an OAuth 2.0 access token. CXone uses the Client Credentials flow for server-to-server communication.

JavaScript: OAuth Token Retrieval

Create a file named auth.js. This script handles the token acquisition and caching.

const axios = require('axios');

const CXONE_DOMAIN = 'https://api.mynicecx.com'; // Replace with your specific domain
const CLIENT_ID = 'your_client_id';
const CLIENT_SECRET = 'your_client_secret';

/**
 * Fetches an OAuth 2.0 access token from the CXone Identity Provider.
 * @returns {Promise<string>} The access token.
 */
async function getAccessToken() {
    try {
        const response = await axios.post(`${CXONE_DOMAIN}/api/v2/oauth/token`, {
            grant_type: 'client_credentials',
            client_id: CLIENT_ID,
            client_secret: CLIENT_SECRET
        });

        if (response.status !== 200) {
            throw new Error(`Failed to authenticate. Status: ${response.status}`);
        }

        return response.data.access_token;
    } catch (error) {
        console.error('Authentication failed:', error.message);
        if (error.response) {
            console.error('Response data:', error.response.data);
        }
        process.exit(1);
    }
}

module.exports = { getAccessToken };

Scope Requirement: The client used above must have the Studio:Read scope to inspect script actions and Admin:DatabaseConnections:Read to verify the database connection object exists.

Implementation

The core of this tutorial is the configuration of the DBConnector action within a Studio script. Unlike traditional code, Studio actions are defined via JSON payloads when using the API, or via the UI. To ensure precision and version control, we will define the action structure programmatically.

Step 1: Verify the Database Connection

Before writing the Studio action, you must confirm that the database connection object exists in CXone Admin. The DBConnector action references this object by its ID.

Run this script to list available database connections.

const { getAccessToken } = require('./auth');

async function listDatabaseConnections() {
    const token = await getAccessToken();

    try {
        const response = await axios.get(`${CXONE_DOMAIN}/api/v2/admin/databaseconnections`, {
            headers: {
                'Authorization': `Bearer ${token}`,
                'Accept': 'application/json',
                'Content-Type': 'application/json'
            }
        });

        if (response.status === 200) {
            console.log('Available Database Connections:');
            response.data.entities.forEach(conn => {
                console.log(`ID: ${conn.id}, Name: ${conn.name}, Type: ${conn.connectionType}`);
            });
        } else {
            console.error(`Unexpected status: ${response.status}`);
        }
    } catch (error) {
        console.error('Error fetching connections:', error.message);
    }
}

listDatabaseConnections();

Expected Response:
A JSON array of database connection objects. Note the id of the connection you intend to use. For this tutorial, assume the ID is db-conn-12345.

Error Handling:

• 401 Unauthorized: Check your Client ID/Secret.
• 403 Forbidden: Ensure the API user has Admin:DatabaseConnections:Read scope.
• 404 Not Found: Verify the domain URL is correct for your region.

Step 2: Define the DBConnector Action Payload

The DBConnector action requires a specific JSON structure. It does not execute raw SQL directly in the action definition; instead, it references a pre-defined query or allows inline parameterization. The most robust method is using parameterized queries to prevent SQL injection.

The action configuration includes:

1. connectionId: The ID retrieved in Step 1.
2. query: The SQL statement. Use ? for parameters.
3. parameters: An array of objects mapping Studio variables to SQL parameters.
4. resultVariable: The Studio variable where the result set will be stored.

Here is the JSON structure for a lookup based on a phone number.

{
    "name": "LookupCustomer",
    "type": "dbconnector",
    "connectionId": "db-conn-12345",
    "query": "SELECT customer_id, first_name, last_name, loyalty_tier FROM customers WHERE phone_number = ?",
    "parameters": [
        {
            "name": "phoneParam",
            "value": "{input.PhoneNumber}"
        }
    ],
    "resultVariable": "dbResult",
    "timeout": 5000
}

Critical Parameter Explanation:

• {input.PhoneNumber}: This is a Studio variable reference. It must match the variable name defined earlier in the script (e.g., via a Prompt action).
• resultVariable: This variable will hold an array of objects, even if only one row is returned.
• timeout: Set this to a reasonable value (e.g., 5000ms). Database lookups should be fast; long timeouts block the media stream.

Step 3: Update the Studio Script via API

To apply this action to your script, you must update the script’s definition. Studio scripts are immutable in their core structure; you submit a new version of the script definition.

First, retrieve the current script definition.

const { getAccessToken } = require('./auth');

const SCRIPT_ID = 'your-script-id';

async function getScriptDefinition() {
    const token = await getAccessToken();

    try {
        const response = await axios.get(`${CXONE_DOMAIN}/api/v2/studio/scripts/${SCRIPT_ID}`, {
            headers: {
                'Authorization': `Bearer ${token}`,
                'Accept': 'application/json'
            }
        });

        if (response.status === 200) {
            console.log(JSON.stringify(response.data, null, 2));
            return response.data;
        } else {
            console.error(`Failed to fetch script. Status: ${response.status}`);
            return null;
        }
    } catch (error) {
        console.error('Error fetching script:', error.message);
        return null;
    }
}

getScriptDefinition();

Expected Response:
A large JSON object containing the script’s actions, variables, and triggers. Locate the actions array. This is where you will insert the DBConnector action.

Step 4: Insert the DBConnector Action

You must add the action to the actions array. Ensure it is placed after any action that sets the input variable (e.g., a Prompt or IVR menu).

Here is a Node.js script that modifies the script definition and submits the update.

const { getAccessToken } = require('./auth');

const SCRIPT_ID = 'your-script-id';
const DB_CONNECTION_ID = 'db-conn-12345';

const DB_CONNECTOR_ACTION = {
    "name": "LookupCustomer",
    "type": "dbconnector",
    "connectionId": DB_CONNECTION_ID,
    "query": "SELECT customer_id, first_name, last_name, loyalty_tier FROM customers WHERE phone_number = ?",
    "parameters": [
        {
            "name": "phoneParam",
            "value": "{input.PhoneNumber}"
        }
    ],
    "resultVariable": "dbResult",
    "timeout": 5000
};

async function updateScriptWithDBLookup() {
    const token = await getAccessToken();
    let scriptData = await getScriptDefinition();

    if (!scriptData) {
        return;
    }

    // Check if the action already exists to avoid duplicates
    const existingActionIndex = scriptData.actions.findIndex(a => a.name === 'LookupCustomer');
    if (existingActionIndex > -1) {
        console.log('Action already exists. Updating...');
        scriptData.actions[existingActionIndex] = DB_CONNECTOR_ACTION;
    } else {
        console.log('Adding new action...');
        // Insert after the first action (assuming first action is a Prompt setting input.PhoneNumber)
        scriptData.actions.splice(1, 0, DB_CONNECTOR_ACTION);
    }

    // Ensure the result variable is declared in the script variables
    const varExists = scriptData.variables.some(v => v.name === 'dbResult');
    if (!varExists) {
        scriptData.variables.push({
            "name": "dbResult",
            "type": "array"
        });
    }

    try {
        const response = await axios.put(`${CXONE_DOMAIN}/api/v2/studio/scripts/${SCRIPT_ID}`, scriptData, {
            headers: {
                'Authorization': `Bearer ${token}`,
                'Accept': 'application/json',
                'Content-Type': 'application/json'
            }
        });

        if (response.status === 200) {
            console.log('Script updated successfully.');
            console.log('New Script Version:', response.data.version);
        } else {
            console.error(`Failed to update script. Status: ${response.status}`);
            console.error(response.data);
        }
    } catch (error) {
        console.error('Error updating script:', error.message);
        if (error.response) {
            console.error('Response data:', error.response.data);
        }
    }
}

updateScriptWithDBLookup();

Logic Explanation:

1. Fetch: Retrieves the current script state.
2. Modify: Inserts the DB_CONNECTOR_ACTION into the actions array. It also ensures the dbResult variable is declared in the variables array. Studio requires variables to be declared before use.
3. Submit: Sends a PUT request to update the script. This creates a new version.

Error Handling:

• 400 Bad Request: The JSON structure is invalid. Check for typos in connectionId or query.
• 409 Conflict: The script version is outdated. You may need to handle versioning logic in production code.
• 500 Internal Server Error: The database connection might be down. Check the Admin console for connection status.

Step 5: Process the Results in Studio

Once the DBConnector action completes, the dbResult variable contains an array of objects. You must check if the array has items before accessing properties.

In the Studio UI (or subsequent API actions), you would typically add a Condition action.

// This is a conceptual representation of the Condition action JSON
{
    "name": "CheckIfCustomerFound",
    "type": "condition",
    "expression": "{dbResult.length} > 0",
    "trueAction": "GreetCustomer",
    "falseAction": "HandleUnknownCustomer"
}

If the condition is true, you can access fields like {dbResult[0].first_name} in subsequent Prompt or TTS actions.

Complete Working Example

Below is a complete Node.js module that encapsulates the authentication, script retrieval, modification, and update process. Save this as studio_db_setup.js.

const axios = require('axios');

// Configuration
const CXONE_DOMAIN = 'https://api.mynicecx.com';
const CLIENT_ID = 'your_client_id';
const CLIENT_SECRET = 'your_client_secret';
const SCRIPT_ID = 'your-script-id';
const DB_CONNECTION_ID = 'db-conn-12345';

// Action Definition
const DB_CONNECTOR_ACTION = {
    "name": "LookupCustomer",
    "type": "dbconnector",
    "connectionId": DB_CONNECTION_ID,
    "query": "SELECT customer_id, first_name, last_name, loyalty_tier FROM customers WHERE phone_number = ?",
    "parameters": [
        {
            "name": "phoneParam",
            "value": "{input.PhoneNumber}"
        }
    ],
    "resultVariable": "dbResult",
    "timeout": 5000
};

async function getAccessToken() {
    try {
        const response = await axios.post(`${CXONE_DOMAIN}/api/v2/oauth/token`, {
            grant_type: 'client_credentials',
            client_id: CLIENT_ID,
            client_secret: CLIENT_SECRET
        });
        return response.data.access_token;
    } catch (error) {
        console.error('Auth failed:', error.message);
        process.exit(1);
    }
}

async function updateStudioScript() {
    const token = await getAccessToken();

    try {
        // 1. Get Current Script
        const scriptRes = await axios.get(`${CXONE_DOMAIN}/api/v2/studio/scripts/${SCRIPT_ID}`, {
            headers: { 'Authorization': `Bearer ${token}`, 'Accept': 'application/json' }
        });

        let scriptData = scriptRes.data;

        // 2. Modify Script
        // Remove existing action if present
        scriptData.actions = scriptData.actions.filter(a => a.name !== 'LookupCustomer');
        
        // Add new action at index 1 (after initial prompt)
        scriptData.actions.splice(1, 0, DB_CONNECTOR_ACTION);

        // Ensure variable exists
        if (!scriptData.variables.some(v => v.name === 'dbResult')) {
            scriptData.variables.push({ "name": "dbResult", "type": "array" });
        }

        // 3. Update Script
        const updateRes = await axios.put(`${CXONE_DOMAIN}/api/v2/studio/scripts/${SCRIPT_ID}`, scriptData, {
            headers: { 
                'Authorization': `Bearer ${token}`, 
                'Accept': 'application/json',
                'Content-Type': 'application/json'
            }
        });

        console.log('Success! Script updated. New Version:', updateRes.data.version);
        console.log('Action "LookupCustomer" added successfully.');

    } catch (error) {
        if (error.response) {
            console.error('API Error:', error.response.status, error.response.data);
        } else {
            console.error('Network Error:', error.message);
        }
    }
}

updateStudioScript();

Common Errors & Debugging

Error: 400 Bad Request - Invalid Query Syntax

• Cause: The SQL query in the query field contains syntax errors specific to the database dialect (e.g., using MySQL syntax on a PostgreSQL connection).
• Fix: Test the SQL query directly in your database client. Ensure you use ? for parameters if using the parameterized approach. Do not concatenate variables directly in the query string to avoid injection.

Error: 403 Forbidden - Insufficient Permissions

• Cause: The API user does not have Studio:Write or Admin:DatabaseConnections:Read.
• Fix: Log in to the CXone Admin console, navigate to Admin > Integrations > API, and add the required scopes to the API client.

Error: Timeout Exception in Studio

• Cause: The database query takes longer than the timeout value specified in the action.
• Fix: Optimize the SQL query. Add indexes to the columns used in the WHERE clause. Increase the timeout value in the action definition if the query is legitimately complex, but avoid exceeding 10 seconds to maintain caller experience.

Error: dbResult is Undefined

• Cause: The variable dbResult was not declared in the script’s variables array.
• Fix: Ensure the script definition includes { "name": "dbResult", "type": "array" } in the variables array. Studio does not auto-declare variables for DBConnector results.

Official References

• NICE CXone Studio API Documentation
• DBConnector Action Reference
• OAuth 2.0 Client Credentials Flow
• Database Connections Admin Guide