Second use of a QSqlDatabase instance in a QSqlQuery fails.

[ecurren]

Hello all,
We are writing an application where, when the app initializes, an instance of QSqlDatabase is created, a database added via the static addDatabase function and the connection then opened. The database uses the default connection rather than naming it.

In later classes we are creating instances of QSqlQuery and running queries against the database. We use on instance of QSqlQuery for each SQL statement that we run as reuse of a query object is not practical given the design of the application.

The problem we are encountering is that the first query works, however subsequent QSqlQuery instances which are created are failing with a message similar to "The database could not be opened" even though we have not called database.close(). The failure is encountered quickly. Depending on whether we add the SQL to the QSqlQuery constructor or defer the SQL to a prepare statement, the failure happens within one of these two lines.

Thank you for your help with this.
Ed

[lgeyer]

Can you provide same example code?

I use the exact same setup as you are describing above in many projects and I never encountered such problems.

Which SQL driver do you use?
Is it possible that the first query write locks the database?
What does QSqlQuery::isActive() return for your first query?
Have you tried explicitly finishing the query using QSqlQuery::finish()?

[ecurren]

Hi Lukas,
We have tried the code with both sqlite and mysql. I will need to recompile the code and run it again to get the isActive value. We have tried finish, however I'm not sure how many other variables were in play at the time. Both are tests we will try. A snippet of code shows how we are constructing things. I was also mistaken in the error we were getting. The error about the "database could not be opened" was verbage in our application. The underlying (or at least one of the underlying) error is "Driver not loaded", which does not make sense considering we have had a successful query execution.

Thanks for your help Lukas.

============================================================================

@
class UserDataProvider
{
public:
...

private:
static QSqlDatabase *m_Database;
};

static QSqlDatabase m_Database = QSqlDatabase::addDatabase("QSQLITE", USER_DB);

UserDataProvider::UserDataProvider()
{
QString dbName = ConfigProvider::GetValue("DatabaseName");
m_Database.setDatabaseName(dbName);

if( m_Database.open() == false )
{
THROW_USERDATA_EXCEPTION_DB(2003, m_Database.lastError(), "Cannot open User database.")
}
}

int UserDataProvider::AddUserAccount(User user)
{

QString insertUserNameSql = GetSqlFromFile(":/sql/InsertUser.sql");
QSqlQuery query(insertUserNameSql, m_Database);
query.bindValue(":UserName", user.GetAccountUserName());
query.bindValue(":FirstName", user.GetFirstName());
query.bindValue(":LastName", user.GetLastName());
query.bindValue(":AccountPassword", user.CreateHashData());
query.bindValue(":Comment", user.GetComment());

if( query.exec() == false )
{
THROW_USERDATA_EXCEPTION(2004, "Cannot execute query to create new user account.")
}

return query.lastInsertId().toInt();
}

void UserDataProvider::RemoveUserAccount(User user)
{
QString removeUserSql = GetSqlFromFile(":/sql/RemoveUser.sql");
QSqlQuery query(removeUserSql, m_Database);
query.bindValue(0, USER_STATE_REMOVED);
query.bindValue(1, QDateTime::currentDateTime().toString());
query.bindValue(2, user.GetAccountUserName());
if( query.exec() == false )
{
THROW_USERDATA_EXCEPTION_DB(2004, m_Database.lastError(), "Cannot execute query to remove a user.")
}

}
@

[lgeyer]

Just a few remarks to your code.

• QSqlDatabase::addDatabase returns a QSqlDatabase, not a QSqlDatabase*.
• QSqlQuery executes the query usually immediately as soon as it is instantiated. If you like to use prepared statements try the QSqlQuery::prepare() method instead.
  @
  QSqlQuery query;
  query.prepare("INSERT INTO person (id, forename, surname) "
  "VALUES (:id, :forename, :surname)");
  query.bindValue(":id", 1001);
  query.bindValue(":forename", "Bart");
  query.bindValue(":surname", "Simpson");
  query.exec();
  @
• Be aware that you can usually execute just one SQL statement per QSqlQuery() / QSqlDatabase::exec() call.
• You might qDebug()' QSqlQuery::lastQuery() after QSqlQuery::exec() to see if it has been assembled correctly.
• Be aware that initialization of static globals is sometimes a bit tricky (see QT_GLOBAL_STATIC). However you can omit your m_Database member as the database is accessible through the static QSqlDatabase::database() method - you might call QSqlDatabase::addDatabase() in your constructor. The error you posted might be related to this.
• Be aware that not all database drivers might support QSqlQuery::lastInsertedId() according to the documentation.
• Last but not least you might embrace your code snippets with @'s - so they get formatted correctly ;-)

EDIT: Stuff added.

[ecurren]

Ahhh, that is so much easier to read when it's formated :o).

Thank you for your insights. I suspect that the problem lies somewhere in there.

I appreciate your help.