Howdy, currently I work in a project of connected hive, I am working on a program that allows me to retrieve SMS messages received from a Huawei 3G E3531 key and then insert them into the mysql database. However, after creating the program I have no idea how to automate my program, i.e. when I receive a new SMS I would like the program to automatically delete the oldest SMS that is stored in the memory of the E3531 key.
if you post the flow you have it will be easier to help suggest ways you can acihieve what you want to do.
1 Like
Does the table in the db have a time stamp of when a row is inserted?
Have you tried a google search with something like ‘sql find last row Inserted using timestamp’?
[{"id":"448aaa1d.3cec14","type":"inject","z":"12a60b70.753df5","name":"Lancer récupération SMS","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":310,"y":200,"wires":[["d8dd2de3.4dfc5"]]},{"id":"7f3fb544.b9c36c","type":"debug","z":"12a60b70.753df5","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","x":1230,"y":20,"wires":[]},{"id":"a4252fce.3d541","type":"http request","z":"12a60b70.753df5","name":"API E3531 sms-list","method":"POST","ret":"txt","url":"http://192.168.1.1/api/sms/sms-list","tls":"","x":750,"y":200,"wires":[["532d337a.09874c"]]},{"id":"d8dd2de3.4dfc5","type":"function","z":"12a60b70.753df5","name":"Requete SMS","func":"msg.headers = {'Content-Type':'text/xml'};\nmsg.payload = '<request><PageIndex>1</PageIndex><ReadCount>1</ReadCount><BoxType>1</BoxType><SortType>0</SortType><Ascending>0</Ascending><UnreadPreferred>1</UnreadPreferred></request>';\nreturn msg;","outputs":1,"noerr":0,"x":540,"y":200,"wires":[["a4252fce.3d541"]]},{"id":"532d337a.09874c","type":"xml","z":"12a60b70.753df5","name":"","property":"payload","attr":"","chr":"","x":930,"y":200,"wires":[["7f3fb544.b9c36c","8f8a45a2.4a9118","ab7f4f2.b6807b","1dfd947f.17a0dc","ca16d10a.7dfc3","2cba27b7.abd278"]]},{"id":"8f8a45a2.4a9118","type":"debug","z":"12a60b70.753df5","name":"Emetteur","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload.response.Messages.0.Message.0.Phone.0","x":1140,"y":220,"wires":[]},{"id":"ab7f4f2.b6807b","type":"debug","z":"12a60b70.753df5","name":"Index","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload.response.Messages.0.Message.0.Index.0","x":1150,"y":140,"wires":[]},{"id":"1dfd947f.17a0dc","type":"debug","z":"12a60b70.753df5","name":"Contenu","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload.response.Messages.0.Message.0.Content.0","x":1120,"y":260,"wires":[]},{"id":"ca16d10a.7dfc3","type":"debug","z":"12a60b70.753df5","name":"Date réception","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload.response.Messages.0.Message.0.Date.0","x":1160,"y":180,"wires":[]},{"id":"4ea0e7d0.c02638","type":"inject","z":"12a60b70.753df5","name":"Supprimer un SMS","topic":"","payload":"40001","payloadType":"str","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":230,"y":620,"wires":[["af1b1ff3.f862c","627a078d.e8e3d8"]]},{"id":"e5986537.f6e8a8","type":"debug","z":"12a60b70.753df5","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","x":1090,"y":620,"wires":[]},{"id":"8ac2a1f2.ba14a","type":"http request","z":"12a60b70.753df5","name":"API E3531 delete-sms","method":"POST","ret":"txt","url":"http://192.168.1.1/api/sms/delete-sms","tls":"","x":700,"y":620,"wires":[["bb0b270b.95cac8"]]},{"id":"af1b1ff3.f862c","type":"function","z":"12a60b70.753df5","name":"Requete SMS","func":"msg.headers = {'Content-Type':'text/xml'};\nmsg.payload = '<request><Index>'+msg.payload+'</Index></request>';\nreturn msg;","outputs":1,"noerr":0,"x":480,"y":620,"wires":[["8ac2a1f2.ba14a","cf13b463.2b0ea8"]]},{"id":"bb0b270b.95cac8","type":"xml","z":"12a60b70.753df5","name":"","property":"payload","attr":"","chr":"","x":870,"y":620,"wires":[["e5986537.f6e8a8"]]},{"id":"627a078d.e8e3d8","type":"debug","z":"12a60b70.753df5","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":470,"y":540,"wires":[]},{"id":"cf13b463.2b0ea8","type":"debug","z":"12a60b70.753df5","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":710,"y":560,"wires":[]}]
Please your flow to place ``` before and after, as currently you cannot import the flow into Node-RED
Hello thank you for you reply, actually, I updated my flow can you try?
Please read and respond to my previous post.
OK so if I understand your question, the real question is how do I delete the oldest message stored in the E3531?
From your flow you appear to get the list of SMS messages stored on the device?
To do anything with that list you need it as a javascript object but I would start by putting a debug node after the API E3531 sms-list node and see what that returns. As the property names you are using in your debug look very complicated and there might be a way of simplifying them.
Alternatively as the data ends up in a nested array you should be able to iterate over the array to find the oldest timestamp and then send the text messages unique-ID onto you delete flow.
I don't know, for the moment I haven't touched the database too much, however the program recovers the API time stamp from E3531(Hilink).
"From your flow you appear to get the list of SMS messages stored on the device?"
Yes the POST method is used and when I put a debug after API E3531, I have :
"<?xml version="1.0" encoding="utf-8"?>↵<response>↵→<Count>1</Count>↵→<Messages>↵→→<Message>↵→→→<Smstat>0</Smstat>↵→→→<Index>40002</Index>↵→→→<Phone>1010</Phone>↵→→→<Content>test</Content>↵→→→<Date>2018-08-01 10:17:34</Date>↵→→→<Sca></Sca>↵→→→<SaveType>4</SaveType>↵→→→<Priority>0</Priority>↵→→→<SmsType>1</SmsType>↵→→</Message>↵→</Messages>↵</response>"
help me please
Hi @Ymer_G - it may help if you restate clearly what you are trying to do and what stage you have got to. It's hard to come to back to an old thread and and read through its history to understand what the problem is.
The list of SMS messages you posted only appears to contain one message, but want to delete "the oldest message" which suggests you think that there is more than one? So I have no idea how you determine the message you want to delete from the info you have shared.
Also as the timestamp only displays down to the nearest second. What happens if you have received two messages within a second?
