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

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...
    image
  • 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
2 Likes

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.

Hi.

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

CTRL+I

image

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

Thanks.

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


I used the alternative install method.

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?
image

Yes I installed beta version above:
image

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

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.

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

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...

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

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

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

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.

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

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.

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

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
  }
]

Thanks for the suggestions.

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





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", ...
```