HTTP request missing consistently

I’m trying to use an HTTP GET request to communicate with SQL Server Express 2014. Using the code below, which is supposed to hit my database every 4 seconds if everything works correctly, I’m only seeing a successful entry to the database every minute or so.

I’ve been working on this for quite a while now and have been on the spark community forums and found the SPARK_WLAN_Loop(); from this thread.

My code here:

byte server[] = { 
  192, 168, 1, 8 }; // Google
int location = 1;
int voltage = 1;
int ampere = 1;

unsigned long lastUpdate = millis();
void setup()
{
  pinMode(7, OUTPUT);
  digitalWrite(7, HIGH);
  Serial.begin(9600);
  SPARK_WLAN_Loop();
}

void loop()
{
  digitalWrite(7, HIGH);
  // while(!Serial.available()) SPARK_WLAN_Loop();

  Serial.println("connecting...");

  if (millis() - lastUpdate > 4000){
    //SPARK_WLAN_Loop();
    if (client.connect(server, 80))
    {
      Serial.println("connected");
      client.print("GET /go/LogArduinoData.aspx?Location=");
      client.print(location);
      digitalWrite(7, LOW);
      client.print("&Voltage=");
      client.print(voltage);
      client.print("&Ampere=");
      client.println(ampere);

      client.println("Host: 192.168.1.8");
      client.println("Content-Length: 0");
      // client.println();
      Serial.println("Sent");
    }
    else
    {
      Serial.println("connection failed");
    }
  }

  if (client.available())
  {
    char c = client.read();
    Serial.print(c);
  }

  if (!client.connected())
  {
    Serial.println();
    Serial.println("disconnecting.");
    client.stop();
    // client.flush();
    delay(4000);
  }
}

My database shows

ID	TimeStamp	        LocationVoltage	Ampere
90917	2014-12-14 20:33:39.127	1	1	1
90916	2014-12-14 20:32:30.470	1	1	1
90915	2014-12-14 20:31:52.787	1	1	1
90914	2014-12-14 20:30:44.130	1	1	1
90913	2014-12-14 20:28:36.007	1	1	1
90912	2014-12-14 20:27:27.390	1	1	1
90911	2014-12-14 20:26:38.230	1	1	1
90910	2014-12-14 20:25:51.153	1	1	1
90909	2014-12-14 20:24:42.520	1	1	1
90908	2014-12-14 20:23:43.780	1	1	1
90907	2014-12-14 20:22:47.447	1	1	1
90906	2014-12-14 20:21:38.730	1	1	1
90905	2014-12-14 20:20:42.413	1	1	1
90904	2014-12-14 20:19:33.680	1	1	1
90903	2014-12-14 20:19:10.560	1	1	1
90902	2014-12-14 20:18:05.430	1	1	1
90901	2014-12-14 20:17:08.787	1	1	1
90900	2014-12-14 20:16:00.083	1	1	1

My serial monitor shows the spark trying to connect but only some made it through:

disconnecting.
connecting...
connected
Sent
Dconnecting...
connection failed

disconnecting.
connecting...
connection failed

disconnecting.
connecting...
connection failed

disconnecting.
connecting...
connection failed

disconnecting.
connecting...
connection failed

disconnecting.
connecting...
connection failed

disconnecting.
connecting...
connection failed

disconnecting.
connecting...
connection failed

disconnecting.
connecting...
connection failed

disconnecting.
connecting...
connection failed

disconnecting.
connecting...
connection failed

disconnecting.
connecting...
connected
Sent
Dconnecting...
connection failed

disconnecting.
connecting...
connection failed

disconnecting.
connecting...
connection failed

disconnecting.
connecting...
connection failed

disconnecting.
connecting...
connection failed

disconnecting.
connecting...
connection failed

disconnecting.
connecting...
connected
Sent
Dconnecting...
connection failed

Any suggestions on getting the spark to communicate on 4 second intervals? Thanks!

You should check because you have forgot the instruction client.flush(); just before Serial.println("Sent");
without this commande, the request is not sent to the server

It’s also better to add a client.stop(); at the end of your request.

That didn’t work.

TCPClient client;
byte server[] = { 
  192, 168, 1, 8 }; // Google
int location = 1;
int voltage = 1;
int ampere = 1;

unsigned long lastUpdate = millis();
void setup()
{
  pinMode(7, OUTPUT);
  digitalWrite(7, HIGH);
  Serial.begin(9600);
 // SPARK_WLAN_Loop();
}

void loop()
{
  digitalWrite(7, HIGH);
  // while(!Serial.available()) SPARK_WLAN_Loop();

  Serial.println("connecting...");

  if (millis() - lastUpdate > 4000){
   // SPARK_WLAN_Loop();
    if (client.connect(server, 80))
    {
      Serial.println("connected");
      client.print("GET /go/LogArduinoData.aspx?Location=");
      client.print(location);
      digitalWrite(7, LOW);
      client.print("&Voltage=");
      client.print(voltage);
      client.print("&Ampere=");
      client.println(ampere);

      client.println("Host: 192.168.1.8");
      client.println("Content-Length: 0");
      // client.println(); 
      client.stop();
      client.flush();
      
      Serial.println("Sent");
    }
    else
    {
      Serial.println("connection failed");
    }
  }

  if (client.available())
  {
    char c = client.read();
    Serial.print(c);
  }

  if (!client.connected())
  {
    Serial.println();
    Serial.println("disconnecting.");
   // client.stop();
    // client.flush();
    delay(2000);
  }
}

connection failed

disconnecting.
connecting…
connection failed

disconnecting.
connecting…
connection failed

disconnecting.
connecting…
connection failed

disconnecting.
connecting…
connection failed

disconnecting.
connecting…
connection failed

disconnecting.
connecting…
connection failed

disconnecting.
connecting…
connection failed

disconnecting.
connecting…
connection failed

disconnecting.


I let it run for 15 minutes and it never hit the db.

I think you will need the client.println() that you have commented out as that tells the server that you have finished sending the headers. Also client flush must be before the stop and there needs to be a 400ms delay before the stop.

Ok thanks for your replies, I did notice my 5 second loop was only affecting half of my void loop(). I now have the entire void loop() run on the 5 second timer.

TCPClient client;
byte server[] = { 
  192, 168, 1, 8 }; // Google
int location = 1;
int voltage = 1;
int ampere = 1;

unsigned long lastUpdate = millis();
void setup()
{
  pinMode(7, OUTPUT);
  digitalWrite(7, HIGH);
  Serial.begin(9600);
  SPARK_WLAN_Loop();
}

void loop()
{
    digitalWrite(7, HIGH);
  if (millis() - lastUpdate > 5000){
    // while(!Serial.available()) SPARK_WLAN_Loop();

    Serial.println("connecting...");


    // SPARK_WLAN_Loop();
    if (client.connect(server, 80))
    {
      Serial.println("connected");
      client.print("GET /go/LogArduinoData.aspx?Location=");
      client.print(location);
      digitalWrite(7, LOW);
      client.print("&Voltage=");
      client.print(voltage);
      client.print("&Ampere=");
      client.println(ampere);

      client.println("Host: 192.168.1.8");
      client.println("Content-Length: 0");
      client.println("Content-Length: 0");
      // client.println(); 
      client.stop();
      client.flush();
      
      Serial.println("Sent");
    }
    else
    {
      Serial.println("connection failed");
    }


    if (client.available())
    {
      char c = client.read();
      Serial.print(c);
    }

    if (!client.connected())
    {
      Serial.println();
      Serial.println("disconnecting.");
   
      delay(2000);
    }
  }
}

This seems to work now.

connected
Sent

disconnecting.
connecting...
connected
Sent

disconnecting.
connecting...
connected
Sent

disconnecting.
connecting...
connected
Sent

disconnecting.
connecting...
connected
Sent

disconnecting.
connecting...
connected
Sent

disconnecting.
connecting...
connected
Sent

disconnecting.
connecting...
connected
Sent

disconnecting.
connecting...
connected
Sent

disconnecting.
connecting...
connected
Sent

disconnecting.
connecting...
connected
Sent

disconnecting.
connecting...
connected
Sent

disconnecting.
connecting...
connected
Sent

disconnecting.
connecting...
connected
Sent

disconnecting.
connecting...
connected
Sent

disconnecting.
connecting...
connected
Sent

disconnecting.
connecting...
connected
Sent

disconnecting.
connecting...
connected
Sent

disconnecting.
connecting...
connected
Sent

disconnecting.
connecting...
connected
Sent

disconnecting.
connecting...
connected
Sent

disconnecting.
connecting...
connected
Sent

disconnecting.
connecting...
connected
Sent

disconnecting.
connecting...
connected
Sent

The database reports almost every 5 seconds now.

ID	TimeStamp	Location	Voltage	Ampere
91316	2014-12-15 07:17:39.253	1	1	1
91315	2014-12-15 07:17:33.620	1	1	1
91314	2014-12-15 07:17:27.973	1	1	1
91313	2014-12-15 07:17:22.323	1	1	1
91312	2014-12-15 07:17:20.293	1	1	1
91311	2014-12-15 07:17:14.640	1	1	1
91310	2014-12-15 07:17:09.007	1	1	1
91309	2014-12-15 07:17:03.357	1	1	1
91308	2014-12-15 07:16:57.727	1	1	1
91307	2014-12-15 07:16:52.090	1	1	1
91306	2014-12-15 07:16:46.450	1	1	1
91305	2014-12-15 07:16:44.403	1	1	1
91304	2014-12-15 07:16:38.737	1	1	1
91303	2014-12-15 07:16:33.097	1	1	1
91302	2014-12-15 07:16:27.460	1	1	1

Hootie I’ll add in your suggested

      client.println();
      client.flush();
      delay(500);
      client.stop();

Thanks.

1 Like

Glad you got it sorted… just looking at it again, this should be a while loop, otherwise you will only read 1 char before it carries on… and you need to do it before the flush and stop

Well I take that back. I’m loading the same code today. Nothing’s changed and the spark will not communicate at all.
This is from the Serial Monitor today:

disconnecting.
connecting...
connection failed

disconnecting.
connecting...
connection failed

disconnecting.
connecting...
connection failed

disconnecting.
connecting...
connection failed

disconnecting.
connecting...
connection failed

disconnecting.
connecting...
connection failed

disconnecting.
connecting...
connection failed

disconnecting.
connecting...
connection failed

disconnecting.
connecting...
connection failed

disconnecting.
connecting...
connection failed

Here’s something odd though. If I allow:

while(!Serial.available()) SPARK_WLAN_Loop();

to run by uncommenting and then comment out the

if (millis() - lastUpdate > 5000){
}

then it works perfectly. I can’t leave it like this because I wont have any way to hit the keyboard to start the program, the spark will be inside an enclosed box.

TCPClient client;
byte server[] = { 
  192, 168, 1, 8 }; // Google
int location = 1;
int voltage = 1;
int ampere = 1;

unsigned long lastUpdate = millis();
void setup()
{
  pinMode(7, OUTPUT);
  digitalWrite(7, HIGH);
  Serial.begin(9600);
  SPARK_WLAN_Loop();
}

void loop()
{
    digitalWrite(7, HIGH);
  //if (millis() - lastUpdate > 5000){
     while(!Serial.available()) SPARK_WLAN_Loop();

    Serial.println("connecting...");


    // SPARK_WLAN_Loop();
    if (client.connect(server, 80))
    {
      Serial.println("connected");
      client.print("GET /go/LogArduinoData.aspx?Location=");
      client.print(location);
      digitalWrite(7, LOW);
      client.print("&Voltage=");
      client.print(voltage);
      client.print("&Ampere=");
      client.println(ampere);

      client.println("Host: 192.168.1.8");
      client.println("Content-Length: 0");
      client.println("Content-Length: 0");
      // client.println(); 
      client.stop();
      client.flush();
      
      Serial.println("Sent");
    }
    else
    {
      Serial.println("connection failed");
    }


    if (client.available())
    {
      char c = client.read();
      Serial.print(c);
    }

    if (!client.connected())
    {
      Serial.println();
      Serial.println("disconnecting.");
   
      delay(2000);
    }
 // }
}

Here’s the Serial from that run:

disconnecting.
connecting...
connected
Sent

disconnecting.
connecting...
connected
Sent

disconnecting.
connecting...
connected
Sent

disconnecting.
connecting...
connected
Sent

disconnecting.
connecting...
connected
Sent

disconnecting.
connecting...
connected
Sent

disconnecting.
connecting...
connected
Sent

disconnecting.
connecting...
connected
Sent

disconnecting.
connecting...
connected
Sent

And from the Database:

ID	TimeStamp	Location	Voltage	Ampere
94361	2014-12-15 18:18:46.203	1	1	1
94360	2014-12-15 18:18:44.140	1	1	1
94359	2014-12-15 18:18:42.033	1	1	1
94358	2014-12-15 18:18:39.647	1	1	1
94357	2014-12-15 18:18:37.587	1	1	1
94356	2014-12-15 18:18:35.310	1	1	1
94355	2014-12-15 18:18:33.203	1	1	1
94354	2014-12-15 18:18:31.010	1	1	1
94353	2014-12-15 18:18:28.767	1	1	1
94352	2014-12-15 18:18:26.707	1	1	1
94351	2014-12-15 18:18:24.553	1	1	1
94350	2014-12-15 18:18:22.247	1	1	1

So why does the while(!Serial.available()) SPARK_WLAN_Loop(); make the spark work? When I comment it out I get nothing(yesterday I did but now I don’t).

I believe the spark to be unreliable! Or maybe the one I have is defective. I don’t know but many hours have been burnt to find out the same code works sometimes and not others with no changes in environment, so what is changing here? Here is the code that’s working at the moment:

TCPClient client;
byte server[] = { 
  192, 168, 1, 8 }; 
int location = 1;
int voltage = 1;
int ampere = 1;

unsigned long lastUpdate = millis();
void setup()
{
  pinMode(7, OUTPUT);
  digitalWrite(7, HIGH);
  Serial.begin(9600);
}

void loop()
{
    digitalWrite(7, HIGH);
  if (millis() - lastUpdate > 5000){
       lastUpdate = millis();
   SPARK_WLAN_Loop();

    Serial.println("connecting...");

    if (client.connect(server, 80))
    {
      Serial.println("connected");
      client.print("GET /go/LogArduinoData.aspx?Location=");
      client.print(location);
      digitalWrite(7, LOW);
      client.print("&Voltage=");
      client.print(voltage);
      client.print("&Ampere=");
      client.println(ampere);

      client.println("Host: 192.168.1.8");
      client.println("Content-Length: 0");
      client.println("Content-Length: 0");
      client.println(); 
      client.flush();
      delay(400);
      client.stop();
     
      Serial.println("Sent");
    }
    else
    {
      Serial.println("connection failed");
    }


    if (client.available())
    {
      char c = client.read();
      Serial.print(c);
    }

    if (!client.connected())
    {
      Serial.println();
      Serial.println("disconnecting.");
   
      delay(2000);
    }
  }
}

I just rebooted it and no connection at all - while it ran through the loop for 10 minutes. Is anyone else experiencing code that works and then rebooting and the same code doesn’t work?

Same code not working now…

Yep i think a lot of us have had issues with the TCP client, its very temperamental. but with a bit of trial and error some of us have good working code that does the job. A lot of people are really looking forward to see how the photon will perform.

Try this code i quickly modded of yours… the only thing i have added that will make a difference to the connection is a 400ms delay right before the connect call. however there are some important changes below that, i commented them in the code… see how it goes. only other thing i can suggest is collating 4 lots of data then only using the client every 20 seconds or so and see how it goes.

TCPClient client;
byte server[] = { 
  192, 168, 1, 8 }; 
int location = 1;
int voltage = 1;
int ampere = 1;

unsigned long lastUpdate = millis();

void setup()
{
  pinMode(D7, OUTPUT);
  digitalWrite(D7, HIGH);
  Serial.begin(9600);
}

void loop()
{
    digitalWrite(D7, HIGH);
  if (millis() - lastUpdate > 5000){
       lastUpdate = millis();
   SPARK_WLAN_Loop();

    Serial.println("connecting...");
    delay(400);
    if (client.connect(server, 80))
    {
      Serial.println("connected");
      client.print("GET /go/LogArduinoData.aspx?Location=");
      client.print(location);
      digitalWrite(7, LOW);
      client.print("&Voltage=");
      client.print(voltage);
      client.print("&Ampere=");
      client.println(ampere);

      client.println("Host: 192.168.1.8");
      client.println("Content-Length: 0");  //this says there is 0 bytes after the header section
      client.println();  //this empty line signals end of the header section
      while( client.available()==0 && millis()-lastUpdate<2000) { //two second timeout should do for local server
          }  //wait here for the servers response (ie 200OK)
        while (client.available()) //while there is data available print 1 char at a time
        {
        char c = client.read(); 
        Serial.print(c); //comment out this line only if you dont want the response printed 
        }
      client.flush(); //there shouldnt be any data left once we get here but just flush to be sure
      delay(400); //this helps sometimes not sure why
      client.stop(); 
     
      Serial.println("Sent");
    }
    else
    {
      Serial.println("connection failed");
      delay(400); //this helps sometimes not sure why
      client.stop(); 
    }

  }
}

I suspect there is a layer 2 issue regarding arp in the CC3000. I’ve seen a similar issue on my local lan, the spark can only connect to my server if I ping the spark from the server first. This somehow teaches the spark the mac address of the server and packets start to flow as long as the arp stays fresh. If you let it expire (2 min typically), it will stop working agin until I ping back. Theres a new CC3000 update available that claims to have some arp issues fixed, I’m testing it but seeing bad connectivity to the api causing it to lose subscription and restart a lot, note sure if its due to the update or problems on the cloud their end.

Thanks for your replies. It seems to work 40% of the time if I let it sit for 20 minutes. Not sure why the timing is like this.

Hootie81 thanks for your suggestions and input. Good to know it might not be all me. I will definitely put in your changes.

1 Like

I was messing around with some code and decided to put my “get request” code into the spark TCP client example.

TCPClient client;
byte server[] = { 192, 168, 1, 8 }; // Google
int location = 333;
int Irms = 222;
int Irms2 = 444;
void setup()
{
  // Make sure your Serial Terminal app is closed before powering your Core
  Serial.begin(9600);
  // Now open your Serial Terminal, and hit any key to continue!
  while(!Serial.available()) SPARK_WLAN_Loop();

  Serial.println("connecting...");


  if (client.connect(server, 80))
  {
          Serial.println("connected");

       Serial.println(millis());
      client.print("GET /go/LogArduinoData.aspx?Location=");
      client.print(location);
      client.print("&Voltage=");
      client.print(Irms);
      client.print("&Ampere=");
      client.println(Irms2);
      
    client.println("Content-Length: 0");
    client.println();
  }
  else
  {
    Serial.println("connection failed");
  }
}

void loop()
{
  if (client.available())
  {
    char c = client.read();
    Serial.print(c);
  }

  if (!client.connected())
  {
    Serial.println();
    Serial.println("disconnecting.");
    client.stop();
    for(;;);
  }
}

I noticed that as the setup loop ran once, it immediately wrote to my SQL server 2014 database. So the question is why does this code write immediately to my DB but my original code takes 20 minutes to make a successful DB write?

I’m not a veteran programmer so how can I make the code above write every 10 seconds to my DB? Obviously to continually write we’d have to get the loop that writes the “get” out of the setup void function and into the void loop first of all.

#include "SemonLib20.h"                   // Include Emon Library
TCPClient client;

byte server[] = {
  192, 168, 1, 8 };

int location = 16;
int voltage = 2;
int amps = 3;
unsigned long lastUpdate = millis();

void setup()
{
  pinMode(7, OUTPUT);
  digitalWrite(7, HIGH);
  Serial.begin(9600);
  SPARK_WLAN_Loop();
}

void loop()
{
  

  digitalWrite(7, HIGH);
  if (millis() - lastUpdate > 6000){ 
    lastUpdate = millis();
    delay(200);

    if (client.connect(server, 80))
    {
      Serial.println(millis());
      client.print("GET /go/LogArduinoData.aspx?Location=");
      client.print(location);
      client.print("&Voltage=");
      client.print(voltage);
      client.print("&Ampere=");
      client.println(amps);
      client.println("Host: 192.168.1.8");
      client.println("Content-Length: 0");  //defining 0 bytes after the header
      client.println();                     //signals end of header

      while( client.available()==0 && millis()-lastUpdate<2000) { //two second timeout should do for local server
        //wait here for the servers response (ie 200OK)
        char c;
        c = client.read(); 
        //Serial.print(c);                    //comment out this line only if you dont want the response printed 
      }

      client.flush();
      delay(200);
      client.stop();
      Serial.println("SQL Sent");
      digitalWrite(7, LOW);               //delay to see the LED
      delay(50);

    }
  }
}

Thanks!