Skip to content
  • Categories
  • Recent
  • Tags
  • Popular
  • Users
  • Groups
  • Search
  • Get Qt Extensions
  • Unsolved
Collapse
Brand Logo
  1. Home
  2. Qt Development
  3. General and Desktop
  4. Strange behaviour when connecting multiple times to the same SQLite database
Forum Updated to NodeBB v4.3 + New Features

Strange behaviour when connecting multiple times to the same SQLite database

Scheduled Pinned Locked Moved General and Desktop
sqlite
6 Posts 2 Posters 3.0k Views 2 Watching
  • Oldest to Newest
  • Newest to Oldest
  • Most Votes
Reply
  • Reply as topic
Log in to reply
This topic has been deleted. Only users with topic management privileges can see it.
  • L Offline
    L Offline
    Lionel
    wrote on last edited by
    #1

    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!

    1 Reply Last reply
    0
    • SGaistS Offline
      SGaistS Offline
      SGaist
      Lifetime Qt Champion
      wrote on last edited by
      #2

      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.

      Interested in AI ? www.idiap.ch
      Please read the Qt Code of Conduct - https://forum.qt.io/topic/113070/qt-code-of-conduct

      1 Reply Last reply
      0
      • L Offline
        L Offline
        Lionel
        wrote on last edited by Lionel
        #3

        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?

        1 Reply Last reply
        0
        • SGaistS Offline
          SGaistS Offline
          SGaist
          Lifetime Qt Champion
          wrote on last edited by
          #4

          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 ?

          Interested in AI ? www.idiap.ch
          Please read the Qt Code of Conduct - https://forum.qt.io/topic/113070/qt-code-of-conduct

          1 Reply Last reply
          0
          • L Offline
            L Offline
            Lionel
            wrote on last edited by
            #5

            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! :)

            1 Reply Last reply
            0
            • SGaistS Offline
              SGaistS Offline
              SGaist
              Lifetime Qt Champion
              wrote on last edited by
              #6

              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.

              Interested in AI ? www.idiap.ch
              Please read the Qt Code of Conduct - https://forum.qt.io/topic/113070/qt-code-of-conduct

              1 Reply Last reply
              0

              • Login

              • Login or register to search.
              • First post
                Last post
              0
              • Categories
              • Recent
              • Tags
              • Popular
              • Users
              • Groups
              • Search
              • Get Qt Extensions
              • Unsolved