MSSQL node imports "xxx... & ... " to "xxx... & ..."

General
1

Hi, MSQL node converts the "&" into "&" in a text field.

Here is the code in the MSSQL node:

INSERT INTO [mrs_Work]
(PrjNo, ProjectName)
VALUES (
'{{{payload.PrjNo}}}',
'{{{payload.ProjectName}}}'
)

The inbound field msg.payload.ProjectName contains - for instance" Integrated Plan & Schedule - which then is stored in MSSQL as Integrated Plan & Schedule

How can I stop this conversion?

2

If you are using node-red-contrib-mssql-plus you can setup parameters in the UI & use them in the query...

INSERT INTO [mrs_Work]
(PrjNo, ProjectName)
VALUES (
  @PrjNo,
  @ProjectName
)

Better still, create a stored procedure & pass in parameters.

See the 1st screenshot in the readme

3

Thank you! I will try it and report back. But I am using node node-red-contrib-mssql-plus.

4

@Steve-Mcl - found my problem - mybad, I had a text string inserted with '{{x}}', instead of '{{x}}}' in the node.

I am doing bulk'ish upload, 9300 records, did not know there were a beta solution - I came over the problem by batching in batches of 1000 - once you come ou of beta - I will use that rather. the 10,000 records are growing ...

5

Bulk insert is out of beta. Are you running v0.7.1?

Also, are the updates/inserts coming from free text? Using mustache is vulnerable to SQL injection hacks, you would be better off taking my advice about using parameters or stored procedure.

6

Just upgraded to 0.7.1 - okay - did not know - let me refactor to use Bulk - right now I package an array of 1000 objects, then through them at a Spit note - feed them into the MSSQL node - one at a time, 2 seconds later the next 1000, and so on, but still getting niggling errors.

Let me see how to do that - and get rid of mustaches....

7

I download from our ERP into a spreadsheet, then use the Excel node to package it into a json Array, then point that to the 1000' breaker upper....

8

Yuk. What a kerfuffle.

Doesn't the ERP have a callable endpoint to avoid the whole excel part?

9

Yes, it has, but IT does not want to let me in .... 8-( Still busy convincing them. IN the mean time - I must run a business.,.....

1 Like
10

I never understand that. If there is a http endpoint that has been coded correctly there is nothing your application can do to break the ERP.

Bloody IT. :wink:

11

Funny, examples not showing....

No Examples

12

Where is it running? Have you refreshed browser after update?

13

Okay to download in a spreadsheet.... but not an end-point - as then I have access to the data. Wait until they find out I am pushing it into SQL .... from the spreadsheet.......

14

Closed browser now, re-opened, still not working. Safari in Mac. MS SQL on a NAS on Ubuntu - in Docker container.... Sweat solution .....

15

Most odd.

You can get examples here

16

Now not sure how I will use BULK. Can I point a 10,000 msg.payload formatted in JSON, array with 10,000 objects at it too? Do not want to download a spreadsheet from ERP, then by hand convert that into CSV, with all the problems with commas in fields, and then point a CSV to the MSSQL node. But let me look into your examples to see if such solutions exists.

17

If formatted correctly yes.

Have a look at example.

18

FLIPPEN HELL !!!! Ran the Bulk demo now, IT IS BLAZINGLY FAST !!!

2 Likes
19

Okay - got your BULK thing working, deleted reams and reams of work-around flows I had to build. Thank you Steve.

1 Like