Studio DBConnector action returning empty result set despite valid SQL

Trying to understand why my CXone Studio script is failing to populate variables from a DBConnector lookup. i’ve got a Node.js middleware service that syncs customer data to an external Postgres DB, and now i need to fetch that data during the call flow. the DBConnector action seems to execute without throwing an immediate error in the studio logs, but the output variables come back null.

here’s the JSON config i’m passing to the action:

{
 "connectionName": "prod-customer-db",
 "sql": "SELECT * FROM customer_profiles WHERE msisdn = '{{trigger.msIsdn}}'",
 "resultVariable": "dbResult"
}

the trigger.msIsdn is definitely populated. i’ve tested the exact SQL string in pgAdmin and it returns a row instantly. the connection name matches the one in the Admin UI. i even added a debug step to log the raw input to the DBConnector and it looks fine.

the weird part is that when i check the execution trace, the status says ‘Success’ but the result count is 0. no error code, just empty. i’m wondering if there’s a timeout issue or maybe a permission problem with the DB user that the connector uses? or is there a specific way i need to map the columns to variables in Studio that i’m missing?

i’m used to handling these kinds of silent failures in Node.js where the promise resolves but the data is malformed, but this feels different. any ideas on how to debug the actual SQL execution happening on the CXone side? thanks

have you tried checking the resultCount field in the action’s output? it’s often zero when the SQL returns no rows, not null. also, ensure your query uses parameterized inputs like {{input_var}} instead of string concatenation to avoid injection and parsing errors.

SELECT * FROM customers WHERE id = {{input_var}}

if the count is zero, double-check the data sync from your Node service.

have you tried checking the notification API instead? Studio DBConnector is notoriously slow for real-time lookups.

the documentation states: “Notifications are delivered in near-real-time via WebSocket.” you should subscribe to routing.queues.stats or a custom event channel to get that data instantly.

{
 "channels": [
 {
 "channelId": "routing.queues.stats",
 "eventType": "routing.queue.stats.update"
 }
 ]
}