B4R Question ESP8266 connect to mySQL and copy to global variable

[Chris160179]

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:

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:

Public SQL_SensorTemperatur() As Double = Array As Double(0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.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

 

[Erel]

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

 

[Chris160179]

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.

 

[Erel]

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.

Are you using an embedded broker? Switch to Mosquitto.

 

[Chris160179]

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?

 

[Erel]

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.

 

[BillMeyer]

For info:

https://test.mosquitto.org/

If you want to test mosquitto.

 

[Chris160179]

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

 

[Chris160179]

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

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

 

[Erel]

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?

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

Mosquitto is then but no online broker, right?

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

 

[BillMeyer]

Mosquitto is then but no online broker, right?

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.

 

[Chris160179]

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

Greetings
Chris