How to properly return MySQL results to a calling function?

This is a discussion on How to properly return MySQL results to a calling function? within the C Programming forums, part of the General Programming Boards category; Hello, I am modifying a C program that had MySQL C API calls everywhere, so that all MySQL interaction is ...

  1. #1
    Registered User
    Join Date
    Jun 2011
    Posts
    2

    How to properly return MySQL results to a calling function? See code sample.

    Hello, I am modifying a C program that had MySQL C API calls everywhere, so that all MySQL interaction is done by a single safe and well-coded function. This function should receive the query strings as a parameter and must return the resultset, if any, to the calling function.

    My question is: If I am using return (MYSQL_RES) results; at the function, how can I properly free MySQL vars and properly close the MySQL connection? Please see some example code below:

    Code:
    //This is a single function aimed at doing all further MySQL querying. It must return resultsets to calling functions, if any is not NULL.
    MYSQL_RES query_db(char *my_query) {
    
      MYSQL *conn;
      MYSQL_RES *result;
      MYSQL_ROW row;
      int num_fields;
      int i;
    
      my_query=malloc(1000*sizeof(char));
      conn=mysql_init(NULL);
        if (conn == NULL) {
        printf("Error %u: %s\n", mysql_errno(conn), mysql_error(conn));
        exit(1);
      }
    
      if (mysql_real_connect(conn, server, user, 
          passwd, db, 0, NULL, 0) == NULL) {
          printf("Error %u: %s\n", mysql_errno(conn), mysql_error(conn));
          exit(1);
      }
      
      printf("\nMySQL client version: %s\n", mysql_get_client_info());
      printf("\nUsing:\n \
      SQL Server: [%s]\n \
      SQL DB: [%s]\n \
      SQL USER: [%s]\n", server, db, user);
    
      printf("\nRunning query: %s\n", my_query);
    
      if (mysql_query(conn, my_query)) {
        printf("Error %u: %s\n", mysql_errno(conn), mysql_error(conn));
        exit(1);
      }
      
      result = mysql_use_result(conn);
      if (result != NULL) {
        return *result;
      }
    
    //The following will never be executed if I return with the results above. This is a problem.
    mysql_free_result(result);
    mysql_close(conn);
    return 0;
    }
    
    
    //Some example function that uses the above (query_db) function
    int listProduct(char *productName) {
      MYSQL_RES query_results;
      MYSQL_ROW row;
      int num_fields;
      
      char *sql_query="SELECT FULL_DESCRIPTION FROM product_kb WHERE FULL_DESCRIPTION REGEXP \'484053\'";
      query_results=query_db(sql_query);
      MYSQL_ROW row;
      int num_fields;
      int i;
    
      num_fields = mysql_num_fields(query_results);
      while ((row = mysql_fetch_row(query_results))) {
      //Do some stuff with the data, regexp, etc. Lets just printf for now.
          for(i=0; i<num_fields; i++) {
            printf("%s ", row[i] ? row[i] : "NULL");
          }
          printf("\n");
        }
     return 0;
    }
    Last edited by Effenberg0x0; 06-08-2011 at 05:15 AM. Reason: typos

  2. #2
    Registered User
    Join Date
    Jun 2011
    Posts
    2
    UPDATE: I was told by someone that my only solution would be to use a global MYSQL_RES or create a similar global struct to hold the resultset, so I can free MySQL resources and close the connection, return 0 (of course previously defining the function as int) and still use the results at the calling function. Ok, I understand that is doable, but I think I should be avoiding global vars as much as possible: The full program is a threaded daemon that performs more than 100.000 queries a day. I think I can easily create a future problem if I rely too much on global vars.

  3. #3
    a_capitalist_story
    Join Date
    Dec 2007
    Posts
    2,648
    You are correct to want to avoid global variables. There's no reason you can't pass a pointer to the MYSQL_RES structure you've created on the stack in listProduct to the query_db function and populate that, although I believe you're going to have to use mysql_store_result rather than mysql_use_result, as according to the docs:

    mysql_use_result() initiates a result set retrieval but does not actually read the result set into the client like mysql_store_result() does. Instead, each row must be retrieved individually by making calls to mysql_fetch_row(). This reads the result of a query directly from the server without storing it in a temporary table or local buffer, which is somewhat faster and uses much less memory than mysql_store_result(). The client allocates memory only for the current row and a communication buffer that may grow up to max_allowed_packet bytes.
    I believe this means if you're closing your connection on exiting the query_db function, you will need to have actually retrieved all the results into the MYSQL_RES structure via mysql_store_result. You can free the result set in the listProduct function when you're done with it.

  4. #4
    _jr
    _jr is offline
    Registered User
    Join Date
    May 2006
    Posts
    10
    Quote Originally Posted by Effenberg0x0 View Post
    My question is: If I am using return (MYSQL_RES) results; at the function, how can I properly free MySQL vars and properly close the MySQL connection? Please see some example code below:
    Code:
    [B]
    // alter your function to return a  pointer
    MYSQL_RES *query_db(char *my_query) {
    ...  
      MYSQL_RES  *result;
    
    
      result = mysql_use_result(conn);
      // Don't need the conn anymore, close it here.
      mysql_close(conn);
      if (result != NULL) {
        // You shall use the returned 'result' pointer to free the result set (mysql_free_result)
        // whenever your're done on the calling function.
        return result;
      }
    ...
    Last edited by _jr; 06-09-2011 at 03:48 AM.

Popular pages Recent additions subscribe to a feed

Similar Threads

  1. strcspn return incorrect results
    By qualia in forum C Programming
    Replies: 5
    Last Post: 10-08-2010, 08:40 AM
  2. Calling a python script and obtaining a return value
    By bhdavis1978 in forum C Programming
    Replies: 3
    Last Post: 09-22-2010, 09:08 AM
  3. calling functions: exit and return
    By 911help in forum C Programming
    Replies: 3
    Last Post: 12-28-2007, 12:24 PM
  4. Help on how to properly return a value
    By dld333 in forum C++ Programming
    Replies: 12
    Last Post: 12-06-2005, 11:18 PM
  5. [WIN32] Printing MySQL results
    By publikum in forum C++ Programming
    Replies: 6
    Last Post: 01-30-2005, 06:33 AM

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21