CXone Studio DBConnector: Handling empty result sets in subsequent actions

Running into a snag with the DBConnector action in a CXone Studio script. The goal is straightforward: look up a customer record based on an inbound phone number and then branch logic based on whether that record exists. The SQL query is simple enough, just a SELECT id, status FROM customers WHERE phone = :inbound_phone.

The issue is how Studio handles the response when no rows match. I’m assigning the output to a session variable dbResult. When a match is found, dbResult contains the expected JSON object. When there’s no match, the variable seems to be null or undefined, but subsequent actions that reference dbResult.id throw a generic runtime error instead of gracefully handling the missing data.

Here’s the snippet of the Studio configuration for the DBConnector action:

{
 "action": "DBConnector",
 "connectionName": "prod_db_conn",
 "queryType": "SELECT",
 "sql": "SELECT id, status FROM customers WHERE phone = :inbound_phone",
 "parameters": {
 "inbound_phone": "{session.inbound_phone}"
 },
 "outputVariable": "dbResult"
}

And the immediate next action is an IF statement checking dbResult.id. It crashes the flow with this error payload in the logs:

{
 "timestamp": "2023-10-27T14:30:00Z",
 "error": "Cannot read property 'id' of undefined",
 "action": "IF_Check_Customer",
 "sessionId": "sess_12345"
}

I’ve tried wrapping it in a try-catch block using a JavaScript snippet action, but Studio’s execution engine doesn’t seem to allow custom JS in the main flow control for this specific connector type. Is there a standard way to check if the DBConnector returned an empty set before trying to access properties on the result object? Or do I need to structure the SQL query to always return a dummy row if no match is found? That feels like a hack. Looking for the cleanest approach to handle null results from DBConnector in Studio.

Studio’s DBConnector is notoriously finicky with empty sets. It doesn’t return null or an empty array by default; it often throws a silent exception or leaves the variable undefined, which breaks downstream logic. You can’t just check if dbResult == null.

Since you’re dealing with a potential “no row” scenario, the cleanest fix isn’t in the SQL itself, but in how you handle the connector’s output in the Studio flow. You need to wrap the DBConnector in a Try/Catch block or use a specific “On Error” behavior. However, if you want to keep the flow linear without extra error nodes, tweak the SQL to always return a row. Use a UNION with a dummy row.

Here’s the pattern I use in my Terraform-defined flows (via the CX-as-Code provider) to avoid this drift:

SELECT id, status FROM customers WHERE phone = :inbound_phone
UNION
SELECT NULL as id, 'NOT_FOUND' as status FROM dual
LIMIT 1

Wait, that LIMIT might not apply cleanly in all Studio SQL dialects. Better approach:

SELECT 
 COALESCE(c.id, -1) as id, 
 COALESCE(c.status, 'MISSING') as status 
FROM (SELECT id, status FROM customers WHERE phone = :inbound_phone) c

If c is empty, COALESCE doesn’t work on an empty set. You still get zero rows.

The real fix is checking the row count. In Studio, after the DBConnector action, add a simple Script action (JavaScript) or a Decision node. Check if the dbResult object exists and has a length.

// In a Script action
var result = session.getVariable("dbResult");
if (!result || result.length === 0) {
 session.setVariable("foundCustomer", false);
} else {
 session.setVariable("foundCustomer", true);
 session.setVariable("customerId", result[0].id);
}

This way, your downstream Decision node just checks foundCustomer == true. It’s verbose, but it stops the flow from crashing when the DB returns nothing. I’ve seen Studio scripts fail silently on empty sets for months until we added this explicit check.