Qlik Sense PosgreSql connector cut off strings containing more than 255 characters

Andrea_Bertazzo · Sep 29, 2023 4:58:05 AM

The native Qlik PostgreSql connector (ODBC package) does not import strings with more of 255 characters. Such strings are just cut off, the following characters are not shown in the applications. No warnings are thrown during the script execution.

The problem affects the Qlik connector but not all the DNS drivers.

Environment

Qlik Sense February 2023 and higher versions
Qlik Cloud

Resolution

When setting up the PostgreSQL connection, set the parameter TextAsLongVarchar with value 1 in the connector Advanced settings.

When TextAsLongVarchar is set, and the Max String Length is set to 4096, 4096 characters are loaded.

Notice that there still are limitations to this functionality related to the datatype used in the database. Data types like text[] are currently not supported by Simba and they are affected by the 255 characters limitation even when the TextAsLongVarchar parameter is applied.

Qlik has opened an improvement request to Simba to support them.

As workaround, it is possible to test a custom connector using a DSN driver to deal with these data types.

Internal Investigation ID

QB-21497

AneeshKanakdande

Hi @Andrea_Bertazzo ,

I still face an error after doing what you mentioned above, the data type in postgreSQL is character varying, and it is getting cut off at 255 characters.

Below are the connection settings as you mentioned:

Andrea_Bertazzo

Hi Aneesh,

Thanks for your question.
Could you, please, set the "Max String Lenght" parameter to 4096?
Kindly, let us know if this resolves the problem.

AneeshKanakdande

Hey @Andrea_Bertazzo ,

I tried it with 4096, still kept getting the same error, we figured out a temporary workaround by splitting the string in 250 characters since we knew the max length, into 4 different fields and then ended up concatenating them to read the entire string in one field.

What else do you think could help resolve this issue?

Andrea_Bertazzo

Hi @AneeshKanakdande
Thanks for testing.

If you are sure that your field is not in text[] format, please, open a case with Qlik Support. If possible, please, send a copy of the database and the script you are using so we can replicate the problem and proceed faster with the analysis.

Thanks

cjgorrin

We were experiencing this issue yesterday (the 255 char limitation) and we managed to fix it with (apparently) no limit of characters by turning off the Bulk Reader.

https://community.qlik.com/t5/Official-Support-Articles/Qlik-Sense-ODBC-database-connector-performan...

Just add an advanced parameter to your data connection with "useBulkReader" as Name and "False" as Value. You lose a bit of performance, but you get rid of all these problems.

We are using version May 2023 Patch 9.

AneeshKanakdande

@cjgorrin Thanks for this reference, it works now. I was facing this issue not on Prem but on Qlik Cloud.

Qlik Sense PosgreSql connector cut off strings containing more than 255 characters