Thread: How to properly return MySQL results to a calling function?

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;
}

Originally Posted by Effenberg0x0

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;
  }
...