search

bestlong / node-red-contrib-mssql-plus

A Node-RED node to read and write to Microsoft MS SQL Databases
MIT License
31 stars 18 forks source link

Bulk insert does not work. #79

Closed belgianbeer closed 7 months ago

belgianbeer commented 10 months ago

I installed Nod-RED with node-red-contrib-mssql-plus.

After installed node-red-contrib-mssql-plus, I tested the "Bulk demo.json". (BTW, this example looks not in module. I got it from repository of Github.)

"Create demo_device_parameters_table" and "Drop demo_device_parameters_table" work well. But inject "raws:1000" has error. Here is error message.

11 Sep 10:46:51 - [info] [MSSQL-CN:My SQL Server connection] Error connecting to server : sake-tank-db0-svr.database.windows.net, database : sake-tank-db0, port : 1433, user : sakesqlmaster
RequestError: Invalid string.
    at BulkLoad.done [as callback] (/var/lib/nodered1/.node-red/node_modules/mssql/lib/tedious/request.js:307:19)
    at Parser.onEndOfMessage (/var/lib/nodered1/.node-red/node_modules/tedious/lib/connection.js:2823:22)
    at Object.onceWrapper (node:events:627:28)
    at Parser.emit (node:events:513:28)
    at Readable.<anonymous> (/var/lib/nodered1/.node-red/node_modules/tedious/lib/token/token-stream-parser.js:32:12)
    at Readable.emit (node:events:513:28)
    at endReadableNT (node:internal/streams/readable:1359:12)
    at process.processTicksAndRejections (node:internal/process/task_queues:82:21) {
  code: 'EREQUEST',
  originalError: TypeError: Invalid string.
      at Object.validate (/var/lib/nodered1/.node-red/node_modules/tedious/lib/data-types/nvarchar.js:146:13)
      at RowTransform._transform (/var/lib/nodered1/.node-red/node_modules/tedious/lib/bulk-load.js:116:26)
      at Transform._write (node:internal/streams/transform:175:8)
      at writeOrBuffer (node:internal/streams/writable:392:12)
      at _write (node:internal/streams/writable:333:10)
      at Writable.write (node:internal/streams/writable:337:10)
      at Readable.ondata (node:internal/streams/readable:766:22)
      at Readable.emit (node:events:513:28)
      at Readable.read (node:internal/streams/readable:539:10)
      at flow (node:internal/streams/readable:1023:34),
  number: undefined,
  lineNumber: undefined,
  state: undefined,
  class: undefined,
  serverName: undefined,
  procName: undefined,
  precedingErrors: []
}
11 Sep 10:46:51 - [error] [MSSQL:bulk insert -> demo_device_parameters_table] Invalid string.

Simple INSERTs and SELECTs are working fine.

My environment as follows.

Am I misunderstanding something?

Steve-Mcl commented 10 months ago

Works for me

https://github.com/bestlong/node-red-contrib-mssql-plus/assets/44235289/7bd35c59-718e-4d13-8f8d-535b01cd4020

Unfortunately, I do not have access to an azure DB so I used a linux docker image

belgianbeer commented 9 months ago

I created another server (not on Azure) and prepared a docker environment. So I started the docker version of Node-RED and tested it. After starting Node-RED docker, I added "node-red-contrib-mssql-plus" and loaded and executed the demo. "Create demo_device_parameters_table" and "Drop demo_device_parameters_table" worked fine. But "bulk insert -> demo_device_parameters_table" gave the same error.

Steve-Mcl commented 9 months ago

I created another server (not on Azure)

Can you link to the exact image you used please

belgianbeer commented 9 months ago

I used the following command line when starting Node-RED:

docker run -it -p 1880:1880 -v node_red_data:/data --name mynodered nodered/node-red

It's probably https://hub.docker.com/r/nodered/node-red

Steve-Mcl commented 9 months ago

I used the following command line when starting Node-RED:

docker run -it -p 1880:1880 -v node_red_data:/data --name mynodered nodered/node-red

I was referring to Azure:

I created another server (not on Azure)

Can you link to the exact image you used please

as in when you created ANOTHER SERVER - which docker image did you use?

EDIT:

I am re-reading this again - I may be mixed up.

Are you STILL testing against the original DB (i.e. did you simply create another Node-RED) OR Did you create and test the first failing Node-RED instance against a different Azure/SQL Server?

However: Since I ss you are also using docker for Node-RED, that may be part of the problem. Are you in a position to test the bulk demo against your Azure DB with node-red running natively/on metal?

belgianbeer commented 9 months ago

I will sort out the situation.

First reported configuration:

Second reported configuration:

I can also set up a metal server if needed. I'm thinking of using the first configuration in the project I'm currently working on, so I'd really like to resolve any issues with node-red-contrib-mssql-plus.

Steve-Mcl commented 9 months ago
  • I had a Debian 11 VM on my office server that I usually use, so I installed Docker on it.

It would be very useful to know if you could install Node-RED on the VM (this should work as "on metal") and try again.

And since your VM is Debian, can I ask that you install Node-RED using the official installer script with --node18 appended to the command. see here: https://nodered.org/docs/getting-started/raspberrypi#installing-and-upgrading-node-red

belgianbeer commented 9 months ago

I have prepared a Debian Bullseye metal server. After that I installed nodejs 18.18. I installed Node-RED using the Raspberry Pi script in that URL. I ran the same test after starting Node-RED and got the same error regarding bulk insert. I will continue to try changing the nodjs version to 16.

belgianbeer commented 9 months ago

It worked! But, It wat not my mistake. The example was bad. If I use the sample as is, the type of the fifth parameter in the bulk insert flow was NVarChar(200). But I think this should be a Float.

2023-09-25 142434

belgianbeer commented 9 months ago

I created a pull request for fix this issue.

belgianbeer commented 7 months ago

Thank you!