topic Re: CLOB conversion to varchar in Qlik Replicate

CLOB conversion to varchar

sergsyb — Mon, 03 Oct 2022 16:16:23 GMT

Hi guru. There are 2 Sources MS SQL Server and Postgres and one Target - Sybase IQ

For column with CLOB datatype (varchar (max) for MS SQL and text for Postgres) there is datatype conversion from CLOB to String (1000) without any transformation functions on it. See screenshot

For Postgres values for CLOB columns replicates well in both modes FullLoad and ChangeProcessing

But for MSSQL CLOB columns loaded well in FullLoad mode, but in ChangeProcessing any changes CLOB column leads to replicating empty string on the Target.

In which way is it possible replicate CLOB to Varchar for MS SQL server in change processing mode?

Re: CLOB conversion to varchar

Michael_Litz — Mon, 03 Oct 2022 17:15:08 GMT

Hello,

It is a limit of replicate that it can not perform string functions on a CLOB field.

This article will describe a work around that will allow the CLOB string function that you want to work by using a source lookup function to get the value and then that is wrapped by the string function you want to perform.

Please check out;

Transformation - CLOB - string functions
https://community.qlik.com/t5/Official-Support-Articles/Transformation-Source-Lookup-CLOB-datatype-limitations-on-string/ta-p/1987353/message-revision/1987353:1

Please let me know if this helps out.

Thanks,
Michael

Re: CLOB conversion to varchar

sergsyb — Mon, 03 Oct 2022 17:32:34 GMT

Hi Michael,

I know that attempt to use function on LOB column leads to NULL value on the Targer. But in our case we don't use string functions on a CLOB field. We just changed datatype from CLOB to STRING. And in that case, this is a fully working solution for Postgres replication, but for MS SQL it leads to empy string (not null) while ChangeProcessing mode is using

Re: CLOB conversion to varchar

Michael_Litz — Mon, 03 Oct 2022 17:43:42 GMT

Hello,

That makes sense, I missed the part of your comment where you mentioned that there are no transformations.

I will look into this and reply back with my findings.

Thanks,
Michael