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

  • Qt Champions 2018

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

  • Qt Champions 2018

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

Log in to reply
 

Looks like your connection to Qt Forum was lost, please wait while we try to reconnect.