How to generate SQL insert with hex (blob) content for e.g. a Flyway migration script

Question

How can I generate SQL inserts with hex (blob) content for e.g. a Flyway migration script.

I can open the binary content in an editor, e.g. UltraEdit using Hex mode. However, cannot find a way to copy and paste this content to my SQL editor, e.g. MySQL Workbench. I can copy a few bytes, but the files I need are around 60k to 100k.

Suggestions please anyone?

score 2 · Answer 1 · answered Nov 14 '12 at 06:38

2

As an alternative you can keep your BLOBs either in binary or hex format, and load them with a Flyway Java-based migration.

answered Nov 14 '12 at 06:38

Axel Fontaine

34,542
16
106
137

Thanks for the reply Axel. We do not currently use Java-based migration, but I might consider that soon, if I cannot progress this without it. – MikeRoger Nov 14 '12 at 10:00
This is how we are doing it on Oracle. Similar mechanism for MySQL I hope https://stackoverflow.com/questions/43758759/how-to-insert-clob-blob-data-using-flyway/45104219#45104219 – johnm Jul 14 '17 at 14:01
.. without a java migration.. just stored procedures – johnm Jul 14 '17 at 14:11

score 2 · Answer 2 · answered Apr 15 '15 at 22:33

From within MySQL Workbench 6.2 I went to Server->Data Export in the top menu. I selected the objects that what I wanted. Next I selected Export to Self-Contained File. Finally, I clicked on the Advanced Options button at the upper right side and then checked the hex-blob option.

Once I exported, the exported file had the INSERTS that I wanted with the hex blobs.

score 1 · Answer 3 · answered Nov 13 '12 at 15:58

1

Try DataExport feature in dbForge Studio for MySQL. Click 'Export Data' command in popup menu on table in Database Explorer, select fields and rows you need to export, and press 'Export' button; you will get SQL script with INSERT statements.

answered Nov 13 '12 at 15:58

Devart

119,203
23
166
186

Thanks for the reply Devart. We do not use dbForge Studio, but I may try it shortly if I cannot make progress. – MikeRoger Nov 14 '12 at 09:59

MikeRoger · Accepted Answer · 2012-11-14T16:41:07.003

After talking to colleagues here, tried this quick process and verified it works OK:-

Using MySQL Workbench, right click on the blob field and "Load Value From File" with the new content.
On command line (DOS or Cygwin), use the mysqldump utility, e.g. mysqldump -u root -psomestring --hex-blob my_schemaname my_tablename > destination_file.sql
Open resulting destination_file.sql, copy and paste hex content strings to Flyway script.

QED

How to generate SQL insert with hex (blob) content for e.g. a Flyway migration script

4 Answers4