Bulk insert with POINT

[green13]

How can I use bulk insert with POINT?
I use http://stackoverflow.com/a/25086900/5607875 like example. It doesn't work.

[sidorares]

could you post full simple example ( with schema creation ) so I can try to reproduce and suggest solution?

[green13]

Server version: 10.0.22-MariaDB-1~jessie
mysql2 version: 0.15.8

Schema:

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET NAMES utf8mb4 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;

-- Dumping database structure for test
CREATE DATABASE IF NOT EXISTS `test` /*!40100 DEFAULT CHARACTER SET utf8 */;
USE `test`;


-- Dumping structure for table test.coordinates
CREATE TABLE IF NOT EXISTS `coordinates` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `location` point NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- Data exporting was unselected.
/*!40101 SET SQL_MODE=IFNULL(@OLD_SQL_MODE, '') */;
/*!40014 SET FOREIGN_KEY_CHECKS=IF(@OLD_FOREIGN_KEY_CHECKS IS NULL, 1, @OLD_FOREIGN_KEY_CHECKS) */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;

Node.js:

var mysql = require('mysql2');
var connection = mysql.createConnection({
    "host": "localhost",
    "user": "me",
    "password": "secret",
    "database": "test"
});

var values = [
    [{x: 1, y: 4}],
    [{x: 23, y: -8.345}]
];

connection.connect();

var q = connection.query('INSERT INTO coordinates (location) ?', [values], function (err) {
    if (err) {
        throw err;
    }
});
console.log("SQL:", q.sql);

connection.end();

Output:

SQL: INSERT INTO coordinates (location) ('[object Object]'), ('[object Object]')

Error: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ''[object Object]'), ('[object Object]')' at line 1
    at Packet.asError (c:\Projects\mysql2-bulk-insert\node_modules\mysql2\lib\packets\packet.js:497:13)
    at Query.Command.execute (c:\Projects\mysql2-bulk-insert\node_modules\mysql2\lib\commands\command.js:25:22)
    at Connection.handlePacket (c:\Projects\mysql2-bulk-insert\node_modules\mysql2\lib\connection.js:310:28)
    at PacketParser.onPacket (c:\Projects\mysql2-bulk-insert\node_modules\mysql2\lib\connection.js:83:65)
    at PacketParser.executeStart (c:\Projects\mysql2-bulk-insert\node_modules\mysql2\lib\packet_parser.js:39:12)
    at Socket.<anonymous> (c:\Projects\mysql2-bulk-insert\node_modules\mysql2\lib\connection.js:95:31)
    at emitOne (events.js:77:13)
    at Socket.emit (events.js:169:7)
    at readableAddChunk (_stream_readable.js:146:16)
    at Socket.Readable.push (_stream_readable.js:110:10)

[sidorares]

yes, it's currently not supported by this library ( and by node-mysql )
I'll try to port mysqljs/mysql#877 once it gets merged

[green13]

Thanks for clarification.

[beeeku]

@sidorares Any fix for this issue ?

[sidorares]

I'm pretty sure POINT is supported now - have a look at ebd30fd and #93 . Have you tried recently? Any issues?