1.0.3 • Published 3 years ago
node-red-contrib-mssql-mac v1.0.3
node-red-contrib-mssql-mac
A Node-RED node to execute queries, stored procedures and bulk inserts in Microsoft SQL Server and Azure Databases SQL2000 ~ SQL2019.
Importantly, this package comes with pre-built linux drivers for communicating with the Azure & MS SQL services (using TDS protocol), removing the need to set-up environment level MSSQL (or similar) drivers.
Features include...
- Connect to multiple SQL Servers and Azure databases from SQL2000 ~ 2019
- Perform multiple queries in one go & get back multiple recordsets (depends on the queries sent)
- Supports Stored Procedure execute
- Supports Bulk Insert
- Built in examples (node-red hamburger menu → import → examples → node-red-contrib-mssql-plus)
- TVP - A demo of calling a stored procedure and passing in a table valued parameters
- BULK - A demo of inserting a large amount of data in bulk mode
- Use env vars in the config node for all fields (including credentials). e.g...
- Server
{{{SQL_IP}}}
- Password
{{{SQL_PW}}}
- Server
- Use mustache in your SQL queries including msg, flow and global context. e.g...
SELECT TOP {{{payload.maxRows}}} * FROM [MyTable] WHERE Name = '{{{flow.name}}}' AND quantity <= {{{global.maxQty}}}
- View the final query (mustache rendered into values) in
msg.query
to understanding what happened to your {{{mustache}}} parameters
- Enter parameters in the UI or send parameters in via
msg
,flow
orglobal
variables for use in your SQL queries e.g...SELECT * FROM [MyTable] WHERE Name = @name AND quantity <= @maxQty
- View the final parameters (rendered with final values) in
msg.queryParams
that were used in the query to aid debugging - View output parameters values in
msg.queryParams
after the query has executed
- Choose between throwing an error to the catch node or outputting an error property in
msg.error
- Additional properties are in the msg object (use a debug node with "complete msg object" set to see whats available)
Install
Easiest
Use the Manage Palette > Install option from the menu inside node-red
Harder
Alternatively in your Node-RED user directory, typically ~/.node-red, run
npm install node-red-contrib-mssql-mac
Usage
Please refer to the built in help in the info panel in node red.
## Other
This node based on [node-red-contrib-mssql](https://github.com/redconnect-io/node-red-contrib-mssql).