MSSQL - 'Insert' Missing basic functionality (null-handling, escape quotes, string-processing)

Problem
The Microsoft SQL Server node shows major issues when performing basic ‘insert’ into the db processes.

Summary

  1. Empty cells generate an error, I think this is due to the fact that it should pass a NULL value, but instead it passes an empty string.
  2. Special characters like single quotes are not escaped. When you have a value that contains a single quote it will generate an error. Instead it should escape this by passing two single quotes ‘’.
  3. It generates errors when part of a string contain only numbers and the letter ‘e’. It interprets this as an exponential number which is too large for the CPU, however, it is a string.\

Reference
I have posted detailed steps to reproduce, error logs and places in the code to sole this error in these Github issues:

Conclusion
Since we rely on the ‘insert’ functionality of the MSSQL Server node this makes N8N highly difficult to use for us in our operations. All our datasets contain one of these issues which at the moment resulted in not being able to deploy even a single Workflow for our clients. I hope this quickly gets resolved.

Welcome to the community @laurens-novyx!

Sorry to hear that you have issues. Looks like the first PR for one of those issues got already opened last night:

@laurens-novyx two out of the three were fixed. Will tackle the one left hopefully today.

@laurens-novyx I have not been able to replicate the issue with the UUID. What am I missing?

That is a good question. For me, the value comes from a function that follows a HTTP Request:

You can see the output data of the function, the row that causes the error is visible here.
Perhaps the type of Node that process the SQL Server node causes the difference.

what type is that column in your database? varchar as well? Cuz if in your database, the column it’s a GUID type that might be the issue.

No, that isn’t the issue, I checked and the ID column is a NVARCHAR(255).

Screenshot 2021-06-23 at 18.54.43

Come to think of it. I remember seeing this error:
"The floating point value ‘5767e365’ is out of the range of computer representation (8 bytes)."
…earlier in the function node itself. In my current workflow it shows up as an error in MS SQL but in another workflow I saw it in the Function Node. It might be a core N8N error instead of specific for MS SQL.
(I am not able to reproduce my earlier workflow, unfortunately.)

When I look at StackOverflow I see that people seem to get the same error when inserting GUIDs in SQL Server with the wrong type of quotes:

The floating point value is out of the range of computer representation (8 bytes) - Google Suche.

Could it be that the condition…
typeof val === ‘string’
…does not resolve to ‘true’?

I have never seen that error in the function node, and it’s hard to fix it in the SQL Server node without replicating it :sweat_smile:. But if I understand correctly, that error it’s not happening anymore on your side?

No, the error is still there. I can reproduce it in my SQL Server Node (and most people seem to have it in SQL Server as well, on StackOverflow.)

I don’t see it in the Function node anymore, however.

Is it possible that you share (privately) the data and the database schema that you are using? Of course, you can change the sensitive information. Also, I can create an instance for you with the changes that I made. If I do so, can you test that the other two issues are solved on your side in that instance?

That is a good idea. I am going to sleep now and am working again tomorrow.
How can I contact you to safely send over the data?

You can either send me a private message here on the community or to my email [email protected]

I have sent you an e-mail

This PR fixes the 3 issues.

1 Like

Fix got released with [email protected]

It is working greatly now!

Screenshot 2021-06-27 at 18.09.32

2 Likes