New to the forum and relatively new to EZMorph.
The MySQL databases in my organization love binary ID’s. When importing to EZMorph, it’s not terribly bad to HEX() them so you can match identifiers when necessary. However, it’s not uncommon for us to import data where we need that referential integrity. Because binary isn’t a supported data type, we’re required to basically:
- Generate UID’s.
- Calculate a column representing the INSERT values, using UNHEX() statements.
- Construct a JSON array to combine those column results.
- Prepend the values with the INSERT columns to create a full INSERT statement.
- Iterate through the query using a dynamic query module.
- Reference this same ID downstream in other INSERT or UPDATE statements, repeating the process.
It’s both time consuming and inefficient. Is there any better method than we’ve been using and/or is there any near-term plan to extend the Export to Database action to support this? Thanks!
Hello @alexstepp, and welcome to Community!
We've discussed this internally and decided to add an option to the "Export to database" action to insert values from certain columns as-is. The user will be responsible for formatting, wrapping, and escaping values in those columns.
Will this be enough for your case? Or you would like us to add a similar option to other DB-related actions?
Hello Andrew,
I apologize that this has been sitting a while and I only recently had clear reason to come back to it.
So I think the crux of the issue is that we need to convert a UID to binary(16) in MySQL when we perform an insert. The version I'm currently running doesn't show the "as-is" option in the action. A very basic test of importing data that contains a UID we need and exporting a record back to the database gives the error:
Error: Export of row #1 failed with the following error: Column [id] contains a value which is not compatible with the Other data type
Source: action "Export to database", table "Imported table 1"
The error suggests EZM isn't even reaching the point of trying to insert because the table ID field is of type 'Other'. The 'id' field inside EZM simply shows "#Unsupported data type" and I'm not convinced it has read or carried over the actual value. There are also situations in which we need to generate the UID in EZM and then convert to a binary. I did actually try this by updating the id text to include "UNHEX(...)" but get the error:
Error: Export of row #1 failed with the following error: Data too long for column 'id' at row 1
Source: action "Export to database", table "Imported table 1"
I'm wondering if that second option would work with the as-is option. In any case, binary ID needs to be recognized as such and some ability to convert or wrap with UNHEX needs to be available, or we otherwise will always need to manually create insert queries as described in the original post.
Hello @alexstepp,
We haven't implemented the option to export columns as-is yet. But it's on our roadmap for one of the following releases. The option should allow you to insert values such as "UNHEX(...)". As for importing BINARY values, EasyMorph currently doesn't support binary types, so the error that you mentioned appears in the dataset instead of the actual value. But you can work around this by adding an expression that casts the column to a text like this:
