Connection with database error

[zenofmud]

Please paste in a copy of the table's schema

You have created a database. The database has tables in it. You created a table (or more than one table ) defining it's columns. This is the schema. I want to see the defination of your table.

Please export your flow and insert it in a reply.

Click on the 'hamburger menu - top right of editor window
Click on 'Export'

select 'Current Flow'
copy and paste that into a reply

[shipra]

With previous solution given by @zenofmud invalid rfid is giving SUCCESS which is wrong i want to show FAIL there

[zenofmud]

@zenofmud

To compare data from two different places, you need to get the data into the same msg. In your case, you are sending data fron the RFID mqtt-in node to the mssql node. Put a debug node (set to displau the Complete msg object) on the output of both th e two nodes. Then you can see what the data looks like after exiting the mssql node.

I imagine that the mssql node will over write the msg.payload, so you will probably need to add a change node, between the two nodes, to copy the original msg.payload to another msg variable. Then in the output of the mssql node you should see all the information and be able to do what ever compares you need.

I told you that once you have both data in one msg you could do the compare you need to do. But at that point in time you were saying you were using mssql not mysql.

So please, if you want help, answer ALL the questions someone ask's you or they may give up helping.

If you want my continued help, please answer my previous questions or just ask me to drop out of tthis thread.

[Colin]

@shipra

With previous solution given by @zenofmud invalid rfid is giving SUCCESS which is wrong i want to show FAIL there

What does your working solution show with an invalid id?

[shipra]

this is my schema

[shipra]

[{"id":"57935e3d.9f13d","type":"mqtt out","z":"f4ce26f0.22055","name":"","topic":"","qos":"","retain":"","x":310,"y":440,"wires":}]

@zenofmud

Please export your flow and insert it in a reply.

[shipra]

I dont have that sql solution like how can i validate my rfid with database rfid no

[zenofmud]

You have exported one node from your flow. Did you read what I wrote when I asked you to export your flow?

[shipra]

ok Sorry for my mistake .now check ones.

[{"id":"f4ce26f0.22055","type":"tab","label":"Flow 2","disabled":false,"info":""},{"id":"ec3347d0.94006","type":"mqtt in","z":"f4ce26f0.22055","name":"","topic":"RFID","qos":"1","datatype":"utf8","broker":"f119edb0.e6ff1","x":130,"y":140,"wires":[["dd8ebadc.9267c"]]},{"id":"e5a55f4b.c1511","type":"debug","z":"f4ce26f0.22055","name":"rfid_debug","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":510,"y":140,"wires":[]},{"id":"dd8ebadc.9267c","type":"function","z":"f4ce26f0.22055","name":"CheckRFID","func":"msg.topic=\"SELECT rfidno FROM rfid1 WHERE rfidno='E1234561'\";\nreturn msg;\n\n/*msg.topic=\"SELECT rfidno FROM rfid1 WHERE rfidno='E1234561'\";\nif(msg.topic!='E1234562')\n  msg.topic=\"select 'CR_SUCCESS' as ' '\";\nelse\n  msg.topic=\"select 'CR_FAIL' as ' ' \";\nreturn msg;*/","outputs":1,"noerr":0,"initialize":"","finalize":"","x":270,"y":240,"wires":[["386fc529.c39e9a","ebb6bfc.62204c"]]},{"id":"386fc529.c39e9a","type":"mysql","z":"f4ce26f0.22055","mydb":"38417304.575e9c","name":"","x":470,"y":240,"wires":[["e5a55f4b.c1511"]]},{"id":"ebb6bfc.62204c","type":"debug","z":"f4ce26f0.22055","name":"checkrfid_debug","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":540,"y":380,"wires":[]},{"id":"57935e3d.9f13d","type":"mqtt out","z":"f4ce26f0.22055","name":"","topic":"","qos":"","retain":"","x":310,"y":440,"wires":[]},{"id":"f119edb0.e6ff1","type":"mqtt-broker","z":"","name":"RFID","broker":"tcp://mqtt.eclipse.org","port":"1883","clientid":"NCS_Client_03","usetls":false,"compatmode":false,"keepalive":"60","cleansession":true,"birthTopic":"","birthQos":"2","birthPayload":"","closeTopic":"","closeQos":"0","closePayload":"","willTopic":"","willQos":"0","willPayload":""},{"id":"38417304.575e9c","type":"MySQLdatabase","z":"","name":"","host":"127.0.0.1","port":"3306","db":"rfid","tz":""}]

[shipra]

check this

[zenofmud]

So why do you hard code the RFID in the SQL statement? You need to get the RFID from the output of the MQTT-in node.

To see what that data looks like, put a debug (set to display the Complete msg object) on the output of the the MQTT-in node.

[shipra]

Sorry Actually some hanging issue occuured and mssql is not getting connected with node red so i uninstall mssql and took the alternative.
Sorry @zenofmud.just drop that thread.
Thank You

[shipra]

like this you are telling

[shipra]

I want FAIL to be shown in msg.payload

[zenofmud]

Try this,
1 - remove the wire coming from the mqtt-in node to the function.
2 - add a template node and add the following to it:

SELECT rfidno FROM rfid1 
WHERE rfidno='{{payload}}';

3 - connect the mqtt-in node to the template node, connect the output of the template node to the mysql node

this will now do a query based on the data passed in from the mqtt-in node and you should be able to test a 'good' rfid` and a bad 'rfid' and look at the results.

You will need to add a switch node after the mysql node to check if the result is good or bad and then a change node to set the msg payload as you see fit.

[shipra]

@zenofmud

You will need to add a switch node after the mysql node to check if the result is good or bad and then a change node to set the msg payload as you see fit.

How to do this?

[shipra]

@zenofmud

• remove the wire coming from the mqtt-in node to the function .
  2 - add a template node and add the following to it:

SELECT rfidno FROM rfid1 
WHERE rfidno='{{payload}}';

This i have done but showing parse error.check ones

[knolleary]

@shipra

This i have done but showing parse error.check ones

You will need to configure the template node to set msg.topic as that is the property the mysql node expects the query to be in.

I then recommend you add a Debug node after the mysql node so you can see exactly what it is returning. That will help you/us identify how to configure the Switch node to test if the query found a match or not.

[zenofmud]

Start by reading the documentation about the switch node

[shipra]

Check ones