I have a small set of actions that:
- ‘Import from database’ using a Custom SQL query via a connector that is ODBC (Other SQL). The database I’m importing from is InterSystems Cache.
- ‘Database command’ set to create a table using a PostgreSQL connector.
- ‘Export to database’ to a PostgreSQL database using default settings.
In the second step, many of the text columns in my dataset are set to the Data Type ‘character varying (255)’. When I execute action 3, I get an error saying ‘Error: Export of row #13 failed with the following error: 22001: value too long for type character varying(255)’.
I would like to be able to set a default Data Type for text (and number) columns, ideally setting a Data type of ‘text’ or ‘string’ that will have no character limits, so I don’t receive this error. A manual workaround is to set each column’s data type in action 2 but this is a time consuming process.
I’ve read this article in the forum - Create Table - Incorrect date data type - however, when I try to edit the DataTypesMap.xml file and restart the application, nothing has changed from the default settings.
My questions is:
- Does setting the default data types in the XML file not work any more?
- Does the fact that I’m using a custom SQL query in (1) complicate things? (Perhaps I could cast to a different data type here).
Happy to provide further information as required.