Multiple connections to in-memory QSqlDatabase

[bepaald]

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!

[bepaald]

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!

[JonB]

@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?

[Christian Ehrlicher]

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

[JonB]

@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...

[bepaald]

@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.

[bepaald]

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!