Hello ! I have a requirement to connect to view in an external database (Oracle) using a service account from Appian.
I established the connection via Datasource from the admin console and it was a success. Created the CDT for this view and mapped it to the data store entity, verified and published it without issues.
Now, I tried querying data using a!queryEntity() and get the following error “Expression evaluation error at function a!queryEntity: An error occurred while retrieving the data.” I believe the reason for this error is the service account in question has access to not just one schema but many schemas in the target database. I am unable to establish a connection to the specific schema which contains the view I need to query data from. My understanding is schema name can't be configured in the XSD. Tried the below, with quotes, without quotes. I had no luck.
<xsd:annotation> <xsd:appinfo source="appian.jpa"> @Table(name ="V_EMPLOYEE_INFO", schema="APPIANTEST") </xsd:appinfo>
To confirm data was being fetched, used a Query database smart service with the below query and I was able to retrieve data:
SELECT APPIANTEST.V_EMPLOYEE_INFO.EMP_ID, APPIANTEST.V_EMPLOYEE_INFO.FIRSTNAME from APPIANTEST.V_EMPLOYEE_INFO where APPIANTEST.V_EMPLOYEE_INFO.EMP_ID = ac!EMP_ID
We use Oracle SQL Developer tool and I see the data in the view but I have to navigate to "Others" -> APPIANTEST (my schema) -> V_EMPLOYEE_INFO(my view) as this is a service account profile.
I need to be able to fetch data in batches and process it. Any help on how to configure this/fetch data using a!queryEntity()/ any othersolution would be highly appreciated. Thank you !
Discussion posts and replies are publicly visible
If you were able to publish your datastore, I don't think it is likely that it would be problems finding a schema.
Hi Scott, Thank you for your response. To answer your questions:
Chris's explanation below was the solution we decided upon too.