Fetching Data from external source (Oracle SQL Developer software)

Question

Hi, 
 I am using Query database smart service in the Process model to get the data from the external data source which is in DataMart. Due to some access restriction, we were not able access external database. So, we have added the source in the admin console, and we are using it in smart service. I am getting 1000 rows of data, and I am storing it in a Process variable which is of CDT type. I want to fetch all the rows like I have more than 4000 rows of data. But I am able to fetch only 1000 rows. Is there any way to fetch all the rows. This is my current configuration. We need to display the data in an interface grid which will be viewed in Site

Shubham Aware · Answer

Query Database smart service has a 1000-row hard limit. Use a loop in your process model with LIMIT 1000 OFFSET ac!offsetValue, starting at 0 and incrementing by 1000 each iteration. Stop the loop when returned rows < 1000 and append each batch to your CDT list PV. Pass ac!offsetValue as an input node in the Data tab of the smart service, starting at 0 and dynamically passing the updated offset after each iteration. SELECT SOURCE_ID, ADD_COMMENT, MAX_INVOICE_DATE, AMOUNT
FROM AHN_EXCEPTIONS_BY_SALES_VW
ORDER BY SOURCE_ID
OFFSET ac!offsetValue ROWS FETCH NEXT 1000 ROWS ONLY

Harsha Sharma · Answer

As per documentation, the default limit is 1000 rows for a select query when Query Database smart service is used but it can be updated. Check this documentation to get the limit updated as per need

VarunTejaGurrapu · Answer

Hi srinivaast629400 , 
 The Query Database Smart Service is not designed to load huge datasets into process variables for UI consumption. 
 By default: 
 
 It fetches limited rows (commonly 1000 depending on config/query) 
 Storing 4000+ rows in PV is a bad design ( upto 1000 is maximum). 
 If you absolutely must use Query Database Smart Service. Then implement:
 
 batching 
 pagination loop

Question: Why are you not using the Integration call to get the data into Appian?

Harsha Sharma · Answer

Did you try setting up a record type to work with external data source? What challenges did you face 
 Editing because I cant reply to this thread any longer, and am getting a warning. srinivaast629400 Please see below my latest reply 
 As per documentation, the default limit is 1000 rows for a select query when Query Database smart service is used but it can be updated. Check this documentation to get the limit updated as per need

Fetching Data from external source (Oracle SQL Developer software)

Top Replies