Skip to content
  • Categories
  • Recent
  • Tags
  • Popular
  • Users
  • Groups
  • Search
  • Get Qt Extensions
  • Unsolved
Collapse
Brand Logo
  1. Home
  2. Qt Development
  3. General and Desktop
  4. Open sqlite database on network drive
Forum Updated to NodeBB v4.3 + New Features

Open sqlite database on network drive

Scheduled Pinned Locked Moved Solved General and Desktop
17 Posts 5 Posters 2.6k 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.
  • O Offline
    O Offline
    OlivierLdff
    wrote on 15 Jun 2021, 07:44 last edited by
    #1

    Hi,
    I'm trying to open a sqlite database with QSqlDatabase from a network drive mounted in my ubuntu 20 system.

    const QString path = "/run/user/1000/gvfs/smb-share:server=mynas.local,share=drive/db.sqlite";
    auto db = QSqlDatabase::addDatabase("QSQLITE", path);
    db.open(); // Fail with "unable to open database file Error opening database"
    

    This code is working as expected anywhere else.
    Any idea what i'm missing? I'm able to manipulate file on this drive with QFile without any issue.
    Also using DBBrowser For SQLite I don't have any issue (it is not using QSqlDatabase).

    Thanks for any hint.
    Have a nice day.

    J A 2 Replies Last reply 15 Jun 2021, 07:52
    0
    • O OlivierLdff
      15 Jun 2021, 07:44

      Hi,
      I'm trying to open a sqlite database with QSqlDatabase from a network drive mounted in my ubuntu 20 system.

      const QString path = "/run/user/1000/gvfs/smb-share:server=mynas.local,share=drive/db.sqlite";
      auto db = QSqlDatabase::addDatabase("QSQLITE", path);
      db.open(); // Fail with "unable to open database file Error opening database"
      

      This code is working as expected anywhere else.
      Any idea what i'm missing? I'm able to manipulate file on this drive with QFile without any issue.
      Also using DBBrowser For SQLite I don't have any issue (it is not using QSqlDatabase).

      Thanks for any hint.
      Have a nice day.

      J Offline
      J Offline
      jsulm
      Lifetime Qt Champion
      wrote on 15 Jun 2021, 07:52 last edited by
      #2

      @OlivierLdff said in Open sqlite database on network drive:

      This code is working as expected anywhere else.

      Where is it not working? Or do you mean local path is working?

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

      O 1 Reply Last reply 15 Jun 2021, 08:39
      0
      • O OlivierLdff
        15 Jun 2021, 07:44

        Hi,
        I'm trying to open a sqlite database with QSqlDatabase from a network drive mounted in my ubuntu 20 system.

        const QString path = "/run/user/1000/gvfs/smb-share:server=mynas.local,share=drive/db.sqlite";
        auto db = QSqlDatabase::addDatabase("QSQLITE", path);
        db.open(); // Fail with "unable to open database file Error opening database"
        

        This code is working as expected anywhere else.
        Any idea what i'm missing? I'm able to manipulate file on this drive with QFile without any issue.
        Also using DBBrowser For SQLite I don't have any issue (it is not using QSqlDatabase).

        Thanks for any hint.
        Have a nice day.

        A Offline
        A Offline
        artwaw
        wrote on 15 Jun 2021, 08:22 last edited by
        #3

        @OlivierLdff
        Since addDatabase(const QString &type, const QString &connectionName = QLatin1String(defaultConnection)) please try:

        const QString path = "/run/user/1000/gvfs/smb-share:server=mynas.local,share=drive/db.sqlite";
        auto db = QSqlDatabase::addDatabase("QSQLITE");
        db.setDatabaseName(path);
        if (!db.open()) {
            qDebug() << db.lastError().text();
        }
        

        Connection name != database name (or, in case of SQLite, database file name).

        For more information please re-read.

        Kind Regards,
        Artur

        C 1 Reply Last reply 15 Jun 2021, 09:12
        3
        • J jsulm
          15 Jun 2021, 07:52

          @OlivierLdff said in Open sqlite database on network drive:

          This code is working as expected anywhere else.

          Where is it not working? Or do you mean local path is working?

          O Offline
          O Offline
          OlivierLdff
          wrote on 15 Jun 2021, 08:39 last edited by
          #4

          @jsulm The code is working for example if I use path ~/db.sqlite, or in any other directory. Only this network drive with the strange path fail.

          @artwaw I tried your solution with no luck. It still fails with the same error.

          Is there a way to enable more logs from QSqlDatabase?

          A 1 Reply Last reply 15 Jun 2021, 08:41
          0
          • O OlivierLdff
            15 Jun 2021, 08:39

            @jsulm The code is working for example if I use path ~/db.sqlite, or in any other directory. Only this network drive with the strange path fail.

            @artwaw I tried your solution with no luck. It still fails with the same error.

            Is there a way to enable more logs from QSqlDatabase?

            A Offline
            A Offline
            artwaw
            wrote on 15 Jun 2021, 08:41 last edited by
            #5

            @OlivierLdff if you tried what I posted you should have error text in the console. Please paste it.
            "more logs" can be obtained by examining the value of QSqlDatabase::lastError() which is of class QSqlError

            For more information please re-read.

            Kind Regards,
            Artur

            O 1 Reply Last reply 15 Jun 2021, 09:28
            2
            • A artwaw
              15 Jun 2021, 08:22

              @OlivierLdff
              Since addDatabase(const QString &type, const QString &connectionName = QLatin1String(defaultConnection)) please try:

              const QString path = "/run/user/1000/gvfs/smb-share:server=mynas.local,share=drive/db.sqlite";
              auto db = QSqlDatabase::addDatabase("QSQLITE");
              db.setDatabaseName(path);
              if (!db.open()) {
                  qDebug() << db.lastError().text();
              }
              

              Connection name != database name (or, in case of SQLite, database file name).

              C Online
              C Online
              Christian Ehrlicher
              Lifetime Qt Champion
              wrote on 15 Jun 2021, 09:12 last edited by
              #6

              @artwaw said in Open sqlite database on network drive:

              "/run/user/1000/gvfs/smb-share:server=mynas.local,share=drive/db.sqlite"

              I doubt this is a valid file name for a sqlite database.

              Qt Online Installer direct download: https://download.qt.io/official_releases/online_installers/
              Visit the Qt Academy at https://academy.qt.io/catalog

              1 Reply Last reply
              3
              • A artwaw
                15 Jun 2021, 08:41

                @OlivierLdff if you tried what I posted you should have error text in the console. Please paste it.
                "more logs" can be obtained by examining the value of QSqlDatabase::lastError() which is of class QSqlError

                O Offline
                O Offline
                OlivierLdff
                wrote on 15 Jun 2021, 09:28 last edited by OlivierLdff
                #7

                @artwaw So it seems I'm not even allowed to do touch db.sqlite once the file have been removed. Maybe this is an issue with my NAS?
                Anyway I tried another name to see, foo.sqlite. So you were right, opening work now!

                Error:  QSqlError("10", "Unable to fetch row", "disk I/O error")
                Error:  QSqlError("", "Parameter count mismatch", "")
                
                

                File foo.sqlite gets created on my NAS but size is 0 bytes.

                @Christian-Ehrlicher I can open this location with a QFile, and write to it without any problems.

                C A 2 Replies Last reply 15 Jun 2021, 09:33
                0
                • O OlivierLdff
                  15 Jun 2021, 09:28

                  @artwaw So it seems I'm not even allowed to do touch db.sqlite once the file have been removed. Maybe this is an issue with my NAS?
                  Anyway I tried another name to see, foo.sqlite. So you were right, opening work now!

                  Error:  QSqlError("10", "Unable to fetch row", "disk I/O error")
                  Error:  QSqlError("", "Parameter count mismatch", "")
                  
                  

                  File foo.sqlite gets created on my NAS but size is 0 bytes.

                  @Christian-Ehrlicher I can open this location with a QFile, and write to it without any problems.

                  C Online
                  C Online
                  Christian Ehrlicher
                  Lifetime Qt Champion
                  wrote on 15 Jun 2021, 09:33 last edited by
                  #8

                  @OlivierLdff said in Open sqlite database on network drive:

                  I can open this location with a QFile, and write to it without any problems.

                  But sqlite can not handle this path since fopen() will for sure not accept it. Try it on the command line with sqlite3 executable.

                  Qt Online Installer direct download: https://download.qt.io/official_releases/online_installers/
                  Visit the Qt Academy at https://academy.qt.io/catalog

                  O 1 Reply Last reply 15 Jun 2021, 09:43
                  1
                  • C Christian Ehrlicher
                    15 Jun 2021, 09:33

                    @OlivierLdff said in Open sqlite database on network drive:

                    I can open this location with a QFile, and write to it without any problems.

                    But sqlite can not handle this path since fopen() will for sure not accept it. Try it on the command line with sqlite3 executable.

                    O Offline
                    O Offline
                    OlivierLdff
                    wrote on 15 Jun 2021, 09:43 last edited by
                    #9

                    @Christian-Ehrlicher I should have started with that.

                    sqlite3 /run/user/1000/gvfs/smb-share:server=mynas.local,share=drive/foo.sqlite
                    sqlite> create table test(one varchar(10), two smallint);
                    Error: disk I/O error
                    

                    So could this be related to my network drive?

                    1 Reply Last reply
                    0
                    • O OlivierLdff
                      15 Jun 2021, 09:28

                      @artwaw So it seems I'm not even allowed to do touch db.sqlite once the file have been removed. Maybe this is an issue with my NAS?
                      Anyway I tried another name to see, foo.sqlite. So you were right, opening work now!

                      Error:  QSqlError("10", "Unable to fetch row", "disk I/O error")
                      Error:  QSqlError("", "Parameter count mismatch", "")
                      
                      

                      File foo.sqlite gets created on my NAS but size is 0 bytes.

                      @Christian-Ehrlicher I can open this location with a QFile, and write to it without any problems.

                      A Offline
                      A Offline
                      artwaw
                      wrote on 15 Jun 2021, 09:45 last edited by
                      #10

                      @OlivierLdff size will be zero until you write anything to the db (create table for example), that is only to be expected.
                      Like @Christian-Ehrlicher wrote - SQLite driver uses fopen() so the path variable need to conform.

                      For network resources (and I'd like to point out that there is a strong advocacy against using sqlite over network) you might want to consider URI notation https://sqlite.org/uri.html

                      For more information please re-read.

                      Kind Regards,
                      Artur

                      O 1 Reply Last reply 15 Jun 2021, 09:56
                      1
                      • A artwaw
                        15 Jun 2021, 09:45

                        @OlivierLdff size will be zero until you write anything to the db (create table for example), that is only to be expected.
                        Like @Christian-Ehrlicher wrote - SQLite driver uses fopen() so the path variable need to conform.

                        For network resources (and I'd like to point out that there is a strong advocacy against using sqlite over network) you might want to consider URI notation https://sqlite.org/uri.html

                        O Offline
                        O Offline
                        OlivierLdff
                        wrote on 15 Jun 2021, 09:56 last edited by OlivierLdff
                        #11

                        @artwaw I tried reading from an existing sqlite file, no problem.
                        But writing doesn't seems to work, and always result in to disk I/O error. (even with prepending file: to the path)

                        So is my issue a known limitation of sqlite?

                        KroMignonK A 2 Replies Last reply 15 Jun 2021, 09:57
                        0
                        • O OlivierLdff
                          15 Jun 2021, 09:56

                          @artwaw I tried reading from an existing sqlite file, no problem.
                          But writing doesn't seems to work, and always result in to disk I/O error. (even with prepending file: to the path)

                          So is my issue a known limitation of sqlite?

                          KroMignonK Offline
                          KroMignonK Offline
                          KroMignon
                          wrote on 15 Jun 2021, 09:57 last edited by
                          #12

                          @OlivierLdff said in Open sqlite database on network drive:

                          So is my issue known limitation of sqlite?

                          I don't think so, I think you don't have write access.

                          It is an old maxim of mine that when you have excluded the impossible, whatever remains, however improbable, must be the truth. (Sherlock Holmes)

                          O 1 Reply Last reply 15 Jun 2021, 09:59
                          1
                          • KroMignonK KroMignon
                            15 Jun 2021, 09:57

                            @OlivierLdff said in Open sqlite database on network drive:

                            So is my issue known limitation of sqlite?

                            I don't think so, I think you don't have write access.

                            O Offline
                            O Offline
                            OlivierLdff
                            wrote on 15 Jun 2021, 09:59 last edited by
                            #13

                            @KroMignon Is there something special to get them?
                            I'm able to write QFile at this location.

                            KroMignonK 1 Reply Last reply 15 Jun 2021, 10:07
                            0
                            • O OlivierLdff
                              15 Jun 2021, 09:59

                              @KroMignon Is there something special to get them?
                              I'm able to write QFile at this location.

                              KroMignonK Offline
                              KroMignonK Offline
                              KroMignon
                              wrote on 15 Jun 2021, 10:07 last edited by
                              #14

                              @OlivierLdff said in Open sqlite database on network drive:

                              Is there something special to get them?
                              I'm able to write QFile at this location.

                              Your file path looks uncommon to me.
                              I am not sure it can be handled directly.
                              Does this works?

                              touch /run/user/1000/gvfs/smb-share:server=mynas.local,share=drive/foo.sqlite
                              

                              It is an old maxim of mine that when you have excluded the impossible, whatever remains, however improbable, must be the truth. (Sherlock Holmes)

                              O 1 Reply Last reply 15 Jun 2021, 10:13
                              0
                              • KroMignonK KroMignon
                                15 Jun 2021, 10:07

                                @OlivierLdff said in Open sqlite database on network drive:

                                Is there something special to get them?
                                I'm able to write QFile at this location.

                                Your file path looks uncommon to me.
                                I am not sure it can be handled directly.
                                Does this works?

                                touch /run/user/1000/gvfs/smb-share:server=mynas.local,share=drive/foo.sqlite
                                
                                O Offline
                                O Offline
                                OlivierLdff
                                wrote on 15 Jun 2021, 10:13 last edited by
                                #15

                                @KroMignon said in Open sqlite database on network drive:

                                touch /run/user/1000/gvfs/smb-share:server=mynas.local,share=drive/foo.sqlite

                                Yes it works.

                                From what I see this issue isn't Qt related as I tought at the beginning.

                                1 Reply Last reply
                                0
                                • O OlivierLdff
                                  15 Jun 2021, 09:56

                                  @artwaw I tried reading from an existing sqlite file, no problem.
                                  But writing doesn't seems to work, and always result in to disk I/O error. (even with prepending file: to the path)

                                  So is my issue a known limitation of sqlite?

                                  A Offline
                                  A Offline
                                  artwaw
                                  wrote on 15 Jun 2021, 10:27 last edited by
                                  #16

                                  @OlivierLdff might be this is somehow related to the way SQLite driver operates. I'd either mount the share to be accessible via usual path (like /media/share) or ditch sqlite in favour of psql/mysql.
                                  Please also note two things:

                                  • smb share might have performance issues with sqlite, this might lead to either transfer data loss or file being garbled (at the extreme);
                                  • at the same time psql/mysql might be actually faster than sqlite over smb

                                  Of course, it all depends on how extensively you use that db.
                                  Part of the performance penalties can be alleviated by using nfs instead. In both cases it is recommended to turn off any kind of read/write caching for sqlite over network as caching introduces potential errors in the data. SQLite is designed to compete with file system operations (fopen, read, seek, etc) hence using it over the network is always tricky (I agree that for lightweight use it works most of the time... except where it does not and debugging those issues is never easy).

                                  For more information please re-read.

                                  Kind Regards,
                                  Artur

                                  1 Reply Last reply
                                  0
                                  • O Offline
                                    O Offline
                                    OlivierLdff
                                    wrote on 15 Jun 2021, 10:32 last edited by
                                    #17

                                    @artwaw Thanks a lot for the informations.
                                    For my particular case, I just need to populate a database with some data, then close it.
                                    I think it will be way easier to write the sqlite database in some tmp/ folder, then save it on my NAS with a copy operation.

                                    Thanks a lot for your support have a nice day.

                                    1 Reply Last reply
                                    0

                                    1/17

                                    15 Jun 2021, 07:44

                                    • Login

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