mySQL connector issue

Projects Programming
1

I am having a strange issue (probably just dont understand and cant find other topics) regarding sending info to a mySQL database. I can send integers to multiple fields but don't understand what I am doing wrong that I cannot send the date up to a datetime field.

I also cannot send a string like a name. The field just gets a single charachter or some random integer depending on what I have the placeholder set to.

could the issue be that I cant get the dtostrf to work as the board we are using is the MKR1000?

here is some of my code of the cloud update void:

void cloudUpload() {

    String dateStamp="";
    String timeStamp="";
    dateStamp += "20";
    dateStamp += (rtc.getYear());
    if (rtc.getMonth() < 10) {
    dateStamp += "0";
    }
    dateStamp += (rtc.getMonth());
    if (rtc.getDay() < 10) {
    dateStamp += "0";
    }
    dateStamp += (rtc.getDay());

    if (rtc.getHours() < 10) {
    timeStamp += "0";
    }
    timeStamp += (rtc.getHours());
    //timeStamp += ":";
    if (rtc.getMinutes() < 10) {
    timeStamp += "0";
    }
    timeStamp += (rtc.getMinutes());
    //timeStamp += ":";
    if (rtc.getSeconds() < 10) {
    timeStamp += "0";
    }
    timeStamp += (rtc.getSeconds());
         
     //Serial.println(timeStamp);
     //Serial.println(cardName);

     String cardName = "Dandy";
     

char INSERT_DATA[] = "INSERT INTO tek.access (timedateStamp, custSerial, cardCode, facilityCode, readerNum, accessGranted, cardName, timeStamp) VALUES ('%d','%d','%d','%d','%d','%d','%S','%d')";
char query[128];

WiFiClient client;
MySQL_Connection conn((Client *)&client);

 //Serial.println("db Connecting...");
  if (conn.connect(server_addr, 3306, user, password)) {
    delay(500);
    MySQL_Cursor *cur_mem = new MySQL_Cursor(&conn);
    //dtostrf(50.125, 1, 1, custSerial);  // this does not seem to work on the MKR1000 
    sprintf(query, INSERT_DATA, dateStamp.toInt(), custSerial, cardCode, facilityCode, readerNum, accessGranted, cardName, timeStamp.toInt());
    // Execute the query
    cur_mem->execute(query);
    delete cur_mem;
  }
  else
    Serial.println("db Connection failed.");
  conn.close();

}

Any help or pointing in the correct direction would be good and thanks!
Andrew

2

cardName is an Arduino String, I don't think this is what %S will handle in your sprintf()
where is the buffer for custSerial defined?

Don't post snippets (Snippets R Us!)

3

int custSerial = 2;
right at the top, this is just a customer ID, integer numeric

so cardName is a reserved variable?

4

we would have loved to see the top... :grimacing:

that does not look like a char buffer, does-it? check again what's expected for dtostrf()

don't you get compiler warnings or error on the %S ?
suggest you try to print query...

5

I have looked up differente placeholders and cannot find what I should use if %s is not correct placeholder for the string variable

6

so you went for %S based on what documentation?
No it's not a reserved word. Just that sprintf() does not deal with Arduino String.

use %s and for the parameter use cardName.c_str()

7

no compile errors....

nothing else defined at the top (yet) as the name currently is just defined at the top of the void

but the biggest issue is I cannot grab the time and date and send it to the database it just enters 0000-00-00 00:00:00

8

I will try that for the cardName.... will let you know

Thoughts on the timedate?

9

worked like a charm....!!!

10

I think Mysql is fussy about date format. Make sure you can do it from a SQL window on your server before trying to do it in code.

11

OK I will try to do that

12

May be something like this but as your code is not complete, I've no clue if the %d placeholders make sense or not

String dateStamp = "20";
dateStamp += rtc.getYear();
if (rtc.getMonth() < 10) dateStamp += "0";
dateStamp += (rtc.getMonth());

if (rtc.getDay() < 10) dateStamp += "0";
dateStamp += rtc.getDay();

String timeStamp = "";
if (rtc.getHours() < 10) timeStamp += "0";
timeStamp += rtc.getHours();
if (rtc.getMinutes() < 10) timeStamp += "0";
timeStamp += rtc.getMinutes();
if (rtc.getSeconds() < 10) timeStamp += "0";
timeStamp += rtc.getSeconds();

String cardName = "Dandy";

char custSerial[20];
dtostrf(50.125, 1, 1, custSerial);

char INSERT_DATA[] = "INSERT INTO tek.access (timedateStamp, custSerial, cardCode, facilityCode, readerNum, accessGranted, cardName, timeStamp) VALUES ('%s','%s','%d','%d','%d','%d','%s','%s')";
char query[256];

sprintf(query, INSERT_DATA, dateStamp.c_str(), custSerial, cardCode, facilityCode, readerNum, accessGranted, cardName.c_str(), timeStamp.c_str());
13

just a bit more fiddling with your code and the timedate stamp now uploads.... thanks

2022-02-05 19:07:20 0 44175 30 3 0 Dandy

14

This topic was automatically closed 180 days after the last reply. New replies are no longer allowed.