Doubt with "LOOP" and "DNS"

[gark81]

Hi @ChuckBell ,

I'm trying to set your library in my code, and i've been testing some examples. I have 2 DS18B20 sensors, and i wish to "connect - write to SQL- and then disconnect from database" In the final code the loop will be about 1 minute or more, so i wish to connect-write-disconnect mode Is it possible? I've test the "saveTempData" function of the example, which first connect at the setup, and it's connected ever. But i'm trying to get connected and disconnected at every loop.

Here is my code:

`//#include

include

include

include

include

include

include

include

//#include

define ONE_WIRE_BUS 2 // DS18B20 Data Pin

OneWire oneWire(ONE_WIRE_BUS); DallasTemperature sensors(&oneWire);

// MySQL

// DNS Hostname //char hostname[] = "www.google.com"; // change to your server's hostname/URL

//IPAddress server_addr; IPAddress server_addr(x,x,x,x); // MySQL SERVER char user[] = "esp8266"; // MySQL USERNAME char password[] = "esp8266"; // MySQL PASSWORD //char INSERT_DATA[] = "INSERT INTO esp8266.test (value, created) VALUES (%s, NOW() + INTERVAL 1 HOUR)"; char INSERT_DATA[] = "INSERT INTO esp8266.DS18B20 (Sensor01_Desc, Sensor01_Temp, Sensor02_Desc, Sensor02_Temp) VALUES ('%s', %s, '%s', %s)";

char query[128]; char temp01[10]; char temp02[10]; char sensor01_desc[30] = "Temperature 01"; char sensor02_desc[30] = "Temperature e"; char sensor01ID[20]; char sensor02ID[20];

// WiFi char ssid[] = "MySSID"; // SSID NAME char pass[] = "MyPassword"; // SSID PASSWORD

WiFiClient client; MySQL_Connection conn((Client *)&client);

//DNSClient dns_client; // DNS instance

void setup() { Serial.begin(115200); WiFi.begin(ssid, pass); sensors.begin();

while ( WiFi.status() != WL_CONNECTED ) { delay ( 500 ); Serial.print ( "." ); } Serial.println ( "" ); Serial.print ( "Connected to " ); Serial.println ( ssid ); Serial.print ( "IP address: " ); Serial.println ( WiFi.localIP() );

//DNS / //dns_client.begin(WiFi.dnsServerIP()); dns_client.begin(Ethernet.dnsServerIP()); dns_client.getHostByName(hostname, server_addr); Serial.println(server_addr); /

// SQL Serial.println("DB - Connecting..."); while (conn.connect(server_addr, 3306, user, password) != true) { delay(500); Serial.print ( "." ); } conn.close();

}

void loop() { // saveTempData();

SQLConexionLOOP(); }

void saveTempData() { sensors.requestTemperatures(); MySQL_Cursor *cur_mem = new MySQL_Cursor(&conn); Serial.print("Temp: "); Serial.println(sensors.getTempCByIndex(0)); Serial.print("Query: "); dtostrf(sensors.getTempCByIndex(0), 4, 2, temp01); dtostrf(sensors.getTempCByIndex(1), 4, 2, temp02); //sprintf(query, INSERT_DATA, temp01);

        sprintf(query, INSERT_DATA, sensor01_desc, temp01, sensor02_desc, temp02);

        cur_mem->execute(query);
        Serial.println(query);
        delete cur_mem;
        Serial.println("Datos guardados!");
        delay(30000);

}

// SQL LOOP void SQLConexionLOOP(){ conn.connect(server_addr, 3306, user, password);
if (conn.connect(server_addr, 3306, user, password)) { delay(1000); Serial.println("Conectado a MySQL...."); MySQL_Cursor *cur_mem = new MySQL_Cursor(&conn);

  sensors.requestTemperatures();
  dtostrf(sensors.getTempCByIndex(0), 4, 2, temp01);
  dtostrf(sensors.getTempCByIndex(1), 4, 2, temp02);

  sprintf(query, INSERT_DATA, sensor01_desc, temp01, sensor02_desc, temp02);

  cur_mem->execute(query);    

  delete cur_mem;   

  Serial.println("Datos guardados.");
  }
  else {
  Serial.println("FALLO de conexion a SQL.");
  }
conn.close();
delay(15000);

} `

Then, i wish to set DNS connection too, but i've been trying without success.. (commented lines DNS) i get "0.0.0.0" in serial monitor. But if i put my public IP connects well

Could you help me with the code?

Thanks in advance.

P.D. I've been used NodeMCU ESP-12 to test the code, but the final code i wish to run it in ESP01

And with the "saveTempData", i get "Exception (9)" at second loop (with NodeMCU)

[ChuckBell]

Hi,

On 5/25/18 10:37 AM, gark81 wrote:

In the final code the loop will be about 1 minute or more, so i wish to connect-write-disconnect mode Is it possible?

Yes.

I've test the "saveTempData" function of the example, which first connect at the setup, and it's connected ever. But i'm trying to get connected and disconnected at every loop.

Don't connect in setup. Only to it inside the loop() method.

Here is my code:

...

//DNS /* //dns_client.begin(WiFi.dnsServerIP()); dns_client.begin(Ethernet.dnsServerIP()); dns_client.getHostByName(hostname, server_addr); Serial.println(server_addr);

The DNS client will likely not work with the ESP8266, sorry. You must use IP address for the server.

// SQL LOOP void SQLConexionLOOP(){ conn.connect(server_addr, 3306, user, password); if (conn.connect(server_addr, 3306, user, password)) { delay(1000); Serial.println("Conectado a MySQL...."); MySQL_Cursor *cur_mem = new MySQL_Cursor(&conn);

|sensors.requestTemperatures(); dtostrf(sensors.getTempCByIndex(0), 4, 2, temp01); dtostrf(sensors.getTempCByIndex(1), 4, 2, temp02); sprintf(query, INSERT_DATA, sensor01_desc, temp01, sensor02_desc, temp02); cur_mem->execute(query); delete cur_mem; Serial.println("Datos guardados."); } else { Serial.println("FALLO de conexion a SQL."); } conn.close(); delay(15000); |

}

You have (2) connect calls here. You need only the one. You should also close the cursor at the end of the loop. Like this:

void loop() { Serial.println("Sleeping..."); delay(2000); Serial.println("Connecting..."); if (conn.connect(server_addr, 3306, user, password)) { delay(500); Serial.println("Running a query"); cur.execute("SHOW DATABASES"); // execute a query cur.show_results(); // show the results cur.close(); // close the cursor conn.close(); // close the connection } else { Serial.println("Connect failed. Trying again on next iteration."); } }

See the example sketch "connect_disconnnect" for this pattern.

Then, i wish to set DNS connection too, but i've been trying without success.. (commented lines DNS) i get "0.0.0.0" in serial monitor. But if i put my public IP connects well

Yes, use IP addresses.

Dr. Bell

[gark81]

Thanks Dr.Bell

I'll try your loop example (and connect-disconnect from it) it's a pity not being able to use the connection through DNS server, but if it can't be, then nothing.. :-(

Congratulations for your work!