How to do lists with MySQL native-ish-ly

Support/help with CloverETL (4.9) and CloverDX (5.0 or newer) implementation problems

uykusuzzzz
Posts: 3
Joined: Thu Apr 23, 2020 7:35 pm

How to do lists with MySQL native-ish-ly

Postby uykusuzzzz » Sun Apr 25, 2021 6:46 pm

Hello,

I have a particular workflow that uses lists quite heavily. It works fine but when it comes to stuffing things to MySQL in the end, there is no way (that I could find) to write and then later read them back from MySQL. so I am just nulling it on the MySQL side at the moment but is there an easy way to get this to work?

Disclaimer: I am lazy. I don't want to have to create shadow copies of all my metadata where the list fields are normal strings and I concat to write or read from db and split later into proper lists. I kind of want to have this work as-is. I am happy to fiddle with drivers, extra jar files and what not if there is such a solution.

Cheers,
uykusuZzZz

cholastal
Posts: 142
Joined: Tue Sep 01, 2015 1:22 pm

Re: How to do lists with MySQL native-ish-ly

Postby cholastal » Tue May 11, 2021 3:15 pm

Hi uykusuZzZz,

It appears the MySQL database does not support any type of list containers or similar. That is why it doesn't work if you try to insert a CloverDX list into the database. As this is a limitation of the MySQL database there is nothing we can do from the CloverDX side to make this work "as is".

You will need to create a workaround. One way might be to use Reformat component to concatenate the values of the list into one delimited string and insert that into the database. Nevertheless, this is not very elegant solution as the database is basically trying to force you not to use lists. Here is quite nice explanation.

Another way is to insert the values as they should be, i.e. each value to a separate row. This can be achieved with a Normalizer component within CloverDX.

When you decide to use either of the ways described above, you can create a subgraph containing all the logic required to achieve the right result and then re-use the subgraph whenever it's needed.

Let me know if you have any questions, please.

Best regards.

---
Lukas Cholasta
CloverCARE Support
CloverDX

Visit us online at http://www.cloverdx.com


cron