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

Multiple connections to in-memory QSqlDatabase



  • Hi! I'm trying to open a second connection to a sql database that is only in memory. When trying to use QSqlDatabase::cloneDatabase(), I get no errors, but an empty database. Running the following:

    #include <QtSql>
    #include <QStringList>
    #include <QDebug>
    
    int main()
    {
      // create sql database
      QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
      db.setDatabaseName(":memory:"); /* *** */
      qDebug() << "Opening original database: " << db.open();
    
      // add some tables
      QSqlQuery query;
      query.exec("CREATE TABLE table1 (_id INTEGER PRIMARY KEY)");
      query.exec("CREATE TABLE table2 (_id INTEGER PRIMARY KEY)");
    
      qDebug() << "Tables in original: " << db.tables();
    
      // try to clone connection...
      QSqlDatabase clone = QSqlDatabase::cloneDatabase(db, "clone");
      qDebug() << "Opening clone: " << clone.open();
      qDebug() << "Tables in clone: " << clone.tables();
    
      return 0;
    }
    

    Output is

    [~/sqltest] $ ./sqltest 
    Opening original database:  true
    Tables in original:  ("table1", "table2")
    Opening clone:  true
    Tables in clone:  ()
    [~/sqltest] $
    

    When changing the setDatabaseName() call to any temporary filename, output is as expected:

    [~/sqltest] $ ./sqltest 
    Opening original database:  true
    Tables in original:  ("table1", "table2")
    Opening clone:  true
    Tables in clone:  ("table1", "table2")
    [~/sqltest] $ 
    

    Am I doing something stupid? Or is this just not possible? Any way around it?

    Thanks!



  • Nevermind, I got it. To enable an URI database name, I also had to set the QSQLITE_OPEN_URI option. So calling setConnectOptions("QSQLITE_OPEN_URI;QSQLITE_ENABLE_SHARED_CACHE") and setDatabaseName("file::memory:") everything seems to behave as I hoped (at least in this minimal example).

    Thanks!



  • @bepaald

    I'm trying to open a second connection to a sql database that is only in memory.

    I read elsewhere hints like

    As far as I know , the scenario of having different connections in each thread to the same physical file
    is not applicable to in-memory database because you can not open several connections to the same in-memory
    database.

    The suggestion is: why do you need to do this?


  • Lifetime Qt Champion

    According to https://www.sqlite.org/inmemorydb.html the database must be opened in 'shared mode'.



  • @JonB
    From @Christian-Ehrlicher 's link I see:

    hence is very little difference between a pure in-memory database created by ":memory:" and a temporary database created by an empty filename

    You could try that and see if it behaves more like you want when you used your own "temporary filenames", which you say worked. Or it might exhibit the same problem...



  • @Christian-Ehrlicher

    Thanks! I had tried to call db.setConnectOptions("QSQLITE_ENABLE_SHARED_CACHE") before, but since it had no effect, I removed it to keep the posted code as compact as possible.

    According to the page you linked 'In-memory databases are allowed to use shared cache if they are opened using a URI filename.', that is, they must be opened with "file::memory:" instead of just ":memory:". However, when I try to use this URI filename by calling setDataBaseName("file::memory:") just creates an on-disk database called "file::memory:", so no luck.

    @JonB

    In my application a database is created and filled, much like in the example code. Later in the program, I would like to run queries on this database from another thread. For this I need to open a new connection to this db from that thread, since existing connections can only be used from the thread they were opened in. I think there are probably some other workarounds for this problem, but they will all take a lot more effort than if this just worked. The database does not need to be saved after the program exits and might in fact contain sensitive information, so I would like to avoid touching the disk if possible.
    Using setDataBaseName("") seems to behave the same as using ":memory:", so still no luck.



  • Nevermind, I got it. To enable an URI database name, I also had to set the QSQLITE_OPEN_URI option. So calling setConnectOptions("QSQLITE_OPEN_URI;QSQLITE_ENABLE_SHARED_CACHE") and setDatabaseName("file::memory:") everything seems to behave as I hoped (at least in this minimal example).

    Thanks!


Log in to reply