QMYSQL query failing

Question

I'm currently working on my project within C++ using Qt. I have MySQL as database storage and the idea is to make a small messenger like MSN and Skype.

However, my MySQL queries are failing. It simply gets no results or gives me an error. There's loads of code coming up, I'm sorry for that.

This is my mysql.cpp which creates and opens the database connection:

#include "mysql.h"

mysql::mysql()
{
    this->db = QSqlDatabase::addDatabase("QMYSQL", "QMYSQL");
    this->db.setHostName("localhost");
    this->db.setUserName("root");
    this->db.setPassword("Eequi4");
    this->db.setDatabaseName("test");
    this->db.open();
}

mysql::~mysql()
{
}

mysql_result mysql::create_result(QString query)
{
    return mysql_result(this->db.exec(query));
}

QSqlError mysql::error()
{
    return this->db.lastError();
}

The connection is opened. That works correctly.

This is my mysql_result.cpp, the file I use to add parameters, insert, get results etc:

#include "mysql_result.h"

mysql_result::mysql_result(QSqlQuery query)
{
    this->query = query;
}

void mysql_result::add_parameter(QVariant value)
{
    this->query.addBindValue(value);
}

void mysql_result::add_parameter(QString key, QVariant value)
{
    this->query.bindValue(key, value);
}

int mysql_result::get_last_id()
{
    return this->query.lastInsertId().toInt();
}

void mysql_result::execute()
{
    this->query.execBatch();
}

QSqlQuery mysql_result::get_query()
{
    return this->query;
}

mysql_result::~mysql_result()
{
}

Okay, this should work. If I have the following code, it correctly returns all member first_name's from the database:

mysql_result res = _mysql->create_result("SELECT * FROM members");
QSqlQuery qry = res.get_query();
while (qry.next())
{
    qDebug() << qry.value("first_name");
}

In member_controller.cpp (the class I use to retrieve members by name/id), I got this:

member* member_controller::get_member(int id)
{
    mysql_result result = engine::get_mysql().create_result("SELECT * FROM members WHERE member_id = :ID");
    result.add_parameter(":ID", id);
    QSqlQuery query = result.get_query();

    if (query.exec() && query.next())
    {
        return new member(id, query.value("first_name").toString(), query.value("second_name").toString(), query.value("screen_name").toString(), query.value("email").toString(), query.value("status").toString());
    }
    else
    {
        qDebug() << engine::get_mysql().error() << "\n";
        qDebug() << query.lastError() << "\n";
    }

    return new member(0, "", "", "", "", "");
}

What it does it will go to the else, and I get the error invalid syntax near :ID. If I replace :ID with @ID (just like in C#), it will go to the else without error code.. I don't know what the problem is.

Two things. The code needs to be optimized a bit and made easier, I'm gonna work on that. Also, is it possible/allowed to put code in a pastebin and paste the URL rather than put the code here?

Possible duplicate of [Qt QSqlQuery bindValue works with ? but not with :placeholders](http://stackoverflow.com/questions/20786003/qt-qsqlquery-bindvalue-works-with-but-not-with-placeholders) — Shadow, Oct 08 '15 at 15:02
See http://stackoverflow.com/questions/20786003/qt-qsqlquery-bindvalue-works-with-but-not-with-placeholders for answer — Shadow, Oct 08 '15 at 15:05

user3791372 · Accepted Answer · 2015-10-08T14:51:31.863

1

Try changing your query to this:

"SELECT * FROM members WHERE member_id = ?"

and add your param like this:

result.add_parameter(0, id);

I'd also suspect, if (query.exec() && query.next()) is incorrect, and the check for .next() should be removed as I'd imagine that requires another record to exist in the result set.

edited Oct 08 '15 at 14:51

answered Oct 08 '15 at 14:45

user3791372

4,445
6
44
78

This didn't work, as I ended up with this error: QSqlError("1064", "QMYSQL: Unable to execute query", "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '?' at line 1") – Joshua Bakker Oct 08 '15 at 14:52
1

because your create_result method is performing the sql without binding. why are you adding params to a result set, then trying to get the query object? – user3791372 Oct 08 '15 at 14:53
What do you mean with that? Are you referring to this->db.exec(query) inside my create_result method? – Joshua Bakker Oct 08 '15 at 14:55
1

follow the code through. the `select ... where id = ?` is executedbefore the param is bound – user3791372 Oct 08 '15 at 14:56
@JoshuaBakker don't forget to upvote the answer and mark it as the answer as it's helped you :) – user3791372 Oct 09 '15 at 14:48
I still have another bug: QSqlError("2036", "QMYSQL3: Unable to bind value", "Using unsupported buffer type: 6029404 (parameter: 1)") Most of the time the type is 253. – Joshua Bakker Oct 09 '15 at 17:49

QMYSQL query failing

1 Answers1

Linked