Stored procedure - Commands out of sync after mysql_free_result()
I have written the following code to call a stored procedure which returns a temporary table and stores a value in a variable:
MYSQL *conn;
MYSQL_RES *res;
MYSQL_ROW row;
void read_data()
{
int num_cols, j;
/* create the string for first statement */
strcpy(buff, "CALL proc_read_user('");
concat(buff, date_start);
concat(buff, "', '");
concat(buff, date_end);
concat(buff, "', '");
concat(buff, user_id);
concat(buff, "', '");
concat(buff, curr_time);
concat(buff, "', @");
concat(buff, tot_time);
concat(buff, ");");
if(mysql_query(conn, buff))
{
fprintf(fptr_errors, "Error : Procedure not called : %s\n", mysql_error(conn));
exit(1);
}
if(!(res = mysql_store_result(conn)))
{
fprintf(fptr_errors, "no result returned by procedure\n");
exit(1);
}
num_cols = mysql_num_fields(res);
while ((row = mysql_fetch_row(res)) != NULL)
{
fprintf(fptr_reads, "%s", row[0]);
for(j=1;j<num_cols;++j)
{
fprintf(fptr_reads, "\t||\t%s", row[j]);
}
fprintf(fptr_reads, "\n\n");
}
mysql_free_result(res);
res = mysql_store_result(conn);
mysql_free_result(res);
/* Second statement */
strcpy(buff, "SELECT @");
concat(buff, tot_time);
concat(buff, ";");
if (mysql_query(conn, buff))
{
fprintf(fptr_errors, "Error in reading the total time : %s\n", mysql_error(conn));
exit(1);
}
if(!(res = mysql_use_result(conn)))
{
fprintf(fptr_errors, "No result returned by select query\n");
exit(1);
}
while ((row = mysql_fetch_row(res)) != NULL)
{
//if(row[0]==NULL) cout<<sizeof(row)<<'\n';
cout<<row[0]<<'\n';
fprintf(fptr_reads, "Total time : %s second(s)\n", row[0]);
}
}
------------------------------------------------------------------------
The two MySQL statements that are executed are :
CALL proc_read_user('2008-01-01', '2013-12-31', 'ashish', '23:59:59', @tot_time_01);
SELECT @tot_time_01;
The first statement is executed successfulyy and the temporary table is retuned and printed.
However, after that when I execute the second statement, the error is igven:
Commands out of sync; you can't run this command now
I have used mysql_store_result() as well as mysql_free_result() functions before executing the second statement.
Then why does this error occur? How can the second statement be executed correctly?