Views:
Behavior:
When using Local Data Connector and trying to access a MySQL, an ORACLE, Microsoft SQL-Server or other (ODBC) database, you cannot see / select the required tables / views.
The Local Data Connector does not show tables / views to connect. The drop-down for selecting tables / views remains empty.
 
Solution:
When using the Local Data Connector, specific restrictions apply. These differ by the selected database / driver.
You will find specific information based on the database further down below.

 
  1. ORACLE
  2. ODBC
  3. MySQL
  4. Microsoft SQL-SERVER (MSSQL)

1. ORACLE:
 
DocuWare supports only Singletenant Architecture installations of ORACLE. The highest ORACLE version Local Data Connector supports with the built-in ORACLE driver is 19c.
Please ensure that the TNS_NAME variable is configured and available system-wide and accessible via SQLPLUS / SQL DEVELOPER.

The Local Data Connector can only visualise the tables / views of the ORACLE owner / schema. (ORACLE driver)
If the user you are authenticating with, is not the schema user, you
will not be able to select the corresponding tables / views.

Please enquire the database administrator, if you can receive the permission, to use the schema owner for authenticating. This will resolve the selection issue.

If this is not feasible due to constraints, then you have two options:
  1. Access the database via ORACLE ODBC driver, please select the appropriate driver
    1. Due to the high amount of different drivers, we can not provide support for any upcoming problems: /knowledgebase/article/KBA-36009
  2. Create a view with the user you are authenticating to the database
    1. In Local Data Connector you have used the user e.g. "docuware"
    2. Create a view with the user docuware in the ORACLE database (automatically assigns owner / schema docuware) 
    3. Please check with the database administrator that you are allowed to create views. Some general hints about creating views: /knowledgebase/article/KBA-35282
    4. Additionally, please keep the following limitations in mind /knowledgebase/article/KBA-KBA-37229
 
Question
When connecting to an ORACLE database, I receive the error "ORA-00942: table or view does not exist" what should I do?
 
Answer:
The error message indicates that the connected user is possibly not allowed to "see" the table / view. To verify if the user can access the required table / view or if it exists, please perform the following steps.
 
  1. Authenticate to the ORACLE database via SQLPLUS / SQL DEVELOPER
  2. Adapt the following statement to look for the view / table by replacing 'OBJECT_NAME' with the name of the view / table
    SELECT *
    FROM all_objects
    WHERE object_type IN ('TABLE','VIEW')
    AND object_name = 'OBJECT_NAME';
  3. If the result of the statement remains empty, please contact your ORACLE DBA to check the permissions / grants of your user
  4. If you have received more than one result line, e.g. two tables / views with the same name but different owner, please enquire to rename the view / table name to be unique
    1. Local Data Connector is not able to fully qualify the statement to the table / view, therefore the access to the table / view has to be unique
  5. If you have received exactly one line showing the table / view, please contact support for further troubleshooting advice

2. ODBC

The Local Data Connector expects a standard ODBC driver connection.
Due to the different ODBC drivers and architectures, we can only support the standard case, more information can be found in the article /knowledgebase/article/KBA-KBA-36009


Some specific databases may have a different syntax or different commands entirely. We have evaluated that the databases SAP Hana and IBM DB2 fall into these criteria.
Due to the syntax of the select statement in SAP Hana, additional effort is required to enable a connection with the Local Data Connector. SAP Hana Select Syntax
For IBM DB2, the additional effort arises due to the different limiter command. DB2 Fetch First

We expect the following syntax and limiter commands:

Syntax: SELECT DISTINCT TOP
Limiter: TOP / LIMIT

Furthermore, similar restrictions as to the ORACLE connection apply. The database tables / views will only be accessible by the owner / schema.
Access to tables / views are limited to the user provided in the database authentication.

Please check the explanation in the ORACLE section to enable a selection of the data. (Schema user or Create view)

3. MySQL

Due to a software bug within MySQL (.Net Bug) and the effects on the DocuWare Software including Local Data Connector, the maximum supported version of MySQL is 8.0.28.
MySQL's databases with the version up to 8.0.28 can be connected with the integrated MySQL driver. Newer versions have to be connected via ODBC.


With a MySQL database, additionally to the appropriate user / owner, you are restricted by the capability of showing the tables / views.
​​​​​The Local Data Connector executes the following statement with the provided database user.
show full tables;

Should your user not have the rights to execute the above command, the drop-down will remain empty.

4. Microsoft SQL Server - MSSQL

Please ensure that the following applies to your Microsoft SQL Server database:

  • ​​​​​​The databases, tables, and views must be located in the DBO schema
  • Your database user should have at least the following rights DATAREADER and DATAWRITER
  • The objects (tables / views) have to be assigned to the SCHEMA_ID of the authenticated user

The Local Data Connector executes the following statements to evaluate the tables / views assigned to the user:

SELECT [name] FROM [sys].[objects] WHERE ((type = 'U') AND (schema_id = SCHEMA_ID()));

SELECT [name] FROM [sys].[objects] WHERE ((type = 'V') AND (schema_id = SCHEMA_ID()));

Should the drop-down remain empty, run the above commands with your MSSQL database user and verify the result set. 
For additional information on the SCHEMA_ID, please refer to the official documentation: sys.objects (Transact-SQL) - SQL Server | Microsoft Learn
Additionally, please contact your Database Administrator for the configuration and setup.


For general troubleshooting tips regarding Local Data Connector, please check the guide: /knowledgebase/article/KBA-36420