Code Getting Hung Up On Multiple Insert Functions

[nicktrick92]

So I've been rolling with the library when it comes to sql select statements, but I've hit quite the snag as I now approach the need for insert statements in my code. I have the below two functions that I've created:

void setPuzzleState(String room, String state, int puzzleID) {
  String queryString = formatString("UPDATE redacted.puzzlestates set state='%s', lastupdate=CURRENT_TIMESTAMP WHERE Room='%s' AND puzzleid=%s", state, room, String(puzzleID));
  char query [queryString.length() + 1];
  queryString.toCharArray(query, queryString.length() + 1);
  Serial.print(query);

  //EXECUTE QUERY
  cur.execute(query);
  cur.close();
}

void setPuzzleStates(String room, String state) {
  String queryString = formatString("UPDATE redacted.puzzlestates set state='%s', lastupdate=CURRENT_TIMESTAMP WHERE Room='%s'", state, room);
  char query [queryString.length() + 1];
  queryString.toCharArray(query, queryString.length() + 1);
  Serial.print(query);

  //EXECUTE QUERY
  cur.execute(query);
  cur.close();
}

The first of the two changes a specific column of a specific row in the DB, while the second changes a specific column in all rows (That match the room name). Please note, I wrote the formatString function myself since I'm more of a lua guy, and a more farmiliar string formatting function made my job alot easier.

The interesting thing now is that I can run one of these as many times as I want. However, if I try to call both, in any order, the code will get caught on "Connecting..." See below:

These work:

Serial.println("Connecting...");
if (conn.connect(server_addr, 3306, user, password)) {
    delay(1000);
    setPuzzleStates("TEST", "TEST");
    setPuzzleStates("TEST", "TEST");
  }
  else {
    Serial.println("Connection failed.");
  }

and

Serial.println("Connecting...");
if (conn.connect(server_addr, 3306, user, password)) {
    delay(1000);
    setPuzzleState("TEST", "TEST", 10);
    setPuzzleState("TEST", "TEST", 10);
  }
  else {
    Serial.println("Connection failed.");
  }

However, this does not work:

Serial.println("Connecting...");
if (conn.connect(server_addr, 3306, user, password)) {
    delay(1000);
    setPuzzleStates("TEST", "TEST");
    setPuzzleState("TEST", "TEST", 10);
  }
  else {
    Serial.println("Connection failed.");
  }

Any input would be greatly appreciated since I've been going at this for a couple hours without being able to solve anything. I've tried just about every debug option I can think of. Thanks in advance.

P.S. I realize the code is sloppy and portions such as the String -> Char[] is bad. This is iteration one for testing purposes and that same method is working flawlessly in the select functions, it seems to only be the insert functions giving me grief.

[Muller59]

Nicktrick,

try

MySQL_Connection my_conn((Client *)&client);

my_conn.connect(MySQL_server_addr, MySQL_Port, MySQL_User, MySQL_Password)

then when writing in a loop, for each write do

char INSERT_DATA[] = "INSERT INTO rob.table1 (tab_key, datetime, message) VALUES (%d, \"%s\", \"%s\") ON DUPLICATE KEY UPDATE datetime=\"%s\",message=\"%s\" "; char query[1000]; // used to send the query to MySQL char isoDate[20]; // formatted date to send to MySQL

MySQL_Cursor *cur_mem = new MySQL_Cursor(&my_conn); // Prepare query statement sprintf(query, INSERT_DATA, Count, isoDate, "Message content",isoDate, "Message content updated"); cur_mem->execute(query); // Note: since there are no results, we do not need to read any data // Deleting the cursor also frees up memory used delete cur_mem;

just my €.02

Regards,

Rob

[nicktrick92]

I restructured my function to match the above, but still no luck, unfortunately. I know it isn't an issue with the query being bad or misformatted, as I've printed it out in a debug. Here is what the function now looks like:

void setPuzzleState(String room, String state, int puzzleID) {
  String queryString = formatString("UPDATE majorescapes.puzzlestates set state='%a', lastupdate=CURRENT_TIMESTAMP WHERE Room='%b' AND puzzleid=%c", state, room, String(puzzleID));
  char query [queryString.length() + 1];
  queryString.toCharArray(query, queryString.length() + 1);
  Serial.print(query); //DEBUG

  MySQL_Cursor *cur_mem = new MySQL_Cursor(&conn);
  cur_mem->execute(query);
  delete cur_mem;
  return;
}

For more reference, this what what the top of my code looks like:

IPAddress server_addr(192,168,1,16);
char user[] = "usernameWasHere";   
char password[] = "passwordWasHere";  

EthernetClient client;
MySQL_Connection conn((Client *)&client);
// Create an instance of the cursor passing in the connection
MySQL_Cursor cur = MySQL_Cursor(&conn);

And the Setup:

void setup() {
  Serial.begin(115200);
  while (!Serial); 
  Ethernet.begin(mac_addr);
  Serial.println("Attempting to make a database connection...");
  if (conn.connect(server_addr, 3306, user, password)) {
    delay(1000);
  }
  else {
    Serial.println("Connection to database failed!");
  }

  setPuzzleState("TEST", "Test", 9); //Test statement that causes program to not work

}

The select functions are still working fine.

[nicktrick92]

After reading over the examples again, I noticed this at the top:

  CAUTION: Don't mix and match the examples. Use one or the other in your
           own sketch -- you'll get compilation errors at the least.

So I am going to assume that the use of both insert and select functions (or even multiple functions of the same type) within the same program is not possible. Which is unfortunate. I will have to use this library for the select statements and perhaps PHP for the insert statements. Unless the author has a workaround perhaps?

[ChuckBell]

No, that’s not what that means. You can use any combination of queries. It means don’t mix and match between the types of query instances or progmem examples.

Which version of MySQL are you using?

On Fri, Apr 5, 2019 at 14:53 nicktrick92 notifications@github.com wrote:

After reading over the examples again, I noticed this at the top:

CAUTION: Don't mix and match the examples. Use one or the other in your own sketch -- you'll get compilation errors at the least.

So I am going to assume that the use of both insert and select functions (or even multiple functions of the same type) within the same program is not possible. Which is unfortunate. I will have to use this library for the select statements and perhaps PHP for the insert statements. Unless the author has a workaround perhaps?

— 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/94#issuecomment-480385426, or mute the thread https://github.com/notifications/unsubscribe-auth/AH0j4ET7xrwp-y1yQUw06kRcwxRfaXoYks5vd5u8gaJpZM4cZgGe .

[ChuckBell]

PM me your entire sketch, please. d r c h a r l e s b e l l at g m a I l dot c o m

On Fri, Apr 5, 2019 at 14:55 Charles Bell drcharlesbell@gmail.com wrote:

No, that’s not what that means. You can use any combination of queries. It means don’t mix and match between the types of query instances or progmem examples.

Which version of MySQL are you using?

On Fri, Apr 5, 2019 at 14:53 nicktrick92 notifications@github.com wrote:

After reading over the examples again, I noticed this at the top:

CAUTION: Don't mix and match the examples. Use one or the other in your own sketch -- you'll get compilation errors at the least.

So I am going to assume that the use of both insert and select functions (or even multiple functions of the same type) within the same program is not possible. Which is unfortunate. I will have to use this library for the select statements and perhaps PHP for the insert statements. Unless the author has a workaround perhaps?

— 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/94#issuecomment-480385426, or mute the thread https://github.com/notifications/unsubscribe-auth/AH0j4ET7xrwp-y1yQUw06kRcwxRfaXoYks5vd5u8gaJpZM4cZgGe .

[ChuckBell]

Hi. Some general advice. Let me know if these help.

• I would back out your string handling and temporarily use normal char strings. Put back when it works.

• Do not attempt two queries without a delay between them. Add a delay of at least 1 or 2 seconds (or even 3) then reduce until it becomes unstable.

• Do not hold the connection open. Open, execute, then close through each pass in the loop().

• Send me your full sketch if you want more detailed suggestions (I need to see all of the code).

• Please check your MySQL version. The connector is only designed to work with Oracle provided MySQL. Any other variants have known to have issues.