How to allocate one buffer for all formatted SQL queries?

Hi,

I’m new to Arduino and C# but have started to play around with the MySQL Connector Arduino Library (by Dr. Charles A. Bell). It’s quite fun and interesting, but I have a question that properly is rather basic. Here it goes:

In one of the code examples Dr. Charles A Bell writes in the comments that sprintf uses twice the memory and an alternative option is “to allocate one buffer for all formatted queries or allocate memory and free it when you’re done”.

This might be a very basic question, but I hope that someone would be kind to explain how to do this? Or point me in a direction with some examples.

Here is a link to Bell’s full example otherwise here is the specific comment:

Serial.println("> Running SELECT with dynamically supplied parameter");

  // Initiate the query class instance
  MySQL_Cursor *cur_mem = new MySQL_Cursor(&conn);
  // Supply the parameter for the query
  // Here we use the QUERY_POP as the format string and query as the
  // destination. This uses twice the memory so another option would be
  // to allocate one buffer for all formatted queries or allocate the
  // memory as needed (just make sure you allocate enough memory and
  // free it when you're done!).
  sprintf(query, QUERY_POP, 9000000);
  // Execute the query
  cur_mem->execute(query);

runevn:
Hi,

I'm new to Arduino and C# but have started to play around with the MySQL Connector Arduino Library (by Dr. Charles A. Bell). It's quite fun and interesting, but I have a question that properly is rather basic. Here it goes:

In one of the code examples Dr. Charles A Bell writes in the comments that sprintf uses twice the memory and an alternative option is "to allocate one buffer for all formatted queries or allocate memory and free it when you're done".

This might be a very basic question, but I hope that someone would be kind to explain how to do this? Or point me in a direction with some examples.

Here is a link to Bell's full example otherwise here is the specific comment:

Serial.println("> Running SELECT with dynamically supplied parameter");

// Initiate the query class instance
 MySQL_Cursor *cur_mem = new MySQL_Cursor(&conn);
 // Supply the parameter for the query
 // Here we use the QUERY_POP as the format string and query as the
 // destination. This uses twice the memory so another option would be
 // to allocate one buffer for all formatted queries or allocate the
 // memory as needed (just make sure you allocate enough memory and
 // free it when you're done!).
 sprintf(query, QUERY_POP, 9000000);
 // Execute the query
 cur_mem->execute(query);

sprintf() is used to insert variable values into a text string. in this code example, the sprintf(query,QUERY_POP,9000000); is taking the parameter const char QUERY_POP and inserting the value 9,000,000 into it somewhere. The resultant zero terminated char array is returned in query.

so, what I think he is saying is that there are two similar copies of QUERY_POP in RAM at the same time, and his query buffer is allocated, but only used once.

Personally instead of using sprintf() I would use sprintf_P(). The difference is that the format string is stored in FLASH(program memory) instead of RAM.

char buf[100]; // character buffer to receive formatted string 
sprintf_P(buf,PSTR("This is a big number %ld"),9000000L);
Serial.println(buf);

with the AVR chips, the GCC complier uses RAM to store a copy of any string literal. So if you used:

char buf[100]; // character buffer to receive formatted string 
sprintf(buf,"This is a big number %ld",9000000L);
Serial.println(buf);

The String literal "This is a big number %ld" exists in two memory locations, in FLASH, then in a permanent RAM location so that it's address can be passed to the sprintf() function. The Harvard architecture of the AVR chips have two memory spaces, RAM and FLASH. All data is assumed to be in RAM, so any literal string must reside in RAM, but through a little extra work using the PSTR() (Program Memory, FLASH) macro, the direct address in FLASH of a literal can be known. Using this direct FLASH memory address, with functions that know how to access it, Constant data can be stored in FLASH without wasting RAM.
[/code]

Chuck.

Thank you Chuck for the very thorough answer. I think I will follow your example and try to experiment with sprintf_P().

Again, thanks for your answer!

Best
Rune