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.
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.
- ORACLE
- ODBC
- MySQL
- Microsoft SQL-SERVER (MSSQL)
Please ensure that the TNS_NAME variable is configured available system-wide, and accessible via SQLPLUS / SQL DEVELOPER.
The Local Data Connector can only visualize 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 ask the database administrator if you can receive 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:
- Access the database via ORACLE ODBC driver, please select the appropriate driver
- Due to the high amount of different drivers, we can not provide support for any upcoming problems: /knowledgebase/article/KBA-36009
- Create a view with the user you are authenticating to the database
- In Local Data Connector you have used the user e.g. “docuware”
- Create a view with the user docuware in the ORACLE database (automatically assigns owner / schema docuware)
- Please check with the database administrator that you are allowed to create views. Some general hints about creating views: /knowledgebase/article/KBA-35282
- Additionally, please keep the following limitations in mind /knowledgebase/article/KBA-KBA-37229
When connecting to an ORACLE database, I receive the error “ORA-00942: table or view does not exist.” What should I do?
- Authenticate to the ORACLE database via SQLPLUS / SQL DEVELOPER
- 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'; - If the result of the statement remains empty, please contact your ORACLE DBA to check the permissions/grants of your user
- 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
- 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
- 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
To enable a connection to an ODBC database, create the respective connection within “ODBC Data Sources”.
The newly created System DSN Name will be your Database Server Name within the Local Data Connector data source connection.
Example: You have created the ODBC connection DWTEST, please enter this in the field Database Server Name in Local Data Connector.
Please create 32-bit and 64-bit ODBC connections with the same name. /knowledgebase/article/KBA-KBA-36311
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 commands. DB2 Fetch First
We expect the following syntax and limiter commands:
Syntax: SELECT DISTINCT TOP |
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
- If this does not apply, please consider creating views in the DBO schema
- Please check /knowledgebase/article/KBA-35282 and /knowledgebase/article/KBA-KBA-37229
- 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
