Qt SQL Class

Kris Revi

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

jsulm

@Kris-Revi What examples do you have in mind?
And did you read the documentation you where pointed to? There it is clearly stated...

Kris Revi

@jsulm said in Qt SQL Class:

And did you read the documentation you where pointed to? There it is clearly stated...

reading now! so for login i should use QSqlQueryModel since it is read-only and not QSqlQuery am i understanding that correctly?

SGaist

No, you should:

properly handle the QSqlDatabase object
properly check that all your queries are successful

QSqlQuery will not modify your database unless you write such a statement.

Kris Revi

@SGaist said in Qt SQL Class:

QSqlQuery will not modify your database unless you write such a statement.

Sooo THIS works it throws error if it cannot find username and password and i get "logged in" when i provide the correct username and password... am i still doing ANYTHING wrong?

    connect(ui->pushButtonLogin, &QPushButton::clicked, [=]()
    
        {        
    
            QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
    
            //db.setHostName("acidalia");
    
            db.setDatabaseName("C:/crap/db.sqlite");
    
            //db.setDatabaseName("customdb");
    
            //db.setUserName("mojito");
    
            //db.setPassword("J0a1m8");
    
            bool ok = db.open();
    
            if(ok)
    
            {
    
                qInfo() << "Database Connected!";
    
    
    
                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.next())
    
                {
    
                    m_username = ui->usernameText->text();
    
                    m_password = ui->passwordText->text();
    
                    m_isloggedin = true;
    
                    ui->errorLabel->setVisible(false);
    
                    ui->stackedWidget->setCurrentIndex(1);
    
                    QString name = query.value(1).toString();
    
                    qDebug() << name << "is logged in";
    
                }
    
                else
    
                {
    
                    m_username = "";
    
                    m_password = "";
    
                    m_isloggedin = false;
    
                    ui->errorLabel->setVisible(true);
    
                    ui->errorLabel->setText("Invalid username or password.");
    
                    qWarning() << "Query Error : " << query.lastError();
    
                }
    
            }
    
        });
    
    }

SGaist

Yes, you are recreating your connection every time this slot is called which is not needed. And you are likely getting a warning while running your application and clicking your pushButtonLogin button multiple times.

Kris Revi

@SGaist said in Qt SQL Class:

Yes, you are recreating your connection every time this slot is called which is not needed. And you are likely getting a warning while running your application and clicking your pushButtonLogin button multiple times.

moved QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE"); to the mainwindow.h file

JonB

@Kris-Revi
I'm afraid that is not a good idea! Assuming you mean it is now a member variable, that is what @SGaist/Qt are asking you not to do. Have another careful read through https://doc.qt.io/qt-6/qsqldatabase.html#details, particularly the red box:

Warning: It is highly recommended that you do not keep a copy of the QSqlDatabase around as a member of a class, as this will prevent the instance from being correctly cleaned up on shutdown. If you need to access an existing QSqlDatabase, it should be accessed with database(). If you chose to have a QSqlDatabase member variable, this needs to be deleted before the QCoreApplication instance is deleted, otherwise it may lead to undefined behavior.

Do the addDatabase() and open stuff somewhere early in code, don't save the QSqlDatabase db.

You don't need the member variable because you can use QSqlDatabase db = QSqlDatabase::database(); just as a local variable whenever you need to access the default connection.

SGaist

If you are only using the default connection, then there's no need to do anything special beside the setup since it's the connection that will be used when no others have been created.

No need to over-engineer that stuff.
The usual main reason to retrieve the default connection is for example when you want to change credentials or in your case change the path to the database file.