none
How to enter data from the serial port to the MySQL database using the C / C ++ programming language. RRS feed

  • Question

  • I am again trying to get temperature data from a microcontroller via the Com3 serial port. The data will be entered in the mysql database using C / C ++ programming.

    From the outgoing serial port the data is:

    26.37
    26.27
    26.38
    26.50
    ...

    The MySQL table is:

    +-------+---------+------+-----+---------+----------------+
    | Field | Type    | Null | Key | Default | Extra          |
    +-------+---------+------+-----+---------+----------------+
    | id    | int(11) | NO   | PRI | NULL    | auto_increment |
    | temp  | float   | YES  |     | NULL    |                |
    +-------+---------+------+-----+---------+----------------+

    Code C / C ++ are read serial ports are:

        char TempChar;
        DWORD NoBytesRead;
    
        char query[100];
        char INSERT_DATA[] = "INSERT INTO lm35 (id, temp) VALUES (%d,%f)";
    do{
        ReadFile(hComm,&TempChar,sizeof(TempChar),&NoBytesRead,NULL);
        sprintf(query, INSERT_DATA, 00, (float)TempChar);
    
         if (mysql_query(conn,query))
          {
           printf(" Error: %s\n", mysql_error(conn));
           return 0;
        }
    
          printf("OK \n");
    }while(!kbhit());
    
     CloseHandle(hComm);
     mysql_close(conn);

    Then after it is run, in the mysql table data will be entered as below:

    +---+------+
    | id| temp |
    +---+------+
    | 1 |   50 |
    | 2 |   10 |
    | 3 |   13 |
    | 4 |   56 |
    | 5 |   56 |
    +---+------+

    Is the temperature data from the serial port entering the MySQL table inaccurate? How about the C programming code to enter the correct data?

    Thursday, November 14, 2019 10:46 AM

Answers

  • Ok,

    instead of inserting all TempChar into the database, you must insert a full line into the database.

    So you must at first create the line and then call atof and the insert command:

    char TempChar;
    DWORD NoBytesRead;
    
    char query[100];
    char INSERT_DATA[] = "INSERT INTO lm35(id,suhu) VALUES(%d,%f)";
    char szline[100];
    double f;
    szline[0] = 0;
    int index = 0;
    do {
    	ReadFile(hComm, &TempChar, sizeof(TempChar), &NoBytesRead, NULL);
    	printf("%c", TempChar);
    
    	if (TempChar != '\n')
    	{
    		szline[index++] = TempChar;
    	}
    	else
    	{
    		szline[index++] = '\0';
    		f = atof(szline);
    		sprintf(query, INSERT_DATA, 0, f);
    
    		if (mysql_query(conn, query))
    		{
    			printf(" Error: %s\n", mysql_error(conn));
    			return 0;
    		}
    		szline[0] = 0;
    		index = 0;
    	}
    } while (!kbhit());
    

    Regards, Guido

    • Marked as answer by zacknov Friday, November 15, 2019 8:41 AM
    Friday, November 15, 2019 6:50 AM

All replies

  • Hello,

    what are you reading in ReadFile? You only give a char but a char is not the output that you show (26.37,26.27,...).

    You should debug your code. We cannot help you debugging you code.

    But the ReadFile seems to be wrong and the sprintf is definitly wrong: you should not cast a char to float. What do you expect to get? You should ReadFile a string ( char[MAXSTRING] ) per line ("26.37", "26.27",...), then you can use atof to convert the string to float.

    Regards, Guido

    Thursday, November 14, 2019 10:58 AM
  • On a USB microcontroller device connected. The microcontroller sends data to the USB port (serial port) on Com3, in the form of:
    26.37
    26.27
    Then the data with ReadFile reads the serial port to be entered into the MySQL database. So how do you do it?
    Thursday, November 14, 2019 1:26 PM
  • On a USB microcontroller device connected. The microcontroller sends data to the USB port (serial port) on Com3, in the form of:
    26.37
    26.27
    Then the data with ReadFile reads the serial port to be entered into the MySQL database. So how do you do it?

    Here is a class for Serial communication: http://www.naughter.com/serialport.html

    You must ensure that you read the values correctly. Normally the values should be in a char array (string). Then it is easy to convert the value to float, e.g. atof.

    You still don't show us how you get the values 26.37, 26.27 in their own lines. This is how you must read the port. Your ReadFile is wrong.

    You don't have a problem in putting the value to the database. The problem is reading the serial port. Your INSERT-command is correct, but you must have the correct float value.

    Regards, Guido


    Thursday, November 14, 2019 2:02 PM
  • Code C / C ++ are read serial ports are:

        char TempChar;
        DWORD NoBytesRead;
    
        char query[100];
        char INSERT_DATA[] = "INSERT INTO lm35 (id, temp) VALUES (%d,%f)";
    do{
        ReadFile(hComm,&TempChar,sizeof(TempChar),&NoBytesRead,NULL);
        sprintf(query, INSERT_DATA, 00, (float)TempChar);
    
         if (mysql_query(conn,query))
          {
           printf(" Error: %s\n", mysql_error(conn));
           return 0;
        }
    
          printf("OK \n");
    }while(!kbhit());
    
     CloseHandle(hComm);
     mysql_close(conn);

    It does not seem likely that a single byte can contain the values you are expecting.  After the call to ReadFile, add a debugging call to printf to display the value in TempChar in hexadecimal.  That will tell you what values you are processing.

    sprint is a variadic function.  Any float values passed as arguments are automatically converted to double.  You might as well change your cast to double and avoid a second conversion.

    Just out of curiosity, is there a reason you want an octal value for the third argument to sprint?

    Thursday, November 14, 2019 2:31 PM
  • My microcontroller issued the data temperature is as shown below:

    I use the code below to retrieve that data. The code is:

    #include <stdio.h>
    #include <stdlib.h>
    #include <mysql.h>
    
    int main()
    {
     MYSQL *conn;
    
     char *server = "localhost";
     char *user = "root";
     char *password = "";
     char *database = "c_dan_mysql";
    
    conn = mysql_init(NULL);
    
     if (!mysql_real_connect(conn, server,user, password, database, 0, NULL, 0))
     {
       printf(" MySQL Server %s. Error: %s\n", server, mysql_error(conn));
       return 0;
     }
    
    
        HANDLE hComm;
        hComm=CreateFile("\\\\.\\COM3",GENERIC_READ | GENERIC_WRITE, 0, NULL,
                         OPEN_EXISTING,0,NULL );
        DCB dcbSerialParams={0};
    
        dcbSerialParams.DCBlength=sizeof(dcbSerialParams);
        GetCommState(hComm,&dcbSerialParams);
    
        dcbSerialParams.BaudRate=CBR_9600;
        dcbSerialParams.ByteSize=8;
        dcbSerialParams.StopBits=ONESTOPBIT;
        dcbSerialParams.Parity=NOPARITY;
    
        SetCommState(hComm,&dcbSerialParams);
    
        COMMTIMEOUTS timeouts={0};
        timeouts.ReadIntervalTimeout=50;
        timeouts.ReadTotalTimeoutConstant=50;
        timeouts.ReadTotalTimeoutMultiplier=10;
        timeouts.WriteTotalTimeoutConstant=50;
        timeouts.WriteTotalTimeoutMultiplier=10;
    
    //SetCommTimeouts(hComm,&timeouts);
    
       SetCommMask(hComm,EV_RXCHAR);
    
        DWORD dwEventMask;
        WaitCommEvent(hComm,&dwEventMask,NULL);
    
        char TempChar;
        DWORD NoBytesRead;
    
        char query[100];
        char INSERT_DATA[] = "INSERT INTO lm35(id,suhu) VALUES(%d,%f)";
    
         do{
           ReadFile(hComm,&TempChar,sizeof(TempChar),&NoBytesRead,NULL);
           sprintf(query, INSERT_DATA, 00, (float)TempChar);
           printf("%c",TempChar);
    
         if (mysql_query(conn,query))
          {
           printf(" Error: %s\n", mysql_error(conn));
           return 0;
          }
    
    
    
        }while(!kbhit());
     printf("OK \n");
     CloseHandle(hComm);
     mysql_close(conn);
    
    
     return 0;
    
    }
    

    The results of running the code above are:

    When opened in a database, the result is:

    So the value in the Mikrontroller is the same as the result of running the C language but not the same value in the mysql database. The problem is converting the serial port data and entering it into MySQL. I do not know how. Using the atof function, it must be a program error. Sorry if over and over because I first tried programming C on the serial port. Thank you ...

    Thursday, November 14, 2019 3:23 PM
  • Since your call to printf does not print a '\n', the only way you could get the output you show is if ReadFile stored the following characters in TempChar in succession: '2', '8', '\n', '2', '7', '.', etc.  This is further born out by the fact that '2' is stored in TempChar as 0x32.  When that value is converted to float, it becomes 50.0 which what you send to SQL.  '8' is stored as 0x38 which after conversion becomes 56.0.  '\n' is stored as 0x0d which becomes 13.0 followed by 0x0a which becomes 10.0.  Etc.

    The bottom line is you are not processing the output from ReadFile correctly.  You must either read a char at a time (until you hit the 0x0d0a) and manually concatenate them together to get a float value you want (using something like strtod instead of a cast) or maybe you read a block of characters at once and parse them yourself knowing that each value terminates with a 0x0d0a sequence.

    I don't know ReadFile at all but I wonder if there is some way to tell it to read a block of characters and stop at the 0x0d0a (effectively read a line at a time).  This would eliminate the need for you to parse.


    Friday, November 15, 2019 2:14 AM
  • Ok,

    instead of inserting all TempChar into the database, you must insert a full line into the database.

    So you must at first create the line and then call atof and the insert command:

    char TempChar;
    DWORD NoBytesRead;
    
    char query[100];
    char INSERT_DATA[] = "INSERT INTO lm35(id,suhu) VALUES(%d,%f)";
    char szline[100];
    double f;
    szline[0] = 0;
    int index = 0;
    do {
    	ReadFile(hComm, &TempChar, sizeof(TempChar), &NoBytesRead, NULL);
    	printf("%c", TempChar);
    
    	if (TempChar != '\n')
    	{
    		szline[index++] = TempChar;
    	}
    	else
    	{
    		szline[index++] = '\0';
    		f = atof(szline);
    		sprintf(query, INSERT_DATA, 0, f);
    
    		if (mysql_query(conn, query))
    		{
    			printf(" Error: %s\n", mysql_error(conn));
    			return 0;
    		}
    		szline[0] = 0;
    		index = 0;
    	}
    } while (!kbhit());
    

    Regards, Guido

    • Marked as answer by zacknov Friday, November 15, 2019 8:41 AM
    Friday, November 15, 2019 6:50 AM
  • @Guido Thank you,I've tried the above code, it's been running and successful. Programs compiled with C are:

    And the data inputted into MySQL are:

    I will learn more about this code. I am very happy because it's one of the subjects of my teaching about the data acquisition.

    Friday, November 15, 2019 7:54 AM
  • @Guido Thank you,I've tried the above code, it's been running and successful. 

    You're welcome. So now you can close this question by marking the answer. Then other people who have the same problem can find the answer with the code easily.

    Regards,
    Guido

    Friday, November 15, 2019 8:14 AM
  • @Guido Franzke ok, already marked the answer. :-)
    Friday, November 15, 2019 8:43 AM

  • do {
    	ReadFile(hComm, &TempChar, sizeof(TempChar), &NoBytesRead, NULL);
    	printf("%c", TempChar);
    
    	if (TempChar != '\n')
    	{
    		szline[index++] = TempChar;
    	}
    	else
    	{
    		szline[index++] = '\0';
    		f = atof(szline);
    		sprintf(query, INSERT_DATA, 0, f);
    


    You should be aware that ReadFile here treats the end-of-line sequence (0xod0a) differently than the standard I/O routines.  The standard routines such as fgetc will combine the 0x0d0a sequence into a single '\n' (0x0a) in the buffer.  ReadFile returns each character in turn for a total of 2.

    For example, if the data source contains "z\x0d\x0aZ\x0d\x0a", then
       repeated calls to fgetc would return 'z', '\n', 'Z'. '\n'
       but repeated calls to ReadFile would return 'z', '\x0d', '\n', 'Z'. '\x0d' '\n'

    This is significant because for your data, when the first '\n' is encountered, szline contains "27.83\xod".  The call to atof works because atof is smart enough to stop when it detects any character that could not be part of this conversion.

    Saturday, November 16, 2019 10:13 AM