Skip to content
  • Categories
  • Recent
  • Tags
  • Popular
  • Users
  • Groups
  • Search
  • Get Qt Extensions
  • Unsolved
Collapse
Brand Logo
  1. Home
  2. Qt Development
  3. Mobile and Embedded
  4. How to use relative path on SQLite .db file on iOS
Forum Updated to NodeBB v4.3 + New Features

How to use relative path on SQLite .db file on iOS

Scheduled Pinned Locked Moved Solved Mobile and Embedded
19 Posts 4 Posters 1.0k Views 2 Watching
  • Oldest to Newest
  • Newest to Oldest
  • Most Votes
Reply
  • Reply as topic
Log in to reply
This topic has been deleted. Only users with topic management privileges can see it.
  • K Kiovtorov

    @SGaist
    I got that the size of my database is 0 bytes which means that the copying of the file doesn't work but I can't figure it out

    jsulmJ Offline
    jsulmJ Offline
    jsulm
    Lifetime Qt Champion
    wrote on last edited by
    #8

    @Kiovtorov said in How to use relative path on SQLite .db file on iOS:

    which means that the copying of the file doesn't work

    So, you see "Failed to copy database to writable location:" in the log?

    https://forum.qt.io/topic/113070/qt-code-of-conduct

    1 Reply Last reply
    0
    • K Offline
      K Offline
      Kiovtorov
      wrote on last edited by
      #9

      I tried in a test app that I can not copy a file from a resource file (.qrc). I am now searching for another way

      JonBJ 1 Reply Last reply
      0
      • K Kiovtorov

        I tried in a test app that I can not copy a file from a resource file (.qrc). I am now searching for another way

        JonBJ Offline
        JonBJ Offline
        JonB
        wrote on last edited by JonB
        #10

        @Kiovtorov
        First try again on QFile::copy() but remove the qrc, keep the :, so QFile resourceFile(":/resources/Finbank.db");. https://stackoverflow.com/questions/10724899/how-to-copy-qrc-resource-file-into-filesystem
        https://forum.qt.io/topic/13131/how-to-extract-a-resource-from-qrc-solved
        Hopefully that does work? I think it's supposed to.

        If it really does not work, you would have to do the copy yourself via QFile::open(), read content, write to extracted file location.

        K 1 Reply Last reply
        1
        • SGaistS Offline
          SGaistS Offline
          SGaist
          Lifetime Qt Champion
          wrote on last edited by
          #11

          @JonB is correct, I missed that in your code. qrc:/ is used in QML because it uses URI however, in the C++ world, it's :/ that is used to signify that you want to access a file in the resource system.

          Interested in AI ? www.idiap.ch
          Please read the Qt Code of Conduct - https://forum.qt.io/topic/113070/qt-code-of-conduct

          1 Reply Last reply
          1
          • JonBJ JonB

            @Kiovtorov
            First try again on QFile::copy() but remove the qrc, keep the :, so QFile resourceFile(":/resources/Finbank.db");. https://stackoverflow.com/questions/10724899/how-to-copy-qrc-resource-file-into-filesystem
            https://forum.qt.io/topic/13131/how-to-extract-a-resource-from-qrc-solved
            Hopefully that does work? I think it's supposed to.

            If it really does not work, you would have to do the copy yourself via QFile::open(), read content, write to extracted file location.

            K Offline
            K Offline
            Kiovtorov
            wrote on last edited by Kiovtorov
            #12

            Hi @JonB

            Here is the corrected path but it doesn't work

            void DatabaseManager::OpenConnection() {
                // Configure the database connection parameters for SQLite
                m_db = QSqlDatabase::addDatabase("QSQLITE");
            
                // Determine the writable location for the database
                QString writablePath = QStandardPaths::writableLocation(QStandardPaths::AppDataLocation);
                QString databasePath = writablePath + "/Finbank.db";
            
                // Ensure the writable directory exists
                QDir dir(writablePath);
                if (!dir.exists()) {
                    dir.mkpath(writablePath);
                }
            
                if (!QFile::exists(databasePath)) {
                    QFile resourceFile(":/resources/Finbank.db");
                    if (resourceFile.exists()) {
                        if (resourceFile.open(QIODevice::ReadOnly)) {
                            QFile destinationFile(databasePath);
                            if (destinationFile.open(QIODevice::WriteOnly)) {
                                destinationFile.write(resourceFile.readAll());
                                resourceFile.close();
                                destinationFile.close();
                                qDebug() << "Database successfully copied to writable location.";
                            } else {
                                qDebug() << "Failed to open destination file:" << destinationFile.errorString();
                            }
                        } else {
                            qDebug() << "Failed to open resource file:" << resourceFile.errorString();
                        }
                    } else {
                        qDebug() << "Database resource file not found.";
                    }
                }
            
                // Set the SQLite database file location
                m_db.setDatabaseName(databasePath);
            
                // Open the database connection and handle success or failure
                if (m_db.open() && m_db.isOpen()) {
                    qDebug() << "Database opened successfully at" << databasePath;
                } else {
                    // Handle connection error
                    qDebug() << "Database failed to open";
                    qDebug() << m_db.lastError().text();
                }
            }
            
            

            I get an output of

            Database opened successfully at "/Users/boyankiovtorov/Library/Application Support/appFinbank/Finbank.db"
            

            I was unable to access that directory to check the file but nevertheless I think it is generated .db file which is 0 bytes and not the db file from the resource file.
            I am currently looking at a stackoverflow case - https://stackoverflow.com/questions/50828041/how-to-open-database-sqlite-file-on-iphone-real-device

            But i am getting no success so far because I do not have Application Support folder like him

            jsulmJ 1 Reply Last reply
            0
            • K Offline
              K Offline
              Kiovtorov
              wrote on last edited by Kiovtorov
              #13

              @JonB AI thought of copying the database from the CMakeFIle.txt which worked

              cmake_minimum_required(VERSION 3.16)
              
              project(sqlite VERSION 0.1 LANGUAGES CXX)
              
              set(CMAKE_CXX_STANDARD_REQUIRED ON)
              
              find_package(Qt6 6.5 REQUIRED COMPONENTS Quick Sql)
              
              qt_standard_project_setup(REQUIRES 6.5)
              
              qt_add_executable(appsqlite
                  main.cpp
              )
              
              qt_add_qml_module(appsqlite
                  URI sqlite
                  VERSION 1.0
                  QML_FILES
                      Main.qml
                      SOURCES maketransactions.hpp maketransactions.cpp
                      RESOURCES resources.qrc
              )
              
              # Qt for iOS sets MACOSX_BUNDLE_GUI_IDENTIFIER automatically since Qt 6.1.
              # If you are developing for iOS or macOS you should consider setting an
              # explicit, fixed bundle identifier manually though.
              set_target_properties(appsqlite PROPERTIES
              #    MACOSX_BUNDLE_GUI_IDENTIFIER com.example.appsqlite
                  MACOSX_BUNDLE_BUNDLE_VERSION ${PROJECT_VERSION}
                  MACOSX_BUNDLE_SHORT_VERSION_STRING ${PROJECT_VERSION_MAJOR}.${PROJECT_VERSION_MINOR}
                  MACOSX_BUNDLE TRUE
                  WIN32_EXECUTABLE TRUE
              )
              
              target_link_libraries(appsqlite
                  PRIVATE Qt6::Quick Qt6::Sql
              )
              
              include(GNUInstallDirs)
              install(TARGETS appsqlite
                  BUNDLE DESTINATION .
                  LIBRARY DESTINATION ${CMAKE_INSTALL_LIBDIR}
                  RUNTIME DESTINATION ${CMAKE_INSTALL_BINDIR}
              )
              
              
              # Add the database file to the bundle
              set(APP_RESOURCES Finbank.db)
              
              # Copy resources to the macOS/iOS bundle or build directory
              foreach(resource ${APP_RESOURCES})
                  configure_file(${resource} ${resource} COPYONLY)
              endforeach()
              
              # Ensure it is included in the app bundle
              if(APPLE)
                  set_source_files_properties(${APP_RESOURCES} PROPERTIES MACOSX_PACKAGE_LOCATION "Resources")
              endif()
              
              # Include it in the target
              target_sources(appsqlite PRIVATE ${APP_RESOURCES})
              
              

              The problem there is that for different OSs the path to this db file is different. It generates it at

              /Users/boyankiovtorov/Documents/sqlite/build/Qt_6_8_0_for_macOS-Debug/Finbank.db
              

              Do you think it is a good idea?

              1 Reply Last reply
              0
              • K Kiovtorov

                Hi @JonB

                Here is the corrected path but it doesn't work

                void DatabaseManager::OpenConnection() {
                    // Configure the database connection parameters for SQLite
                    m_db = QSqlDatabase::addDatabase("QSQLITE");
                
                    // Determine the writable location for the database
                    QString writablePath = QStandardPaths::writableLocation(QStandardPaths::AppDataLocation);
                    QString databasePath = writablePath + "/Finbank.db";
                
                    // Ensure the writable directory exists
                    QDir dir(writablePath);
                    if (!dir.exists()) {
                        dir.mkpath(writablePath);
                    }
                
                    if (!QFile::exists(databasePath)) {
                        QFile resourceFile(":/resources/Finbank.db");
                        if (resourceFile.exists()) {
                            if (resourceFile.open(QIODevice::ReadOnly)) {
                                QFile destinationFile(databasePath);
                                if (destinationFile.open(QIODevice::WriteOnly)) {
                                    destinationFile.write(resourceFile.readAll());
                                    resourceFile.close();
                                    destinationFile.close();
                                    qDebug() << "Database successfully copied to writable location.";
                                } else {
                                    qDebug() << "Failed to open destination file:" << destinationFile.errorString();
                                }
                            } else {
                                qDebug() << "Failed to open resource file:" << resourceFile.errorString();
                            }
                        } else {
                            qDebug() << "Database resource file not found.";
                        }
                    }
                
                    // Set the SQLite database file location
                    m_db.setDatabaseName(databasePath);
                
                    // Open the database connection and handle success or failure
                    if (m_db.open() && m_db.isOpen()) {
                        qDebug() << "Database opened successfully at" << databasePath;
                    } else {
                        // Handle connection error
                        qDebug() << "Database failed to open";
                        qDebug() << m_db.lastError().text();
                    }
                }
                
                

                I get an output of

                Database opened successfully at "/Users/boyankiovtorov/Library/Application Support/appFinbank/Finbank.db"
                

                I was unable to access that directory to check the file but nevertheless I think it is generated .db file which is 0 bytes and not the db file from the resource file.
                I am currently looking at a stackoverflow case - https://stackoverflow.com/questions/50828041/how-to-open-database-sqlite-file-on-iphone-real-device

                But i am getting no success so far because I do not have Application Support folder like him

                jsulmJ Offline
                jsulmJ Offline
                jsulm
                Lifetime Qt Champion
                wrote on last edited by
                #14

                @Kiovtorov But do you see any of the errors you're printing (like "Failed to open resource file:"")?

                https://forum.qt.io/topic/113070/qt-code-of-conduct

                K 1 Reply Last reply
                0
                • jsulmJ jsulm

                  @Kiovtorov But do you see any of the errors you're printing (like "Failed to open resource file:"")?

                  K Offline
                  K Offline
                  Kiovtorov
                  wrote on last edited by
                  #15

                  @jsulm
                  No, I am getting

                  Database opened successfully at "/Users/boyankiovtorov/Library/Application Support/appFinbank/Finbank.db"
                  

                  but it is just because it automatically generates it.

                  jsulmJ 1 Reply Last reply
                  0
                  • K Kiovtorov

                    @jsulm
                    No, I am getting

                    Database opened successfully at "/Users/boyankiovtorov/Library/Application Support/appFinbank/Finbank.db"
                    

                    but it is just because it automatically generates it.

                    jsulmJ Offline
                    jsulmJ Offline
                    jsulm
                    Lifetime Qt Champion
                    wrote on last edited by
                    #16

                    @Kiovtorov said in How to use relative path on SQLite .db file on iOS:

                    but it is just because it automatically generates it.

                    If it automatically generates it the copying failed.
                    Run through debugger step by step to see what happens.

                    https://forum.qt.io/topic/113070/qt-code-of-conduct

                    1 Reply Last reply
                    0
                    • K Offline
                      K Offline
                      Kiovtorov
                      wrote on last edited by
                      #17

                      I just tested a new method which works (kind of)

                      void DatabaseManager::OpenConnection() {
                          // Configure the database connection parameters for SQLite
                          m_db = QSqlDatabase::addDatabase("QSQLITE");
                      
                          // Use QStandardPaths to get a writable location
                          QString dbName = "identifier.sqlite";
                          QString dbLocation = QStandardPaths::writableLocation(QStandardPaths::DocumentsLocation);
                          QString fullDbPath = dbLocation + "/" + dbName;
                      
                          // Set the SQLite database file location
                          m_db.setDatabaseName(fullDbPath);
                      
                          qDebug() << "Database path:" << fullDbPath;
                      
                          // Check if the database file exists
                          if (!QFile::exists(fullDbPath)) {
                              qDebug() << "Database does not exist, creating a new one.";
                          }
                      
                          // Open the database connection and handle success or failure
                          if (m_db.open()) {
                              qDebug() << "Database opened successfully";
                      
                              if (!QFile::exists(fullDbPath)) {
                                  // If the database is newly created, initialize tables
                                  QSqlQuery query(m_db);
                                  query.prepare("SELECT * FROM users WHERE username = :username");
                                  query.bindValue(":username", "test");
                      
                                  if (!query.exec()) {
                                      qDebug() << "Error creating settings table:" << query.lastError().text();
                                  } else {
                                      qDebug() << "Settings table created successfully.";
                                      qDebug() << "lite: " << query.value("username");
                                  }
                              }
                          } else {
                              // Handle connection error
                              qDebug() << "Database failed to open";
                              qDebug() << m_db.lastError().text();
                          }
                      }
                      

                      it creates a database in my Documents folder or the ios devide documents folder. I tested if it works with creating a new table

                      QSqlQuery query(db);
                                  query.prepare("CREATE TABLE settings ("
                                                "`id` INTEGER PRIMARY KEY AUTOINCREMENT, "
                                                "`username` TEXT, "
                                                "`password` TEXT, "
                                                "`remember_login` TEXT);");
                                  if (!query.exec())
                                  {
                                      qDebug() << "ERROR: FAILED TO CREATE TABLE -" << query.lastError().text();
                                  }
                      
                                  query.prepare("INSERT INTO settings (username, password, remember_login) VALUES ('aaa', 'bbb', '1')");
                                  query.exec();
                      
                                  query.prepare("SELECT * FROM settings");
                                  query.exec();
                      
                      

                      and it works on macos and ios. Before I couldn't even find the file so I can now at least debug more. I will now try to copy the data from my original sqlite file to that one in the documents

                      1 Reply Last reply
                      0
                      • K Offline
                        K Offline
                        Kiovtorov
                        wrote on last edited by
                        #18

                        FINALLY!
                        I don't know what is the difference between a db file and sqllite file but with .sqlite file the copying from .qrc file works
                        Here is my code for everyone with the same problem

                        
                        void DatabaseManager::OpenConnection() {
                            // Configure the database connection parameters for SQLite
                            m_db = QSqlDatabase::addDatabase("QSQLITE");
                        
                            // Use QStandardPaths to get a writable location
                            QString dbName = "identifier.sqlite";
                            QString dbLocation = QStandardPaths::writableLocation(QStandardPaths::DocumentsLocation);
                            QString fullDbPath = dbLocation + "/" + dbName;
                        
                            // Set the SQLite database file location
                            m_db.setDatabaseName(fullDbPath);
                        
                            qDebug() << "Database path:" << fullDbPath;
                        
                            // Check if the database file exists in the writable location
                            if (!QFile::exists(fullDbPath)) {
                                qDebug() << "Database does not exist in Documents, copying from resources.";
                        
                                // Path to the resource database file
                                QString resourceDbPath = ":/resources/identifier.sqlite"; // Adjust the path to your resource file
                        
                                // Attempt to copy the database from resources to the writable location
                                if (QFile::copy(resourceDbPath, fullDbPath)) {
                                    qDebug() << "Database copied successfully to Documents.";
                                    // Set file permissions to writable (necessary for some platforms)
                                    QFile::setPermissions(fullDbPath, QFileDevice::ReadOwner | QFileDevice::WriteOwner);
                                } else {
                                    qDebug() << "Failed to copy database from resources.";
                                }
                            }
                        
                            // Open the database connection and handle success or failure
                            if (m_db.open()) {
                                qDebug() << "Database opened successfully.";
                        
                                // Sample query to verify the database contents
                                QSqlQuery query(m_db);
                                query.prepare("SELECT * FROM users WHERE username = :username");
                                query.bindValue(":username", "test");
                        
                                if (!query.exec()) {
                                    qDebug() << "Error executing query:" << query.lastError().text();
                                } else {
                                    while (query.next()) {
                                        qDebug() << "Username:" << query.value("username").toString();
                                    }
                                }
                            } else {
                                // Handle connection error
                                qDebug() << "Database failed to open.";
                                qDebug() << m_db.lastError().text();
                            }
                        }
                        
                        1 Reply Last reply
                        0
                        • K Kiovtorov has marked this topic as solved on
                        • K Offline
                          K Offline
                          Kiovtorov
                          wrote on last edited by
                          #19

                          Here is the same code using appDataLocation

                          void DatabaseManager::OpenConnection() {
                              // Configure the database connection parameters for SQLite
                              m_db = QSqlDatabase::addDatabase("QSQLITE");
                          
                              // Use QStandardPaths to get the application data location
                              QString dbName = "identifier.sqlite";
                              QString dbLocation = QStandardPaths::writableLocation(QStandardPaths::AppDataLocation);
                              QString fullDbPath = dbLocation + "/" + dbName;
                          
                              QDir dbDir(dbLocation);
                              if (!dbDir.exists()) {
                                  if (dbDir.mkpath(dbLocation)) {
                                      qDebug() << "Created application data directory:" << dbLocation;
                                  } else {
                                      qDebug() << "Failed to create application data directory:" << dbLocation;
                                      return; // Exit if the directory cannot be created
                                  }
                              }
                          
                              // Set the SQLite database file location
                              m_db.setDatabaseName(fullDbPath);
                          
                              // Check if the database file exists in the application data location
                              if (!QFile::exists(fullDbPath)) {
                                  qDebug() << "Database does not exist in AppDataLocation, copying from resources.";
                          
                                  // Path to the resource database file
                                  QString resourceDbPath = ":/resources/identifier.sqlite"; // Adjust the path to your resource file
                          
                                  // Attempt to copy the database from resources to the writable location
                                  if (QFile::copy(resourceDbPath, fullDbPath)) {
                                      qDebug() << "Database copied successfully to AppDataLocation.";
                                      // Set file permissions to writable (necessary for some platforms)
                                      QFile::setPermissions(fullDbPath, QFileDevice::ReadOwner | QFileDevice::WriteOwner);
                                  } else {
                                      qDebug() << "Failed to copy database from resources.";
                                      return; // Exit if the database cannot be copied
                                  }
                              }
                          
                              // Open the database connection and handle success or failure
                              if (m_db.open()) {
                                  qDebug() << "Database opened successfully.";
                          
                                  // Sample query to verify the database contents
                              } else {
                                  // Handle connection error
                                  qDebug() << "Database failed to open.";
                                  qDebug() << m_db.lastError().text();
                              }
                          }
                          
                          1 Reply Last reply
                          0

                          • Login

                          • Login or register to search.
                          • First post
                            Last post
                          0
                          • Categories
                          • Recent
                          • Tags
                          • Popular
                          • Users
                          • Groups
                          • Search
                          • Get Qt Extensions
                          • Unsolved