Qt MySQL Query - Unable to bind value

Question

I finally fixed my MySQL connection in C++ Qt. However, when I try to bind values, I get the following error:

QSqlError("2036", "QMYSQL3: Unable to bind value", "Using unsupported buffer type: 1701052421  (parameter: 1)")

I have these files:

Engine.h:

#ifndef ENGINE_H
#define ENGINE_H

#include "database/mysql.h"

class engine
{
private:
    static mysql* _mysql;
public:
    static void initialize();
    static void destroy();

    static mysql get_mysql();
};

#endif // ENGINE_H

Engine.cpp:

#include "engine.h"
#include "entities/member_controller.h"
#include <QDebug>

mysql* engine::_mysql;

void engine::initialize()
{
    _mysql = new mysql();

    member* mem = member_controller::get_member(1);
    qDebug() << "mem name = " << mem->getFirstName() << " " << mem->getSecondName();
    delete mem;
}

void engine::destroy()
{
    delete _mysql;
}

mysql engine::get_mysql()
{
    return *_mysql;
}

mysql.h:

#ifndef MYSQL_H
#define MYSQL_H

#include <QtSql>
#include <QString>
#include "mysql_result.h"

class mysql
{
private:
    QSqlDatabase db;
public:
    mysql();
    ~mysql();
    mysql_result create_result(QString query);
    QSqlError error();

    QSqlQuery query_prepare(QString query1)
    {
        QSqlQuery query(this->db);
        query.prepare(query1);
//        this->query = query;
        return query;
    }
};

#endif // MYSQL_H

(query_prepare body temp. in header file just to test)

mysql.cpp

#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()
{
}

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

member_controller.h:

#ifndef MEMBER_CONTROLLER_H
#define MEMBER_CONTROLLER_H

#include <QString>
#include "member.h"

class member_controller
{
public:
   static member* get_member(unsigned int id);
   static member* get_member(QString email);
};

#endif // MEMBER_CONTROLLER_H

member_controller.cpp:

#include "member_controller.h"
#include "database/mysql_result.h"

#include "engine.h"
#include "database/mysql_result.h"
#include <QtSql/QSqlQuery>

member* member_controller::get_member(unsigned int id)
{
    QSqlQuery result = engine::get_mysql().query_prepare("SELECT * FROM members WHERE member_id = :mem_id");
    result.bindValue(":mem_id", id);

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

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

I hope this is all the code needed. I tried using questionmark except of :mem_id but no luck either.

Answer 1

I am not C++ or Qt expert and have no possibility to debug your code.

But just because of curiosity I've started to investigate your code and found suspicious line (the 2nd one) in your code:

mysql_result result = engine::get_mysql().create_result("SELECT * FROM members WHERE member_id = ?");

Since I am not expert and you didn't provide any includes I don't know neither what is your engine namespace nor function get_mysql() return type nor create_result return.

So I did some guess: get_mysql() probably return QSqlDatabase object? isn't it?

But that type does not support any create_result method! So I stuck there.

Next thanks to google I've found your another question made a week ago. I would please to include such important information in your post next time, so people can see your classes and functions like:

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

and

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

Now I can understand what that 2nd line of your code is trying to do.

The suspicious thing I see here is return mysql_result(this->db.exec(query));. That seems according to the function names that you are trying to execute query and get result from mysql server.

But according to the algorithm I see in your member_controller::get_member it seems to me that you are only on prepare stage but not executing yet. I see that Qt documentation is not clear enough (and again i am not an expert) because: Executes a SQL statement on the database and returns a QSqlQuery object. And technically you can say that you have QSqlQuery as a result and you can expect that this result is absolutely the same as if you do:

//mysql_result mysql::create_result(QString query)
QSqlQuery  mysql::query_prepare(QString query)
{
    QSqlQuery query(this->db);
    query.prepare(query);
    this->query = query;
    return this->query;
}

But IMHO it isn't. In your case it is already executed, that is why you can't bind any parameter later. So I would suggest you to change your mysql_result mysql::create_result(QString query) or create another function QSqlQuery mysql::query_prepare(QString query) which has more sense to me. and change your first lines of code to:

member* member_controller::get_member(int id)
{
    QSqlQuery query = engine::get_mysql().query_prepare("SELECT * FROM members WHERE member_id = ?");
    query.addBindValue(value);

    if (query.exec() && query.first())
...
...

And very last point I don't understand why are you trying to reinvent the weel? If you already have Qt mysql classes which looks very good to me why do you create your own class and methods with one line to call Qt method like:

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

Sorry but IMHO that is not very good idea and has almost no sense.

UPDATE Looking into your updated code I see:

 result.bindValue(":mem_id", id);

where result type is QSqlQuery so you are calling to the native method bindValue where second parameter according to documentation is const QVariant & val. So I would change your call to:

 result.bindValue(":mem_id", QVariant(id));

or

 QVariant mem_id(id);
 result.bindValue(":mem_id", mem_id);