logo elektroda
logo elektroda
X
logo elektroda

[Solved] ESP8266 + mySQL on QNAP TS-251+: No connection to the database

artusiek 1068 6
ADVERTISEMENT
Treść została przetłumaczona polish » english Zobacz oryginalną wersję tematu
  • #1 17280148
    artusiek
    Level 9  
    Hello . I will describe what it is about . I want to make with the 8266 power temp and humidity sensors in different rooms with writing to a database
    I have a QNAP TS 251+ running mySQL , with a database created , user - dedicated only to this database
    database : house ; user : domtemp , pass : same (I hit) also tests without password effect same .
    Esp8266 programmed with C ( code of the example on which I learn below ) . Unfortunately ...
    With the network connects without problems , but with the base no longer .
    At first I thought that I knocked something in the database itself .... but from python the connection to the database works without problems .
    Where did I make a mistake ..... Can he help me ?
    
    #include <DHT.h>
    #include <MySQL_Connection.h>
    #include <MySQL_Cursor.h>
    #include <ESP8266WiFi.h>
    #include <WiFiClient.h>
    
    
    #define DHTPIN D6     // what digital pin we're connected to. If you are not using NodeMCU change D6 to real pin
    #define DHTTYPE DHT22   // DHT 22  (AM2302), AM2321
    
    DHT dht(DHTPIN, DHTTYPE);
    
    char ssid[] = "siec";                 // Network Name
    char pass[] = "haslo";                 // Network Password
    
    WiFiServer server(80);
    IPAddress ip(192, 168, 1, 29);
    IPAddress gateway(192, 168, 1,1);
    IPAddress subnet(255, 255, 255, 0);
    
    WiFiClient client;
    MySQL_Connection conn((Client *)&client);
    
    char INSERT_SQL[] = "INSERT INTO dom.dom_temp( temperatura , wilgotnosc) VALUES (NULL, NULL)";
    char query[128];
    
    IPAddress server_addr(192,168,1,31);          // MySQL server IP
    char user[] = "domtemp";           // MySQL user
    char password[] = "domtemp";       // MySQL password
    
    void setup() {
    
      Serial.begin(9600);
    
      Serial.println("Initialising connection");
      Serial.print(F("Setting static ip to : "));
      Serial.println(ip);
    
      Serial.println("");
      Serial.println("");
      Serial.print("Connecting to ");
      Serial.println(ssid);
      WiFi.config(ip, gateway, subnet); 
      WiFi.begin(ssid, pass);
    
      while (WiFi.status() != WL_CONNECTED) {
        delay(200);
        Serial.print(".");
      }
    
      Serial.println("");
      Serial.println("WiFi Connected");
    
      WiFi.macAddress(mac);
      
      Serial.println("");
      Serial.print("Assigned IP: ");
      Serial.print(WiFi.localIP());
      Serial.println("");
    
      Serial.println("Connecting to database");
    
    
    // na tej pętli się zawiesza ..... nie łączy... drukuje same kropki 
      while (conn.connect(server_addr, 3306, user , password) != true) { 
        delay(200);
        Serial.print ( "." );  
      }
    
      Serial.println("");
      Serial.println("Connected to SQL Server!");  
    
    }
    
    void loop() {
    
      
     float  humidity = dht.readHumidity();
     float temperature = dht.readTemperature();
    
      delay(10000); //10 sec
    
      sprintf(query, INSERT_SQL, temperature , humidity  );
     // sprintf(query, INSERT_SQL, soil_hum, t);
    
      Serial.println("Recording data.");
      Serial.println(query);
      
      MySQL_Cursor *cur_mem = new MySQL_Cursor(&conn);
      
      cur_mem->execute(query);
    
      delete cur_mem;
    
    }
    
    .
  • ADVERTISEMENT
  • Helpful post
    #2 17281254
    krzbor
    Level 27  
    I suggest a different, much better and developmental solution - give up ESP communication directly with the database and instead connect to the web server on the QNAP. I suggest writing the appropriate scripts in PHP on the QNAP. You send the temperature from the ESP as a GET or POST request (there are loads of examples here), and PHP will store it in the database. Advantages of the solution:
    1. security - the database is hosted by the server and does not need to be exposed to the outside world.
    2. versatility - it is easy to change the structure of the database or completely rework it - just change the PHP script, not the program on ESP.
    3. Convenient action handling - PHP can not only save the data, but also send it and take an action e.g. turn on the heating.
    4. Ease of testing - once the PHP script has been created, you can test it from a browser (prepare a GET request) and see if everything saves. Only in the next step send this data from the ESP.
    5. possibility to change the database - instead of MySQL you can use another database or even write to a file. File processing in PHP is very convenient.
    etc.
  • ADVERTISEMENT
  • #3 17281829
    artusiek
    Level 9  
    Thanks for the hint .... I will play around with this solution .
    But I still don't understand ( and it annoys me ) what I did wrong ....
  • Helpful post
    #4 17281853
    krzbor
    Level 27  
    I have never programmed databases via ESP, but in PHP, for example, access is more complex, which the programmer does not see. It goes like this: PHP software (functions visible to the programmer) -> database client -> database. Perhaps there is something missing on ESP like a "client"? "Client" is usually the library responsible for communication, transmission encryption (if any) and many other things.
  • ADVERTISEMENT
  • Helpful post
    #5 17284579
    JacekCz
    Level 42  
    krzbor wrote:
    I have never programmed databases via ESP, but in PHP, for example, access is more complex, which the programmer does not see. It goes like this: PHP software (functions visible to the programmer) -> database client -> database. Perhaps there is something missing on ESP like a "client"? "Client" is usually the library responsible for communication, transmission encryption (if any) and many other things.


    A colleague is using/attempting to use "some" client for MySQL, at the same time he does not inform what he is using. This one specifically is unknown to me, but intuitively I find a lot wrong here.

    EDIT: too many some "mental shortcuts", intuitively smells to me like pasting from something random, mixing 2-3 levels of network connection (lowest/MySQL) , this is almost always a bad symptom.

    Added after 49 [seconds]:

    krzbor wrote:
    I will suggest another, much better and developmental solution - give up ESP communication directly with the database, and instead connect to the web server on the QNAP. I suggest writing appropriate scripts in PHP on the QNAP...
    .


    I support for a number of reasons.
    UPDATE: Theory suggests that POST is better for actively updating data.
    It's a bit harder to test (you can't type in the browser line), but there are developer add-ons for browsers.

    Added after 1 [minute]:

    artusiek wrote:
    Thanks for the hint .... I will play around with this solution .
    But I still don't understand ( and it annoys me ) what I did wrong ....


    You are using some rare API, in addition you don't give information what it is. You have a small percentage of chances that someone will say something concrete.

    Added after 37 [minutes]:

    I spent a few minutes googling "what library could you have used".
    I found examples of Mr ChuckBell pn MySQL_Connector_Arduino

    This is some kind of nightmare, objects "somehow" refer to each other without explicitly depending on each other, some weird static initialization, some objects appearing to never be used .... brrr....
    the project is so good that the author abandoned it 3 years ago.
  • #6 17285922
    artusiek
    Level 9  
    Thanks for the suggestions ... I abandon the thought of sending directly to the database and will play with - following your wound POST/GET .
    Currently - also on the advice of a colleague I am testing instead of C - Micropython . I will see what comes out of it .
    Thank you for your help .
    I close the topic .
  • ADVERTISEMENT
  • #7 17285923
    artusiek
    Level 9  
    Thanks for the suggestions ... I abandon the thought of sending directly to the database and will play with - following your wound POST/GET .
    Currently - also on the advice of a colleague I am testing instead of C - Micropython . I will see what comes out of it .
    Thank you for your help .
    I close the topic .

    Added after 1 [minute]: .

    Right wounds of the forumers !
    As per previous post

Topic summary

An ESP8266 module was programmed in C to collect temperature and humidity data from DHT sensors and intended to write this data directly to a MySQL database hosted on a QNAP TS-251+ NAS. Although the ESP8266 connected successfully to the network, it failed to establish a connection with the MySQL database, despite the database and user credentials being correctly set and accessible via Python scripts. Responses recommended abandoning direct database communication from the ESP8266 due to complexity and security concerns. Instead, a more robust and secure approach involves creating PHP scripts on the QNAP web server to handle HTTP GET or POST requests from the ESP8266, which then insert data into the database. This method improves security by not exposing the database directly, enhances flexibility in database management, and simplifies testing and action handling. The author acknowledged these suggestions and decided to switch from C to MicroPython for further development, adopting the PHP-based web server intermediary approach for database interactions.
Summary generated by the language model.
ADVERTISEMENT