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
Hi Sujatha - We have a similar use case and are trying to connect to the data source with a service account.
When you connected to the data source via "Service Account", are you referring to something like a key or username and password?
Hey ! So we have a service account created over the target db and we have configured that as a datasource with the connection details like username, password, jdbc url etc., Is that what you were looking for ?
Hi Sujatha, since it is a service account we have API key. So in the connection details for username and password what do you provide? do u pass the API key as password? nd if so what would be the username ?
HI Shwetak. I am a bit confused with your question. You say API key right, so we create those when we need external systems to invoke Appian Web APIs and associate it with service accounts created in Appian. My case was to fetch data from an external database into Appian. This was done by configuring the target system connection details in 'Data Sources' on the admin console.
My requirement has nothing to do with Web API keys. Hope that helps !