how to insert data string from variable char (parsing data xml)

[mydhitz]

hi Dr. @ChuckBell

i have variable string in my arduino String name; and data string parsing from xml using code : if (client.connected()){ if(client.find("<dname>")){ name = client.readStringUntil('<'); Serial.print("Name : "); Serial.print(name); Serial.print(" , "); //client.stop(); client.flush(); }

data xml parsing form : http://wxdata.weather.com/wxdata/weather/local/ITXX0067?cc=*&unit=m

and this data from xml, <loc id="ITXX0067"><dnam>Rome, LZ, Italy</dnam><tm>3:30 AM</tm><lat>41.90</lat><lon>12.48</lon><sunr>5:35 AM</sunr><suns>8:49 PM</suns><zone>2</zone></loc>

and i will insert into database mysql const char INSERT_SQL[] = "INSERT INTO test_arduino.weather (Location,Time,Latitude,Longitude,Sunrise,Sunset,Zone) VALUES ('%s','%s','%s','%s','%s','%s',%d)";

but in serial monitor showing al tag xml because variable name parsing data using client.readStringUntil('<'); and all tag xml typedata char, how to change "%s" to variable, im trying to change '"+name+"' but not succefully, and show error message exit status 1 invalid operands of types 'const char*' and 'const char [17]' to binary 'operator+'

thanx,, sorry my english is bad,, Dito from Indonesia

[ChuckBell]

Hi,

You need to use sprintf(). I will need to see your entire sketch in order to offer more guidance. Please send it to d r c h a r l e s b e l l at g m a i l dot c o m.

Dr. Bell

On 6/10/18 10:21 PM, dito wrote:

i have variable string in my arduino |String name;| and data string parsing from xml |1sutejo23

Yogyakarta

| and i will insert into database mysql |const char INSERT_SQL[] = "INSERT INTO test_arduino.biodata (Id,Name,Age,Sex,Addres) VALUES (%d,'%s',%d,'%s','%s')";| but in serial monitor showing al tag xml, how to change "%s" to variable, im trying to change '"+name+"' but not succefully, thanx,, Dito from Indonesia

— 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/45, or mute the thread https://github.com/notifications/unsubscribe-auth/AH0j4NzbPzTcV0WUjmDDFvqvPwDwpvtwks5t7dQcgaJpZM4Uh-wi.

[mydhitz]

thanks Dr @ChuckBell

im already to use sprintf() after INSERT_SQL[] and in serial monitor showing all tag from xml because %s reading all type data string

const char INSERT_SQL[] = "INSERT INTO test_arduino.weather (Location,Time,Latitude,Longitude,Sunrise,Sunset,Zone) VALUES ('%s','%s','%s','%s','%s','%s',%d)"; sprintf (query, INSERT_SQL, 'name', 'times', 'lat', 'lon', 'sunr', 'suns', zone);

my sketch will send to your email. thank you for your time.

[ChuckBell]

Hello,

I see three problems here.

First, the buffer is defined with 128 characters, but your string without substitution is 129 characters. So, this won’t work:

char query[128];

I suggest to avoid buffer overflow, use this or similar:

char query[256];

However, be careful with memory. Check the compilation message to ensure you have at least 512 bytes available after compilation. Like this:

Sketch uses 18970 bytes (58%) of program storage space. Maximum is 32256 bytes.

Global variables use 1587 bytes (77%) of dynamic memory, leaving 461 bytes for local variables. Maximum is 2048 bytes.

Low memory available, stability problems may occur.

Notice the compiler is warning us about potential problems.

Specifically, if you’re trying to use an Arduino Uno or Leonardo or similar, your sketch will likely fail. You will need to either save memory such as changing the String classes to C strings or use a larger Arduino board. ESP8266’s are out of the question.

Second, you’ve placed quotes around all of your variables in the sprintf() call. Thus, these will be substituted literally as shown. Specifically, this line:

sprintf (query, INSERT_SQL, 'dname', 'times', 'lat', 'lon', 'sunr', 'suns', zone);

And let’s say zone = 999.

This will result in this string:

INSERT INTO test_arduino.weather (Location,Time,Latitude,Longitude,Sunrise,Sunset,Zone) VALUES (‘dbname’,'times’,'lat’,’lon’,'sunr’,’sim’,999)

...which is not what you want. Take the quotes out of the sprintf() statement.

Third, you’re using the string class and the sprintf() statement requires char * (“C”) strings. Thus, you must get the C string from the string class. Thus, the sprintf() function should be:

sprintf (query, INSERT_SQL, dname.c_str(), times.c_str(), lat.c_str(), lon.c_str(), sunr.c_str(), suns.c_str(), zone);

Additionally, you’re storing a lot of character data that I think can be other forms. For example, latitude and longitude can be float. Times can be time, etc. That may not save you space in the end, but it will make for better database storage.

Dr. Bell

On 6/11/18 10:34 PM, dito wrote:

thanks Dr @ChuckBell https://github.com/ChuckBell

im already to use sprintf() after INSERT_SQL[] and in serial monitor showing all tag from xml because %s reading all type data string

|const char INSERT_SQL[] = "INSERT INTO test_arduino.biodata (Id,Name,Age,Sex,Addres) VALUES (%d,'%s',%d,'%s','%s')";| |sprintf (query, INSERT_SQL, id, 'name', age, sex,address);|

my sketch will send to your email. thank you for your time.

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/ChuckBell/MySQL_Connector_Arduino/issues/45#issuecomment-396446584, or mute the thread https://github.com/notifications/unsubscribe-auth/AH0j4IPAbsnlePqG3NzNzE_l6RQdmesOks5t7yivgaJpZM4Uh-wi.

[mydhitz]

Thank you very much Dr. Bell. it works, im just add dname.c_str() to statement sprintf() to get data String form xml. Thanks for your times and your work has helped me.