Qt SQL Class

[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

[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