Insert data to MSQLL

groov RIO
#1

counter-mssql
counter-mssql1172×465 42.9 KB

Need help!
I am waiting 2 groove Rio to arrive in the meantime I am working in the project that consist to capture the machine cycles and hours of operation, There are two nodes that can work in my project but for a particular reason that I don’t understand is when using both ( counter and hourglass nodes ) with mssql there is a message showing syntax error, I have used the node mssql in another projects sending vibration data to a mssql with no problem, can anyone tell me what I am doing wrong.

#2

SQL query- counter
Also I am sharing the query

#3

Welcome to the forums.

Please expand both payloads in the debug tab and take another screenshot.
Also if you can help us understand which debug tab message belongs to which debug node, that would really helpful.
With them folded up like they are, we cant see what the JSON structure is like.
Looks like the issue has nothing to do with the top 11 nodes, so the focus can be on the bottom 7?

#4

The error is because the count value is larger than what fits in the integer data type. You will need to use a BIGINT in your database to fit that number.

2 Likes
#5

Sintaxis error full screen
Sintaxis error full screen1366×768 105 KB

Yes the focus will be the 7 nodes

#6

Hi I change in my database to BIGINT but persist the syntaxis error

#7

Did you change your SQL as well?

#8

Screen shot of mssql server
Yes I change the column count to bigint

#9

So you have DECLARE @count bigint?

#10

Yes also in the mssql node I change to bigint in declare
I have been struggling almost a week and I was wondering if there is a different way to do this since it’s no working, I have try with other counters for example countula and also the same issue.

#11

The debug shows the error near ;
I would try and remove the ; after the count and place it after the insert at the end. (Or use two, one in each location).

#12

Is there a “query” field that has been put in the msg object after the MSSQL PLUS node that you can show us.

#13

Yes I have try removing it after the count, also adding it at the end in Insert but still showing the error

#14

Query mode
Query mode1366×768 110 KB

Attached the message object screen shot after the MSSQL Plus node

#15

it seems that the message flows through the MSSQL node and counts but does not send the count number to the database

#16

Have you tried just inserting the payload rather than doing the set @count thing?
Seems to be double handling and I am not sure why that’s required.
Something like this perhaps (don’t copy paste this, use it as a guide only)…

INSERT INTO ciclo (count, TimeStamp) VALUES ( ‘{{{msg.payload}}}’. CURRENT_TIMESTAMP);

EDIT, also, I know the line feed / carriage returns are probably Ok in the insert statement, but they make me uncomfortable / question if they really are Ok, so try putting in one flat line.

1 Like
#17

Your count value is not in the payload, it is in the msg object. So payload.count is nothing which shows in the query output:

image

Change your {{{payload.count}}} to just {{{count}}}.

1 Like
#18

Hi finally flow is working fine, thank you for your support Philip and Beno, it has been a great learning

Happy to made it
Happy to made it1366×768 115 KB

Cheers from Honduras, Central America

#19

@renedavid6 Could please post the final insert statement so we could see and learn what got it going.

Thanks and glad to hear you got up and running.

#20

Query
Query1366×768 79.4 KB

Hello attached the screen shots