[Solved] QT5.1 / Android - How to Bundle and use SQLite database in application.

[sanjaysampat]

I have created a sample test application. It uses the SqLite database. query it and returns me the data of records to display in edit box.

To bundle the database, I used the folder ''android/assets' in 'Other files' in the project. I kept the SqLite file there.

I have used Application Home Location folder to store the database after the installation.

@
MainWindow::MainWindow(QWidget *parent) :
QMainWindow(parent),
ui(new Ui::MainWindow)
{
ui->setupUi(this);
// any other init .....
//find if database exists at user's home directory location
tmpString = QStandardPaths::writableLocation(QStandardPaths::HomeLocation);
QFileInfo databaseFileInfo(QString("%1/%2").arg(tmpString).arg("databaseFileName"));
databasePath = databaseFileInfo.absoluteFilePath();
ui->msgTextEdit->append("databasePath: "+databasePath); // to display full name with path of database
if ( !databaseFileInfo.exists() )
{
ui->msgTextEdit->append("Database does not exist");
bool copySuccess = QFile::copy( QString("assets:/databaseFileName"), databasePath );
if ( !copySuccess )
{
QMessageBox::critical(this, "Error:", QString("Could not copy database from 'assets' to %1").arg(databasePath));
databasePath.clear();
}
}
}

void MainWindow::on_loadDatabasePushButton_clicked()
{
QApplication::setOverrideCursor(QCursor(Qt::WaitCursor));

    QMessageBox mb;
    QSqlDatabase myDB = QSqlDatabase::addDatabase("QSQLITE");
    if( !myDB.isValid() )
    {
        // This shows a message Box if Database Connection Fails.
        QString str;
        str = databasePath;
        str.prepend(" Cannot Add database ");
        mb.setText(str);
        mb.exec();
    }
    //myDB.setHostName("localhost");
    myDB.setDatabaseName(databasePath);
    //myDB.setUserName("username");
    //myDB.setPassword("password");
    if (!myDB.open())
    {
        // This shows a message if System is unable to Open created Database
        mb.setText("Cannot open database  ");
        mb.exec();
        qDebug(&#41; << myDB.lastError(&#41;<<"not created";
    }

    QString qryStr;
    QSqlQuery query(myDB);
    qryStr = QString("SELECT Id,  Name, Description FROM Table1");
    query.prepare(qryStr);
    if( !query.exec&#40;qryStr&#41; &#41;
        qDebug(&#41; << query.lastError();
    else
    {
        ui->msgTextEdit->append(QString("\nThe records in Table1:"));
        while(query.next())
        {
            ui->msgTextEdit->append(QString("\nId:%1  Name:%2  Desc:%3")
                                    .arg(query.value("Id").toString())
                                    .arg(query.value("Name").toString())
                                    .arg(query.value("Description").toString())
                                    );
        }
     }

    QApplication::restoreOverrideCursor();

}
@

tmpString and databasePath are class level QString variables.

Best Regards

[AlterX]

Is this a working solution?

[sanjaysampat]

Yes, this is working sample.
the mainwindow.cpp has the following include...
@
#include "mainwindow.h"
#include "ui_mainwindow.h"
#include <QDebug>
#include <QFile>
#include <QFileInfo>
#include <QMessageBox>
#include <QStandardPaths>
#include <QtSql>
@

It is required to create a sqlite Database called "databaseFileName"

As mentioned earlier
when you are in Android build, you will have "Other files" folder created in your project, where <myProject>.pro file exists.

add the database file in the folder "<myProject>\android\assets"

Build ... Deploy and run ...

(I have NOT tested it on emulator.)

[AlterX]

Ok thanks a lot...I was just searching for something!
I'm just speaking on bugtrack about assets for android...I think is not so portable thinking about ios too...they need to abstract DEPLOYMENTFOLDERS to automatically copy files in right path (android/assets and wherever) for different systems.

[adrz1]

Hello,

What are the implications of copying the database from assets to other location?

thanks

[AlterX]

There is no implications...but Android, like iOS, has the concept of blackbox, that is, you cannot write something outside app's directory.

[adrz1]

Thanks, i don't know if i did something wrong, but for me this is needed to fully operate the database:

@QFile::setPermissions(databasePath, QFile::ReadOwner | QFile::WriteOwner);@

[AlterX]

That is interesting and possible since a friend of mine has had a permission denied.

[sanjaysampat]

Please take care for QT 5.2 RC1 related possible changes
( following copied from
"http://qt-project.org/wiki/Qt_for_Android_known_issues" )

• The way Qt for Android projects are structured has changed in Qt 5.2.0 compared to Qt 5.1.x. An android/ directory is no longer added to your project, and the contents of this directory is no longer automatically the source of the Android APK package. Instead, the source of the project is maintained elsewhere, and you can add or overwrite parts of it if you need to by using the ANDROID_PACKAGE_SOURCE_DIR qmake variable. It’s possible to port old projects to the new system by setting ANDROID_PACKAGE_SOURCE_DIR = $$PWD/android in the .pro file, but you should then take care to remove all unneeded files from the android/ directory (i.e. only keep the files which contain modifications particular to your project.) For instance, if any of the Qt libraries or .jar files are left over in this directory, this will cause conflicts when building the project.

[AlterX]

But using DEPLOYMENTFOLDERS will it automatically create android/assets in target device?