Accessing in memory SQLite databse

abhic

I have integrated sqlite3.* files in our application and am currently using it to create in memory database . All this is being done outside Qt.
Now within same process, I want to access that in memory database to display some data in Qt GUI. Is this kind of flow supported/recommended?

Christian Ehrlicher

@abhic said in Accessing in memory SQLite databse:

Is this kind of flow supported/recommended?

If sqlite itself supports opening an in-memory database twice within one process then I don't see why it should not work. But that's out of control of Qt.

SGaist

Hi and welcome to devnet,

Please go through the SQLite documentation about that matter. There several nuances that come with its handling.

abhic

Thanks everyone for your responses. I will give it a try

abhic

@SGaist I have tried going through it, I am new to this. It seems that usage of shared cache is discouraged. But so far it seems using shared cache is only way to access same in memory db from 2 places within one process

I have not been able to share in memory database even with this.

QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
db.setConnectOptions("QSQLITE_OPEN_URI;QSQLITE_ENABLE_SHARED_CACHE");
db.setDatabaseName("file::memory:");
db.open();

and outside Qt
std::string dbFileName = "file::memory:?cache=shared";
bool readOnly = false;
const int roFlag = readOnly ? SQLITE_OPEN_READONLY : (SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE);
const int status = sqlite3_open_v2(dbFileName.c_str(), &_db, (roFlag|SQLITE_OPEN_FULLMUTEX|SQLITE_OPEN_URI|SQLITE_OPEN_SHAREDCACHE), NULL);

abhic

Is there something missing here? Also is it possible in Qt to use an sqlite3* pointer to existing connection opened outside Qt?

Christian Ehrlicher

@abhic said in Accessing in memory SQLite databse:

Also is it possible in Qt to use an sqlite3* pointer to existing connection opened outside Qt?

Yes, but what should this help - https://doc.qt.io/qt-6/qsqldriver.html#handle

Use a file instead in-memory.

abhic

@Christian-Ehrlicher Thanks for your response.
File based DB is resulting in high runtime. I am exploring in memory DB to get around that. In memory DB is populated outside Qt using sqlite C APIs. Now I need to access that in memory DB inside Qt also in read only mode. I have not been able to do that so far. Below code is also not working

QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
db.setConnectOptions("QSQLITE_OPEN_URI;QSQLITE_ENABLE_SHARED_CACHE");
db.setDatabaseName("file::memory:");
db.open();

and outside Qt
std::string dbFileName = "file::memory:?cache=shared";
bool readOnly = false;
const int roFlag = readOnly ? SQLITE_OPEN_READONLY : (SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE);
const int status = sqlite3_open_v2(dbFileName.c_str(), &_db, (roFlag|SQLITE_OPEN_FULLMUTEX|SQLITE_OPEN_URI|SQLITE_OPEN_SHAREDCACHE), NULL);

Christian Ehrlicher

Simply test it with two c++ only ones. This is imo nothing Qt specific. Also I don't see anything in the Sqlite code which could allow your requirement.
Use a proper db if you want proper db functionality.

ChrisW67

@abhic Some possibilities:

Sqlite was built with this SQLITE_OMIT_SHARED_CACHE option. Not sure: not looked at the relevant source.
Maybe the shared in-memory database needs to be accessed by the same Sqlite library (I do not know how the rendezvous works). At the moment the Qt plugin is probably using built-in code, while the "outside Qt" code may be using either a system library or its own built-in Sqlite.