How can I get a variable in C to be used in a MySQL Query

Question

I have a temperature sensor hooked up to a Raspberry Pi, I am able to read and printf the temperature. What I am trying to do next is to get the values from the sensor and have them get logged into a MySQL database.

Everything works, except, I can not figure out how to format the MySQL insert Query to use the f variable and the h variable that is generated by the read_dht_data() function and pass it to the MIA_temp_insert(float f, float h).

#include <stdio.h>
#include <stdlib.h>
#include <mysql/mysql.h>
#include <wiringPi.h>
#include <stdint.h>

#define MAX_TIMINGS 85
#define DHT_PIN     3   /* GPIO-22 */

int data[5] = { 0, 0, 0, 0, 0 };
float temp_f;

static char *host = "XXX.XXX.XXX.XXX";
static char *user = "XXX";
static char *password = "XXX";
static char *dbname = "XXX";

unsigned int port = 3306;
static char *unix_socket = NULL;
unsigned int flag = 0;

static  MYSQL *conn; //Needed to be static so all of the functions can draw from it

void MIA_mysql_connection()
{
    // Connecting to the Database

    conn = mysql_init(NULL);

    if (!(mysql_real_connect(conn, host, user, password, dbname, port, unix_socket, flag)))
    {
        fprintf(stderr, "\n Error: %s [%d] \n", mysql_error(conn),mysql_errno(conn));
        exit (1);
    }

    //Connected
    printf ("We Are Connected \n");
}

void MIA_mysql_close()
{
    //Closing Connection
    mysql_close(conn);

}

void MIA_temp_insert(float f, float h)
{

    //Inserting into MySQL Table
    if(mysql_query(conn, "INSERT INTO `temperature` (`id`, `Date`,`Time`, `Temperature`, `Humidity`) VALUES (NULL, CURRENT_DATE(), CURRENT_TIME(), '%f' , '%f')") !=0)
{
        fprintf(stderr, "%s\n", mysql_error(conn));
        exit (-1);
    } else {

        printf("Rows were insert \n");
    }   

}

void read_dht_data()
{
    uint8_t laststate   = HIGH;
    uint8_t counter     = 0;
    uint8_t j           = 0, i;

    data[0] = data[1] = data[2] = data[3] = data[4] = 0;

    /* pull pin down for 18 milliseconds */
    pinMode( DHT_PIN, OUTPUT );
    digitalWrite( DHT_PIN, LOW );
    delay( 18 );

    /* prepare to read the pin */
    pinMode( DHT_PIN, INPUT );

    /* detect change and read data */
    for ( i = 0; i < MAX_TIMINGS; i++ )
    {
        counter = 0;
        while ( digitalRead( DHT_PIN ) == laststate )
        {
            counter++;
            delayMicroseconds( 1 );
            if ( counter == 255 )
            {
                break;
            }
        }
        laststate = digitalRead( DHT_PIN );

        if ( counter == 255 )
            break;

        /* ignore first 3 transitions */
        if ( (i >= 4) && (i % 2 == 0) )
        {
            /* shove each bit into the storage bytes */
            data[j / 8] <<= 1;
            if ( counter > 16 )
                data[j / 8] |= 1;
             j++;
        }
    }

    /*
     * check we read 40 bits (8bit x 5 ) + verify checksum in the last byte
     * print it out if data is good
     */
    if ( (j >= 40) &&
         (data[4] == ( (data[0] + data[1] + data[2] + data[3]) & 0xFF) ) )
    {
        float h = (float)((data[0] << 8) + data[1]) / 10;
        if ( h > 100 )
        {
            h = data[0];    // for DHT11
        }
        float c = (float)(((data[2] & 0x7F) << 8) + data[3]) / 10;
        if ( c > 125 )
        {
            c = data[2];    // for DHT11
        }
        if ( data[2] & 0x80 )
        {
            c = -c;
        }
    float f = c * 1.8f + 32;

    //printf( "Humidity = %.1f %% Temperature = %.1f *C (%.1f *F)\n", h, c, f );
        printf ("Temp of the room is : %.1f \n",f);

        //Insert Data into MIA Temperature Table
         MIA_temp_insert(10.0,11.1);

    }else  {
        //printf( "Data not good, skip\n" );
    }
}

int main ()
{
    printf( "Raspberry Pi DHT11/DHT22 temperature/humidity test\n" );

    if ( wiringPiSetup() == -1 )
        exit( 1 );

    while ( 1 )
    {
    MIA_mysql_connection();
    read_dht_data();
    delay (1000);
    MIA_mysql_close();
    printf("End of program \n");
    }

    return(0);
}

Answer 1

You cannot use printf()-style format specifiers in mysql_query() directly as you have above. Instead, you can use sprintf() or snprintf() to write the format string -- with the formatted numbers instead of format specifiers -- into a buffer... then pass that as your query string.

So in MIA_temp_insert(), instead of the following:

if(mysql_query(conn, "INSERT INTO `temperature` (`id`, `Date`,`Time`, `Temperature`, `Humidity`) VALUES (NULL, CURRENT_DATE(), CURRENT_TIME(), '%f' , '%f')") !=0)
{
    fprintf(stderr, "%s\n", mysql_error(conn));
    exit (-1);
    /* ...etc... */

...you could try this:

/* Your original query string, with the %f specifiers */
const char *formatstring = "INSERT INTO `temperature` (`id`, `Date`,`Time`, `Temperature`, `Humidity`) VALUES (NULL, CURRENT_DATE(), CURRENT_TIME(), '%f' , '%f')";

/* snprintf() the query into a buffer, to fill in the numbers */
char buf[256]; // should be large enough for the query with the numbers filled in
if (snprintf(buf, sizeof(buf), formatstring, f, h) >= sizeof(buf))
{
    exit(-1); // if snprintf() returned sizeof(buf) or more, buf was too short
}

/* call mysql_query() with the formatted query from the buffer */
if (mysql_query(conn, buf) != 0)
{
    fprintf(stderr, "%s\n", mysql_error(conn));
    exit (-1);
    /* ...etc... */