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.
We have seen this issue with Oracle when the object attempting to be accessed was in a different schema than the account. The explanation to why the data store would publish but could not be actually utilized was something to do with different Java libraries utilized for DS validation/publish, vs what is actually used at run time to connect (Teneo vs ?).
The resolution was to create a synonym in the Oracle database with the same name as the destination object, that was created in the home schema of the service account.
Sorry for not responding sooner. We decided to create a synonym too but that could change and we might actually create the view directly on that service account due to a few internal DBA setbacks related to maintenance.
I was not aware of the reason for this and your explanation cleared that up for me. Thank you Chris. Appreciate it.
Hi Scott, Thank you for your response. To answer your questions:
Chris's explanation below was the solution we decided upon too.
Great! I was able to locate my notes from February 2016 as well, from Appian at that time, the Java library Hibernate is used for Data Source validation which is able to find objects in other schemas - the Teneo library is used at runtime, which is not able to find objects in other schemas. Still looks to be relevant today :)
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 !