Does anyone know why a DBConnector action in CXone Studio would return an empty result set when the SQL query is verified to work in the underlying database? I am building a serverless integration where a Lambda function triggers a CXone Studio script via webhook. The script needs to fetch customer tier data from an RDS instance using the DBConnector action.
Here is the relevant Studio snippet configuration:
ASSIGN sqlQuery = "SELECT tier_name FROM customer_tiers WHERE account_id = '" + sys.account.id + "'"
DBConnector
ConnectionName: "MyRDSConn"
SqlStatement: sqlQuery
ResultVariable: dbResult
The dbResult variable is always null. I have confirmed the connection credentials are correct and the IAM role attached to the Studio execution context has the necessary permissions. The logs show the SQL executes without error, but the result mapping seems to fail silently. I am using Studio v2.1 and the database is PostgreSQL 14.x.
Is there a specific format required for the result variable or a known issue with how DBConnector handles single-row results? I have tried wrapping the query in a SELECT INTO but that causes a syntax error in the Studio validator.