I want to access sql result data in Custom Output of Query Database Smart Service, but I get an error.
I would like to access the ResultSets, like in the Execute Stored Procedure Smart Service if possible, can I achieve the same operation in the Query Database?
Discussion posts and replies are publicly visible
Is your code snippet just a non-working example or your final code?
Appian describes in the documentation how to do that: https://docs.appian.com/suite/help/23.2/Query_Database_Smart_Service.html#data-tab
Your description and screenshot are miles apart. And I am not clear what are you trying to achieve with that code in your screenshot.
This code is under consideration and does not work.
What I would like to achieve is a map to the CDT structure with custom output.
The best practice might be to store each item of the result in a process variable, but we are considering whether it is possible to implement this in a way that it can be used around QueryDatabase.
Just out of curiosity, any specific reason for using Query Database node instead of a!queryentity() ? this is very rarely used and other options should be considered first.
Still, if you want to index the result sets, you need to store them in a pv! and index those pv! in a script task next to this node.
I am looking for an approach other than QueryEntities to perform complex joins between tables, etc.
I am also considering other methods using procedures, but would you say that using QueryEntities is more Appian compliant?
Use records and record relationships. They were designed for connecting data, and aggregating them while keeping the maximum performance.
Query entities are best when you are querying data from a RDBMS. If the data is complex then a DB view can act as a DSE or as you said procedures can also be used. But as Harshit said, using Records with relationships is considered more preferable and recommended solution to the underlying condition these days.
I highly recommend to use the latest Data Fabric features and synced records to implement complex queries.
Thank you for your confirmation. I will consider reviewing the data structure.