Qt SQL Class

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

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.