‘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).
The DataTypesMap.xml works correctly for me. Keep in mind that it has two entries for the PostgreSQL database. One for the native connector and another for the ODBC connector. So you have to edit the correct one. Or both.
EasyMorph doesn’t use initial datatypes of imported database columns in the following actions, so the custom SQL query in (1) doesn’t affect the following actions.
Thanks for the welcome and apologies for not coming back sooner.
I’ve edited both, just to be sure, but it doesn’t seem to be working. I’ve done several reboots of the server with no fix yet. I’m still on a month trial license but I assume that would have no limitations either.
I’ve attached a few screenshots of my issue:
screenshot of the datatypesmap.xml file
screenshot of the create table action after running.
screenshot of the export table action after running.
I see that you have the “If table already exists” setting set to the “Delete rows, keep table” option. This option leaves the schema of an existing table as is. Can you please try to switch to the “Delete table” option? Will it give you the same error?
That seems to have solved the issue. I thought I tried this but I must have been altering the Data Types radio button at the same time.
I take it from this that Data Types = Simple is completely separate from Data Types = Advanced. In that I mean when Data Types = Simple, ‘Text’ = character varying (2048) and when Data Types = Advanced, ‘Text’ is a default of character varying (255).
Now the tables have been cleared I think this solves the issue I was having.
Yes, the Simple and Advanced data types mode are completely separate. In the Advanced mode you are explicitly chooses the desired type of a database column. So the text column type should create a text database column, not a character varying column.
I just tested it on my side and it works correctly. Can you please retest it on you side? If you are actually getting the character varying column, can you please send us a screenshot of the action settings and a screenshot of the schema of the created table?
Yes, I’ve re-tested it on my side and after using the command to Delete the table to start afresh, selecting Simple and running the actions correctly sets the column to the type set in the DataTypesMap.xml file. I’m happy everything is working as intended.
