MSSQL node - Incorrect syntax near ','

General
1

I am trying to write some data in SQL server via MSSQL node. I connected the node to the server and in the flow correctly, written the query in editor mode, but when I deploy the flow, it sends back the error: “Incorrect syntax near ‘,’.”

My query:

INSERT INTO [dbo].[TEST_NodeRed]
            ([Sch1],
            [Sch12],
            [Sch16]
            )      VALUES
            ({{{payload.Sch1}}},
            {{{payload.Sch12}}},
            {{{payload.Sch16}}}
            )

I first tried to insert query via function node, but it worked even worse than now.

The error occurs once every 10 s.

Thanks in advance.

2

Did you connect a debug node and check the actual statement ? ie. are all values populated ?

3

missing 'quotes’ for string variables?

4

Yes I did. I tried it also with only one column and one value and it works, but when I try to put in whole object (84 elements) it returns the error. I tried also splitting one MSSQL node into 4 to reduce traffic going through one node, and it doesn’t write the data simultaneously into database.

5

There are no string variables. object values are all float numbers

6

Can you try set this setting?

Then the error is send to the output and you see the query which is failing

grafik

7

Well it sounds like you are missing values (occasionaly) ?

8

I made a function node that checks if the object has all the keys in it. If not, it adds the key, with value 0, so I don’t know how would there be missing values.

9

You better believe it: a database does not generate incorrect errors. Do you perhaps have a trailing , for the last key/value (when you generate a missing one) ?

10

I don’t see anywhere any extra ,. I mean the node is writting data in database it’s just that every 10-20 s this error will come up, then it recconects to server and continue writting data.

11

I think anyone trying to investigate this error probably needs to see the query as it is passed to the database, ie after substitution of those payload properties. An example that inserts without error and one that fails

12

This is the query, that gets inserted in node (at least what I can copy):

INSERT INTO [dbo].[TEST_NodeRed]
           ([Pum4002], [Pum600], [Pum800], [Met], [Pot45-2], [Sch], [Sch2], [Sax], [Pot45], [Rya], [Sua], [Sua125], [Wal], [Any], [Sua100], [Cor], [Kik], [Rap2], [Kas8_6], [KasHB250], [Teh], [BpvA32], [BpvC33], [MazA16], [MCFV2080C230], [GoeB61], [GoeA33], [GoeA35], [MCFVA11], [GoeA37], [GoeB41], [MCFVA13], [GoeA41], [GoeB43], [MCFVA14], [GoeB42], [GoeC36], [TosB33], [GoeC42], [GoeC43], [TosA21], [GoeC44], [PorC61], [Kas113], [RefA36], [RefA42], [KasA144], [RefU12], [Taj214], [Kas115], [Ann], [FSQA22], [Meb116], [FsqC22], [RcIbaB37], [TOB], [MatB35], [MatB36], [MatC24], [MatC25], [MazA15], [Fav600], [MatA23], [HvrDoo], [Sek], [StrM1Pa45], [StrM63], [Nil], [Pum400], [Ras], [Sch1], [Sch12], [Sch16], [Gka3v], [Guk1p], [Ilr], [Mas], [Rap22], [Tos], [Uni], [Wot1], [Wot2], [KasA3_3], [Peg])
     VALUES
           (7.725311279296875, 9.633787155151367, 8.745548248291016, 15.323267936706543, 2.598156690597534, 1.4337043762207031, 0.010342753492295742, 4.4619...
13

You can set the debug node to output to the system console, which will be a file somewhere. I guess you are running Node-red on a PC so I don't know where this file will be.

Alternatively you could use a Write File node to save the query into a file, and thus get to see the whole query.

14

Dont use mustache. It is slower, can lead to syntax issues in the final query (especially if values are strings) and in the worst case can open the door to SQL injection hacks.

Use the built in parameter mapping.

image
image721×676 33.7 KB

  1. use @named parameters
  2. map from msg (or whatever) to as type and named parameter
  3. disable mustache
  4. use driver output
1 Like