C++ MySQL++ Delete query statement brain killer question

I'm relatively new to the MySQL++ connector in C++, and have an really annoying issue with it already!

I've managed to get stored procedures working, however i'm having issues with the delete statements. I've looked high and low and have found no documentation with examples.

First I thought maybe the code needs to free the query/connection results after calling the stored procedure, but of course MySQL++ doesn't have a free_result method... or does it?

Anyways, here's what I've got:

#include <iostream>
#include <stdio.h>
#include <queue>
#include <deque>
#include <sys/stat.h>
#include <mysql++/mysql++.h>
#include <boost/thread/thread.hpp>
#include "RepositoryQueue.h"

using namespace boost;
using namespace mysqlpp;

class RepositoryChecker
{
private:
    bool _isRunning;
Connection _con;
public:
RepositoryChecker()
{
    try
    {
        this->_con = Connection(false);
        this->_con.set_option(new MultiStatementsOption(true));
        this->_con.set_option(new Reconnec开发者_如何学CtOption(true));
        this->_con.connect("**", "***", "***", "***");

        this->ChangeRunningState(true);
    }
    catch(const Exception& e)
    {
        this->ChangeRunningState(false);
    }
}
/**
* Thread method which runs and creates the repositories
*/
void CheckRepositoryQueues()
{
    //while(this->IsRunning())
    //{
        std::queue<RepositoryQueue> queues = this->GetQueue();

        if(queues.size() > 0)
        {
            while(!queues.empty())
            {
                RepositoryQueue &q = queues.front();
                char cmd[256];
                sprintf(cmd, "svnadmin create /home/svn/%s/%s/%s", q.GetPublicStatus().c_str(),
                    q.GetUsername().c_str(), q.GetRepositoryName().c_str());
                    if(this->DeleteQueuedRepository(q.GetQueueId()))
                    {
                        printf("query deleted?\n");
                    }
                    printf("Repository created!\n");
                queues.pop();
            }
        }
        boost::this_thread::sleep(boost::posix_time::milliseconds(500));
    //}
}
protected:
/**
* Gets the latest queue of repositories from the database
* and returns them inside a cool queue defined with the 
* RepositoryQueue class.
*/
std::queue<RepositoryQueue> GetQueue()
{
    std::queue<RepositoryQueue> queues;

    Query query = this->_con.query("CALL sp_GetRepositoryQueue();");
    StoreQueryResult result = query.store();
    RepositoryQueue rQ;

    if(result.num_rows() > 0)
    {
        for(unsigned int i = 0;i < result.num_rows(); ++i)
        {
            rQ = RepositoryQueue((unsigned int)result[i][0],
                                (unsigned int)result[i][1],
                                (String)result[i][2],
                                (String)result[i][3],
                                (String)result[i][4],
                                (bool)result[i][5]);
            queues.push(rQ);
        }
    }

    return queues;
}
/**
* Allows the thread to be shut off.
*/
void ChangeRunningState(bool isRunning)
{
    this->_isRunning = isRunning;
}
/**
* Returns the running value of the active thread.
*/
bool IsRunning()
{
    return this->_isRunning;
}
/**
* Deletes the repository from the mysql queue table. This is
* only called once it has been created.
*/
bool DeleteQueuedRepository(unsigned int id)
{
    char cmd[256];
    sprintf(cmd, "DELETE FROM RepositoryQueue WHERE Id = %d LIMIT 1;", id);
    Query query = this->_con.query(cmd);
    return (query.exec());
}
};

I've removed all the other methods as they're not needed...

Basically it's the DeleteQueuedRepository method which isn't working, the GetQueue works fine.

PS: This is on a Linux OS (Ubuntu server)

Many thanks, Shaun

MySQL++ doesn't have a free_result method... or does it?

It doesn't need one. When the result object goes out of scope at the end of GetQueue(), all memory associated with it is automatically freed.

this->_con = Connection(false);

Three problems here:

1. When you create the RepositoryChecker object, you already have created a Connection object. If you need to pass different parameters to its constructor, you'd do that in the initialization list of the RepositoryChecker constructor, not in its body. Read your C++ book.

   What you've done here instead is a) create a default Connection object, then b) create a different Connection object with exceptions turned off, then c) overwrite the first with the second. If that works, it's highly inefficient. MySQL++ Connection objects have had problems with their copy ctors in the past, so if you're using an old version of the library, it could explain your problems.

2. You're telling the Connection object (and every object it creates, even indirectly, which means pretty much everything in MySQL++) you don't want it to throw exceptions, but then you wrap it in a big try block. Pick one.

   I'd suggest using exceptions — the default in MySQL++ — given the way your code is currently structured. If there is a query error way down in DeleteQueuedRepository(), there's no way to see what happened because you'd just pass false up to the caller, which is ignored because there is no else clause on the call. If you do this, log the e.what() message in your catch block. You're just throwing that information away right now.

3. There are several places where you're using constructs that look more like Python (or perhaps JavaScript) than C++. This makes me wonder if your problem isn't damage caused by some other misuse of C++.

   On this line in particular, you're using the this pointer explicitly, for which there is no need in C++. This code does exactly the same thing:

    _con = Connection(false);
   

   Though again, the line should be replaced entirely, using the RepositoryChecker ctor initializer list instead.

Moving on...

sprintf(cmd, "DELETE FROM RepositoryQueue WHERE Id = %d LIMIT 1;", id);

As others have commented, you'd be better off using the Query stream interface:

Query q = _con.query();
q << "DELETE FROM RepositoryQueue WHERE Id = " << id << " LIMIT 1";

This has several advantages:

• Fixes the type safety problem implied by the one who suggested changing your %d to %u. C++ IOStreams take care of that for you.

• Automatic quoting of data inserted into the stream, if needed. (Which it isn't, in this case.)

• Prevents any possibility of running off the end of the buffer. You could use the nonportable snprintf() instead here, but why?

If you're really happy with printf(), there's the template query interface instead.

boost::this_thread::sleep(boost::posix_time::milliseconds(500));

Have you read the threads chapter in the user manual? You don't get thread safety for free in MySQL++. Your problem could be due to memory corruption.

Warren Young, MySQL++ Maintainer

Try changing "%d" to "%u" in sprintf.