I'm trying to issue a bulk row insert using node-mysql, and normally this is not an issue, but I need to execute a MySQL function on one of the columns.
Normally I would execute something like this and be fine:
INSERT INTO table1 (super_repo, reference, path, sub_repo, commit)
VALUES ?
ON DUPLICATE KEY
UPDATE sub_repo = VALUES(sub_repo), commit = VALUES(commit)
But, in this case, the 'commit' column is a BINARY field, so I need to execute the MySQLUNHEX() on the commit string so it stores it properly in the database.
Passing the module this SQL throws the Error: ER_PARSE_ERROR
INSERT INTO table1 (super_repo, reference, path, sub_repo, commit)
VALUES (?, ?, ?, ?, UNHEX(?))
ON DUPLICATE KEY
UPDATE sub_repo = VALUES(sub_repo), commit = VALUES(commit)
I'm guessing this doesn't work as it doesn't tell the MySql module that I am trying to insert multiple rows. Is there a way I can get this to work? Or alternatively, is there a node equivalent of doing the MySQL UNHEX() so that I can use the first SQL query above?
I'm doing this as I'm taking a 40 character SHA1 hash and condensing it into a 20 char BINARY field in MySQL.
Okay so I figured out how to do it in Node 6.x using the first query.
The values I had to pass were as so:
[[[1, 1, 1, 1, Buffer.from('<sha1>', 'hex')],[2, 2, 2, 2, Buffer.from('<sha1>', 'hex')]]]