QT ANDROID - QSqlError("1", "Unable to execute statement", "no such table: <tablename>")

[kingsta]

Hello dear friends

I have a weird issue about Sqlite database. My app works fine on Windows, but doesn't work on Android. I can copy database. Then I can open database with db.open() function, there is no error. But I can't read or write database. I checked with QFileInfo, isWritable and isReadable are true! Where is the problem?

There is a database in qrc:/dosyalar/prog.db. This database is a kind of template.

My flow chart

• Create "veri" folder on QStandardPaths::writableLocation(QStandardPaths::AppDataLocation). I use QDir::mkdir("veri")
  So output-> com.example.app/files/veri
• Copy database to veri/kul.db location. QFile::copy
• Control the copying operation
• setPermissions(QFile::WriteUser | QFile::ReadUser) to veri/kul.db database
• Control the permission operation: I got -> QFileInfo.isWritable() = true, QFileInfo.isReadable() = true
• Control the new database on Device File Explorer on Android Studio. New database looks good. (values and folder-location is okey)
• Get values from database with QSqlDatabase, QSqlTableModel, QSqlRecord

//my app location
veritabaniYolu = QStandardPaths::writableLocation(QStandardPaths::AppDataLocation); 

//template database location
progDBAdi = ":/dosyalar/prog.db"; 

//my new database and location
kulDBAdi.append(QDir::separator()); 
kulDBAdi.append("veri");
kulDBAdi.append(QDir::separator());
kulDBAdi.append("kul.db");

 if( ! QFileInfo::exists(veritabaniYolu + kulDBAdi)){

 QFile sablonVt(progDBAdi);
        QDir veriKlasoru(veritabaniYolu);

        qDebug() << "şablon db var mı: " << sablonVt.exists(); //Control the template database
        if(!sablonVt.exists())
            Q_ASSERT("HATA: ŞABLON DB YOK!"); //There is no template database

        QDir yeniDBKlasoru(veritabaniYolu + QDir::separator() + "veri"); 
        qDebug() << "klasör var mı: " << yeniDBKlasoru.exists(); //Is "veri" folder already exist?

        qDebug() << "klasör açma: " << veriKlasoru.mkdir("veri") << veriKlasoru.path(); //Create new "veri" folder

        qDebug() << "kopyalama: " << sablonVt.copy(veritabaniYolu + kulDBAdi) << " Hata: " << sablonVt.errorString(); //Copy the new database from qrc

        QFile yeniVt(veritabaniYolu + kulDBAdi); //New database
        yeniVt.setPermissions(QFile::WriteUser | QFile::ReadUser); 

        qDebug() << "veritabaniYolu: " << veritabaniYolu;
        qDebug() << "yeniDBKlasoru.path();: " << yeniDBKlasoru.path();
        qDebug() << "veriKlasoru.path(): " << veriKlasoru.path();

        QFileInfo vtBilgisi(veritabaniYolu + kulDBAdi); //new database
        qDebug() << "vtBilgisi.path(): " << vtBilgisi.path();  //no problem
        qDebug() << "vtBilgisi.fileName(): " << vtBilgisi.fileName();  //no problem
        qDebug() << "vtBilgisi.exists(): " << vtBilgisi.exists(); //I got true
        qDebug() << "vtBilgisi.isWritable(): " << vtBilgisi.isWritable();  //I got true
        qDebug() << "vtBilgisi.isReadable(): " << vtBilgisi.isReadable();  //I got true

        QFileInfo vtBilgisi2(progDBAdi); //template database
        qDebug() << "vtBilgisi2.path(): " << vtBilgisi2.path();  //no problem
        qDebug() << "vtBilgisi2.fileName(): " << vtBilgisi2.fileName();  //no problem
        qDebug() << "vtBilgisi2.exists(): " << vtBilgisi2.exists(); // I got true
        qDebug() << "vtBilgisi2.isWritable(): " << vtBilgisi2.isWritable(); //false. because qrc limitation
        qDebug() << "vtBilgisi2.isReadable(): " << vtBilgisi2.isReadable();  // I got true


QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE", veritabaniYolu + kulDBAdi); //connect new database
    qDebug() << "veritabaniYolu: " << veritabaniYolu << " kulDBAdi: " << kulDBAdi;
    db.setDatabaseName("QSQLITE");
    qDebug() << "Sürücü uygun mu?: " << QSqlDatabase::isDriverAvailable("QSQLITE"); //I got true

    if(!db.open()){
        qDebug() << "HATA: db açılmasında sorun oluştu: " <<  db.lastError(); //No problem
        return false;
    }

//Creating model
    QSqlTableModel *model = new QSqlTableModel(NULL,db);
    model->setTable("ayarlarTablosu");
    model->setEditStrategy(QSqlTableModel::OnManualSubmit);
    model->select();

    int deger = 0;

    for (int i = 0; i <= model->rowCount(); ++i) {
        QSqlRecord record = model->record(i);
        qDebug() << "i: " << i << " " << record.value("ayarAdi").toString() << record.value("deger").toString() ;
//I checked manually on database. There are values on database. 
//But I can't get values from this code. Output i:  0   "" ""

        if(record.value("ayarAdi").toString() == "ilkKurulum")
            deger = record.value("deger").toInt();
    }

    qDebug() << "Tablolar: " << db.tables(); //I can't read tables. Output: Tablolar:  ()
    qDebug() << "VT Sürücüleri: " << db.driverName(); //Output VT Sürücüleri:  "QSQLITE"
    qDebug() << "VT Adı: " << db.databaseName(); //Output VT Adı:  "QSQLITE"

    db.close();
    db.removeDatabase("QSQLITE");

Function that writes values to database

QSqlQuery sqlkodu(db);

bool Program::vtVeriGuncelle(QString dbAdi, QString tabloAdi, QString degisecekKolonAdi, int yeniDeger, QString kiyaslamaKolonAdi, QString urunAdi)
{
    QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE", veritabaniYolu + dbAdi);

  if(!db.open()){
        qDebug() << "HATA: db açılmasında sorun oluştu: " <<  db.lastError();
        return false;
    }

    QSqlQuery sqlkodu(db);

bool deger = sqlkodu.exec("UPDATE " + tabloAdi + " SET " + degisecekKolonAdi + "='" + QString::number(yeniDeger)
                              + "' WHERE " + kiyaslamaKolonAdi + "='" + urunAdi +"'");
    db.close();
    db.removeDatabase("QSQLITE");
    qDebug() << "exec: " << deger << "HATASI: " << sqlkodu.lastError();
return deger;
}

[SGaist]

Hi,

The second parameter of addDatabase is the connection name which is unrelated to the database itself.

You have to call setDatabaseName on your dB object before calling open.

I would also recommend printing the error you get in case of failure.

[kingsta]

You are totally right. There is a little mistake but it made me crazy. You have solve this and many other problems like this. Thank you!

Mistake

QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE", veritabaniYolu + dbAdi);

Solution

    QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
    db.setDatabaseName(veritabaniYolu + kulDBAdi);

[SGaist]

Great !

Since you have it working now, please mark the thread as solved using the "Topic Tools" button so that other forum users may know a solution has been found :-)