Advice required for long string used for a MySQL Query!

Hello Forum !

I've just dropped myself into the Arduino world with my first project, which is moving along slowly. The project basically is an energy monitor for our heating system, using a Seeeduino Stalker. It monitors the pulse output of the energy meter (with an interrupt), sums up the number of pulses in a five minute period (using the RTC interrupt) and writes this to an SD card. I also monitor four other signals; three floor temperatures (it is electric underfloor heating) and the load signal generated by the controller (0-100%, depending on outside temperature)

I started off writing to an SD card. But then I wanted to be able to remotely access the Arduino. An ethernet shield mysteriously appeared in the post, and I experimented with writing the data to a client (browser). This was quite nice, until I realised that my NAS has a MySQL server that's been gathering dust for a while. Now, I have included the MySQL connector from Chuck Bell, and this is working very nice too (with only simple hard-coded SQL statements for testing). The advantage with this is I can simply use Excel to access the data from the MySQL server for analysis (or create a web server, but that will be for the future)

There are quite a few variables to write into every row and the construction of the 'test' string, combined with the destruction of the stack, rather rapidly lead me to learn about memory management and the storage of strings in RAM/FLASH..

So now I'm struggling a little bit on how to get the data into my table.

Here's my routine (very much a work in progress, so a kind of sandbox.). You may notice that I have experimented with putting the test string into Program Memory. Well this works, but I would like to replace the hard coded test values with 'real' (formatted) values. Constructing the string is not a problem, but as far as I know you can't modify strings in Program Memory

Is it possible to construct the string dynamically at runtime in program memory? If so how? The string will always be the same length. Is there a more exotic way of accomplishing this task ? I was thinking about creating a routine to insert each data item one by one (date/time field will be a primary key), then I guess I could have the table names in a program memory character array, but I don't know if this is just getting a bit too complicated.

[For my programming experience, I have Pascal, Fortran/PL1, Visual Basic, Delphi, and now C++ (but my skills in each one are not so advanced). I've also used MySQL rather extensively in the past. I have never touched PHP (yet..).]

Thanks in advance for any hints/tips/guidance/direction ! I'll also report any findings/successes !

void WriteData()
{
if (pulseCount > 0) {
DateTime now = RTC.now();
kWh = (pulseCount-prevPulseCount)0.1;
totkWh = pulseCount
0.1;
float kW = kWh*12.0; //(average demand for five minutes)
EEPROM_writeAnything(sizeof(unsigned long), pulseCount);

// MySQL Stuff

Serial.println(my_conn.is_connected());
if (my_conn.is_connected() == 0) {
if (my_conn.mysql_connect(server_addr, 3306, user, password)) {
Serial.println("New connection");
}
}
else
Serial.println("Connection already established");
delay(500);
my_conn.cmd_query_P(PSTR("INSERT INTO Heating.data"
" (timestamp, pulsecount, kWh, TotPulseCount, TotkWh, kW, TempUG, TempSZ, TempOG, ATW, SOL)"
" VALUE"
" ('13.10.13 15:00', 11, 3.14, 12, 3.64, 25.65, 68.36, 45.36, 1.36, 2.72, 4.25)"));
Serial.println("Query Success!");
prevPulseCount=pulseCount;
}
}

It doesn't look like your insert should cause you any significant RAM issues, at least by itself. You should be able to use sprintf to add your dynamic data items.

One additional thing to consider might be to normalize your data: rather than having a column for each data element, just provide key info and the sensor reading. It would make it easier to send the data and would be more easy to change if you add sensors. A table containing an arbitrary id for the arduino , id for the sensor, timestamp and reading will do. Timestamp can be supplied by MySQl too - no need to pass it from the arduino.

I agree with everything Wildbill said.

Also consider executing a stored procedure to perform the insert rather than manipulating the tables explicitly. It's much easier to update a stored procedure than reprogram an embedded Arduino.

Hi all, thanks for the info about stored procedures! Last version of MySQL I used was Version 3.x and that did not have stored procedures. I'll give it a try and see if I can get something up and running.

  • thanks,
    Chris