C function to get data from a MySQL Database and store as a variable

Question

I am able to get a c function to INSERT and UPDATE a cell on a table, but I am having issues to be able to get a value from a cell and have it saved as a variable that I can use to compare and do calculations on it. Thank you in advance.

void MIA_get_data_temperature()
{

    MYSQL_RES *query_results = mysql_store_result(conn);
    MYSQL_ROW row; //This will declare row variable 

    //int total_rows = mysql_num_rows(query_results);
    int num_fields = mysql_num_fields(query_results);
    int i;
    char buffer[256]; // Setting buffer for query string


    const char *query = "SELECT Temperature FROM `temperature` WHERE Mode='Current_Temperature'"; 


    //snprintf() - safer thatn sprint USE THIS
    //checking to make sure query string is not to large for buffer & formatting query to get passed
    if (snprintf(buffer, sizeof(buffer), query) >= sizeof(buffer))
    {
        printf("Issue with Buffer \n");
        exit (-1);

    }



    //Reading from MySQL Table

    if(mysql_query(conn, buffer) !=0)
    {
        fprintf(stderr, "%s\n", mysql_error(conn));
        exit (-1);
    } else {

        while((row = mysql_fetch_row(query_results)) !=0)
        {
            for (i = 0; i < num_fields; i++)
            {
                ??? Saving Result as a variable 
            }

        }
    }       
}

user3813674 · Answer 1 · 2016-01-21T00:37:29.163

1

How about this?

  char* s;        
  while((row = mysql_fetch_row(query_results)) !=0)
    {
      for (i = 0; i < num_fields; i++)
        {
          s = row[i] ? row[i] : "NULL";
          printf("%s\n",s);
        }
    }

NOTE: It also depends on your num_fields. You could declare an array to hold all the retrieved cells in a row. I'm just demonstrating how to store a variable and print it off.

edited Jan 21 '16 at 00:37

answered Jan 21 '16 at 00:31

user3813674

2,553
2
15
26

Thank you for the response, but I do have a question, do you why I can compile the code, but when I run it and I call that function I get an error 139? – Vlad Jan 21 '16 at 00:39
I added an edit to take care of the case where `row[i]` is null. – user3813674 Jan 21 '16 at 00:40
void MIA_get_data_temperature() that code in the question is the whole function. And when I call it in the Main() function, I get that error code. – Vlad Jan 21 '16 at 00:41
Okay use `gdb` and see where did it get the `segmentation fault`. – user3813674 Jan 21 '16 at 01:01
I have never heard of gdb, can you please explain how to use it? – Vlad Jan 21 '16 at 01:02
Use any debugger on your IDE then. – user3813674 Jan 21 '16 at 01:03
I will be honest I have no idea how to do that, I am using Geany – Vlad Jan 21 '16 at 01:14

score 1 · Accepted Answer · answered Jan 21 '16 at 23:50

1

One of the most obvious problems with your code is that you're doing things in the wrong order -- you need to run the SELECT query before you grab the result set, so you should be calling mysql_query() before you call mysql_store_result() and mysql_num_fields() instead of after.

Also, although it won't hurt anything, you don't need to snprintf() your query string into a buffer here because you aren't writing any formatted values into it.

As for actually saving the values from the query, they're returned as text, so if you want something else, like a float or an int, you'll need to convert it accordingly.

So if we adjust your code a bit, skip the unnecessary snprintf(), and do something trivial with the results (assuming Temperature was a float value), we might get:

void MIA_get_data_temperature()
{
  const char *query = "SELECT Temperature FROM `temperature` WHERE "
                      "Mode='Current_Temperature'";

  if (mysql_query(conn, query) != 0)
  {
    fprintf(stderr, "%s\n", mysql_error(conn));
    exit(-1);
  } else {
    MYSQL_RES *query_results = mysql_store_result(conn);
    if (query_results) { // make sure there *are* results..
      MYSQL_ROW row;

      while((row = mysql_fetch_row(query_results)) !=0)
      {
        /* Since your query only requests one column, I'm
         * just using 'row[0]' to get the first field. */

        /* Set a float 'f' to the value in 'row[0]', or
         * 0.0f if it's NULL */
        float f = row[0] ? atof(row[0]) : 0.0f;

        /* Do whatever you need to with 'f' */
        printf("%f\n", f);
      }

      /* Free results when done */
      mysql_free_result(query_results);
    }
  }
}

answered Jan 21 '16 at 23:50

Dmitri

9,175
2
27
34

you mt friend are a hero!! I have been looking at this code for hours now, and looking at different examples online, and could not find what was wring with it. I am really new to C, and have been learning on my own. – Vlad Jan 22 '16 at 00:03
1

If your query retrieved more columns, the first would be in `row[0]`, the second in `row[1]`, third in `row[2]`, etc. You can call `mysql_num_fields(query_results)` to find out how many columns there are in the result set, and there are also other functions that can be used to get the column names and other information about the columns if needed. – Dmitri Jan 22 '16 at 00:31
Okay thank you again, just to make sure mysql_num_fields() returns an unsigned int? Like can I use the function to compare or assign it to a variable and print our the number of columns? – Vlad Jan 22 '16 at 00:40
Yes, `unsigned int`. – Dmitri Jan 22 '16 at 00:43
I have another question if you do not mind. So I got it to work so far, get the float and use a pointer to a global variable so other functions can call it and use it. My question is when getting the data from the DB, it comes out as a string, how can I get that string and save it as a global value so I can use it to compare to other strings? – Vlad Jan 28 '16 at 19:58
@Vlad Can you not just compare as `float`s? If you must keep it a string, you need to set aside storage (have a sufficiently large `char` array, or `malloc()` enough space) and copy eg. with `strcpy()` or similar. You might consider working through a C book or tutorial... but I can't really recommend one (I started with C 25 years ago... the books I started with are out of print and out of date, and were okay but not great even at the time). – Dmitri Jan 29 '16 at 00:31
can you check out this post? It is regarding my question, in a separate post. Can you help me find out what I am doing wrong? http://stackoverflow.com/questions/35074059/getting-a-mysql-query-to-save-as-a-global-variable-is-c?noredirect=1#comment57871951_35074059 – Vlad Jan 29 '16 at 00:45

C function to get data from a MySQL Database and store as a variable

2 Answers2