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. opening a non existing Sqlite Db
Forum Updated to NodeBB v4.3 + New Features

opening a non existing Sqlite Db

Scheduled Pinned Locked Moved Solved General and Desktop
12 Posts 4 Posters 1.4k 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.
  • M Offline
    M Offline
    MasterQ
    wrote on 14 Feb 2024, 16:03 last edited by
    #1

    Hi,

    is it intended that the attempt to open a non existing sqlite database lead to no error?

    auto db =QSqlDatabase::addDatabase("QSQLITE", "test");
    db.setDatabaseName("devel.db2");
    qDebug() <<"open:"<< db.open();
    auto model = QSqlTableModel(nullptr, db);
    model.setTable("Dokumente");
    qDebug() << "select:" << model.select();
    qDebug() << "db.lastError:" << db.lastError();
    qDebug() << "model.lastError:"<< model.lastError();
    qDebug() << "rowCount:" <<model.rowCount();
    

    in this example the database file devel.db2 does not exist. But I get no error from the db. The model is claiming that the table is not found. But why gives the open() command no error?

    open: true
    select: false
    db.lastError: QSqlError("", "", "")
    model.lastError: QSqlError("", "Unable to find table Dokumente", "")
    rowCount: 0
    
    J J 2 Replies Last reply 14 Feb 2024, 16:08
    0
    • M MasterQ
      14 Feb 2024, 16:03

      Hi,

      is it intended that the attempt to open a non existing sqlite database lead to no error?

      auto db =QSqlDatabase::addDatabase("QSQLITE", "test");
      db.setDatabaseName("devel.db2");
      qDebug() <<"open:"<< db.open();
      auto model = QSqlTableModel(nullptr, db);
      model.setTable("Dokumente");
      qDebug() << "select:" << model.select();
      qDebug() << "db.lastError:" << db.lastError();
      qDebug() << "model.lastError:"<< model.lastError();
      qDebug() << "rowCount:" <<model.rowCount();
      

      in this example the database file devel.db2 does not exist. But I get no error from the db. The model is claiming that the table is not found. But why gives the open() command no error?

      open: true
      select: false
      db.lastError: QSqlError("", "", "")
      model.lastError: QSqlError("", "Unable to find table Dokumente", "")
      rowCount: 0
      
      J Offline
      J Offline
      JonB
      wrote on 14 Feb 2024, 16:08 last edited by
      #2

      @MasterQ
      My recollection of SQLite behaviour (I don't use it) is that, by default, if you pass a non-existent filepath to open it creates it as a new, empty database! And opens it. So successful operation, but database is empty. Does the file exist after you have run this when it did not before (not sure where your file will be since you pass a relative path so depends whatever the current directory is at that time)? I think there is some configuration option you can pass when connecting to tell it not to do that if you do not want it to.

      M 1 Reply Last reply 14 Feb 2024, 16:16
      1
      • J JonB
        14 Feb 2024, 16:08

        @MasterQ
        My recollection of SQLite behaviour (I don't use it) is that, by default, if you pass a non-existent filepath to open it creates it as a new, empty database! And opens it. So successful operation, but database is empty. Does the file exist after you have run this when it did not before (not sure where your file will be since you pass a relative path so depends whatever the current directory is at that time)? I think there is some configuration option you can pass when connecting to tell it not to do that if you do not want it to.

        M Offline
        M Offline
        MasterQ
        wrote on 14 Feb 2024, 16:16 last edited by
        #3

        @JonB

        ahh, I see. I can remember to have read about.

        It is set by the connection open. Unfortunately it is not possible to suppress the creation of a non existing sqlite database file.

        thnx

        J 1 Reply Last reply 14 Feb 2024, 16:22
        0
        • M MasterQ has marked this topic as solved on 14 Feb 2024, 16:16
        • M MasterQ
          14 Feb 2024, 16:16

          @JonB

          ahh, I see. I can remember to have read about.

          It is set by the connection open. Unfortunately it is not possible to suppress the creation of a non existing sqlite database file.

          thnx

          J Offline
          J Offline
          JonB
          wrote on 14 Feb 2024, 16:22 last edited by
          #4

          @MasterQ said in opening a non existing Sqlite Db:

          It is set by the connection open. Unfortunately it is not possible to suppress the creation of a non existing sqlite database file.

          I think you are wrong here, and you can change the behaviour to "not create" if you wish, including from Qt. I suspect there is an old post on this forum for this, but good luck finding it! See QSqlDatabase::setConnectOptions(). And I think the option for SQLite is QSQLITE_OPEN_READONLY

          If set, the database is open in read-only mode which will fail if no database exists. Otherwise the database will be opened in read-write mode and created if the database file does not yet exist (default)

          M 1 Reply Last reply 14 Feb 2024, 17:46
          1
          • S Offline
            S Offline
            SGaist
            Lifetime Qt Champion
            wrote on 14 Feb 2024, 17:35 last edited by
            #5

            Hi,

            You can also check the existence of the file before opening it.

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

            M 1 Reply Last reply 14 Feb 2024, 17:46
            1
            • J JonB
              14 Feb 2024, 16:22

              @MasterQ said in opening a non existing Sqlite Db:

              It is set by the connection open. Unfortunately it is not possible to suppress the creation of a non existing sqlite database file.

              I think you are wrong here, and you can change the behaviour to "not create" if you wish, including from Qt. I suspect there is an old post on this forum for this, but good luck finding it! See QSqlDatabase::setConnectOptions(). And I think the option for SQLite is QSQLITE_OPEN_READONLY

              If set, the database is open in read-only mode which will fail if no database exists. Otherwise the database will be opened in read-write mode and created if the database file does not yet exist (default)

              M Offline
              M Offline
              MasterQ
              wrote on 14 Feb 2024, 17:46 last edited by
              #6

              @JonB said in opening a non existing Sqlite Db:

              @MasterQ said in opening a non existing Sqlite Db:

              It is set by the connection open. Unfortunately it is not possible to suppress the creation of a non existing sqlite database file.

              I think you are wrong here, and you can change the behaviour to "not create" if you wish, including from Qt. I suspect there is an old post on this forum for this, but good luck finding it! See QSqlDatabase::setConnectOptions(). And I think the option for SQLite is QSQLITE_OPEN_READONLY

              Yes, of course, but I need the database READ/WRITE and not READONLY!!

              J 1 Reply Last reply 14 Feb 2024, 18:01
              0
              • S SGaist
                14 Feb 2024, 17:35

                Hi,

                You can also check the existence of the file before opening it.

                M Offline
                M Offline
                MasterQ
                wrote on 14 Feb 2024, 17:46 last edited by
                #7

                @SGaist said in opening a non existing Sqlite Db:

                Hi,

                You can also check the existence of the file before opening it.

                I now have added such a check before the db is opened

                M 1 Reply Last reply 14 Feb 2024, 17:49
                1
                • M MasterQ
                  14 Feb 2024, 17:46

                  @SGaist said in opening a non existing Sqlite Db:

                  Hi,

                  You can also check the existence of the file before opening it.

                  I now have added such a check before the db is opened

                  M Offline
                  M Offline
                  MasterQ
                  wrote on 14 Feb 2024, 17:49 last edited by
                  #8

                  @MasterQ

                  this issue came up during checking another (strange) problem. I will open a new thread for that.

                  1 Reply Last reply
                  0
                  • M MasterQ
                    14 Feb 2024, 17:46

                    @JonB said in opening a non existing Sqlite Db:

                    @MasterQ said in opening a non existing Sqlite Db:

                    It is set by the connection open. Unfortunately it is not possible to suppress the creation of a non existing sqlite database file.

                    I think you are wrong here, and you can change the behaviour to "not create" if you wish, including from Qt. I suspect there is an old post on this forum for this, but good luck finding it! See QSqlDatabase::setConnectOptions(). And I think the option for SQLite is QSQLITE_OPEN_READONLY

                    Yes, of course, but I need the database READ/WRITE and not READONLY!!

                    J Offline
                    J Offline
                    JonB
                    wrote on 14 Feb 2024, 18:01 last edited by JonB
                    #9

                    @MasterQ said in opening a non existing Sqlite Db:

                    Yes, of course, but I need the database READ/WRITE and not READONLY!!

                    Yes, it's irritating that option says it suppresses the creation but at the expense of opening existing read-only. Looks like the SQLite driver supports this:

                    public SQLite3::open(string $filename, int $flags = SQLITE3_OPEN_READWRITE | SQLITE3_OPEN_CREATE, string $encryptionKey = ""): void
                    
                    flags
                    Optional flags used to determine how to open the SQLite database. By default, open uses SQLITE3_OPEN_READWRITE | SQLITE3_OPEN_CREATE.
                    
                    SQLITE3_OPEN_READONLY: Open the database for reading only.
                    
                    SQLITE3_OPEN_READWRITE: Open the database for reading and writing.
                    
                    SQLITE3_OPEN_CREATE: Create the database if it does not exist.
                    

                    Qt does not seem to offer these flags, so you could use SQLITE3_OPEN_READWRITE without SQLITE3_OPEN_CREATE.

                    M 1 Reply Last reply 14 Feb 2024, 18:10
                    0
                    • J JonB
                      14 Feb 2024, 18:01

                      @MasterQ said in opening a non existing Sqlite Db:

                      Yes, of course, but I need the database READ/WRITE and not READONLY!!

                      Yes, it's irritating that option says it suppresses the creation but at the expense of opening existing read-only. Looks like the SQLite driver supports this:

                      public SQLite3::open(string $filename, int $flags = SQLITE3_OPEN_READWRITE | SQLITE3_OPEN_CREATE, string $encryptionKey = ""): void
                      
                      flags
                      Optional flags used to determine how to open the SQLite database. By default, open uses SQLITE3_OPEN_READWRITE | SQLITE3_OPEN_CREATE.
                      
                      SQLITE3_OPEN_READONLY: Open the database for reading only.
                      
                      SQLITE3_OPEN_READWRITE: Open the database for reading and writing.
                      
                      SQLITE3_OPEN_CREATE: Create the database if it does not exist.
                      

                      Qt does not seem to offer these flags, so you could use SQLITE3_OPEN_READWRITE without SQLITE3_OPEN_CREATE.

                      M Offline
                      M Offline
                      MasterQ
                      wrote on 14 Feb 2024, 18:10 last edited by MasterQ
                      #10

                      @JonB

                      the C++ documentation( SQL Database Drivers) says different!

                      
                      

                      Connection options:

                      The Qt SQLite plugin honors the following connection options:
                      Attribute Possible value

                      QSQLITE_BUSY_TIMEOUT Busy handler timeout in milliseconds (val <= 0: disabled), see SQLite documentation for more information

                      QSQLITE_OPEN_READONLY If set, the database is open in read-only mode which will fail if no database exists. Otherwise the database will be opened in read-write mode and created if the database file does not yet exist (default)

                      QSQLITE_OPEN_URI The given filename is interpreted as an uri, see SQLITE_OPEN_URI

                      QSQLITE_ENABLE_SHARED_CACHE If set, the database is opened in shared cache mode, otherwise in private cache mode

                      QSQLITE_ENABLE_REGEXP If set, the plugin defines a function 'regex' which can be used in queries, QRegularExpression is used for evaluation of the regex query

                      QSQLITE_NO_USE_EXTENDED_RESULT_CODES Disables the usage of the extended result code feature in SQLite (for backwards compatibility)

                      
                      

                      open command:

                      bool QSqlDatabase::open()

                      Opens the database connection using the current connection values. Returns true on success; otherwise returns false. Error information can be retrieved using lastError().

                      See also lastError(), setDatabaseName(), setUserName(), setPassword(), setHostName(), setPort(), and setConnectOptions().

                      
                      

                      bool QSqlDatabase::open(const QString &user, const QString &password)

                      This is an overloaded function.

                      Opens the database connection using the given user name and password. Returns true on success; otherwise returns false. Error information can be retrieved using the lastError() function.

                      This function does not store the password it is given. Instead, the password is passed directly to the driver for opening the connection and it is then discarded.

                      See also lastError().

                      1 Reply Last reply
                      0
                      • J Offline
                        J Offline
                        JonB
                        wrote on 14 Feb 2024, 18:15 last edited by
                        #11

                        @MasterQ said in opening a non existing Sqlite Db:

                        the C++ documentation( SQL Database Drivers) says different!

                        I know! These are the options/constants offered from Qt. That's why I showed the underlying SQLite options and said it's a "shame" they are missing what the SQLite driver supports. [If you are really lucky there might be un undocumented QSQLITE_OPEN_READWRITE Qt constant!]

                        1 Reply Last reply
                        0
                        • M MasterQ
                          14 Feb 2024, 16:03

                          Hi,

                          is it intended that the attempt to open a non existing sqlite database lead to no error?

                          auto db =QSqlDatabase::addDatabase("QSQLITE", "test");
                          db.setDatabaseName("devel.db2");
                          qDebug() <<"open:"<< db.open();
                          auto model = QSqlTableModel(nullptr, db);
                          model.setTable("Dokumente");
                          qDebug() << "select:" << model.select();
                          qDebug() << "db.lastError:" << db.lastError();
                          qDebug() << "model.lastError:"<< model.lastError();
                          qDebug() << "rowCount:" <<model.rowCount();
                          

                          in this example the database file devel.db2 does not exist. But I get no error from the db. The model is claiming that the table is not found. But why gives the open() command no error?

                          open: true
                          select: false
                          db.lastError: QSqlError("", "", "")
                          model.lastError: QSqlError("", "Unable to find table Dokumente", "")
                          rowCount: 0
                          
                          J Offline
                          J Offline
                          JuZhou He
                          wrote on 15 May 2024, 07:40 last edited by JuZhou He
                          #12

                          @MasterQ Check your build directory! Perhaps "devel. db2" is right there!

                          https://doc.qt.io/qt-6/qsqldatabase.html#setDatabaseName

                          "For the QSQLITE driver, if the database name specified does not exist, then it will create the file for you unless the QSQLITE_OPEN_READONLY option is set."

                          I ran your code,"devel. db2" was created。

                          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