Sending data to mysql or sending to a page?

[josephchrzempiec]

Hello, I'm trying to up temperatures, humidity and voltages to mysql. I was wondering which way would be more better or secure. I was thinking of sending the data to a webpage and have that webpage send the information to mysql for storage. Or would it be better to send it directly to mysql itself?

Joseph

[cotestatnt]

I prefer to send SQL queries directly to the database, but the libraries found in the Arduino environment are not that great in my opinion.

I am currently working on a project where I have to perform some select queries with a ESP32 and I had to get my hands on the source of this library because it is not able to recover text with a length greater than 250 bytes as it currently is.

[josephchrzempiec]

I will take a look at it. I'm not using wifi myself. I use ethernet for this project of mine. But if it works out shouldn't be to hard to merge over, I hope. thank you

[cotestatnt]

@josephchrzempiec

I use ethernet

It's not relevant because you need to pass a generic Client reference to the connector so it can be whatever you need: Ethernet client, WiFi client, GSM client etc etc

[josephchrzempiec]

True.

[J-M-L]

@cotestatnt

I had to get my hands on the source of this library because it is not able to recover text with a length greater than 250 bytes as it currently is.

they document limitations and Tips and Tricks

Memory is really limited on small arduinos so...

where did you see the 250 limits ? (the sum of all db, column, and field names must be < 255 in length)

[cotestatnt]

@J-M-L

where did you see the 250 limits

It's not a limit, its' more a bug. If you try to select a field with more than 250 bytes, it will be truncated.

If the content of a field is greater than 250 bytes the "marker" in the byte stream is equal to 0xFC followed by 2 bytes for the length value. If the marker is 0xFD the bytes are 3 and with 0xFE you will have 8 bytes.

However, this instruction always returns 1 as the value for the length in bytes of the field.
I think it should be passed as parameter the offset itself and not the value of the buffer at the position pointed to by the offset.

And in fact I modified the source in this way as well as other small changes.
Now I can process strings longer than 250 bytes without problems.

This is my fork, but I'm still testing so no commits was done till now.

cotestatnt/MySQL_Connector/blob/067b345a3a9870d836bcfb89c4f709bf0561c8b8/src/MySQL_Cursor.cpp#L384C60-L384C60

[josephchrzempiec]

I did not know that. Interesting

[J-M-L]

@cotestatnt

It's not a limit, its' more a bug

I see indeed they trap 251 but not 252 or 253

ChuckBell/MySQL_Connector_Arduino/blob/067b345a3a9870d836bcfb89c4f709bf0561c8b8/src/MySQL_Cursor.cpp#L386C1-L397C4

and read that incorrectly

worse raising a bug on their GitHub

[cotestatnt]

@J-M-L

I see indeed they trap 251 but not 252 or 253

That it's not a real problem because a valid length value represented using only 1 byte must be <= 250.

If the value is greater, the length value need 2 or more bytes even if it were actually a value in the 252-255 range (251 is reserved for a NULL field value).

I think this library was developed keeping in mind the RAM limitations of small Arduinos, so this bug was probably never "discovered" because no one dreams of managing such long strings with little RAM.

However, I am using an ESP32 and I don't have these limits...

[J-M-L]

@cotestatnt

If the value is greater, the length value need 2 or more bytes even if it were actually a value in the 252-255 range (251 is reserved for a NULL field value).

yes as you said

@cotestatnt

the "marker" in the byte stream is equal to 0xFC followed by 2 bytes for the length value. If the marker is 0xFD the bytes are 3 and with 0xFE you will have 8 bytes

the code catches 251 (0xFB) but does not catch 0xFC or 0xFD or 0xFE and uses this byte for the malloc and strncpy() saves the day by truncating the copy from the buffer.

[qubits-us]

Interesting thread..
I never even looked to see if there was any direct interface to the db servers..
Rolled my own packet server to interface the db server and tried to keep it generic..

I put a 512 byte limit on strings but I also allow for blobs which can be up too 4096 bytes..
The last few changes were adding the blobs and also allowing for inter-communication of the connected devices..
Been busy and had to move but I do also have it ported to pi (NardPi) and will upload that soon and of course got a new delphi release on top of everything..

NARDS

fun, fun.. ~q

[josephchrzempiec]

Does anyone have an example sketch of sending data directly to mysql itself? Everything I found so far sends data to a wenpage then goes directly to mysql from the webpage.

[J-M-L]

Have you seen MySQL Connector Arduino - Arduino Reference ?

[cotestatnt]

The library pointed by @J-M-L works well apart from the bug we've already discussed.

Wandering around the net I also found this library created for the mbed platform which is quite easy to bring into the Arduino environment.

I've been testing stability and reliability with an ESP32-S3 for several hours now and I'm at a good point with porting (I've added also some other things like error response parsing)

If you are interested I can put the code for Arduino platform on a GitHub repository