QSqlite, multiple connections to in memory database.

[adzajac]

Hi.

I'd like to ask you people is there any possibility to create more than one concurrent connection to the SQLite database which is located in memory.
I want to read the database from several threads (different tables). There is no problem to do this on file based database, but I’ve some problems wit memory based one.

Could somebody can give me a hint how to do this.

Regards

[toutarrive]

Can you describe the problem you come across?

[adzajac]

Hi.

I'll try:

We've a connection to the daytabase in memory:

QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE", "connection1");
    db.setDatabaseName(":memory:");
    if (!db.open()) {          
        return false;
    }

QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE", "connection1");
    db.setDatabaseName(":memory:");
    if (!db.open()) {          
        return false;
    }

To copy to clipboard, switch view to plain text mode 

The question is how to create a secod connection to the same database which is named ":memory:".

In the next thread we're doing :

QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE", "connection2");
    db.setDatabaseName(":memory:");
    if (!db.open()) {          
        return false;
    }

QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE", "connection2");
    db.setDatabaseName(":memory:");
    if (!db.open()) {          
        return false;
    }

To copy to clipboard, switch view to plain text mode 

The connection is opened but we're receiving the new instance of empty database.

If the database is working on file i.e.

in one thread 
db.setDatabaseName("myFile.db");
 
on second 
db.setDatabaseName("myFile.db");
 the problem disapears and everything seams to be OK.

in one thread 
db.setDatabaseName("myFile.db");

on second 
db.setDatabaseName("myFile.db");
 the problem disapears and everything seams to be OK.

To copy to clipboard, switch view to plain text mode 

we've two paralel connections.

The question is how to acheive the same result in memory database. Is it possible?

[toutarrive]

You don't need to open a second connection. The sole connection can be used from any thread.

[toutarrive]

Why do you need two transactions in parallel? In general only one
connection can have a transaction open on a database at any time.
Even with two connections, you can't have two active transactions. The second will stall waiting
for the first to complete.

[adzajac]

Hi in fact we've tested connection fo file based database with the SQLite driver. File consists of several tables. One thread was writting to tabe A and the second one was reading from table B. It was working quite OK and in paralel.
Two transactions were executed at the same moment.
Now we're looking to have the same behaviour with the memory based database.

[toutarrive]

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.

[adzajac]

We've thought that i.e. memory mapped file in shared memory segment could be a solution, but the problem is thet the file could be expanded during the write transactions.
And in Qt4 mechanism for mamory shared files is not existing anymore.

[adzajac]

Hi everybdy.

We did it.
We've made our own driver to SQLite. In fact we've used sqlite3_open_v2 methd instead original sqlite3_open_v16 that is implemented in Qt default driver with flag set to NO MUTEXES. At run time we've not serialized acces to in memory database.

If fact the most mportat thing is to spawn only one connection per thread, but it's wrking in paralel.

[toutarrive]

Would like to have a look. Do your mind showing us some code...