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.

[Kris Revi]

@SGaist said in Qt SQL Class:

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

why do i see alot of examples online where people keep it in a class? is this a new thing that changed?

[jsulm]

@Kris-Revi said in Qt SQL Class:

is this a new thing that changed?

No. There are also bad examples out there.

[Kris Revi]

@jsulm said in Qt SQL Class:

No. There are also bad examples out there.

but 90% off the examples all have it in a custom class :S how can ALL of them be bad