Issues when connecting to an external RDBMS (Oracle) via DataSource using a service account

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

Parents
  • 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. 

  • 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 :) 

Reply Children
No Data