B4R Question ESP8266 connect to mySQL and copy to global variable

Discussion in 'B4R Questions' started by Chris160179, Dec 4, 2019 at 12:25 AM.

  1. Chris160179

    Chris160179 Member Licensed User

    Hello everybody,

    i need a little bit help.

    I know it is not the best idea to connect a ESP/Arduino to a SQL Database but i need this to reduce the complexity of my project.
    There are better and faster Solutions like this: https://www.b4x.com/android/forum/threads/rdc-based-on-mqtt.72416/#content

    I used the library in the Link below, to connect an ESP8266 to an mySQL/MariaDB Database.
    Link: https://github.com/ChuckBell/MySQL_Connector_Arduino
    This works perfektly.

    This is the terminal output:
    Connecting MariaDB...
    Connected to server version 5.5.5-10.1.23-MariaDB-9+deb9u1
    id,gain,gain_start,gain_faktor,intervall,wert,akku,rssi
    1,1.89,15.00,6.00,32,3.64,3.22,-70

    The fields in my MariaDB Database Tabel are:
    CREATE TABLE `haussteuerung_db`.`Sensoren_Wetter` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `sensor_name` varchar(45) NOT NULL DEFAULT '',
    `ip` varchar(15) NOT NULL DEFAULT '',
    `gruppe` varchar(45) NOT NULL DEFAULT '',
    `table_messwerte` varchar(45) NOT NULL DEFAULT '',
    `gain` varchar(10) NOT NULL DEFAULT '',
    `gain_start` varchar(10) NOT NULL DEFAULT '',
    `gain_faktor` varchar(10) NOT NULL DEFAULT '',
    `intervall` varchar(10) NOT NULL DEFAULT '',
    `wert` varchar(10) NOT NULL DEFAULT '',
    `akku` varchar(8) NOT NULL DEFAULT '',
    `rssi` varchar(8) NOT NULL DEFAULT '',
    `datum` date NOT NULL DEFAULT '0000-00-00',
    `zeit` time NOT NULL DEFAULT '00:00:00',
    PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4;


    B4R Code:
    Code:
    Sub Process_Globals
        
    Private Output() As Byte
    End Sub


    Sub SQL_Select
       
        RunNative(
    "Select"Null)                                                 
        
    Dim bc As ByteConverter
        
    Dim str As String = Output
        
    Log("Output Länge: ", Output.Length)
        
    Log("Output         : ", Output)
        
    Log("Output         : ", str)
    '    Log("Output 0: ", bc.StringFromBytes(Output))
        Log("Output 0: ", Output(0))
        
    Log("Output 1: ", Output(1))
        
    Log("Output 2: ", Output(2))
        
    Log("Output 3: ", Output(3))
        
    Log("Output 4: ", Output(4))
        
    Log("Output 5: ", Output(5))
        
    Log("Output 6: ", Output(6))
        
    Log("Output 7: ", Output(7))
        
    Log("Output 8: ", Output(8))
        
    Log("Output 9: ", Output(9))
        
    Log("Output 10: ", Output(10))
    End Sub

    #if C
        #include <MySQL_Connection.h>
        #include <MySQL_Cursor.h>

        //MarieDB Settings
        IPAddress server_addr(192, 168, 0, 150); // IP of the MySQL *server* here
        char user[] = "myUser";              // MySQL user login username
        char password[] = "myPassword";        // MySQL user login password

        WiFiClient client;
        MySQL_Connection conn((Client *)&client);
        // Create an instance of the cursor passing in the connection
        MySQL_Cursor cur = MySQL_Cursor(&conn);


        void Select(B4R::Object* o) {
            char querySelect[] = "SELECT id, gain, gain_start, gain_faktor, intervall, wert, akku, rssi FROM haussteuerung_db.Sensoren_Wetter WHERE id = %d";
            char query[128];

            delay(1000);
            Serial.println("Connecting MariaDB...");
            if (conn.connect(server_addr, 3306, user, password)) {
            delay(1000);
            }
            else
            Serial.println("Connection failed.");
            delay(1000);

            //Create new Cursor
            MySQL_Cursor *cur_mem = new MySQL_Cursor(&conn);
           
            // Execute the query
            sprintf(query, querySelect, 1);
            cur_mem->execute(query);

            // Fetch the columns and print them
            column_names *cols = cur_mem->get_columns();
            for (int f = 0; f < cols->num_fields; f++) {
                Serial.print(cols->fields[f]->name);
                if (f < cols->num_fields-1) {
                      Serial.print(',');
                }
            }
            Serial.println();
           
            //Read the rows and print them
            row_values *row = NULL;
            char* output[100];
            do {
                row = cur_mem->get_next_row();
                if (row != NULL) {
                    for (int f = 0; f < cols->num_fields; f++) {
                        Serial.print(row->values[f]);
                        output[f] = row->values[f];
                        if (f < cols->num_fields-1) {
                            Serial.print(',');
                        }
                    }
                    Serial.println();
                }
            } while (row != NULL);

            // Copy the result
            b4r_mariadb::_output->data = output;
            b4r_mariadb::_output->length = sizeof(output);

            //Deleting the cursor also frees up memory used
            delete cur_mem;
        }

    #End If
    But the value transferrd from Inline C to a global variable is not working or im not able to translate the Bytes.

    See here the terminal output:
    Output Länge: 400
    Output : l��?��?��?��?\��?���?���?��?��?��?w��?��!@���?��!@"@���?4S@S����? |@��L@���?�H@`��?U�U�3;@0��?�?@|�!@��?G�!@���?�#@|@,��?(��?�Q@@���?� @0����Q @
    ���h @`���?0N @def~�0���?���?���?`��?k400ai @`��?
    ��?�Q @@x @���@x @P��?0N @P��?]��?<N @
    Output : 1073669400
    Output 0: 108
    Output 1: 252
    Output 2: 254
    Output 3: 63
    Output 4: 132
    Output 5: 1
    Output 6: 255
    Output 7: 63
    Output 8: 148
    Output 9: 1
    Output 10: 255

    I don´t get it.
    Please help me:)

    I would like to set a global array with doubles like this:
    Code:
    Public SQL_SensorTemperatur() As Double = Array As Double(0.000.000.000.000.000.000.000.00)                        'Wetter Sensor draussen
    Or a string with the values separated by "," or something like else.

    Or is it possible to get the whole "MySQL_Cursor *cur_mem" to a globale variable and work with them?

    THX
    Chris

    EDIT:
    I believe my Database table is not the best solution, so if you know a more professional structure i can change the table.
    Or if it is nessesary to get a solution for my first question i change the table.
    THX
     
    Last edited: Dec 4, 2019 at 12:46 AM
  2. Erel

    Erel Administrator Staff Member Licensed User

    Why not use RDC over MQTT? It will be simpler as the data will be serialized with B4RSerializator.
     
  3. Chris160179

    Chris160179 Member Licensed User

    Hello Erel,

    I use this solution with rdc an mqtt, but lost the overview. So my mentioned was to reduce the komplexity and communicate directly to the Database:)

    I have problems with the mqtt, once a month but there is no specific time, the rdc with mqtt hangs with the error message: too many publications.
    When i restart the rdc and Mqtt it runs ok.
     
  4. Erel

    Erel Administrator Staff Member Licensed User

    Are you using an embedded broker? Switch to Mosquitto.
     
  5. Chris160179

    Chris160179 Member Licensed User

    I will do a look at Mosquitto and change from the embedded Broker to Mosquitto, thats stands on my todo list since a long time ago.

    But could you also help me with my first post to my inline c problem?
     
  6. Erel

    Erel Administrator Staff Member Licensed User

    You cannot return a reference to a local array. The local array is stored on the stack.

    I don't believe that this code will be more reliable than using RDC with MQTT.
     
  7. BillMeyer

    BillMeyer Well-Known Member Licensed User

  8. Chris160179

    Chris160179 Member Licensed User

    Oh, ok. Is it possible to get a copy of the reference to the Globalstore variable? Or do i get only the pointer adress into the Globalstore?

    I share your point of view regarding the rdc with mqtt, but i wanted to test it with the direct connection
     
  9. Chris160179

    Chris160179 Member Licensed User

    @BillMeyer
    Thank you, i will test it at the weekend.

    Only for understanding, the installable
    Mosquitto is then but no online broker, right?
     
  10. Erel

    Erel Administrator Staff Member Licensed User

    Technically it is possible. You need to use memcpy. However it can be tricky to get it working correctly.

    You just need to remove the broker from RDC and run Mosquitto on the same server.
     
  11. BillMeyer

    BillMeyer Well-Known Member Licensed User

    On the link I posted in the second line of the first paragraph is a link to the "TEST" version of mosquitto - which is online - they do warn though that it could become unstable - so for testing - 100% but for production or daily use - install it on a server somewhere - easy to do and it is a "Run and Leave" no nonsense broker.
     
  12. Chris160179

    Chris160179 Member Licensed User

    OK, thank you very much, both of you .
    I will test it tomorrow or at the weekend....the time is the problem:)

    Greetings
    Chris
     
Loading...
  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice