Qt SQL Class

Kris Revi

So to separate the SQL code from mainwindow.cpp i made a sql class but my problems comes when i try to access my SQL table (this has been tested and working when the code was all in the mainwindow.cpp)

sql.h

#ifndef SQL_H
#define SQL_H

#include <QtSql>

class sql
{
public:
    sql();
    QSqlDatabase db;
    QSqlQuery query;

    void doLogin(QString username, QString password);
    void initSQL(QString databaseType, QString databaseFileName);

};

#endif // SQL_H

sql.cpp

#include "sql.h"

sql::sql()
{

}

void sql::initSQL(QString databaseType, QString databaseFileName)
{
    db = QSqlDatabase::addDatabase(databaseType);
    db.setDatabaseName(databaseFileName);

    if (db.open())
    {
        qDebug() << "-------------------------------------------------------------------------";
        qDebug() << "Connection successfull";
        qDebug() << "-------------------------------------------------------------------------";
    }
    else
    {
        qDebug() << "-------------------------------------------------------------------------";
        qDebug() << "Connection failed";
        qDebug() << "-------------------------------------------------------------------------";
    }

}

void sql::doLogin(QString username, QString password)
{
    if (query.exec(QString("SELECT * from users WHERE username = '%1' AND password = '%2' ").arg(username, password)))
    {
        qDebug() << "You are now logged in.";
    }
    else
    {
        qDebug() << "Login failed. Invalid username or password.";
    }

}

mainwindow.h

#ifndef MAINWINDOW_H
#define MAINWINDOW_H

#include <QMainWindow>

#include "sql.h"

QT_BEGIN_NAMESPACE
namespace Ui { class MainWindow; }
QT_END_NAMESPACE

class MainWindow : public QMainWindow
{
    Q_OBJECT

public:
    MainWindow(QWidget *parent = nullptr);
    ~MainWindow();
    sql Sql;

private:
    Ui::MainWindow *ui;
};
#endif // MAINWINDOW_H

mainwindow.cpp

#include "mainwindow.h"
#include "ui_mainwindow.h"

MainWindow::MainWindow(QWidget *parent)
    : QMainWindow(parent)
    , ui(new Ui::MainWindow)
{
    ui->setupUi(this);

    Sql.initSQL("QSQLITE", "C:/crap/db.sqlite");

    connect(ui->pushButtonLogin, &QPushButton::clicked, [=]() { Sql.doLogin(ui->usernameText->text(), ui->passwordText->text()); });
}

MainWindow::~MainWindow()
{
    delete ui;
}

but all i get is;

-------------------------------------------------------------------------
Connection successfull
-------------------------------------------------------------------------
QSqlQuery::exec: database not open
Login failed. Invalid username or password.

what am i missing?

Christian Ehrlicher

QSqlQuery has some functions to return the speicifc error. I would guess the table does not exist.
Also better use prepared queries - otherwise my username is dontknow'; drop table users;

Kris Revi

@Christian-Ehrlicher The table does exsist as i stated above (this has been tested with all the code inside themainwindow class) and it worked fine :S for some reason even tho it says Connection successfull i guess the SqlQuery does not see / get that from mainwindow class :S

Christian Ehrlicher

@Kris-Revi I don't care what you guess - add the correct error handling how I told you in my first post and look what it tells you.

Kris Revi

hmmm why do i get Driver not loaded now (from SqlError) :S just because i made a class for the SQL code....

SGaist

Hi,

Your SQL class design is wrong. First thing, don't store a local QSqlDatabase object as explained in the class documentation.

Next, on the QSqlQuery side, again, don't store it as class member. Create these objects where you actually use them. What happens here is that the QSqlQuery object is created at your class construction time, before you create your QSqlDatabase object. It's using the default connection which has not yet been created so it's using an invalid database connection hence the error you have.

Kris Revi

@SGaist ok so i did this

sql.h

#ifndef SQL_H
#define SQL_H

#include <QtSql>

class sql
{
public:
    sql();

    void connect(QSqlDatabase& db);
    void doLogin(QString username, QString password);

};

#endif // SQL_H

sql.cpp

#include "sql.h"

sql::sql()
{

}

void sql::connect(QSqlDatabase& db)
{
    QString connectionName = "C:/crap/db.sqlite";
    QString databaseType = "QSQLITE";
    if (!QSqlDatabase::contains(connectionName))
    {
        db = QSqlDatabase::addDatabase(databaseType, connectionName);
        db.setDatabaseName(connectionName);
    }
    else
    {
        db = QSqlDatabase::database(connectionName, false);
    }

}

void sql::doLogin(QString username, QString password)
{
    QSqlQuery query;

    if (query.exec(QString("SELECT * from users WHERE username = '%1' AND password = '%2' ").arg(username, password)))
    {
        qDebug() << "You are now logged in.";
    }
    else
    {
        qDebug() << "Login failed. Invalid username or password.";
    }

    qDebug() << query.lastError();

}

And then in mainwindow.cpp

    connect(ui->pushButtonLogin, &QPushButton::clicked, [=]()
    {
        QSqlDatabase db;
        Sql.connect(db);
        if (!db.isValid())
        {
            qWarning() << "Database driver is not valid.";
            return;
        }
        if (!db.open())
        {
            QSqlError sqlError = db.lastError();
            if (sqlError.isValid())
            {
                qWarning() << "Can not open database, error message is:" << sqlError.text();
            }

            return ;
        }
        Sql.doLogin(ui->usernameText->text(),
                    ui->passwordText->text());
    });

but i still get

QSqlQuery::exec: database not open
Login failed. Invalid username or password.
QSqlError("", "Driver not loaded", "Driver not loaded")

Christian Ehrlicher

You still don't use the correct connection for your QSqlQuery... and why do you pass the connection around instead using QSqlDatabase::database() as properly explained in the documentation?

Kris Revi

@Christian-Ehrlicher happy?

void sql::doLogin(QString username, QString password)
{
    QSqlQuery query;
    query.prepare("SELECT * from users WHERE username = :username AND password = :password ");
    query.bindValue(":username", username);
    query.bindValue(":password", password);
    
    if (query.exec())
    {
        qDebug() << "You are now logged in.";
    }
    else
    {
        qDebug() << "Login failed. Invalid username or password.";
    }
}

Christian Ehrlicher

@Kris-Revi said in Qt SQL Class:

happy?

No, still wrong db connection...

Kris Revi

i got it working guys :)

Kris Revi

@Christian-Ehrlicher said in Qt SQL Class:

No, still wrong db connection...

yea no shit! that part was only the "query" part you were screaming about! :P

but again i got it working :)

Kris Revi

i seem to still be stuck -.-

jsulm

@Kris-Revi said in Qt SQL Class:

i seem to still be stuck

Then provide more information about current situation

Kris Revi

@jsulm said in Qt SQL Class:

Then provide more information about current situation

so have been doing some modifications from naming to code sql class to dbmanager

dbmanager.h

#ifndef DBMANAGER_H
#define DBMANAGER_H

#include <QSqlDatabase>

class DbManager
{
public:
    DbManager(const QString& path);

    ~DbManager();

    bool isOpen() const;

    bool usernameExists(const QString& username) const;

    void md5Hash(QString& src, QString& dest);

private:
    QSqlDatabase m_db;
};

#endif // DBMANAGER_H

dbmanager.cpp

#include "dbmanager.h"
#include <QSqlQuery>
#include <QSqlError>
#include <QSqlRecord>
#include <QDebug>

DbManager::DbManager(const QString &path)
{
    m_db = QSqlDatabase::addDatabase("QSQLITE");
    m_db.setDatabaseName(path);

    if (!m_db.open())
    {
        qDebug() << "Error: connection with database fail";
    }
    else
    {
        qDebug() << "Database: connection ok";
    }
}

DbManager::~DbManager()
{
    if (m_db.isOpen())
    {
        m_db.close();
    }
}

bool DbManager::isOpen() const
{
    return m_db.isOpen();
}

bool DbManager::usernameExists(const QString& username) const
{
    bool exists = false;

    QSqlQuery checkQuery;
    checkQuery.prepare("SELECT * FROM users WHERE username = (:username)");
    checkQuery.bindValue(":username", username);

    if (checkQuery.exec())
    {
        if (checkQuery.next())
        {
            exists = true;
        }
    }
    else
    {
        qDebug() << "Username does not exists : " << checkQuery.lastError();
    }

    return exists;
}

mainwindow.cpp

#include "mainwindow.h"
#include "ui_mainwindow.h"

#include <dbmanager.h>

MainWindow::MainWindow(QWidget *parent)
    : QMainWindow(parent)
    , ui(new Ui::MainWindow)
{
    ui->setupUi(this);
    ui->errorLabel->setVisible(false);

    static const QString path = "C:/crap/db.sqlite";

    connect(ui->pushButtonLogin, &QPushButton::clicked, [=]()
    {
        DbManager db(path);
        if (db.isOpen())
        {
            QSqlQuery query;
            query.prepare(QString("SELECT * FROM users WHERE username = :username AND password = :password "));
            query.bindValue(":username", ui->usernameText->text());
            query.bindValue(":password", ui->passwordText->text());
            query.exec();

            if (query.size() > 0)
            {
                ui->errorLabel->setVisible(false);
                ui->stackedWidget->setCurrentIndex(1);
                QString name = query.value(1).toString();
                qDebug() << name << "is logged in";
            }
            else
            {
                ui->errorLabel->setVisible(true);
                ui->errorLabel->setText("Invalid username or password.");
                qWarning() << "Query Error : " << query.lastError();
            }
        }
    });
}

MainWindow::~MainWindow()
{
    delete ui;
}

so i get

Database: connection ok
Query Error :  QSqlError("", "", "") // so no errors
Invalid username or password.`

the username and password is easy test and test123 so i know i put it in right

jsulm

@Kris-Revi said in Qt SQL Class:

QSqlDatabase m_db;

You were already told not to keep QSqlDatabase instances - why are you still doing this?

"QSqlQuery query;" - you are still using default connection. You should really read more carefully what others write.

SGaist

You did not check that the query ran successfully.

Also, as you were already explained: do not keep a QSqlDatabase class variable. It's all explained in the class details.

Also, since you are using an SQLite database, ensure that the path you use is correct. If not, a new empty database will be created. This is SQLite specific and not something under Qt control.