Strange behaviour when connecting multiple times to the same SQLite database

[Lionel]

Hello,

I'm trying to use sqlite for the first time, and I have the following issue:

• I use addDatabase("QSQLITE") and setDatabaseName("db.db3") at the very begining of my main class in order to be used everywhere in my code
• In a first class, I connect to my database and execute a SELECT query that returns 8 results.
• For each result, I create another object which needs to connect to the same database (but another table) in order to initialize itself.

The issue if that doing that, my while() loop is executed only once instead of eight times.
Of course, if I remove the line ClassB *myobject = new ClassB();, my loop is indeed executed 8 times...

ClassA::function()
{
	QSqlDatabase db = QSqlDatabase::database();
	if(db.open())
	{
		QSqlQuery query;
		query.prepare("SELECT ...");
		query.exec();

		// Should return 8 results, but executed only once!
		while(query.next())
		{
			ClassB *myobject = new ClassB();
		}
	}
	db.close();
}

ClassB::ClassB()
{
	QSqlDatabase db = QSqlDatabase::database();
	if(db.open())
	{
		QSqlQuery query;
		query.prepare("SELECT ...");
		query.exec();
	}
	db.close();
}

Note that using MySQL connection, this issue does not happens!

Is there any limitation in SQLite that I'm not aware? How to solve this issue?

Thanks in advance for your help!

[SGaist]

Hi,

You might just be lucky with MySQL currently (using 5.5 ?)

Don't open the same connection multiple times. What is happening is that you close the default connection so the first time your loop runs will invalidate the query you are currently using.

[Lionel]

Hi, and thank you for your reply!

This was indeed my assumption, but it's strange that this behaviour is different with MySQL with exactly the same code (indeed I use QT 5.5).

Regarding the default connection, my main idea was to open and close my connection before and after each requests in order not to keep it opened all the time, to prevent too much simultaneous connections if many users run the application at the same time.

However it's my first application using Qt and SQLite, so I'm opened to any suggestion.

Should I keep my default connection opened all the time?
Isn't there a risk of disconnection if it no request is performed during a long period?
Is there a risk of too much simultaneous connections if each application keeps one connection opened all the time?

[SGaist]

There's was bug in the MySQL plugin that's been fixed for Qt 5.5.1 that would return true even if the connection information given was invalid so you might be hitting it.

Are you saying that you are trying to access a SQLite database from multiple process at the same time ?

[Lionel]

Hello,

Well for now I store my data in one single SQlite table which will be stored locally on the computer where I deploy my software.

However I plan in the future to store the data in one MySQL database in order to centralize all the data. And in this case the number of simultaneous connection might be a problem, and that's why I was trying to open my database connection only when I need it, and close it immediately after.

However, I read somewhere that Qt doesn't maintain the connection active all the time, even if you don't use open() and close(): as far as I understood the connection is closed once you leave the scope of the Database varuable, and it is reopened everytime that you call Database::database().
In this case indeed, no need to always close my connection manually!

Thanks for your help! :)

[SGaist]

You're welcome !

You can have multiple connections simultaneously active (beware of SQLite about that matter), the thing you have to do is to use named connections, otherwise you will end up manipulating the same connection like you were doing thus risking races like you did have.