UPDATED: node-red-contrib-mssql-plus 0.7.0 (beta) Bulk Insert support

Steve-Mcl · 11 December 2020 02:10

UPDATE 2021/05/11 V0.7.0 now published to NPM / Flows library.

Original thread below...

Hello all,

Firstly - I would greatly appreciate beta testers and feedback

If anyone is dumping lots of data into a table, please give the beta a go & feedback.
This will allow me to be confident of merging these changes into the main branch and release.

About

I have now published ~~V0.7.0-beta.2~~ ~~V0.7.0-beta.3~~ V0.7.0-beta.4 to NPM - node-red-contrib-mssql-plus - npm

This has numerous changes but most importantly, BULK INSERT mode is now implemented,
Also, the MSSQL library is updated to V7.0.0-alpha (which importantly updates the tedious driver to v9 (tedious v9 has many improvements over v6.x that mssql v6.x currently depends on))

Install method...

stop node-red
open a cmd/terminal and cd to your node-red folder (often cd ~/.node-red )
enter npm i node-red-contrib-mssql-plus@0.7.0-beta.4
run node-red

Alternative method for latest version of node-red...

Download the the package tgz
Open the pallet manger & install using the upload module button...
restart node-red

Info

I have tested inserting 500000 in one go on an RPI3b with 1GB RAM.
I ran it over 200 times over the course of 2 days & memory did climb from 200MB peaking at 500MB but then settles at around 340MB - no leak (leaking memory is partly the reason I added bulk insert mode)

Changes...

layout improvements
add trustServerCertificate param
overhaul promises and add bulk mode (using async/await)
fix query typedInput width (layout improvement)
improve built in docs
spelling, comments, corrections
Add new demo BULK
update readme to reflect new version changes
update TVP demo to use same CN object as bulk demo
added table column options (readOnly, primary, identity, nullable) for bulk import
minor version bump (beta) 0.7.0-beta.4
update mssql to latest 7.0.0-beta.3

berzezo · 4 May 2021 11:10

Hello Steve,
Thanks for the great work.
Could you please provide an example of bulk insert query mode in the query editor?
Thanks in advance.

Steve-Mcl · 4 May 2021 11:14

Hi.

Have you installed the latest beta.4?
Have you tried the built in demo?

CTRL+I

Steve-Mcl · 4 May 2021 11:15

NOTE: I would greatly appreciate your feedback on the bulk feature (good or bad)

Thanks.

berzezo · 4 May 2021 11:32

Sorry but after installing I can't find the examples:

I used the alternative install method.

Steve-Mcl · 4 May 2021 11:33

Did you install the beta version?
Have you refreshed the browser?
Have you restarted node-red after updating to beta version?

what does palette manager show?

berzezo · 4 May 2021 11:42

Yes I installed beta version above:

I used the alternative method (download, and upload the package in the package manager).
Yes, I refreshed and restarted.

Steve-Mcl · 4 May 2021 11:54

Odd, do you have other MSSQL contrib nodes installed?

Can you check in ~/.node-red/node_modules/node-red-contrib-mssql-plus/

there should be an examples folder.

berzezo · 4 May 2021 13:05

I had the mssql nodes before, but I uninstalled as the documentation suggested.
There is no "node-red-contrib-mssql-plus/" folder here...

Steve-Mcl · 4 May 2021 13:12

Where is node-red installed?
Is this node-red on docker or a home assistant plugin?

Post the terminal output from node-red start up.

Also, please answer other question...

berzezo · 4 May 2021 13:33

I had the mssql nodes before, but I uninstalled as the documentation suggested before I installed the mssql-plus.

Node-red is running on windows 10.

Terminal output:

> Welcome to Node-RED
> ===================
> 
> 4 May 15:05:37 - [info] Node-RED version: v1.2.7
> 4 May 15:05:37 - [info] Node.js  version: v14.15.4
> 4 May 15:05:37 - [info] Windows_NT 10.0.17763 x64 LE
> 4 May 15:05:37 - [info] Loading palette nodes
> 4 May 15:05:38 - [info] Dashboard version 2.28.1 started at /ui
> 4 May 15:05:38 - [info] Settings file  : C:\Users\zoltan.berzsenyi\.node-red\settings.js
> 4 May 15:05:38 - [info] Context store  : 'default' [module=memory]
> 4 May 15:05:38 - [info] User directory : \Users\zoltan.berzsenyi\.node-red
> 4 May 15:05:38 - [warn] Projects disabled : editorTheme.projects.enabled=false
> 4 May 15:05:38 - [info] Flows file     : \Users\zoltan.berzsenyi\.node-red\flows_HU-VE-C0066.json
> 4 May 15:05:38 - [info] Server now running at http://127.0.0.1:1880/
> 4 May 15:05:38 - [warn]
> 
> ---------------------------------------------------------------------
> Your flow credentials file is encrypted using a system-generated key.
> 
> If the system-generated key is lost for any reason, your credentials
> file will not be recoverable, you will have to delete it and re-enter
> your credentials.
> 
> You should set your own key using the 'credentialSecret' option in
> your settings file. Node-RED will then re-encrypt your credentials
> file using your chosen key the next time you deploy a change.
> ---------------------------------------------------------------------
> 
> 4 May 15:05:38 - [info] Starting flows
> 4 May 15:05:40 - [info] Started flows

Steve-Mcl · 4 May 2021 13:40

So, look in \Users\zoltan.berzsenyi\.node-red\node_modules\node-red-contrib-mssql-plus\ is there an examples folder in there?

berzezo · 4 May 2021 13:48

There is no such a folder (node-red-contrib-mssql-plus)

Steve-Mcl · 4 May 2021 13:57

Hmm, this must be an issue due to using the upload tgz method. I'll check it out later.

In the mean time, the demo is here

copy all text and import using CTRL+I

Please remember to feedback on your experiences.

Ta.

berzezo · 4 May 2021 14:04

Thanks for your help.
I'll give a feedback as I can test it.

berzezo · 4 May 2021 14:19

I have a syntax error for this flow:

Columns:

{"MachineNo":"MachineNo","MacType":"MacType","MachineName":"MachineName","Date":"Date","PowerOn":"PowerOn","AlarmState":"AlarmState","LimitState":"LimitState","ProgramStopState":"ProgramStopState","FeedHoldState":"FeedHoldState","STMState":"STMState","RunState":"RunState","CuttingState":"CuttingState","SetUp":"SetUp","NoOperator":"NoOperator","Wait":"Wait","Maintenance":"Maintenance","Other":"Other","ManualMode":"ManualMode","MDIMode":"MDIMode","AutoMode":"AutoMode","MacManMode":"MacManMode","ToolSetMode":"ToolSetMode","OriginSetMode":"OriginSetMode","ParameterSetMode":"ParameterSetMode","ProgramOpeMode":"ProgramOpeMode","ProgramFileName":"ProgramFileName","AlarmLevel":"AlarmLevel","AlarmNo":"AlarmNo","AlarmCode":"AlarmCode","AlarmString":"AlarmString","AlarmMessage":"AlarmMessage","AlarmObject":"AlarmObject","PowerOnTime":"PowerOnTime","AlarmStateTime":"AlarmStateTime","AlarmRunStateTime":"AlarmRunStateTime","LimitStateTime":"LimitStateTime","ProgramStopStateTime":"ProgramStopStateTime","FeedHoldStateTime":"FeedHoldStateTime","STMStateTime":"STMStateTime","RunStateTime":"RunStateTime","CuttingTime":"CuttingTime","SpindleCMDTime":"SpindleCMDTime","TotalActivePower":"TotalActivePower","SetUpTime":"SetUpTime","NoOperatorTime":"NoOperatorTime","WaitTime":"WaitTime","MaintenanceTime":"MaintenanceTime","OtherTime":"OtherTime","ManualModeTime":"ManualModeTime","MDIModeTime":"MDIModeTime","AutoModeTime":"AutoModeTime","MacManModeTime":"MacManModeTime","ToolSetModeTime":"ToolSetModeTime","OriginSetModeTime":"OriginSetModeTime","ParameterSetModeTime":"ParameterSetModeTime","ProgramOpeModeTime":"ProgramOpeModeTime","FeedOverride":"FeedOverride","SpindleOverride":"SpindleOverride"}

Rows (msg.payload):

[{"MachineNo":"1","MacType":"0","MachineName":"LU45II","Date":"2021/03/29 15:08:00","PowerOn":"1","AlarmState":"0","LimitState":"1","ProgramStopState":"0","FeedHoldState":"0","STMState":"0","RunState":"1","CuttingState":"0","SetUp":"0","NoOperator":"0","Wait":"0","Maintenance":"0","Other":"0","ManualMode":"0","MDIMode":"0","AutoMode":"1","MacManMode":"0","ToolSetMode":"0","OriginSetMode":"1","ParameterSetMode":"0","ProgramOpeMode":"0","ProgramFileName":"R-41654-FO.MIN","AlarmLevel":"0","AlarmNo":"0","AlarmObject":"0","PowerOnTime":"60000","AlarmStateTime":"0","AlarmRunStateTime":"0","LimitStateTime":"60000","ProgramStopStateTime":"0","FeedHoldStateTime":"0","STMStateTime":"0","RunStateTime":"60000","CuttingTime":"0","SpindleCMDTime":"60000","TotalActivePower":"0","SetUpTime":"0","NoOperatorTime":"0","WaitTime":"0","MaintenanceTime":"0","OtherTime":"0","ManualModeTime":"0","MDIModeTime":"0","AutoModeTime":"60000","MacManModeTime":"0","ToolSetModeTime":"0","OriginSetModeTime":"60000","ParameterSetModeTime":"0","ProgramOpeModeTime":"0","FeedOverride":"0","SpindleOverride":"100"},{"MachineNo":"1","MacType":"0","MachineName":"LU45II","Date":"2021/03/29 15:09:00","PowerOn":"1","AlarmState":"0","LimitState":"1","ProgramStopState":"0","FeedHoldState":"0","STMState":"0","RunState":"1","CuttingState":"0","SetUp":"0","NoOperator":"0","Wait":"0","Maintenance":"0","Other":"0","ManualMode":"0","MDIMode":"0","AutoMode":"1","MacManMode":"0","ToolSetMode":"0","OriginSetMode":"1","ParameterSetMode":"0","ProgramOpeMode":"0","ProgramFileName":"R-41654-FO.MIN","AlarmLevel":"0","AlarmNo":"0","AlarmObject":"0","PowerOnTime":"60000","AlarmStateTime":"0","AlarmRunStateTime":"0","LimitStateTime":"60000","ProgramStopStateTime":"0","FeedHoldStateTime":"0","STMStateTime":"0","RunStateTime":"60000","CuttingTime":"0","SpindleCMDTime":"60000","TotalActivePower":"0","SetUpTime":"0","NoOperatorTime":"0","WaitTime":"0","MaintenanceTime":"0","OtherTime":"0","ManualModeTime":"0","MDIModeTime":"0","AutoModeTime":"60000","MacManModeTime":"0","ToolSetModeTime":"0","OriginSetModeTime":"60000","ParameterSetModeTime":"0","ProgramOpeModeTime":"0","FeedOverride":"0","SpindleOverride":"100"}]

Thanks in advance.

Steve-Mcl · 4 May 2021 17:11

Could you share your table as a CREATE script and I'll test it.

Steve-Mcl · 4 May 2021 17:29

Actually, i suspect that this is due to there are more columns than data items.

If you have no value for certain columns, add them with a value of null

e.g. if your columns were

{
  "MachineNo": "MachineNo",
  "MacType": "MacType",
  "MachineName": "MachineName"
}

and supply the data...

[
  {
    "MachineNo": "1",
    "MacType": "0",
    "MachineName": "LU45II"
  },
  {
    "MachineNo": "1",
    "MacType": "0"
  }
]

i think you will get this (unhelpful) error.

instead, if you have no value for MachineName, send null...

[
  {
    "MachineNo": "1",
    "MacType": "0",
    "MachineName": "LU45II"
  },
  {
    "MachineNo": "1",
    "MacType": "0",
    "MachineName": null
  }
]

berzezo · 6 May 2021 16:59

Thanks for the suggestions.

In the meantime I managed to pass something to SQL database, but all the values are NULLs.

Steve-Mcl · 6 May 2021 17:18

Can you share your table as a create script, a sample of data (10 rows will do) & flow (the SQL node will do - use ctrl+e to export)

Remember to paste code/data into the reply between backticks

SQL...
```
crate table ....
```

Data sample...
```
[{"machine": "1", ...
```

Flow...
```
[{"id": "aba1àc:64f98", ...
```

Topic		Replies	Views
UPDATE: node-red-contrib-mssql-plus V0.2.1 Developing Nodes	1	675	24 July 2019
Bulk Insert into MSSQL-Plus Node General	2	167	25 October 2023
Node-RED 0.20.0-beta.4 released News	24	4293	27 May 2020
UPDATED: node-red-contrib-mssql-plus 0.5.0 Share Your Nodes	1	394	14 October 2020
[SOLVED] Node-red-contrib-mssql-plus General	6	3768	27 September 2019

UPDATED: node-red-contrib-mssql-plus 0.7.0 (beta) **Bulk Insert support**