Stored procedure - Commands out of sync after mysql_free_result()
Posted by: Tushar Bindal
Date: January 28, 2013 11:52PM

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?

Options: ReplyQuote


Subject
Views
Written By
Posted
Stored procedure - Commands out of sync after mysql_free_result()
3813
January 28, 2013 11:52PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.