I’m trying to create database tables from my Metadata that I gained from a MS SQL database. The new tables will be going into a MySQL database. The table creation succeeds and no errors are reported. However as I use my DBInputTable from MSSQL and throw it at a DBOutputTable for MySQL I’m getting errors for data truncation because the field length on the MySQL table wasn’t set large enough from CloverETL.
I’ve gone into the Metadata to see what length was given there and it’s correct. However when I look at the DDL for the Create Table statement, it’s set a length of 80 rather then 100 provided from the Metadata. I’ve attached two pictures showing both the metadata window as well as the ddl from CloverETL.
Anyone have any ideas on why it’s creating different structures? There are more fields it’s doing this for but I’ve only attached one example.
solution is quite simple: After extracting metadata from MS SQL, switch type of resulting metadata to “fixed” or “mixed” (see attached picture). Subsequent database table creation will then take sizes of string fields into account.
Thank you for showing me that feature as that’s not something I even realized I could play with. Changing it to either fixed or mixed seems to give me proper lengths on the individual fields it does however seem to create an incorrect DDL now in being able to generate the tables… Basically in MSSQL you’re allowed to have CHAR’s that specify a length of 500, 2000. MySQL however says no and wants you to use either TEXT or BLOB for the field types. Is there a different route I should be going for this? I’ve attached a screenshot of what it is I’m talking about and receiving error wise.
You can simply change CHAR to VARCHAR in the generated Create table statement. This should resolve your issues. I have raised this with CloverETL development team as bug CLD-3045 to actually use VARCHAR instead of CHAR by default.
