Concat large strings/convert to const char

itswagg · November 22, 2014, 3:26am

Hey All,

I need a bit of guidance.

Context: I've built a small automated greenhouse. I'm using a mega to do the bulk of the processing, and passing sensor values and other info to an uno connected to an ethernet shield.

I'm attempting to use Dr. Charles Bell's connector/arduino methods to send the data to an instance of mySQL hosted on an amazon RDS. I've been able to manually pass data thru the unto to mysql fine, but now I'm trying to concat all of my sensor data together into an INSERT INTO statement, but I think it's overflowing available memory. Furthermore, I'm not certain i have everything in the right format.

I've constructed an array on the mega(master) side, and successfully passed/parsed the array into an array on the uno(slave) side. I'm trying to take that data, and replace this:

char INSERT_SQL[] = "INSERT INTO test_arduino.greenhouse VALUES (72,now())";

Here's what I'm doing in attempt to create a variable I can replace INSERT_SQL[] with:

byte index = 0;
while(Wire.available() > 0 && index < 12)
{
values[index] = Wire.read();
index++;
counter[0]=values[11]; //THIS IS THE COUNTER, INDICATING IF THE DATA IS NEW OR NOT

String insertInto="INSERT INTO test_arduino.data_test VALUES (now(), ";
char dblQuote ='"';
String comma = ",";
char clParen = ')';
// three = dblQuote+ insertInto +values[0]+comma+values[1]+comma+values[2]+comma+values[3]+comma+values[4]+comma+values[5];
// four=comma+values[6]+comma+values[7]+comma+values[8]+comma+values[9]+clParen+dblQuote;
//three = dblQuote+ insertInto +values[0]+clParen+dblQuote;

five=dblQuote+ insertInto +values[0]+comma+values[1]+comma+values[2]+comma+values[3]+comma+values[4]+comma+values[5]+comma+values[6]+comma+values[7]+comma+values[8]+comma+values[9]+clParen+dblQuote;

five.toCharArray(convertBuf,200);

}

I can get Serial.print(three+four) to work, but not combined into one variable.

Any advice on this out there?

Thanks
Justin

itswagg · November 22, 2014, 3:37am

Basically,

what I'm going for here is something like

char INSERT_SQL[] = "INSERT INTO test_arduino.greenhouse VALUES (now(),45,23,12,23,1,0,0,0,1,45,34)";

But for some reason - I'm not really getting what I'm looking for here. I'm more familiar with solving the problem by using macro variables, I would do something like:

char INSERT_SQL[] = "INSERT INTO test_arduino.greenhouse VALUES (now(),&a.,&b.,&c.,&d.)";

etc. If I could.

I appreciate the help/guidance!!!

Justin

guix · November 22, 2014, 11:35am

Hello,

It looks like a job for sprintf

system · November 22, 2014, 12:45pm

It looks like a job for sprintf

Actually, it looks like a job for multiple print() calls. There is no reason to waste resources using the String class, or in trying to stuff all the data into a single array. It makes practically no difference if one client.print() call is made to send 100 characters or if 20 client.print() calls are made to send 5 characters each.

itswagg · November 22, 2014, 5:40pm

I accomplished the task 95% of the way with sprintf. See below:

void loop() {
// Serial.println("Main Loop");
// Serial.print("buf= ");
// Serial.print(buf);

if(counter[0]>counter[1]){
char buf[150];

Serial.println("Inside Counter Loop");
Serial.print("buf= ");

sprintf(buf,"INSERT INTO test_arduino.greenhouse VALUES (now(),%d,%d,%d,%d,%d,%d,%d,%d,%d)",
values[0],values[1],values[2],values[3],values[4],values[5],values[6],values[7],values[8]);

Serial.println(buf);
Serial.print("printed buf");

//my_conn.cmd_query(buf);
counter[1]=counter[0];

}

}

My only issue now is that there's one more value in the values[] array that I can't fit into buf. I've expanded buf to a very large size, but I think im overrunning memory somehow.

When I insert the last value into the sprintf, and print buf, the string isnt complete, and is missing the last value.

Any suggestions here?

Thanks

robtillaart · November 22, 2014, 5:44pm

you can reduce the name of the database table

test_arduino.greenhouse

to

db.tbl1

you would win about 10 characters, might be enough.?

itswagg · November 22, 2014, 5:56pm

Rob,

Thank you! It's laughable, but I guess that's what the forum is for. There's no-one around to be my common sense when working late at night. Your suggestion makes total sense, and works like a charm.

Connecting...
Connected to server version 5.6.19-log
Query Success!
Inside Counter Loop
buf= INSERT INTO gH.dT VALUES (now(),72,70,72,25,0,24,7,0,0,1)
printed buf

In the event that I would run into this situation again, is PROGMEM an option? I seem to only find examples of it being used to define something at the top of a sketch, a constant.

How would you go about the problem?

Thanks again

robtillaart · November 22, 2014, 6:24pm

You could have the static part of the queries in PROGMEM and copy it into buffer like this

#include <avr/pgmspace.h>

prog_uchar query1[] PROGMEM  = {
  "INSERT INTO gh.dt VALUES (now(),"};

prog_uchar query2[] PROGMEM  = {
  "INSERT INTO gh.d2 VALUES (now(),"};

uint32_t freeRam() 
{
  extern int __heap_start, *__brkval; 
  int v; 
  return (uint32_t) &v - (__brkval == 0 ? (uint32_t) &__heap_start : (uint32_t) __brkval); 
};


void setup()
{
  Serial.begin(115200);
  Serial.println("start query test");

  char buf[150];

  strcpy_P(buf, (char*)query1);
  // Serial.println(buf); 
  int len = strlen(buf); // to find \0
  sprintf(&buf[len], "%d,%d,%d,%d,%d,%d,%d,%d,%d)",1,2,3,4,5,6,7,8,9);
  Serial.println(buf);
  Serial.println();
  Serial.println(freeRam());  
}

void loop() 
{

}

prints out 1649 bytes free RAM

uint32_t freeRam() 
{
  extern int __heap_start, *__brkval; 
  int v; 
  return (uint32_t) &v - (__brkval == 0 ? (uint32_t) &__heap_start : (uint32_t) __brkval); 
};


void setup()
{
  Serial.begin(115200);
  Serial.println("start query test");

  char buf[150];

  sprintf(buf, "INSERT INTO gh.dt VALUES (now(),%d,%d,%d,%d,%d,%d,%d,%d,%d)",1,2,3,4,5,6,7,8,9);
  Serial.println(buf);
  Serial.println();
  Serial.println(freeRam());  
}

void loop() {}

results in 1617 bytes free -> so gained 32 bytes of RAM by copying from PROGMEM

think you can copy the whole query in PROGMEM (I'll test it right after the break

robtillaart · November 22, 2014, 6:33pm

for readablility and if RAM would allow I would go for this solution

//
//    FILE: query.ino
//  AUTHOR: Rob Tillaart
// VERSION: 0.1.00
// PURPOSE: demo progmem for SQL
//    DATE: 2014-11-22
//     URL:
//
// Released to the public domain
//
#include <avr/pgmspace.h>

prog_uchar query1[] PROGMEM  = {
  "INSERT INTO gh.dt VALUES (now(),%d,%d,%d,%d,%d,%d,%d,%d,%d)"};

uint32_t freeRam() 
{
  extern int __heap_start, *__brkval; 
  int v; 
  return (uint32_t) &v - (__brkval == 0 ? (uint32_t) &__heap_start : (uint32_t) __brkval); 
};


void setup()
{
  Serial.begin(115200);
  Serial.println("start query test");

  char qry[75];  // longest query should fit
  char buf[150]; 

  strcpy_P(qry, (char*)query1);
  Serial.print(strlen(qry)); 
  Serial.print('\t'); 
  Serial.println(qry);

  sprintf(buf, qry, 1123, 2123, 3123, 454, 345, 4536, 753, 3458, 3459);

  Serial.print(strlen(buf)); 
  Serial.print('\t'); 
  Serial.println(buf);
  Serial.println();
  Serial.println(freeRam());  
}

void loop() 
{
}

results in:

start query test
59 INSERT INTO gh.dt VALUES (now(),%d,%d,%d,%d,%d,%d,%d,%d,%d)
74 INSERT INTO gh.dt VALUES (now(),1123,2123,3123,454,345,4536,753,3458,3459)

1600

OK static part of the query is copied twice... (retry...)

robtillaart · November 22, 2014, 6:43pm

mix both techniques

//
//    FILE: .ino
//  AUTHOR: Rob Tillaart
// VERSION: 0.1.00
// PURPOSE: 
//    DATE: 
//     URL:
//
// Released to the public domain
//

#include <avr/pgmspace.h>

prog_uchar query1_part1[] PROGMEM  = {
  "INSERT INTO gh.dt VALUES (now(),"};
  
prog_uchar query1_part2[] PROGMEM  = {
  "%d,%d,%d,%d,%d,%d,%d,%d,%d)"};

uint32_t freeRam() 
{
  extern int __heap_start, *__brkval; 
  int v; 
  return (uint32_t) &v - (__brkval == 0 ? (uint32_t) &__heap_start : (uint32_t) __brkval); 
};


void setup()
{
  Serial.begin(115200);
  Serial.println("start query test");

  char qry[35];    // longest queryX_part2 should fit
  char buf[150];  // longest expanded query should fit

  strcpy_P(buf, (char*)query1_part1);  // static part
  int len = strlen(buf);
  Serial.print(len); 
  Serial.print('\t'); 
  Serial.println(buf);

  strcpy_P(qry, (char*)query1_part2);  // 'dynamic' part
  
  sprintf(&buf[len], qry, 1123, 2123, 3123, 454, 345, 4536, 753, 3458, 3459);

  Serial.print(strlen(buf)); 
  Serial.print('\t'); 
  Serial.println(buf);
  Serial.println();
  Serial.println(freeRam());  
}

void loop() 
{
}

start query test
32 INSERT INTO gh.dt VALUES (now(),
74 INSERT INTO gh.dt VALUES (now(),1123,2123,3123,454,345,4536,753,3458,3459)

1638

Think it can even be done with less RAM...

itswagg · November 22, 2014, 9:12pm

Rob,

Thanks for your help, you really helped me out here. I appreciate it! This is perfect because I can see more sensor nodes being connected and running into the issue again. Now it seems solved.

Have a great weekend.
Justin

robtillaart · November 23, 2014, 11:03am

Hi Justin,

could not resist the challenge to reduce RAM even more. The code reuses buf to get rid of the qry array. It does the following steps:

copy the dynamic part of the SQL to begin of buf from PROGMEM
use sprintf to fill in the dynamic part and store it just after the dynamic part in buf
move the filled part to the end of buf
copy the static part of the SQL to begin of buf
move the filled part so it concatenates with the static part.

In code:

//
//    FILE: SqlCompacttQuery.ino
//  AUTHOR: Rob Tillaart
// VERSION: 0.1.01
// PURPOSE: demo progmem for SQL
//    DATE: 2014-11-22
//
// Released to the public domain
//

#include <avr/pgmspace.h>

prog_uchar query1_part1[] PROGMEM  = {
  "INSERT INTO gh.dt VALUES (now(),"};

prog_uchar query1_part2[] PROGMEM  = {
  "%d,%d,%d,%d,%d,%d,%d,%d,%d)"};

uint32_t freeRam() 
{
  extern int __heap_start, *__brkval; 
  int v; 
  return (uint32_t) &v - (__brkval == 0 ? (uint32_t) &__heap_start : (uint32_t) __brkval); 
};


void setup()
{
  Serial.begin(115200);
  Serial.println("\nstart query test");

  char buf[150];   // room for whole expanded query

  // COPY DYNAMIC PART OF SQL IN BUFFER
  strcpy_P(buf, (char*)query1_part2);  // 'dynamic' part
  int len0 = strlen(buf);

  // FILL IN THE BYNAMIC PART
  sprintf(&buf[len0+1], buf, 1123, 2123, 3123, 454, 345, 4536, 753, 3458, 3459);
  int len1 = strlen(&buf[len0+1]);

  // MOVE PROCESSED DYNAMIC PART TO END OF BUF (this is the trick)
  memmove(&buf[149-len1], &buf[len0+1], len1+1);

  // COPY STATIC PART TO BEGIN OF BUF
  strcpy_P(buf, (char*)query1_part1);
  int len2 = strlen(buf);

  // MOVE PROCESSED PART TO FIT STATIC PART
  memmove(&buf[len2], &buf[149-len1], len1 + 1);

  Serial.print(strlen(buf)); 
  Serial.print("\t>"); 
  Serial.print(buf);
  Serial.print('<'); 
  Serial.println();
  Serial.println(freeRam());  
}

void loop() 
{
}

output:

start query test
74 >INSERT INTO gh.dt VALUES (now(),1123,2123,3123,454,345,4536,753,3458,3459)<
1667

So it saves ~30 bytes that is approx the size of the qry array.

And because the "%d,%d,%d,%d,%d,%d,%d,%d,%d)" will not be in buf in the same time as "INSERT INTO gh.dt VALUES (now()," the size of buf can be reduced even more. When I reduce the size of buf to 80 bytes it is still large enough.

start query test
74 >INSERT INTO gh.dt VALUES (now(),1123,2123,3123,454,345,4536,753,3458,3459)<
1737

In formula the size of buf is: (for this query)

Size = len(SQL statement) - len(static part) + max(len(static part), len(dynamic part)) + 2;

(indeed size 76 works, size 75 fails length sql=74)

However, if you are not in desperate need of RAM I would advice to use the version that uses the qry array as that is easier to understand and therefore to maintain.

Thanks for the challenge

Topic		Replies	Views
How can I reduce the SRAM memory used by this array of structs Programming	33	446	January 30, 2026
Can´t connect to MySQL using sprintf Programming	11	1776	February 20, 2021
Question about PROGMEM (strcpy_P usage) Programming	74	1488	July 22, 2025
Combining variables into strings without serial General Guidance	29	502	November 22, 2024
Problemi con MariaDB o MySQL Software	10	673	September 10, 2023

Concat large strings/convert to const char

Related topics