QSqlDatabase retrieve connection in different form

[avpro]

hi,

according to manual (QSqlDatabase::addDatabase()):

and subsequent calls to database() without the connection name argument will return the default connection. If a connectionName is provided here, use database(connectionName) to retrieve the connection.

if i would like to retrieve the database connection I need to use the

database()

i'm not sure if i just need to use

database()
or

QSqlDatabase::database() - which doesn't exists

or what else?

could you please give me an example please? I don't have a name for my connection defined.
thanks.

[ChrisW67]

QSqlDatabase db = QSqlDatabase::database();
which does exist in exactly the same place that QSqlDatabase::addDatabase() comes from. Make sure you have "QT += sql" in your PRO file

[avpro]

which does exist in exactly the same place that QSqlDatabase::addDatabase() comes from. Make sure you have "QT += sql" in your PRO file

hi,

I have += sql in my project file.

I would like to retrieve the database connection in a different file, than where it was initially declared. I will try this code to see if works.

edit: I tried this

QSqlDatabase db = QSqlDatabase::database();
db.open();

the app. runs, but the connection is not open. I receive a message in the Application Output.
QSqlQuery::prepare: database not open

any idea what's not correct? please keep in mind that i declared the db connection in one .cpp file and I would like to retrieve the connection in a different .cpp file.
cheers.

[ChrisW67]

Yes, you have not configured the database connection correctly and it is failing to open.
QSqlDatabase::open() and QSqlDatabase::lastError() return a value for a reason.

There are plenty of examples in the reference, overview, and example documentation.

[avpro]

hi,

thanks for guidelines, but I'm still struggling to find a solution for my query. maybe you could help.

my database connection is defined in 1.cpp; everything is fine with the definition is working, tested.
in 2.cpp, which is a form launched from a button of 1.cpp, I would like to re-open the initial connection and execute a query.


QSqlDatabase customer;
customer.open();
QSqlQuery qry1;
qr1.prepare(...);
...
...

customer.close();




on the Application output i receive:

QSqlQuery::prepare: database not open

what's wrong? why i can't open the connection?

[Lesiok]

Just show real code where connection is defined.

[ChrisW67]

You are not initialising customer using QSqlDatabase::database(). See post #2
You are not checking the open(), prepare() and probably other return values for useful clues. See post #4
You are not associating qry1 with the database object called customer. See the other QSqlQuery constructor.
You are executing a different query called qr1 anyway. This is probably the result of retyping/paraphrasing your code rather than copy and pasting it.

my database connection is defined in 1.cpp; everything is fine with the definition is working, tested.
For this statement to be true you must have the correct arrangement of code 1.cpp... Why not just adapt that?



QSqlDatabase customer = QSqlDatabase::database();
if (customer.open()) {
QSqlQuery qry(customer);
if (qry.prepare(" ... ") && qry.exec()) {
// do stuff with query results
}
else {
// report using QSqlQuery::lastError() information
}
}
else {
// report using QSqlDatabase::lastError() information
}

[avpro]

Let’s start again with real code:
As i already mentioned, my database connection is defined in 1.cpp; everything is fine with the definition is working, tested.

1.cpp code here:

QSqlDatabase customer = QSqlDatabase::addDatabase("QMYSQL");
customer.setHostName("localhost");
customer.setDatabaseName("test");
customer.setUserName("root");
customer.setPassword("pass");
customer.open();


in 2.cpp, which is a form launched from a button of 1.cpp, I would like to re-open the initial connection and execute a query.

QSqlDatabase customer;
customer.open();
QSqlQuery qry1;
qr1.prepare(...);
...
customer.close();

In this way, it worked to recall the connection and the query is executed in 2.cpp. I didn’t check and report the lastError yet, but I will integrate it, too.

After the application is running and if I run the query for two times or more, in the Application Output I receive:

QSqlDatabasePrivate::addDatabase: duplicate connection name 'qt_sql_default_connection', old connection removed.

Could you please let me know if this is normal, and if not how could I correct it?
thanks for your input. every little progress makes life easier.

[ChrisW67]

No, it is not normal or correct. Read my previous post. I show you exactly how to code using the database.

[Lesiok]

If you opens db connection in 1.cpp file in 2.cpp file You use it like this :
QSqlDatabase customer = QSqlDatabase::database();
QSqlQuery qry1;
qr1.prepare(...);
...
customer.close();

Reading the documentation does not hurt. From QSqlDatabase::QSqlDatabase doc : Creates an empty, invalid QSqlDatabase object. Use addDatabase(), removeDatabase(), and database() to get valid QSqlDatabase objects.

[avpro]

thank you all for your input. I will read the referenced documentation and return for an opinion.

[avpro]

Hi ChrisW67, Hi Lesiok,

I think I understood the definitions of the classes. I wrote the following code.
Just for info, I'm running an Win XP 32 bit, MySQL Workbench 5.2.47, Qt 5.0.2


on 1.cpp:

void 1::on_addUser_triggered()
{
QSqlDatabase addUser = QSqlDatabase::addDatabase("QMYSQL", "adConn");
addUser.setHostName("localhost");
addUser.setDatabaseName("test");
addUser.setUserName("root");
addUser.setPassword("pass");
usernew *nU = new usernew;
nU ->show();

on 2.cpp

void 2::on_buttonBox_accepted()
{
QSqlDatabase addUser = QSqlDatabase::database("adConn");

if (addUser.open())
{
qDebug () << "opened"; // only for testing
qDebug () << addUser.connectionName(); // only for testing

QSqlQuery q1;
q1.prepare("INSERT INTO test.users (username, pass, name, email)VALUES (:username, :pass, :name, :email)");
q1.bindValue(":username", ui->lnUsername->text());
q1.bindValue(":pass", ui->lnPassword->text());
q1.bindValue(":name", ui->lnName->text());
q1.bindValue(":email", ui->lnEmail->text());
q1.exec();

if (q1.exec())
{
qDebug() << "Query executed with success"; // only for testing
}
else
{
qDebug() << "Query Error: " << q1.lastError().text(); // only for testing
qDebug () << addUser.drivers(); // only for testing
}


}else
{
qDebug() << "Database Error:" << addUser.lastError(); // only for testing
}
addUser.close();
addUser.removeDatabase("adConn");
}


when I run the app, I receive these messeges:

opened - this means the connection is opened.
"adConn" - the connection name is confirmed
QSqlQuery::prepare: database not open - Why I get this error?
Query Error: "Driver not loaded Driver not loaded" - Why I get this error?
("QSQLITE", "QMYSQL", "QMYSQL3", "QODBC", "QODBC3") - due to the above error I checked the drivers. MySql drivers are loaded. I run the tutorial basicQuery from here (https://www.youtube.com/watch?v=LiHHm7cd5Bs&list=SP2D1942A4688E9D63&index=52) and the database is opened and I can insert new values
QSqlDatabasePrivate::removeDatabase: connection 'adUserConn' is still in use, all queries will cease to work. - Why I get this error? is the ::removeDatabase declaration not used correctly?

I would appreciate if I could receive some guidelines on the below questions:
1. QSqlQuery::prepare: database not open - Why I get this error?
2. Query Error: "Driver not loaded Driver not loaded" - Why I get this error?
3. QSqlDatabasePrivate::removeDatabase: connection 'adUserConn' is still in use, all queries will cease to work. - Why I get this error?

thanks for your support.

[ChrisW67]

You are receiving the first two error messages because there is no default database connection.
First listing line 3: you are creating a named connection to a MySql database.
Second listing line 10: you are creating a QSqlQuery object associated with the default connection not the named connection you created earlier. Take a closer look at the QSqlQuery constructor docs.

In general you call addDatabase() only once per-connection per-program-run and use QSqlDatabase::database() to access existing connections when needed. If you call it multiple times, in your case every time a button is pushed, you get your last warning message because you are trashing a connection that other objects may still be related to.

[avpro]

you are right, the query should be used with the database connection. I spent a few hours to find in http://qt-project.org/doc/qt-4.8/qsqlquery.html where this was mentioned.

maybe to help other in the future in line 10 of 2.cpp the query should be called using the database connection name:


QSqlQuery q1(yourDatabase.database("yourDatabaseConnectionName"));


one thing remained unsolved:
QSqlDatabasePrivate::removeDatabase: connection 'adUserConn' is still in use, all queries will cease to work. - How could I close the database connection to get rid of this error and to avoid any leaks?

i checked the documentation here (http://qt-project.org/doc/qt-4.8/qsqldatabase.html#removeDatabase), but I still don't understand.

if i write the


addUser.removeDatabase("adConn");

ouside of my void newUser, I will get other errors. it should be outside of the "if", but the error is still there. any input for this?


you are trashing a connection that other objects may still be related to. is this "trashing" not the correct way to do it?
how to close it and bin it?

thanks.

[ChrisW67]

No, you are still missing the point. The QSqlQuery at line 10 should be created thus:


QSqlQuery q1(addConn);

using the database reference you retrieved at line three. Or, you can create the database connection (i.e addDatabase()) in the first place without a name making it the default connection and all subsequent QSqlQuery objects will associate with that by default.

When you call addDatabase() multiple times you are effectively closing the existing connection, removing its definition (i.e. QDatabase::removeDatabase()) from underneath any object that may still hold a reference to it (i.e. any existing QSqlQuery associated with the connection), and recreating a potential completely unrelated database connection in its place. That is what the warning is about. This is not the same thing as QDatabase::open()/QDatabase::close() on a connection that is already defined, you can do this as much as makes sense. It is unusual to completely redefine a database connection after its initial setup. Generally a program will contain a single addDatabase() call during startup and no removeDatabase() calls, leaving clean up for program exit.

[avpro]

I'm not sure if I understand.


using the database reference you retrieved at line three.

that's what I'm trying to do, just to use the recall of database connection.


Or, you can create the database connection (i.e addDatabase()) in the first place without a name making it the default connection and all subsequent QSqlQuery objects will associate with that by default.
I'm aware about this option. i think using a connection name is safer. it leaves no space for potential error.


When you call addDatabase() multiple times you ...
I'm not calling addDatabase multiple times. Is just once in 1.cpp line 3.


No, you are still missing the point. The QSqlQuery at line 10 should be created thus:
Qt Code:
QSqlQuery q1(addConn);

I tried and is not recognizing the addConn. error: adConn was not declared in this scope.

i tried
QSqlQuery q1("addConn")
and gives me the below error:


QSqlQuery::exec: database not open
QSqlQuery::prepare: database not open
Query Error: "Driver not loaded Driver not loaded"
QSqlDatabasePrivate::removeDatabase: connection 'adUserConn' is still in use, all queries will cease to work.

the only solution working is the one described in post 14.

i will remove the
QSqlDatabase::removeDatabase("addConn");, close the database connection with
addUser.database("addConn").close();

and
leaving clean up for program exit.

I wait and see if other errors strike against the current one. thanks for your support.

[ChrisW67]

I'm not calling addDatabase multiple times. Is just once in 1.cpp line 3.

Yes you are, once every time on_addUser_triggered() is called.

I tried and is not recognizing the addConn. error: adConn was not declared in this scope.

Sorry, my bad. I meant addUser, the reference you retrieve at line 3 of the second listing.


i tried
QSqlQuery q1("addConn")
and gives me the below error:

The QSqlQuery constructor that takes a QString argument is expecting an SQL query and optionally a reference to a database connection, not the name of a database connection. It is interpreting "adConn" as a query on the default database connection. There is no default database connection, hence the error.



i will remove the
QSqlDatabase::removeDatabase("addConn");,

Good this will remove some of the run time warnings.

close the database connection with
addUser.database("addConn").close();

You can close the connection to the database with:


addUser.close();

Just as you did in Listing 2. This does not remove the connection definition known by the name "addConn", which remains available for use. The connection will be reopened by default the next time you do something like this:


QSqlDatabase db = QSqlDatabase::database("addConn");

anywhere in your program. You can suppress the automatic open() but that is not broadly useful.

In short:

addDatabase() defines the existence of a database of a certain type to the program and loads a plugin to suit.
removeDatabase() completely closes and forgets a previously defined database.
database() retrieves a reference to a database connection so you can use it
open() opens the connection to a defined database
close() close the connection to a defined database.