sqlite commands not working in qt

Calvin Richerd

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

mranger90

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

sierdzio

@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! :-)