sqlite commands not working in qt



  • I am making a library management software using Qt and Sqlite3.

    constructor:

    db.setDatabaseName(":/lib/libre coupe.db");
    db.setHostName("Libre Coupe");
    
    db = QSqlDatabase :: addDatabase("QSQLITE");
    model = new QSqlTableModel(this, db);
    
    if(db.open())
    {
        QSqlQuery query(db);
        if (! query.exec("CREATE TABLE IF NOT EXISTS books (NAME VARCHAR(100) NOT NULL, AUTHOR VARCHAR(100) NOT NULL, UID VARCHAR(100) NOT NULL) "))
        {
            QMessageBox::information(this, "title", "Unable to use Sqlite");
        }
    
        if(query.lastError().text() != " ")
            QMessageBox::critical(this, "Oops", query.lastError().text());
    
        model->setTable("books");
        model->select();
    
        model->setHeaderData(0, Qt::Horizontal, tr("Name") );
        model->setHeaderData(1, Qt::Horizontal, tr("Author") );
        model->setHeaderData(2, Qt::Horizontal, tr("Uid") );
        model->setEditStrategy(QSqlTableModel::OnManualSubmit);
    
        if(!query.exec("SELECT * FROM books;"))
            QMessageBox::critical(this, "Oops", query.lastError().text());
    
        int i = 0;
    
        while(query.next())
        {
            model->setData(model->index(i, 0), query.value(query.record().indexOf("NAME")));
            model->setData(model->index(i, 1), query.value(query.record().indexOf("AUTHOR")));
            model->setData(model->index(i, 2), query.value(query.record().indexOf("UID")));
    
            ++i;
        }
    }
    else
        QMessageBox::critical(this, "Oops!", "Could not open the database");\
    

    I faced a problem that the database was not created automatically. So, i created it manually and added it to my resource so that it exists on every computer which uses my application.

    I ran my app and went to the directory containing "libre coupe.db". There using the terminal, i found out that no table was created. I see no error message. My other functions like save don't work too while the same commands typed directly into Sqlite using terminal works as expected.

    I even used the debugger and found that the program does enter the if condition i.e. the database opens successfully.

    I used the following command to check if the table existed:

    sqlite3 "libre coupe.db"
    .tables
    


  • Apparently you can not directly access an SQLite database from a resource. There are some discussions about this, but I cant find the links right now.
    However, you can extract the resource to a db file and then access it, as this example shows:

    
        QSqlTableModel *model;
    
        if (!QFile::copy(":/lib/library/books.db", "/home/joem/tmp/books.db")) {
            QMessageBox::critical(this, "Oops", "Could not extract resource file to database");
        }
    
        ui->setupUi(this);
        QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
        db.setDatabaseName("/home/joem/tmp/books.db");
        db.setHostName("LibreCoupe");
    
        if(db.open())
        {
            model = new QSqlTableModel(this, db);
            qDebug() << "Database file opened";
            QSqlQuery query(db);
            if (! query.exec("CREATE TABLE IF NOT EXISTS books (NAME VARCHAR(100) NOT NULL, AUTHOR VARCHAR(100) NOT NULL, UID VARCHAR(100) NOT NULL) "))
            {
                QMessageBox::information(this, "title", "Unable to use Sqlite");
            }
    ...
    

  • Moderators

    @mranger90 said in sqlite commands not working in qt:

    Apparently you can not directly access an SQLite database from a resource. There are some discussions about this, but I cant find the links right now.

    Indeed, Qt resources are compiled into the executable (or standalone binary file) which is read-only. Good catch! :-)



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