# Connection with database error

**URL:** <https://discourse.nodered.org/t/connection-with-database-error/30172>\
**Category:** General\
**Created:** [16 July 2020 06:36 UTC](https://discourse.nodered.org/t/connection-with-database-error/30172 "2020-07-16T06:36:54Z")\
**Posts on this page:** 20\
**Page:** 3

<div class="post-metadata">

**Author:** ![zenofmud](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/zenofmud/32/316_2.png) [@zenofmud](https://discourse.nodered.org/u/zenofmud)\
**Post date:** [17 July 2020 10:26 UTC](https://discourse.nodered.org/t/connection-with-database-error/30172/42 "2020-07-17T10:26:58Z")

</div>

> 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'  
 ![Screen Shot 2020-07-17 at 6.24.26 AM](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/2/1/2142094843b4ad0e28bb91ce4f258a86bb978251.png)

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

---

<div class="post-metadata">

**Author:** ![shipra](https://avatars.discourse-cdn.com/v4/letter/s/71c47a/32.png) [@shipra](https://discourse.nodered.org/u/shipra)\
**Post date:** [17 July 2020 10:28 UTC](https://discourse.nodered.org/t/connection-with-database-error/30172/43 "2020-07-17T10:28:00Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![zenofmud](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/zenofmud/32/316_2.png) [@zenofmud](https://discourse.nodered.org/u/zenofmud)\
**Post date:** [17 July 2020 10:33 UTC](https://discourse.nodered.org/t/connection-with-database-error/30172/44 "2020-07-17T10:33:35Z")

</div>

> [@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.

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [17 July 2020 10:34 UTC](https://discourse.nodered.org/t/connection-with-database-error/30172/45 "2020-07-17T10:34:10Z")

</div>

> [@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?

---

<div class="post-metadata">

**Author:** ![shipra](https://avatars.discourse-cdn.com/v4/letter/s/71c47a/32.png) [@shipra](https://discourse.nodered.org/u/shipra)\
**Post date:** [17 July 2020 10:35 UTC](https://discourse.nodered.org/t/connection-with-database-error/30172/46 "2020-07-17T10:35:25Z")

</div>

this is my schema ![w3](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/1/9/1906b6b509c916067b892f17185df8a1fc4f9220.png)

---

<div class="post-metadata">

**Author:** ![shipra](https://avatars.discourse-cdn.com/v4/letter/s/71c47a/32.png) [@shipra](https://discourse.nodered.org/u/shipra)\
**Post date:** [17 July 2020 10:36 UTC](https://discourse.nodered.org/t/connection-with-database-error/30172/47 "2020-07-17T10:36:18Z")

</div>

[{"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.

---

<div class="post-metadata">

**Author:** ![shipra](https://avatars.discourse-cdn.com/v4/letter/s/71c47a/32.png) [@shipra](https://discourse.nodered.org/u/shipra)\
**Post date:** [17 July 2020 10:40 UTC](https://discourse.nodered.org/t/connection-with-database-error/30172/48 "2020-07-17T10:40:01Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![zenofmud](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/zenofmud/32/316_2.png) [@zenofmud](https://discourse.nodered.org/u/zenofmud)\
**Post date:** [17 July 2020 10:40 UTC](https://discourse.nodered.org/t/connection-with-database-error/30172/49 "2020-07-17T10:40:14Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![shipra](https://avatars.discourse-cdn.com/v4/letter/s/71c47a/32.png) [@shipra](https://discourse.nodered.org/u/shipra)\
**Post date:** [17 July 2020 10:41 UTC](https://discourse.nodered.org/t/connection-with-database-error/30172/50 "2020-07-17T10:41:58Z")

</div>

ok Sorry for my mistake .now check ones.

```auto
[{"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":""}]

```

---

<div class="post-metadata">

**Author:** ![shipra](https://avatars.discourse-cdn.com/v4/letter/s/71c47a/32.png) [@shipra](https://discourse.nodered.org/u/shipra)\
**Post date:** [17 July 2020 10:48 UTC](https://discourse.nodered.org/t/connection-with-database-error/30172/51 "2020-07-17T10:48:26Z")

</div>

![s](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/d/d/dd0fac2f87b3dfaabcfdb321db28d61f7f5f5a37.png)

check this

---

<div class="post-metadata">

**Author:** ![zenofmud](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/zenofmud/32/316_2.png) [@zenofmud](https://discourse.nodered.org/u/zenofmud)\
**Post date:** [17 July 2020 10:48 UTC](https://discourse.nodered.org/t/connection-with-database-error/30172/52 "2020-07-17T10:48:49Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![shipra](https://avatars.discourse-cdn.com/v4/letter/s/71c47a/32.png) [@shipra](https://discourse.nodered.org/u/shipra)\
**Post date:** [17 July 2020 10:51 UTC](https://discourse.nodered.org/t/connection-with-database-error/30172/53 "2020-07-17T10:51:07Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![shipra](https://avatars.discourse-cdn.com/v4/letter/s/71c47a/32.png) [@shipra](https://discourse.nodered.org/u/shipra)\
**Post date:** [17 July 2020 10:54 UTC](https://discourse.nodered.org/t/connection-with-database-error/30172/54 "2020-07-17T10:54:19Z")

</div>

like this you are telling

 ![ss](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/6/d/6d9ddac04b8cfae71088be1c288e41d4e62ed57d.png)

---

<div class="post-metadata">

**Author:** ![shipra](https://avatars.discourse-cdn.com/v4/letter/s/71c47a/32.png) [@shipra](https://discourse.nodered.org/u/shipra)\
**Post date:** [17 July 2020 10:56 UTC](https://discourse.nodered.org/t/connection-with-database-error/30172/55 "2020-07-17T10:56:43Z")

</div>

I want FAIL to be shown in msg.payload

---

<div class="post-metadata">

**Author:** ![zenofmud](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/zenofmud/32/316_2.png) [@zenofmud](https://discourse.nodered.org/u/zenofmud)\
**Post date:** [17 July 2020 10:57 UTC](https://discourse.nodered.org/t/connection-with-database-error/30172/56 "2020-07-17T10:57:50Z")

</div>

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:

```auto
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.

---

<div class="post-metadata">

**Author:** ![shipra](https://avatars.discourse-cdn.com/v4/letter/s/71c47a/32.png) [@shipra](https://discourse.nodered.org/u/shipra)\
**Post date:** [17 July 2020 11:03 UTC](https://discourse.nodered.org/t/connection-with-database-error/30172/57 "2020-07-17T11:03:25Z")

</div>

> [@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?

---

<div class="post-metadata">

**Author:** ![shipra](https://avatars.discourse-cdn.com/v4/letter/s/71c47a/32.png) [@shipra](https://discourse.nodered.org/u/shipra)\
**Post date:** [17 July 2020 11:07 UTC](https://discourse.nodered.org/t/connection-with-database-error/30172/58 "2020-07-17T11:07:01Z")

</div>

> [@zenofmud](#):
>
> - remove the wire coming from the `mqtt-in` node to the `function` .  
> 2 - add a `template` node and add the following to it:
> 
> ```auto
> SELECT rfidno FROM rfid1 
> WHERE rfidno='{{payload}}';
> 
> ```

This i have done but showing parse error.check ones

 ![c](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/f/a/fa3d4fb86e8f61721fea580a9fbbdc66d463d43c.png)

---

<div class="post-metadata">

**Author:** ![knolleary](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/knolleary/32/3_2.png) [@knolleary](https://discourse.nodered.org/u/knolleary)\
**Post date:** [17 July 2020 11:08 UTC](https://discourse.nodered.org/t/connection-with-database-error/30172/59 "2020-07-17T11:08:17Z")

</div>

> [@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.

---

<div class="post-metadata">

**Author:** ![zenofmud](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/zenofmud/32/316_2.png) [@zenofmud](https://discourse.nodered.org/u/zenofmud)\
**Post date:** [17 July 2020 11:11 UTC](https://discourse.nodered.org/t/connection-with-database-error/30172/60 "2020-07-17T11:11:04Z")

</div>

Start by reading the documentation about the [switch node](https://nodered.org/docs/user-guide/nodes#switch)

---

<div class="post-metadata">

**Author:** ![shipra](https://avatars.discourse-cdn.com/v4/letter/s/71c47a/32.png) [@shipra](https://discourse.nodered.org/u/shipra)\
**Post date:** [17 July 2020 11:11 UTC](https://discourse.nodered.org/t/connection-with-database-error/30172/61 "2020-07-17T11:11:13Z")

</div>

Check ones ![1](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/4/a/4ae06b3b4e680a901e21688f4750ff8a7cf034ad.png)

[Previous page](https://discourse.nodered.org/t/connection-with-database-error/30172.md?page=2)

[Next page](https://discourse.nodered.org/t/connection-with-database-error/30172.md?page=4)