How to POST spark data to database (MySQL)?

Getting Started
1

I’m looking to record the number of times a motion sensor has been tripped and then POSTing each occurrence + a timestamp to a database.

How would one set up a POST from the sparkcore to a database?

Thanks,
E

2

Probably the easiest way would be to get a simple PHP script that runs on the database machine (you can even use PHP 5.4s in built server if you like) and just POST or GET to the script which then passes it on to the database.

Just out of curiosity what are you going to do with the data? If you only want to see data points of when a sensor was tripped, you could use Graphite to store and graph events instead.

1 Like
3

Or alternatively, you can try using a service like Xively (xively.com). They have very simple APIs, beautiful charts and you can use it for free.

1 Like
4

I’ve gone through and done a POST from an Arduino to a MySQL database.
For Spark I’m trying a simpler version (GET) but it says ‘client’ is not declared in this scope. I think this is a library issue but how can I fix it or is there a better approach?

char server[] = "www.google.com";

void setup()
{
}

void loop()
{
    if (client.connect(server, 80)) {
    Serial.println("connecting...");
    client.println("GET /index.html HTTP/1.1");
    client.println("Host: www.google.com");
    client.println("User-Agent: Arduino/1.0");
    client.println("Content-Type: text/html;");
    client.println("Connection: close");
  } 
  else {
    Serial.println("Connection failed");
    Serial.println("Disconnecting.");
    client.stop();
  }
}

I’m planning on storing the data paired to a user. Each device is associated with a user. User can log in and retrieve their data.

Is this the Graphite you are referring to? http://graphite.wikidot.com/

5

You actually have to declare your client variable. Either put a

TCPClient client;

in the first line of your loop or even before the setup function.

6

Thanks.
Added TCPClient to the top.

Do I need to do anything specific to see the GET for the .txt file in the serial connection?
Right now I’m just getting my Serial.println’s for the first 5 seconds and then it stops.

 TCPClient client;
char server[] = "www.arduino.cc";

void setup()
{
    Serial.begin(9600);
}

void loop()
{
    Serial.println("Hello Computer");
    if (client.connect(server, 80)) {
    Serial.println("connecting...");
    client.println("GET /latest.txt HTTP/1.1");
    client.println("Host: www.arduino.cc");
    client.println("User-Agent: Arduino/1.0");
    client.println("Connection: close");
  } 
  else {
    // Serial.println("Connection failed");
    // Serial.println("Disconnecting.");
    client.stop();
  }
}
7

Yes. You have to read from the client in a loop until there is no more. Whatever you have read you have to write into the serial connection.

8

@benwa , @Stevie is correct , you’ll need to read all of the content from the server response first and then output it via serial. Since many people have been asking about this, we put together an annotated example illustrating how to go about doing this on the docs site here. If you stumble into any troubles or insights from this example, please share on this thread! Good luck!

9

For some reason I cannot submit values to my database. Calling the URL with parameters in the browser works flawlessly.

URL looks something like this: http://1.1.1.1/statussubmit.php?&ph=2&ec=1&temp=25&humidity=60&light=1&waterlvl=0

Sketch looks like this:

TCPClient client;
char server[] = "1.1.1.1";

void setup()
{
    Serial.begin(9600);
}

void loop()
{
    Serial.println("Hello Computer");
    if (client.connect(server, 80)) {
    Serial.println("connecting...");
    client.println("GET /statussubmit.php?&ph=2&ec=1&temp=25&humidity=60&light=1&waterlvl=0 HTTP/1.1");
    client.println("Host: 1.1.1.1");
    client.println("User-Agent: Arduino/1.0");
    client.println("Connection: close");
    delay(5000);
    
  } 
  else {
    // Serial.println("Connection failed");
    // Serial.println("Disconnecting.");
    client.stop();
  }
}

Any help appreciated!

10

I’ve gone about it the other way… I have Tomcat running on a raspberrypi that polls the sensor on a user defined basis… The Spark core returns a JSON object which is saved inside of Berkley DB. To be honest I could have done this with mySQL but I thought I’d try something different. Full disclosure here I work for Oracle so I live and breathe mySQL… The reason I done it this way was simply to limit the work the core has to do… I also can leave all the error handing to the server…

It all seems to work pretty well… I have a highcharts front end that simply plots all the results… I’ve tried to make it pretty generic so I can dynamically change the values on the sensor and the results continue to be stored…

I’ll tidy it up shortly and put it up in github…

11

Hmm… I already build everything around the Spark. I would apprechiate so solve it this way… Thanky anyway for your great response Don!

12

help me！！！
How to automatically submit data to the database?
Example
Distance Sensor + sparkcore
How to read the data written directly to the database?

13

statussubmit.php
can you share the source？