multiple mysql_real_query() in while loop

Question

It seems that when I have one mysql_real_query() function in a continuous while loop, the query will get executed OK.

However, if multiple mysql_real_query() are inside the while loop, one right after the other. Depending on the query, sometimes neither the first query nor second query will execute properly.

This seems like a threading issue to me. I'm wondering if the mysql c api has a way of dealing with this? Does anyone know how to deal with this? mysql_free_result() doesn't work since I am not even storing the results.

//keep polling as long as stop character '-' is not read
while(szRxChar != '-')
{
    // Check if a read is outstanding
    if (HasOverlappedIoCompleted(&ovRead))
    {
        // Issue a serial port read
        if (!ReadFile(hSerial,&szRxChar,1,
                &dwBytesRead,&ovRead))
        {
            DWORD dwErr = GetLastError();
            if (dwErr!=ERROR_IO_PENDING)
                return dwErr;
        }
    }

    // Wait 5 seconds for serial input
    if (!(HasOverlappedIoCompleted(&ovRead)))
    {
        WaitForSingleObject(hReadEvent,RESET_TIME);
    }

    // Check if serial input has arrived
    if (GetOverlappedResult(hSerial,&ovRead,
            &dwBytesRead,FALSE))
    {
        // Wait for the write
        GetOverlappedResult(hSerial,&ovWrite,
            &dwBytesWritten,TRUE);

        //load tagBuffer with byte stream
        tagBuffer[i] = szRxChar;
        i++;
        tagBuffer[i] = 0; //char arrays are \0 terminated

        //run query with tagBuffer
        if( strlen(tagBuffer)==PACKET_LENGTH )
        {
            sprintf(query,"insert into scan (rfidnum) values ('");
            strcat(query, tagBuffer);
            strcat(query, "')");
            mysql_real_query(&mysql,query,(unsigned int)strlen(query));

            i=0;
        }

        mysql_real_query(&mysql,"insert into scan (rfidnum) values ('2nd query')",(unsigned int)strlen("insert into scan (rfid) values ('2nd query')"));

        mysql_free_result(res);
    }
}

Answer 1

OK, For test purposes, take your tagBuffer variable out of the first mysql_real_query call and replace it with a constant, like your second test query.

At that point you'd just be repeatedly inserting two constant strings. That really should work.

If that does work, then we have to figure out what is wrong with tagBuffer. Could it be receiving unusual characters that somehow is confusing MySQL but such that it wasn't spotted in the single query case?

Answer 2

Always check the return value of an API call.

mysql_real_query() returns an integer. The value is zero if the call worked, and nonzero if there's an error.

Check the return value and report it if it's nonzero:

if ((err = mysql_real_query(&mysql,"insert into scan (rfidnum) values ('2nd query')",
  (unsigned int)strlen("insert into scan (rfid) values ('2nd query')"))) != 0)
{
  // report err here, get additional information from these two API calls:
  errno = mysql_errno(&mysql);
  errmsg = mysql_error(&mysql);
}

update: If you get a nonzero result, you need to check mysql_error() to find out which error. Since you said you get an error if the second query is a SELECT, I would guess it's CR_COMMANDS_OUT_OF_SYNC, which means the API thinks there are some results pending (even if the result consists of zero rows). You can't start the next SQL query until you have finished fetching results of a SELECT (or calling a stored procedure), even if that query's result is empty.

Here's a brief explanation in the MySQL docs: "Commands out of sync"

You need to use mysql_free_result() before you can run another query. And that means you need to use mysql_use_result() before you can free it.

Here's an excerpt from mysql_use_result() doc:

After invoking mysql_query() or mysql_real_query(), you must call mysql_store_result() or mysql_use_result() for every statement that successfully produces a result set (SELECT, SHOW, DESCRIBE, EXPLAIN, CHECK TABLE, and so forth). You must also call mysql_free_result() after you are done with the result set.

Answer 3

After update maybe U should commit the transcation.