efficient way of updating tables using mysql c api

Question

I 'm doing an data logger project where i get data from the sensors and store them in a database . I'm using mysql database which is hosted on Beaglebone (Arm linux based computer ),i'm using C api's to work with the mysql database.

I poll the sensors with a sample time of 5 seconds and get data from them and store them onto the tables as per the below code , the code does what is meant to do i just wanted to know whether there's an efficient way of updating the tables .Below is the code

    #include <my_global.h>
    #include <mysql.h>
    #include <stdlib.h>
    #include <unistd.h>
    #include <string.h>

    int main(int argc,char *argv[])
    {

    /* creates a new mysql object */
     MYSQL *con = mysql_init(NULL);

     float tempval = 0;
     float humidval=0;

     char query[100]={0};

     if (con == NULL) 
     {
          fprintf(stderr, "%s\n", mysql_error(con));
          exit(1);
     }

    /* connect to db */
     if(mysql_real_connect(con,"localhost","root","passwrd12#",0,0,0,0)==NULL)
     {
         fprintf(stderr, "%s\n", mysql_error(con));
         mysql_close(con);
         exit(1);

     }
/* Select the db */

      if(mysql_select_db(con,"TestDb")!=0)
      {
        fprintf(stderr,"%s \n",mysql_error(con));
        mysql_close(con);
        exit(1);
      }

     while(1)
     {

      //Read temperature and humidity sensors on pin1 and pin2 

        tempval=Read_Sensordata(1);
        humidval=Read_Sensordata(2)

      memset(query,0,sizeof query);

/* update the temperature and humidity values */    
      sprintf(query,"UPDATE Datavalues SET Temperature = %f,Humidity = %f,Time=NOW() WHERE Rownum=0",tempval,humidval);

      if (mysql_query(con,query)) 
      {
          fprintf(stderr, "%s\n", mysql_error(con));
          mysql_close(con);
          exit(1);
      }

       usleep(5000000);


     }
      mysql_close(con);

      exit(0);

    }

Define "efficient". What is wrong with your code? Did you mean to post this on CodeReview? — Lightness Races in Orbit
– Lightness Races in Orbit, Commented May 3, 2014 at 14:34
Yes, I know. That does not matter. Stack Overflow is not for you. That you got your problem solved is entirely irrelevant. Your question is supposed to help somebody else some day, so please fix it by addressing my previous comment. — Lightness Races in Orbit
– Lightness Races in Orbit, Commented May 3, 2014 at 19:00
@LightnessRacesinOrbit : I follwed the answer given below and will modify my code . was using the above code these days . And from now on i'll ensure that similar kind of questions will be posted in code review and not here . I didnt know that there was a site on code reviews on stack exchange .Thanks for pointing it. — Santhosh Pai
– Santhosh Pai, Commented May 5, 2014 at 5:30

eggyal · Accepted Answer · 2018-10-10 21:08:59Z

Rather than execute a fresh query each time, you could prepare the query once and execute it each time with the revised values. For example (without any error checking):

strmov(query, "               \
  UPDATE Datavalues           \
  SET    Temperature = ?,     \
         Humidity    = ?,     \
         Time        = NOW()  \
  WHERE  Rownum      = 0      \
");

MYSQL_STMT *stmt = mysql_stmt_init(con);
mysql_stmt_prepare(stmt, query, strlen(query));

MYSQL_BIND bind[2];
memset(bind, 0, sizeof(bind));
bind[0].buffer_type = bind[1].buffer_type = MYSQL_TYPE_FLOAT;
bind[0].buffer = (char *) &tempval;
bind[1].buffer = (char *) &humidval;
mysql_stmt_bind_param(stmt, bind);

while (1) {
  tempval  = Read_Sensordata(1);
  humidval = Read_Sensordata(2);
  mysql_stmt_execute(stmt);
  usleep(5000000);
}

mysql_stmt_close(stmt);

Furthermore, you may wish to consider utilising MySQL's Automatic Initialization and Updating for TIMESTAMP and DATETIME to save you having to explicitly set the Time column from within the UPDATE command.

Collectives™ on Stack Overflow

efficient way of updating tables using mysql c api

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related