0

My intention is to upload a blob data into MySQL/MariaDB table field automatically every day. I want to use SQL scheduled events for that. But I cannot sort out the SQL query. The original file is on the web, but even for my local machine, I can't find a query...

When I use the "Add event" in phpMyAdmin, I can browse the file on my local machine. And this is the code which I get automatically generated:

INSERT INTO `items` (`id`, `data`) 
VALUES (NULL, 0x5b7b0d0a202020202276616c31223a2031312c0d0a202020202276616c32223a2031322c0d0a202020202276616c33223a2031330d0a7d2c207b0d0a202020202276616c31223a2032312c0d0a202020202276616c32223a2032322c0d0a202020202276616c33223a2032330d0a7d2c207b0d0a202020202276616c31223a2033312c0d0a202020202276616c32223a2033322c0d0a202020202276616c33223a2033330d0a7d5d);

It's not like ...VALUES (NULL, "C:\data.json"); so I am a bit clueless. Any guidance/examples are greatly appreciated.

ZygD
  • 22,092
  • 39
  • 79
  • 102

1 Answers1

1

For security reasons, MySQL/MariaDB does not have any way to reach out into the disk, etc. You will have to write code in some other language (PHP/Java/...) to open the file, then perform the INSERT.

That hex looks like ordinary text ("val1": 11, utf8:19:19'...), is there some reason for using hex? In fact, it looks like JSON; is that relevant?

See also UNHEX() function. See various JSON functions. Etc.

Rick James
  • 135,179
  • 13
  • 127
  • 222
  • The first 2 sentences explained it. Thank you. That file is was custom created by myself, and yes, it's json. The hex came from the DB when it auto-generated the query. And thanks for references, however in order to use JSON functions, i first must have the file in my db :) – ZygD Feb 19 '17 at 19:05
  • Then you _probably_ need: `INSERT ... VALUES ( ..., UNHEX("5b7b0d0a202020202276616c31223a20"))`. Note that the `0x` was removed. – Rick James Feb 19 '17 at 20:03