migrating a legacy lookup process into CXone Studio to reduce dependency on external APIs. The specific requirement involves querying an on-premise SQL Server database to retrieve customer loyalty points based on the caller’s ID. We have configured the DBConnector action with a pre-approved DSN that has been validated via the Studio connection wizard. The connection test passes without error, confirming that the authentication credentials and network path are correct.
The issue arises when the script executes during a live call. The DBConnector action completes successfully, returning a status code of 200. However, the result set variable remains empty. To isolate the problem, we have simplified the SQL query to a basic SELECT statement: SELECT points FROM loyalty_table WHERE customer_id = {{flow.customerId}}. We have verified that the flow.customerId variable contains a valid integer and that a matching record exists in the database. Executing this exact query manually in SQL Server Management Studio returns the expected row immediately.
We have also attempted to hardcode the customer ID in the SQL statement within the Studio snippet to rule out variable interpolation issues, but the result set remains empty. The debug logs indicate that the query was sent and received, but no rows were mapped to the output variables. We are using the standard SQL Server driver provided by the platform. Is there a known limitation with how the DBConnector handles result sets for simple single-row lookups? We have reviewed the documentation but found no mention of implicit row limits or formatting requirements for the return payload. We are unsure if we need to wrap the result in a specific JSON structure or if the variable mapping syntax is incorrect. Any insight into common pitfalls with the DBConnector action would be appreciated.