cannot execute queries on a database

user4592357

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();
}

Christian Ehrlicher

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

KroMignon

@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

user4592357

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;
}

jsulm

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

KroMignon

@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;
}

EdwinSA

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");
}

}

Christian Ehrlicher

@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.