How to correctly close a database connection

[anoraxis]

Hi, in my app I use the same database for several objects. Each one creates a new connection to the database on the constructor and in the other functions the databse is opened, a query is executed and the database is closed. On the destructor of the class I use the function QSqlDatabase::removeDatabase("connectionName");. In other words:

// I don't have this specified but this class is like abstract, I never instantiates it.
class DAO 
{
	protected:
		QSqlDatabase db;
        private:
		QString connName;
	public:
		DAO(QString dbName, QString connName)
		{
			this->connName = connName;
			this->db = QSqlDatabase::addDatabase("QSQLITE", this->connName);
			this->db.setDatabaseName(dbName);
		}
 
		~DAO ( )
		{
                       /* when this is executed I receive the warning "QSqlDatabasePrivate::removeDatabase: connection 'this->connName' is still in use, all queries will cease to work" the this->connName is replaced by its value */
			QSqlDatabase::removeDatabase(this->connName);
		}
 
};
 
// this is the class that I use
class SomethingDAO : public DAO
{
	SomethingDAO(QString dbName):DAO(dbName, "something"){};
 
	QString getSomethingWithId(int id)
	{
		this->db.open();
		QSqlQuery q(db);
		q.exec("...") //the query
		this->db.close();
 
		// ... stuff with the query
	}
 
};

// I don't have this specified but this class is like abstract, I never instantiates it.
class DAO 
{
	protected:
		QSqlDatabase db;
        private:
		QString connName;
	public:
		DAO(QString dbName, QString connName)
		{
			this->connName = connName;
			this->db = QSqlDatabase::addDatabase("QSQLITE", this->connName);
			this->db.setDatabaseName(dbName);
		}

		~DAO ( )
		{
                       /* when this is executed I receive the warning "QSqlDatabasePrivate::removeDatabase: connection 'this->connName' is still in use, all queries will cease to work" the this->connName is replaced by its value */
			QSqlDatabase::removeDatabase(this->connName);
		}

};

// this is the class that I use
class SomethingDAO : public DAO
{
	SomethingDAO(QString dbName):DAO(dbName, "something"){};

	QString getSomethingWithId(int id)
	{
		this->db.open();
		QSqlQuery q(db);
		q.exec("...") //the query
		this->db.close();

		// ... stuff with the query
	}

};

To copy to clipboard, switch view to plain text mode 

When I use one of my DAO objetcs I do it like:

SomethingDAO*sDAO = new SomethingDAO("data.db3");
QString a = sDAO->getSomethingWithId(1);
delete sDAO;

SomethingDAO*sDAO = new SomethingDAO("data.db3");
QString a = sDAO->getSomethingWithId(1);
delete sDAO;

To copy to clipboard, switch view to plain text mode 

I have some other classes like SomethingDAO (Something1DAO, Something2DAO ...), each one especialized on the queries related to a specific class. The problem arises because Something1DAO uses Something2DAO and this uses Something3DAO. In a moment a get by db.lastError().text() the message "Driver not loaded" when trying to open the database. If I comment one of the "create dao object - query - delete dao object" sections this problem is solved but obviously I don't load all the data that I need. Is there a maximum number of connections allowed?

Initially all my connections had the same name, and in some moment I received the warning of duplicate connection, the database dosn't open, the "driver not loaded" error appears and the program crash, so, for each especialized class the connection name is now "something1" or "something2". With this, the problem is solved but I expect that in some moment the problem appears again if I try to make more connections.

So, how can I delete all the references to the db connection because it seems that using only QSqlDatabase::removeDatabase(this->connName) is not enought? (in the documentation says that all the objects using the database must be out of scope and I think this is not the problem except by this->db but this is closed).

Thanks for tips and ideas...

[mcosta]

Using a QSqlDatabase member object isn't so correct.

Use QSqlDatabase::database each time you need it using connName

// I don't have this specified but this class is like abstract, I never instantiates it.
class DAO 
{
        protected:
		QString connName;
	public:
		DAO(QString dbName, QString connName)
		{
			this->connName = connName;
			QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE", this->connName);
			db.setDatabaseName(dbName);
		}
 
		~DAO ( )
		{
                       /* when this is executed I receive the warning "QSqlDatabasePrivate::removeDatabase: connection 'this->connName' is still in use, all queries will cease to work" the this->connName is replaced by its value */
                        {
                            QSqlDatabase db = QSqlDatabase::database(this->connName, false);
			    if (db.isOpen())
                                db.close();
                        }
			QSqlDatabase::removeDatabase(this->connName);
		}
 
};
 
// this is the class that I use
class SomethingDAO : public DAO
{
	SomethingDAO(QString dbName):DAO(dbName, "something"){};
 
	QString getSomethingWithId(int id)
	{
                QSqlDatabase db = QSqlDatabase::database (this->connName); // Open Connection
		QSqlQuery q(db);
		q.exec("...") //the query
		this->db.close();
 
		// ... stuff with the query
	}
 
};

// I don't have this specified but this class is like abstract, I never instantiates it.
class DAO 
{
        protected:
		QString connName;
	public:
		DAO(QString dbName, QString connName)
		{
			this->connName = connName;
			QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE", this->connName);
			db.setDatabaseName(dbName);
		}

		~DAO ( )
		{
                       /* when this is executed I receive the warning "QSqlDatabasePrivate::removeDatabase: connection 'this->connName' is still in use, all queries will cease to work" the this->connName is replaced by its value */
                        {
                            QSqlDatabase db = QSqlDatabase::database(this->connName, false);
			    if (db.isOpen())
                                db.close();
                        }
			QSqlDatabase::removeDatabase(this->connName);
		}

};

// this is the class that I use
class SomethingDAO : public DAO
{
	SomethingDAO(QString dbName):DAO(dbName, "something"){};

	QString getSomethingWithId(int id)
	{
                QSqlDatabase db = QSqlDatabase::database (this->connName); // Open Connection
		QSqlQuery q(db);
		q.exec("...") //the query
		this->db.close();

		// ... stuff with the query
	}

};

To copy to clipboard, switch view to plain text mode 

[anoraxis]

Hi and thank you. Your solution works well but I don't want to create a QSqlDatabase every time I need to make a query so I solved the problem using another solution.

Instead of removing the QSqlDatabase instance from my class I modified the destructor of the class. Now it looks like

DAO::~DAO()
{
    this->db = QSqlDatabase();
    QSqlDatabase::removeDatabase(this->connName);
}

DAO::~DAO()
{
    this->db = QSqlDatabase();
    QSqlDatabase::removeDatabase(this->connName);
}

To copy to clipboard, switch view to plain text mode 

On the firts instruction of the destructor, the reference to my database is destroyed so now I can remove the database with the removeDatabase function. Tested and working perfect: all the warnings gone!

[mcosta]

QSqlDatabase uses Implicit data sharing in order to minimize copying.

Using my version (suggested by Qt) you copy very small amount of data