Important: Please read the Qt Code of Conduct - https://forum.qt.io/topic/113070/qt-code-of-conduct

cannot execute queries on a database



  • first time working with databases in qt.
    this is my code.
    the first query execution returns false.
    what's wrong?
    i'm using qt with visual studio, and i enabled qtsql module from "qt vs tools"->"qt project settings"->"qt modules".

    #include <QApplication>
    #include <QMessageBox>
    #include <QSqlDatabase>
    #include <QSqlError>
    #include <QSqlQuery>
    #include <QDebug>
    
    bool createConnection()
    {
    	auto db = QSqlDatabase::addDatabase("QSQLITE");
    	db.setHostName("localhost");
    	db.setDatabaseName("musicdb");
    	db.setUserName("root");
    	db.setPassword("root");
    	if (!db.open())
    	{
    		QMessageBox::critical(nullptr, QObject::tr("Database Error"), db.lastError().text());
    		return false;
    	}
    
    	QSqlQuery query;
    	if (!query.exec("CREATE TABLE artist (ID integer Name varchar)"))
    		return false;
    
    	if (!query.exec("INSERT INTO artist VALUES (1, 'Billie Eilish')"))
    		return false;
    
    	if (!query.exec("SELECT * FROM artist"))
    		return false;
    
    	while (query.next())
    	{
    		const int artistID = query.value(0).toInt();
    		const QString artistName = query.value(1).toString();
    		qDebug() << artistID << ' ' << artistName;
    	}
    
    	return true;
    }
    
    int main(int argc, char *argv[])
    {
    	QApplication app(argc, argv);
    
    	if (!createConnection())
    		return 1;
    
    	return QApplication::exec();
    }
    

  • Lifetime Qt Champion

    You should take a look at QSqlQuery::lastError() when the statements fails. I would guess your create statement is wrong.



  • @user4592357 I think your select wrong SQL driver,

    // For SQLite DBConnection 
    auto db = QSqlDatabase::addDatabase("QSQLITE");
    db.setDatabaseName(<path_to _QLite_DB_file>);
    
    // For mySQL DBConnection
    auto db = QSqlDatabase::addDatabase("QMYSQL");
    db.setHostName("localhost");
    db.setDatabaseName("musicdb");
    db.setUserName("root");
    db.setPassword("root");
    

    Regards



  • okay i changed to this.
    i create the table with 2 columns but when i exec "INSERT INTO artist VALUES (1, 'Billie Eilish')" it says the table has only one column. when i insert only one column, the is output: 0 ""

    bool createConnection()
    {
    	if (!QSqlDatabase::drivers().contains("QSQLITE"))
    		QMessageBox::critical(nullptr, "Unable to load database", "This demo needs the SQLITE driver");
    
    	auto db = QSqlDatabase::addDatabase("QSQLITE");
    	db.setDatabaseName("musicdb");
    	if (!db.open())
    	{
    		QMessageBox::critical(nullptr, QObject::tr("Database Error"), db.lastError().text());
    		return false;
    	}
    
    	const auto tables = db.tables();
    
    	QSqlQuery query;
    	if (tables.contains("artist"))
    	{
    		query.exec("DROP TABLE artist;");
    	}
    
    	if (!query.exec("CREATE TABLE artist (ID integer primary key Name varchar)"))
    	{
    		qDebug() << query.lastError().text();
    		return false;
    	}
    
    	if (!query.exec("INSERT INTO artist VALUES ('Billie Eilish')"))
    	{
    		qDebug() << query.lastError().text();
    		return false;
    	}
    
    	if (!query.exec("SELECT * FROM artist"))
    	{
    		qDebug() << query.lastError().text();
    		return false;
    	}
    
    	while (query.next())
    	{
    		const int artistID = query.value(0).toInt();
    		const QString artistName = query.value(1).toString();
    		qDebug() << artistID << ' ' << artistName;
    	}
    
    	return true;
    }
    

  • Lifetime Qt Champion

    @user4592357 You're missing ',' in your create table queries.



  • @user4592357 said in cannot execute queries on a database:

    if (!query.exec("CREATE TABLE artist (ID integer primary key Name varchar)"))
    {
    qDebug() << query.lastError().text();
    return false;
    }

    should be:

    if (!query.exec("CREATE TABLE artist (ID integer primary key, Name varchar)"))
    {
        qDebug() << query.lastError().text();
        return false;
    }
    


  • Hello, I am also having an issue with mySql queries that are not run. I managed to get the connection but the queries are not running. please see the below code I have on the mainWindow.cpp file.

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

    MainWindow::MainWindow(QWidget *parent) :
    QMainWindow(parent),
    ui(new Ui::MainWindow)
    {
    ui->setupUi(this);
    ui->userID->setPlaceholderText("Enter your user ID");
    ui->password->setPlaceholderText("Enter your password");
    }

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

    void MainWindow::on_loginButton_clicked()
    {
    //Connecting to MySql database
    db = QSqlDatabase::addDatabase("QMYSQL","MyConnect");
    db.setHostName("localhost");
    db.setUserName("root");
    db.setPassword("");
    db.setDatabaseName("test");

    QString username = ui->userID->text();
    QString password = ui->password->text();
    
    if(db.open()){
    
        //Creating My Queries
    
        QMessageBox::information(this, "Database Success", "Database Connection Successful");
    
        QSqlQuery query(QSqlDatabase::database("MyConnect"));
    
        query.prepare(QString("SELECT * FROM users WHERE username = :username AND password = :password"));
    
        query.bindValue(":username", username);
        query.bindValue(":password", password);
    
        if(!query.exec()){
    
            QMessageBox::information(this, "Failed", "UserID or Password are incorrect");
        }else{
            while(query.next()){
    
                QString usernameFromDB = query.value(1).toString();
                QString passwordFromDB = query.value(2).toString();
    
                if(usernameFromDB == username && passwordFromDB == password){
    
                    QMessageBox::information(this, "Success", "Login Successful");
    
                    page_one = new suceesslog(this);
                    page_one -> show();
                }else{
    
                    QMessageBox::information(this, "Failed", "Login Failed");
                }
            }
        }
    
    }else{
    
        QMessageBox::information(this, "Database Failed", "Database Connection Failed");
    }
    

    }


  • Lifetime Qt Champion

    @EdwinSA said in cannot execute queries on a database:

    but the queries are not running

    And what does this mean?
    btw: QSqlQuery has some functions to get the error string - you should use them.


Log in to reply