Important: Please read the Qt Code of Conduct - https://forum.qt.io/topic/113070/qt-code-of-conduct

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



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

  • Lifetime Qt Champion

    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.



  • 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);
    

  • Lifetime Qt Champion

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


Log in to reply