Qlik Direct Access Gateway ODBC data type mapping not working properly

[NadiaB]

Depending on how a third-party vendor's ODBC driver handles source data types, Qlik Cloud may experience incorrect data being loaded or errors during reload.

If this occurs, you can override the Qlik Cloud data type or its specification (such as scale or precision).

This requires changes to the custom_type_mappings.json. One parameter that needs to be set is the identifier; if this value is not set properly, the mapping won't work.

Since the value is not necessarily the "driver" name field displayed in the ODBC Data Source Administrator (A), or the driver displayed in the error description when a reload fails, this value needs to be retrieved from the ODBC logs. 

We will explain how to do this in the article.

Resolution

Since Qlik Data Gateway Direct Access 1.7.4, the value of the identifier can be found in the ODBC connector logs. 

1. Trigger a reload using your third-party driver
2. Once the reload is completed, collect the Reload Id from the App Reload History
3. In a file browser, go to the Direct Access Gateway logs (C:\Program Files\Qlik\ConnectorAgent\data\logs)
4. Search the Reload Id in the folder, either through the default Windows file search or your preferred file search tool
5. Once you have identified the ODBC log that contains the records associated with your Reload Id, open the log and search for the Reload Id
6. You will see Request Ids related to the reload; for simplicity, we suggest creating an app with one query only using the ODBC connector in question
7. After identifying the Request Id, locate the record naming the identifier:
   
   
   
   
8.  Use this value to configure your custom_type_mappings.json file
   
   Example:
   
   

   [
      {
        "Identifier": "PSQLODBC35W.DLL",
        "NativeDataType": "date",
        "QlikDataType": "Date",
        "Size": 0, 
        "IsBit": false
      }
   ]

9. Save the custom_type_mappings.json file and restart the Qlik Data Gateway - Direct Access service

10. Reload the data

Sample without mapping (tailing time)

Sample with the mapping correctly applied:

Cause

Incorrect identifier in custom_type_mappings.json

Internal Investigation ID(s):

SUPPORT-3236

Environment

• Qlik Cloud Analytics
• Qlik Data Gateway - Direct Access