Webhook - Reciving software and store in sql

General
1

Hello Community.

I was wondering if someone knows a software ( open source ) to use to recive webhooks and then store it to SQL.

I have been looking at Azure, Telegraph etc but they very fast get expensive and would eat all my profit :slight_smile: since i earn money on selling hardware and not software, So i was wondering if i could not make my own SQL on a PC at my home and recive the webhooks and store it. It will be used as simple notifications.

i also heard that there will be enhancment to the particle cloud but on the git page its nothing declared so not sure what the future holds for the cloud?

BR
Emil

2

Heres an example using PHP, you can call it directly instead of using a webhook if you prefer.

String path = String::format("/incoming.php?v=1&id=%s&temp=%0.2f&hum=%0.2f&dp=%0.2f&hi=%0.2f&A0=%u&A1=%u&A2=%u&A3=%u&D0=%u&D1=%u&D2=%u&D3=%u&RSSI=%i", id, temp, hum, dp, hi, analogSensors[0], analogSensors[1], analogSensors[2], analogSensors[3], digitalSensors[0], digitalSensors[1], digitalSensors[2], digitalSensors[3], WiFi.RSSI());

request.hostname = "myserver.com";
request.port = 80;
request.path = path;
http.get(request, response, headers);

PHP

$link = mysqli_connect("127.0.0.1", "my_user", "my_password", "my_db");
$stmt = $mysqli->prepare("INSERT INTO readings (`host`, `key`, `value`, `datetime`) VALUES (?, ?, ?, NOW()) ON DUPLICATE KEY UPDATE `value`=VALUES(`value`)");
$host = $_GET["id"];

foreach ( $_GET as $key => $value )
{
    if ($key == "v" || $key == "id")
        continue;

    $stmt->bind_param('ssd',$host,$key,$value);
    $stmt->execute();
}

Table

CREATE TABLE IF NOT EXISTS `readings` (
  `host` varchar(20) NOT NULL,
  `key` varchar(20) NOT NULL,
  `value` double NOT NULL,
  `datetime` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

ALTER TABLE `readings` ADD PRIMARY KEY (`host`,`key`,`datetime`);
2 Likes
3

Also, if you’re going to run your own server, you also have the option of not using a web hook at all. Your server can subscribe to the events directly using server-sent events (SSE). Once you have the the data from the events you can do whatever you want with it, including storing it in a SQL database. I do it using Java, but you can use node.js, PHP, Python, whatever you like.

1 Like
4

Thank you guys for the help.

Rickkas: Do you mean if i use the Particle local server? how do i send it from particle server to my sql, Im new to the game so not sure. Maybe its obvious if i install the local server :smile: ?

Br
Emil

5

No, using the real production Particle servers. It’s part of the Cloud API. The Events API is not only really fast, but also it only requires an outbound connection to the Particle servers. This allows it to work from a home network without a fixed IP address and without messing with a firewall or router.
https://docs.particle.io/reference/api/#events

6

aa i see. got ya :slight_smile:

Thanx.
/Emil

7

Another option you have is getting into AWS (Amazon Web Services). You can easily create webhooks to send data to their API tooling, and store data in their DynamoDB. That setup does not require any code to be running all the time (and is only charged per call, vs. time). If you just have one device sending data every few seconds, you will most likely still be below the AWS threshold for their free service (if not, you might be out a few dollars).

A good getting started guide is at : https://www.hackster.io/gusgonnet/email-notifications-using-amazon-web-services-cd2bf3. You will need to dive into AWS DynamoDB to figure out how to hook it up to the API system, but that is pretty well documented on the AWS side.

8

AWS is pretty cool, but it can get out of hand in costs, since traffic, ips, services, etc. all costs when over free tier.

Running a server does not have to cost an arm and a leg, basic logging for a few devices will run on the smallest VPS you can find.

DigitalOcean have a 5$/month VPS with 512mb memory, 20GB SSD storage and 1 TB of traffic included.
When you need more you can upgrade, and if you select to make a flexible upgrade, you only get more ram and cpu, so you can downgrade again later if needed (a permanent upgrade also gives more disk space, but then you cant downgrade quite as easy).

512MB is not alot after the OS takes up its share, but its enough to run a database and apache webserver.

1 Like
9

I dident even consider Amazon tbh. i have been looking in to it now.
As MORA say i guess even Amazon gets expensive, its so darn hard to track what the cost will be in this services.

But anyway, i tried your tutorial which is great but i ended up with a error that i cant track down.

Invalid parameter “TopicArn or TargetArn Reason: no value for required parameter”

When i do the test on the Integration Request a email is sent so i guess its something with the Method request.
As long as i can see i have followed your tutorial except another name on the SNS and API.

Here is my Webhook

{
 "event": "Reset press detected",
 "url": "https://myapi.execute-api.us-west-2.amazonaws.com/prod/email",
 "requestType": "POST",
 "headers": {
   "x-api-key": "apikey",
   "Content-Type": "multipart/form-data; boundary=----WebKitFormBoundary7MA4YWxkTrZu0gW"
},
 "query": {
   "Subject": "{{SPARK_EVENT_VALUE}}",
   "Message": "{{SPARK_EVENT_VALUE}}",
   "TopicArn": "arn:aws:sns:us-west-2:id:Photon"
},
 "mydevices": true,
 "noDefaults": true
}

And my method request looks as your guide.
Any thoughts what can be wrong? I have tried googling the error but dont understand :frowning:

/Emil