SQLite node possible bug, how to workaround?

General
1

Hi Everyone,

I've spend the last couple of days getting to know the SQLite node and attempting to apply it to a use case I'm trying to solve. Sadly, it seems a possible bug is thwarting my efforts, please see:

Is there a way I can workaround this issue, I'm really keen to use the functionality of the prepared node (it's clean and allows me oversight of my query), perhaps it's even user error and not a bug!

Help would be appreciated.

Thanks :slight_smile:

2

Have you tried converting the value you are inserting to a string before passing it to the node?

3

Yes, I tried...

var chat_id = 1111111111111111
chat_id.toString()

No change sadly...

4

It isn't appending .0 it has just made a guess at how many decimal places you might want. Use toFixed().

5

Hi Colin,

I assumed my understanding wasn't correct - thanks for clearing that up. I'll try your suggestion now.

Why do I not see this behaviour when running the same query via other tools?

6

I guess that the node is using toString() to convert it. What other tool are you using that does something different? Note that if you use a command line client for example then you are passing it a string, not a number.

7

I tried command line and some Mac SQLite applications to run the same query, never had the same issue, regardless, that's not proof there's an issue, merely an observation.

Further, it only happens with 'long' numbers, like the one in my example I posted to GitHub, I've not quite tracked down at what point it starts to add the '.0'

8

I tried this and the prepared node:

var chatid = 1111111111111111
chatid.toFixed(0)
node.error(chatid)

msg.params = {
    $message_id:1111,
    $user_id:1111,
    $chat_id:chatid,
    $verification_status:1111
}

return msg

chat_id shows in the the table as: 1.11111111111111e+15 :frowning:

9

Note that storing it with a trailing .0 is not incorrect. It is a perfectly valid representation of the number. If you read it back out again and convert it to a number you will end up with the number you started with. Javascript does not have the concept of integers and floats, they are all just numbers.

10

So, 111111111111 becomes 111111111111.0
1111111111111111 becomes 1.11111111111111e+15

P.S. I'm not a coder, so feel free to slap me :rofl:

11

Are you seeing that with .toString(). I don't. This in a function node displays the numbers as they are entered.

x = 11111;
node.warn(x.toString())
x = 11111111;
node.warn(x.toString())
x = 11111111111;
node.warn(x.toString())
x = 111111111111;
node.warn(x.toString())
x = 11111111111111;
node.warn(x.toString())
x = 1111111111111111;
node.warn(x.toString())
return msg;

What version of nodejs are you using?
node -v

12

No, I don't see it with the function node and your example.

I'm running the latest docker which includes Node v8.15.1

13

It's worth noting I only see the behaviour with the 'prepared' node, it works fine if I inject the query via the msg.topic or used the 'fixed' node.

14

You said you saw it with toString().

15

Argh, only when used with the node, i.e. to build the msg.params...

To be clear, debug terminal never shows an issue, only the final value at the table is wrong.

I assume the issue occurs between the 'prepared' node receiving the msg.parms, and converting them, before it applies the query. Hence my assumption that it's an issue with the SQLite node somewhere (a pretty uneducated assumption, but it's all I have!).

16

So if you put a function node to convert it with toString() and then feed that into the node it doesn't put in the db the string generated by toString()?

17

Well, the msg.params is built with a function node, so I'm not sure I can do much more, I tried toFixed() and toString() there.

My example is in the GitHub issue, it's pretty simple if you want to try (I have a feeling it's a JS thing that I don't understand...).

18

To save me getting the whole flow can you just post the function please? I presume you have put a debug on the output of that to see what it is showing.

19

What is the reason, you are using a number for _id?
It will be much more safe if you generate unique string. Yes that may fail as random is random but this is rare and can be predicted.

Something like
var id = "chat_"+Math.random().toString(36).substr(2, 6)+"."+Math.random().toString(36).substr(3, 7);

20

Just looked back at your code and see

The second line converts it to a string but doesn't save it. You need
chatid = chatid.toFixed(0)

1 Like