Results 1 to 10 of 10

Thread: Threads and database connection

  1. #1
    Join Date
    Jan 2006
    Posts
    185
    Thanks
    1
    Thanked 2 Times in 2 Posts
    Qt products
    Qt4
    Platforms
    Unix/X11 Windows

    Default Threads and database connection

    In my program I create several thread. Each thread holds a database connection, it means, each thread instantiates the "database" class which allows them to work with the database.

    When the first thread is "run", then all queries to the database work fine. When the second thread in "run", then all queries to the database work too, but the first thread cannot use the database anymore.

    1. Is this a correct approach of having each thread a connection to the database, or should I have a general instance of a "database" and have each thread use it ?

    2. If it is correct to have each thread with an instance of the "database", what could be wrong ?

  2. #2
    Join Date
    Jan 2006
    Location
    Warsaw, Poland
    Posts
    33,364
    Thanks
    3
    Thanked 5,012 Times in 4,791 Posts
    Qt products
    Qt3 Qt4 Qt5 Qt/Embedded
    Platforms
    Unix/X11 Windows Android Maemo/MeeGo
    Wiki edits
    10

    Default Re: Threads and database connection

    Can we see some code?

  3. #3
    Join Date
    Jan 2006
    Posts
    185
    Thanks
    1
    Thanked 2 Times in 2 Posts
    Qt products
    Qt4
    Platforms
    Unix/X11 Windows

    Default Re: Threads and database connection

    The class that initializes the "database" class

    Qt Code:
    1. void Client::run(){
    2. cout << "Client thread has enter the thread execution\n";
    3. message = new Message(this);
    4. database = new Database(this);
    5. message->knowDatabase(database);
    6. tcpSocket = new QTcpSocket();
    7. if(!tcpSocket->setSocketDescriptor(_socketDescriptor))
    8. cout << "Error - setting socket descriptor\n";
    9. connect(tcpSocket, SIGNAL(readyRead()), this, SLOT(readMessage()));
    10. connect(tcpSocket, SIGNAL(disconnected()), this, SLOT(clientDisconnected()));
    11. exec();
    12. }
    To copy to clipboard, switch view to plain text mode 


    The database class:

    Qt Code:
    1. Database::Database(Client *client){
    2. cout << "Initializing database connection\n";
    3. _client = client;
    4. db = QSqlDatabase::addDatabase("QMYSQL3");
    5. db.setHostName("localhost");
    6. db.setDatabaseName("infospeed");
    7. db.setUserName("santiago");
    8. db.setPassword("santipass");
    9. }
    10.  
    11. void Database::connectToDatabase(){
    12. bool ok = db.open();
    13. if(ok)
    14. cout << "Database connection successful\n";
    15. else
    16. cout << "Error connecting to the database\n";
    17. }
    18.  
    19.  
    20. QString Database::getName(QString userId, QString password){
    21. connectToDatabase();
    22. QString myQuery("SELECT name, lastname FROM user WHERE id=\"");
    23. myQuery += userId;
    24. myQuery += "\" and password=\"";
    25. myQuery += password;
    26. myQuery += "\"";
    27. QSqlQuery query(myQuery, db);
    28. query.next();
    29. QString name = query.value(0).toString();
    30. QString lastName = query.value(1).toString();
    31. QString userName = name + " " + lastName;
    32. cout << "User name " << userName.toStdString() << endl;
    33. disconnectFromDatabase();
    34. return userName;
    35. }
    To copy to clipboard, switch view to plain text mode 


    I hope this helps to solve the issue !!!

  4. #4
    Join Date
    Jan 2006
    Location
    Warsaw, Poland
    Posts
    33,364
    Thanks
    3
    Thanked 5,012 Times in 4,791 Posts
    Qt products
    Qt3 Qt4 Qt5 Qt/Embedded
    Platforms
    Unix/X11 Windows Android Maemo/MeeGo
    Wiki edits
    10

    Default Re: Threads and database connection

    Can't you use a single connection to the database for all threads?

  5. #5
    Join Date
    Jan 2006
    Posts
    185
    Thanks
    1
    Thanked 2 Times in 2 Posts
    Qt products
    Qt4
    Platforms
    Unix/X11 Windows

    Default Re: Threads and database connection

    yes, I can create just one instance for the database and use it from all threads.

    Is this a better approach than using one connection per thread ?

  6. #6
    Join Date
    Jan 2006
    Location
    Warsaw, Poland
    Posts
    33,364
    Thanks
    3
    Thanked 5,012 Times in 4,791 Posts
    Qt products
    Qt3 Qt4 Qt5 Qt/Embedded
    Platforms
    Unix/X11 Windows Android Maemo/MeeGo
    Wiki edits
    10

    Default Re: Threads and database connection

    No (the docs even specify you can't do it), but if it works then why not?

    What is the content of disconnectFromDatabase()?

    And just to make sure - are we talking Qt4 here?

  7. #7
    Join Date
    Jan 2006
    Posts
    128
    Thanked 28 Times in 27 Posts
    Qt products
    Qt4
    Platforms
    Unix/X11 Windows

    Default Re: Threads and database connection

    Quote Originally Posted by probine View Post
    yes, I can create just one instance for the database and use it from all threads.

    Is this a better approach than using one connection per thread ?
    Well, actually you are using only one instance from all threads. Since you are sharing the connection.


    If you would like to use one connection per thread you have to use a construct such as this (pseudocode, not compiled):
    Qt Code:
    1. QSqlDatabase Database::getDatabase(){
    2. QString connectionName = QLaint1String("infospeed@");
    3. connectionName += QString::number(QThread::currentThread());
    4. if (QSqlDatabase::contains(connectionName, true)) {
    5. return QSqlDatabase::database(connectionName);
    6. } else {
    7. QSqlDatabase db = QSqlDatabase::addDatabase(QLatin1String("QMYSQL3"), connectionName);
    8. db.setHostName(QLatin1String("localhost"));
    9. db.setDatabaseName(QLatin1String("infospeed"));
    10. db.setUserName(QLatin1String("santiago"));
    11. db.setPassword("santipass");
    12. bool ok = db.open();
    13. if(ok)
    14. cout << "Database connection successful\n";
    15. else
    16. cout << "Error connecting to the database\n";
    17. return db;
    18. }
    19. }
    To copy to clipboard, switch view to plain text mode 

  8. The following user says thank you to camel for this useful post:

    tpf80 (14th March 2007)

  9. #8
    Join Date
    Jan 2006
    Posts
    185
    Thanks
    1
    Thanked 2 Times in 2 Posts
    Qt products
    Qt4
    Platforms
    Unix/X11 Windows

    Default Re: Threads and database connection

    I am not quite sure if I understood.

    I am not using only one instance...

    I am not sharing the connection...

    Each thread holds its own connection to the database... this connection is initialized in the "run" part of each thread, so the database connection should be unique for each thread.

  10. #9
    Join Date
    Jan 2006
    Posts
    128
    Thanked 28 Times in 27 Posts
    Qt products
    Qt4
    Platforms
    Unix/X11 Windows

    Default Re: Threads and database connection

    The problem as I see it is in this line
    Qt Code:
    1. db = QSqlDatabase::addDatabase("QMYSQL3");
    To copy to clipboard, switch view to plain text mode 
    addDatabase is a static function, which implies that is does not care in which Database object it was called; and you do not specify a specific second argument, the connectionName. The defaultvalue for this argument is "QLatin1String( defaultConnection )".

    This means all your database connections will have the same connectionName. Keep this in mind when you read the following warning, copied straigt from the docs:
    Warning: If you add a database with the same name as an existing database, the new database will replace the old one. This will happen automatically if you call this function more than once without specifying connectionName.
    The way I read that is: as soon as your second thread initializes its database object, it will create a new default connection and delete the old one. The SqlDatabase object in the first thread will point to the old now stale connection and should thus cease to work. Which is rather consistent with your symptoms.

    Changing the creation of database objects to the above mentioned method (meaning assuring that each connection/thread gets their own connectionName) should work. (Hopefully, not fully tested as I said ;-)

  11. The following user says thank you to camel for this useful post:

    SubV (14th November 2007)

  12. #10
    Join Date
    Aug 2013
    Posts
    2
    Qt products
    Qt3
    Platforms
    MacOS X

    Default Re: Threads and database connection

    You would typically handle this situation using some sort of database connection pool, which can manage the lifetime of your database connections for you.

Similar Threads

  1. Querying within Threads
    By bera82 in forum Qt Programming
    Replies: 11
    Last Post: 12th March 2008, 01:08

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Digia, Qt and their respective logos are trademarks of Digia Plc in Finland and/or other countries worldwide.