KB-1111 "No matching tables found!" error thrown after verifying entity mappings even though the tables exist

Symptoms

Creating a data store and verifying the entity mappings fails with error message No matching tables found! after confirming that the tables do exist in the database table or view.

Cause

The schema defined in the XSD is lowercased by Appian when verifying entity mappings. This creates a mismatch in casing between the data store and the entity mappings. This issue affects all Oracle datasources and SQL Server datasources that have case sensitive collation configured.

Action

There are two solutions to this issue:

  1. Lowercase the schema name in the database and add the schema attribute to the XSD.
  2. Create a synonym with the exact name and case as the entity. For example if the entity is named MYENTITY the steps are:

    DROP SYNONYM XXXXX.MYENTITY;
    DROP PUBLIC SYNONYM MYENTITY; 
    

    As the Appian database user:

    CREATE OR REPLACE SYNONYM MYENTITY FOR XXXXXX.MYENTITY;
    

    This will ensure that the datasource validation succeeds however querying this datasource would fail. In Oracle for example, the error is:

    ORA-00942: table or view does not exist 
    

    This is typically addressed by ensuring that the XSD entity has the appropriate annotation that maps to the above synonym. For example:

    @Table(name="MYENTITY")
    

Workaround

For Oracle databases, use an alias for the schema that is lowercase.

For SQL Server databases, configure case insensitive collation.

Affected Versions

This article applies to all versions of Appian using Oracle or SQL Server datasources.

Last Reviewed: February 2017

Related
Recommended