Mysql: Bulk insert with parameterized query

[salmanm]

According to the documentation, sending array of arrays would do a bulk insert. However, when I try the same with query having multiple parameters, it fails, is there any issue with that? Is this feature already implemented?

in the meantime, I'll look at the source to find out what's wrong.

[dougwilson]

Hi @salmanm , I'm not sure how I missed your issue on here. Can you provide some more details? Perhaps a code example?

The main thing is I'm not quite sure what you mean by "when I try the same with query having multiple parameters".

Here is an example of doing a bulk insert into the test table for two columns:

var data = [[20,'test1'],[21,'test2']];
connection.query('INSERT INTO table (`id`,`value`) VALUES ?', [data], function(err, result) {
  // ...
});

[salmanm]

Hi,

The example you showed, would replace "?" with say (2, 'val 1'), (2, 'val 1') ...

but if I change the query to the following, It doesn't work.

INSERT INTO tbl (id, name, loc) VALUES(?, ?, POINT(?, ?))

The input array of arrays has same number of element, for eg. [[1, "val 1", 74.23423, 18.234234], [1, "val 1", 74.23423, 18.234234]]

Is there something I should change in the input?

[dougwilson]

Is there something I should change in the input?

Ah. Yes, bulk insert does not (yet) work for POINT values. It is on my TODO so you can call it with the values [[1, "val 1", {x: 74.23423, y: 18.234234}], [1, "val 1", {x: 74.23423, y: 18.234234}]].

[salmanm]

Oh ok. But I think without POINT also it wasn't working. It was trying to
replace only first "?" keeping rest as it is which becomes syntax error.

I'll check again.

Thanks.

-- Sent from my Android device.
On Aug 6, 2014 12:57 AM, "Douglas Christopher Wilson" <
[email protected]> wrote:

Is there something I should change in the input?

Ah. Yes, bulk insert does not (yet) work for POINT values. It is on my
TODO so you can call it with the values [[1, "val 1", {x: 74.23423, y:
18.234234}], [1, "val 1", {x: 74.23423, y: 18.234234}]].

Reply to this email directly or view it on GitHub
https://github.com/felixge/node-mysql/issues/887#issuecomment-51246894.

[dougwilson]

Ah, ok. Did you try my code out? Can you perhaps actually post JavaScript code that can reproduce it? To know I'd have to actually see how it's called, but it sounds like you don't have enough array nesting (look at my example--the array of arrays is in an array itself in the query call).

[salmanm]

Hi,

No, I am aware about the array nesting, see my code sample below. Yes, I
tried your code, and it works fine when there is one "?".

connection.query('INSERT INTO tbl (id, name) VALUES ?', [[[1, 'aaa'],[1, 'bbb']]], function(err, rows, fields) {
    if (err) throw err;
});

However, this fails, (note the parameters in the query)

connection.query('INSERT INTO tbl (id, name) VALUES _(?, ?)_', [[[1, 'aaa'],[1, 'bbb']]], function(err, rows, fields) {
if (err) throw err;
});

Array nesting looks fine here too. Ideally, it should replace each ? with
its corresponding value in the array, right?

BTW, I didn't get why the first one is made like this, I mean replacing one
? with multiple field values doesn't seem a predictable behavior.

[dougwilson]

Ah yes, I see what you're thinking now. Yes, the behavior is not very intuitive. Personally I don't like how it's so complicated and think if there are any placeholders, one ? should just always get one value and leave it as that, but it's already in this library.

But back to what you're doing: I believe it's because you messed up the SQL syntax for inserts. Try the following (also it was nested too deep in arrays for this):

connection.query('INSERT INTO tbl (id, name) VALUES (?), (?)', [[1, 'aaa'],[1, 'bbb']], function(err, rows, fields) { if (err) throw err; });

Is that what you're looking for?

[salmanm]

Kind of, but this also has a limitation, I should know how many rows I am
going to insert and put that much question marks, which is not possible. I
should just be able to give an array and the bulk insert should happen.
right?

On Wed, Aug 6, 2014 at 7:28 PM, Douglas Christopher Wilson <
[email protected]> wrote:

Ah yes, I see what you're thinking now. Yes, the behavior is not very
intuitive. Personally I don't like how it's so complicated and think if
there are any placeholders, one ? should just always get one value and
leave it as that, but it's already in this library.

But back to what you're doing: I believe it's because you messed up the
SQL syntax for inserts. Try the following (also it was nested too deep in
arrays for this):

connection.query('INSERT INTO tbl (id, name) VALUES (?), (?)', [[1, 'aaa'],[1, 'bbb']], function(err, rows, fields) { if (err) throw err; });

Is that what you're looking for?

Reply to this email directly or view it on GitHub
https://github.com/felixge/node-mysql/issues/887#issuecomment-51337740.

Thanks.

-Salman

[dougwilson]

hm, sorry, I'm just not sure what you're asking. Your question now sounds like my first answer I gave would apply here. My answer with the question mark per row was based off your comment before that where you seemed like that's what you wanted.

So, I'm just trying to say I'm just not sure what you're actually looking for here :( Can you post the query you want to use, the values array you want to give it, and the actual SQL you are expecting it to give you, maybe?

[salmanm]

Sorry if I am not able to explain it correctly. Here's the sample input and output.

Query & Input:
INSERT INTO tbl (id, name) VALUES (?, ?)

[[1, 'aaa'], [1, 'bbb'], [1, 'ccc']]

Output:
INSERT INTO tbl (id, name) VALUES (1, 'aaa'), (2, 'bbb'), (3, 'ccc')

Actually, you already answered my question when you said 1) it doesn't support POINT() and 2) it doesn't support replacing each ? with corresponding value at the same index in the input array.

If the later one is supported, I think there won't be separate effort required to support POINT, since the query INSERT INTO tbl (id, loc) VALUES (?, POINT(?, ?)) would expect [[1, 17.234, 73.2314], [2, 17.234, 73.2314]] as the input.

Thanks.

[dougwilson]

Gotcha. The main thing is we do not parse the SQL syntax, so the replacements cannot be very smart. There are actual SQL-building modules you can use if you need more advanced things.