retrieve long text (>500 chars) from database doesn't work correctly

[petermagdy94]

I'm using ESP32 to connect to local database using mariadb, and i want to get value from a table which contains LONGTEXT 570 chars like that:

userID	text
1234	11111111112222222222333333333344444444445555555555
	66666666667777777777888888888899999999990000000000
	11111111112222222222333333333344444444445555555555
	66666666667777777777888888888899999999990000000000
	11111111112222222222333333333344444444445555555555
	66666666667777777777888888888899999999990000000000
	11111111112222222222333333333344444444445555555555
	66666666667777777777888888888899999999990000000000
	11111111112222222222333333333344444444445555555555
	66666666667777777777888888888899999999990000000000
	11111111112222222222333333333344444444445555555555

and here is the code i used:

.
.
void setup()
{
   // connect to wifi

   if (conn.connect(server_ip, 3306, user, password))
   {
        if (conn.connected())
    {
        // Initiate the query class instance
        MySQL_Cursor *cur_mem = new MySQL_Cursor(&conn);

        // Execute the query
        cur_mem->execute("SELECT twxt FROM db.tb WHERE userID=1234");

        // Fetch the columns and print them
        column_names *cols = cur_mem->get_columns();

        // Read the rows and print them
        row_values *row = NULL;
        do {
            row = cur_mem->get_next_row();
            if (row != NULL)
            {
                snprintf(buff, sizeof(buff), "%s", row->values[0]);
            }
        } while (row != NULL);

        // Deleting the cursor also frees up memory used
        delete cur_mem;
        Serial.println(buff);
    }
    }
    else
    {
       Serial.println("Connection failed.");
    }
    conn.close();
}
void loop()
{
}

but when i run it i only get the first 251 chars and dummy data at first as below:

:11111111112222222222333333333344444444445555555555 66666666667777777777888888888899999999990000000000 11111111112222222222333333333344444444445555555555 66666666667777777777888888888899999999990000000000 111111111122222222223333333333444444444455

[ChuckBell]

That’s correct. The connector is not designed to handle large row sizes due to memory restrictions nor can it retrieve large blocks of data. I’m not surprised it doesn’t work and I’m not sure if you can fix it without running out of memory or using some trickery.

I would suggest reducing your row size as much as you can perhaps even using multiple fields so that you’re only retrieving the bare minimum data and avoid “wide” fields.

Out of curiosity, why are you retrieving such large data for a wee little microcontroller?

That said, please understand the connector is not guaranteed to work with non-Oracle distributions of MySQL. It is possible this is one of those situations (but I’m not certain). For MariaDb specifics, you’re in your own. Sorry.

On Sat, Mar 27, 2021 at 17:37 petermagdy94 @.***> wrote:

I'm using ESP32 to connect to local database using mariadb, and i want to get value from a table which contains LONGTEXT 570 chars like that: userID text 1234 11111111112222222222333333333344444444445555555555 66666666667777777777888888888899999999990000000000 11111111112222222222333333333344444444445555555555 66666666667777777777888888888899999999990000000000 11111111112222222222333333333344444444445555555555 66666666667777777777888888888899999999990000000000 11111111112222222222333333333344444444445555555555 66666666667777777777888888888899999999990000000000 11111111112222222222333333333344444444445555555555 66666666667777777777888888888899999999990000000000 11111111112222222222333333333344444444445555555555

and here is the code i used:

` . . void setup() { // connect to wifi

if (conn.connect(server_ip, 3306, user, password)) { if (conn.connected()) { // Initiate the query class instance MySQL_Cursor *cur_mem = new MySQL_Cursor(&conn);

// Execute the query

cur_mem->execute("SELECT twxt FROM db.tb WHERE userID=1234");

// Fetch the columns and print them

column_names *cols = cur_mem->get_columns();

// Read the rows and print them

row_values *row = NULL;

do {

  row = cur_mem->get_next_row();

  if (row != NULL)

  {

      snprintf(buff, sizeof(buff), "%s", row->values[0]);

  }

} while (row != NULL);

// Deleting the cursor also frees up memory used

delete cur_mem;

Serial.println(buff);

}

}

else

{

Serial.println("Connection failed.");

}

conn.close();

} } void loop() { } `

but when i run it i only get the first 251 chars and dummy data at first as below:

:�11111111112222222222333333333344444444445555555555 66666666667777777777888888888899999999990000000000 11111111112222222222333333333344444444445555555555 66666666667777777777888888888899999999990000000000 111111111122222222223333333333444444444455

— You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub https://github.com/ChuckBell/MySQL_Connector_Arduino/issues/166, or unsubscribe https://github.com/notifications/unsubscribe-auth/AB6SHYGIC2QXA7VXZG4PXLTTFZF2BANCNFSM4Z5KCXBQ .

[petermagdy94]

the problem is that this text is a JSON configs data that i will need to parse in my project. so isn't there any way to deal with it instead of splitting this text ?

and thanks for your reply.

[ChuckBell]

JSON you say? Then you have fields and values, yes? A little imagination suggests you can store JSON in a table if the data is uniform. Uniform JSON wastes a lot of space! :)

Suggestion: Store it in a table and reconstruct the JSON on the fly. On Sat, Mar 27, 2021 at 18:14 petermagdy94 @.***> wrote:

the problem is that this text is a JSON configs data that i will need to parse in my project. so isn't there any way to deal with it instead of splitting this text ?

and thanks for your reply.

— You are receiving this because you commented. Reply to this email directly, view it on GitHub https://github.com/ChuckBell/MySQL_Connector_Arduino/issues/166#issuecomment-808809885, or unsubscribe https://github.com/notifications/unsubscribe-auth/AB6SHYAF3RFKDMAOKE54BF3TFZKELANCNFSM4Z5KCXBQ .

[petermagdy94]

yes you are right, i just thought there is an easier way but now i think not. i will go with it thanks :D