Load a new Sqlite database problem

[beware]

Hi,

I've a problem with SQLITE :

My program use a Sqlite database which is set like this :

m_db = QSqlDatabase::addDatabase( "QSQLITE" );
m_db.setDatabaseName( "base1.bdd" );

m_db = QSqlDatabase::addDatabase( "QSQLITE" );
m_db.setDatabaseName( "base1.bdd" );

To copy to clipboard, switch view to plain text mode 

m_db is create like this in the header file :

private :
   QSqlDatabase m_db;

private :
   QSqlDatabase m_db;

To copy to clipboard, switch view to plain text mode 

Then in a second function (in the same class), i execute SQL request :

if ( m_db.open() )
   s_req->data = m_db.exec( "SELECT * FROM tab1 ORDER BY nom COLLATE NOCASE;" );

if ( m_db.open() )
   s_req->data = m_db.exec( "SELECT * FROM tab1 ORDER BY nom COLLATE NOCASE;" );

To copy to clipboard, switch view to plain text mode 

Until here, i've no problem with Sqlite.

But in a third function, i want to load (and use) a new database (without restart program).

To do this, i do :

if ( m_db.isOpen() )
   m_db.close();
 
m_db.setDatabaseName( "base2.bdd" );

if ( m_db.isOpen() )
   m_db.close();

m_db.setDatabaseName( "base2.bdd" );

To copy to clipboard, switch view to plain text mode 

But, here, if i launch program it closes immediately as soon i've come on the if instruction.

I don't know what is wrong.

Thank you for your answer.[/quote]

[ChrisW67]

Hi,
Then in a second function (in the same class), i execute SQL request :

if ( !m_db.open() )
   s_req->data = m_db.exec( "SELECT * FROM tab1 ORDER BY nom COLLATE NOCASE;" );

if ( !m_db.open() )
   s_req->data = m_db.exec( "SELECT * FROM tab1 ORDER BY nom COLLATE NOCASE;" );

To copy to clipboard, switch view to plain text mode 

If the database is not open then try to execute a query... are you sure about that logic?

Also, if you are interested in future proofing: QSqlDatabase::exec() is deprecated. Use QSqlQuery::exec() instead.

Until here, i've no problem with Sqlite.

But in a third function, i want to load (and use) a new database (without restart program).

To do this, i do :

if ( m_db.isOpen() )
   m_db.close();
 
m_db.setDatabaseName( "base2.bdd" );

if ( m_db.isOpen() )
   m_db.close();

m_db.setDatabaseName( "base2.bdd" );

To copy to clipboard, switch view to plain text mode 

But, here, if i launch program it closes immediately as soon i've come on the if instruction.
I don't know what is wrong.

Have you tried a clean build?
Have you tried using a debugger to work out the problem?
What have you tried to debug the problem?
Is there any error message when the program "closes immediately"?
Are there any warnings or error messages issued before the program "closes immediately"?
Has m_db been set when you execute this code, or is it still in the default constructed invalid state? What is the return of m_db.isValid()?

[beware]

Hi,

yes, you're rigth i made a mistake in my post, the condition is of course

if ( m_db.open )

if ( m_db.open )

To copy to clipboard, switch view to plain text mode 

Then, m_db is set in the constructor of the class (m_db is a private member). And i use it in the function of this class.
So when i do m_db.exec in my function, m_db is already set by the constructor. Well i think.

Edit:
Well i've just add this at the end of my main function (which executes sql query):

m_db.close();
qDebug() << m_db.lastError();

m_db.close();
qDebug() << m_db.lastError();

To copy to clipboard, switch view to plain text mode 

Then i ve got this :

QSqlError(-1, "Error on ending file", "unable to close due to unfinalised statements")

QSqlError(-1, "Error on ending file", "unable to close due to unfinalised statements")

To copy to clipboard, switch view to plain text mode 

But i don't know what is it.

[ChrisW67]

Have you started any transactions on that connection that you have yet to commit or rollback?

[beware]

The only SQL function i've used are :
open()
close()
exec()

I don't know if it's important but i don't use a QqslTable

[nish]

Dont store the QSqlDatabse object as private member. Use it on the fly by QSqlDatabase::database().

[beware]

Well if i don't set QSqlDatabase as a member, and if i use it in my function like this :

QSqlDatabase m_db = QSqlDatabase::addDatabase( "QSQLITE" );
m_db.setDatabaseName( db_name );

QSqlDatabase m_db = QSqlDatabase::addDatabase( "QSQLITE" );
m_db.setDatabaseName( db_name );

To copy to clipboard, switch view to plain text mode 

And then i can use it. But, at launch i call this function 5 times. Well the first call is correct, but i ve this error on the second one :

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

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

To copy to clipboard, switch view to plain text mode 

[nish]

read my post again. addDatabase() is called only first time. After that use database().

http://doc.qt.nokia.com/latest/qsqld....html#database

[beware]

If use QSqlQuerry in local, how i send data to others functions?

Edit :
For now, i store all my data in a QStringList that i return to functions.

[beware]

Hi,

i've a problem to open my database when i'm using database. My code is:

QSqlDatabase m_db;
   if ( first_call )
   {
      qDebug() << "first";
      m_db = QSqlDatabase::addDatabase( "QSQLITE" );
      m_db.isValid() ? qDebug() << "valid" : qDebug() << "not valid";
      first_call = false;
   }
   else
   {
      qDebug() << "others";
      m_db = QSqlDatabase::database();
      m_db.isValid() ? qDebug() << "valid" : qDebug() << "not valid";
   }
   m_db.setDatabaseName( m_database );
 
   /** Display an error if database can not be opened **/
   if ( !m_db.open() )
   {
       QSqlQuery q( m_db );
       q.exec( "SELECT count(*) FROM films;" ) ? qDebug() << "query ok" : qDebug() << "query ko";
      while ( q.next() )
   {
      s_stats->nb_total = q.value(0).toString();
   }
}
q.finish();
m_db.close();

QSqlDatabase m_db;
   if ( first_call )
   {
      qDebug() << "first";
      m_db = QSqlDatabase::addDatabase( "QSQLITE" );
      m_db.isValid() ? qDebug() << "valid" : qDebug() << "not valid";
      first_call = false;
   }
   else
   {
      qDebug() << "others";
      m_db = QSqlDatabase::database();
      m_db.isValid() ? qDebug() << "valid" : qDebug() << "not valid";
   }
   m_db.setDatabaseName( m_database );

   /** Display an error if database can not be opened **/
   if ( !m_db.open() )
   {
       QSqlQuery q( m_db );
       q.exec( "SELECT count(*) FROM films;" ) ? qDebug() << "query ok" : qDebug() << "query ko";
      while ( q.next() )
   {
      s_stats->nb_total = q.value(0).toString();
   }
}
q.finish();
m_db.close();

To copy to clipboard, switch view to plain text mode 

Then i got this with qdebug:

first 
valid 
query ok 
QSqlError(-1, "", "unable to close due to unfinalised statements")

first 
valid 
query ok 
QSqlError(-1, "", "unable to close due to unfinalised statements")

To copy to clipboard, switch view to plain text mode 

I get all my data from database, but it 's not clean.

[ChrisW67]

Do you really mean to query a database that failed to open() at lines 20-25?
Line 27 should not compile given that the object it is referring to is out-of-scope.

It is helpful to produce a small, complete example that compiles and demonstrates the problem.

Suggestions:
If this is part of a class then put line 5 in the constructor and dispense with the first_call flag business.

[beware]

#include "sql_functions.hpp"
 
sql_functions::sql_functions( const QString database )
{
   /**
    * Create structure :
    *    - to store data for SQL request
    *    - to store data for statistics
    **/
   s_req   = new Struct_Request;
   s_stats = new Struct_Statistic;
 
   first_call = true;
   m_database = database;
}
 
sql_functions::~sql_functions()
{
   delete s_req;
   delete s_stats;
}
 
void sql_functions::exec( const int request_type, QString movie_title, QString type, QString borrower_name,
                          QString Id_movie_update )
{
   /** Clear data field in request structure **/
   s_req->data.clear();
 
   QSqlDatabase m_db;
   if ( first_call )
   {
      m_db = QSqlDatabase::addDatabase( "QSQLITE" );
      first_call = false;
   }
   else
   {
      m_db = QSqlDatabase::database();
   }
   m_db.setDatabaseName( m_database );
 
   /** If no error occured, SQL request is executed **/
   if ( m_db.open() )
   {      
      s_req->data = QSqlQuery( m_db );
      switch( request_type )
      {
         case RequestSQL::DISPLAY_ALL :
            s_req->data.exec( "SELECT type,titre,dvd_en_pret,bluray_en_pret,dvd_prete_a,bluray_prete_a,est_combo "
                              "FROM films ORDER BY titre COLLATE NOCASE;" );
            break;
 
         case RequestSQL::DISPLAY_DVD :
            s_req->data.exec( "SELECT type,titre,dvd_en_pret,bluray_en_pret,dvd_prete_a,bluray_prete_a,est_combo
                              "FROM films WHERE type IN ('DVD') ORDER BY titre COLLATE NOCASE;" );
            break;
 
         case RequestSQL::DISPLAY_BLURAY :
            s_req->data.exec( "SELECT type,titre,dvd_en_pret,bluray_en_pret,dvd_prete_a,bluray_prete_a,est_combo "
                              "FROM films WHERE type IN ('BLURAY') ORDER BY titre COLLATE NOCASE;" );
            break;
 
         default:
            break;
      }
 
      /** close database **/
      m_db.close();
   }
}

#include "sql_functions.hpp"

sql_functions::sql_functions( const QString database )
{
   /**
    * Create structure :
    *    - to store data for SQL request
    *    - to store data for statistics
    **/
   s_req   = new Struct_Request;
   s_stats = new Struct_Statistic;
   
   first_call = true;
   m_database = database;
}

sql_functions::~sql_functions()
{
   delete s_req;
   delete s_stats;
}

void sql_functions::exec( const int request_type, QString movie_title, QString type, QString borrower_name,
                          QString Id_movie_update )
{
   /** Clear data field in request structure **/
   s_req->data.clear();

   QSqlDatabase m_db;
   if ( first_call )
   {
      m_db = QSqlDatabase::addDatabase( "QSQLITE" );
      first_call = false;
   }
   else
   {
      m_db = QSqlDatabase::database();
   }
   m_db.setDatabaseName( m_database );
   
   /** If no error occured, SQL request is executed **/
   if ( m_db.open() )
   {      
      s_req->data = QSqlQuery( m_db );
      switch( request_type )
      {
         case RequestSQL::DISPLAY_ALL :
            s_req->data.exec( "SELECT type,titre,dvd_en_pret,bluray_en_pret,dvd_prete_a,bluray_prete_a,est_combo "
                              "FROM films ORDER BY titre COLLATE NOCASE;" );
            break;

         case RequestSQL::DISPLAY_DVD :
            s_req->data.exec( "SELECT type,titre,dvd_en_pret,bluray_en_pret,dvd_prete_a,bluray_prete_a,est_combo
                              "FROM films WHERE type IN ('DVD') ORDER BY titre COLLATE NOCASE;" );
            break;

         case RequestSQL::DISPLAY_BLURAY :
            s_req->data.exec( "SELECT type,titre,dvd_en_pret,bluray_en_pret,dvd_prete_a,bluray_prete_a,est_combo "
                              "FROM films WHERE type IN ('BLURAY') ORDER BY titre COLLATE NOCASE;" );
            break;

         default:
            break;
      }

      /** close database **/
      m_db.close();
   }
}

To copy to clipboard, switch view to plain text mode 

Variable s_req->data (QSqlQuerry) is used to share data with others functions in my program.

[ChrisW67]

You cannot use the QSqlQuery to pass data back to other parts of the program if you insist on closing the database connection (even if it let you). It probably won't let you close the database connection because the QSqlQuery you create and execute is still active.

Why do you want to keep opening and closing the connection rather than keeping a persistent connection for the life of your sql_functions instance?

How about something like (untested - not at my machine):

#include "sql_functions.hpp"
 
sql_functions::sql_functions( const QString database ):
    m_database(database)
{
  // Establish the database connection
  QSQLDatabase db = QSqlDatabase::addDatabase( "QSQLITE" );
  db.setDatabaseName( m_database );
  db.open();
 
   /**
    * Create structure :
    *    - to store data for SQL request
    *    - to store data for statistics
    **/
   s_req   = new Struct_Request;
   s_stats = new Struct_Statistic;
}
 
sql_functions::~sql_functions()
{
   delete s_req;
   delete s_stats;
 
   QString connectionName;
   {  // scope the db instance so that removeDatabase does not complain
       // about active connections.
     QSqlDatabase db = QSqlDatabase::database();
     connectionname = db.connectionName();
     if (db.isOpen())
       db.close();
   }
   QSqlDatabase::removeDatabase(connectionName);
 
}
 
void sql_functions::exec( const int request_type, QString movie_title, 
                                   QString type,  QString borrower_name,
                          QString Id_movie_update )
{
   /** Clear data field in request structure **/
   s_req->data.clear();
 
   QSqlDatabase db = QSqlDatabase::database();
 
   /** If no error occured, SQL request is executed **/
   if ( db.open() )
   {      
      s_req->data = QSqlQuery( db );
      switch( request_type )
      {
         case RequestSQL::DISPLAY_ALL :
            s_req->data.exec( "SELECT type,titre,dvd_en_pret,bluray_en_pret,dvd_prete_a,bluray_prete_a,est_combo "
                              "FROM films ORDER BY titre COLLATE NOCASE;" );
            break;
 
         case RequestSQL::DISPLAY_DVD :
            s_req->data.exec( "SELECT type,titre,dvd_en_pret,bluray_en_pret,dvd_prete_a,bluray_prete_a,est_combo
                              "FROM films WHERE type IN ('DVD') ORDER BY titre COLLATE NOCASE;" );
            break;
 
         case RequestSQL::DISPLAY_BLURAY :
            s_req->data.exec( "SELECT type,titre,dvd_en_pret,bluray_en_pret,dvd_prete_a,bluray_prete_a,est_combo "
                              "FROM films WHERE type IN ('BLURAY') ORDER BY titre COLLATE NOCASE;" );
            break;
 
         default:
            break;
      }
 
   }
}

#include "sql_functions.hpp"

sql_functions::sql_functions( const QString database ):
    m_database(database)
{
  // Establish the database connection
  QSQLDatabase db = QSqlDatabase::addDatabase( "QSQLITE" );
  db.setDatabaseName( m_database );
  db.open();

   /**
    * Create structure :
    *    - to store data for SQL request
    *    - to store data for statistics
    **/
   s_req   = new Struct_Request;
   s_stats = new Struct_Statistic;
}

sql_functions::~sql_functions()
{
   delete s_req;
   delete s_stats;

   QString connectionName;
   {  // scope the db instance so that removeDatabase does not complain
       // about active connections.
     QSqlDatabase db = QSqlDatabase::database();
     connectionname = db.connectionName();
     if (db.isOpen())
       db.close();
   }
   QSqlDatabase::removeDatabase(connectionName);

}

void sql_functions::exec( const int request_type, QString movie_title, 
                                   QString type,  QString borrower_name,
                          QString Id_movie_update )
{
   /** Clear data field in request structure **/
   s_req->data.clear();

   QSqlDatabase db = QSqlDatabase::database();

   /** If no error occured, SQL request is executed **/
   if ( db.open() )
   {      
      s_req->data = QSqlQuery( db );
      switch( request_type )
      {
         case RequestSQL::DISPLAY_ALL :
            s_req->data.exec( "SELECT type,titre,dvd_en_pret,bluray_en_pret,dvd_prete_a,bluray_prete_a,est_combo "
                              "FROM films ORDER BY titre COLLATE NOCASE;" );
            break;

         case RequestSQL::DISPLAY_DVD :
            s_req->data.exec( "SELECT type,titre,dvd_en_pret,bluray_en_pret,dvd_prete_a,bluray_prete_a,est_combo
                              "FROM films WHERE type IN ('DVD') ORDER BY titre COLLATE NOCASE;" );
            break;

         case RequestSQL::DISPLAY_BLURAY :
            s_req->data.exec( "SELECT type,titre,dvd_en_pret,bluray_en_pret,dvd_prete_a,bluray_prete_a,est_combo "
                              "FROM films WHERE type IN ('BLURAY') ORDER BY titre COLLATE NOCASE;" );
            break;

         default:
            break;
      }

   }
}

To copy to clipboard, switch view to plain text mode 

no m_db, no first_call, no constant open/close.

[beware]

Thanks for your answer. I update my code.

No compilation problem, but i have an error when i closing my program, here :

QSqlDatabasePrivate::removeDatabase: connection 'qt_sql_default_connection' is still in use, all queries will cease to work.

QSqlDatabasePrivate::removeDatabase: connection 'qt_sql_default_connection' is still in use, all queries will cease to work.

To copy to clipboard, switch view to plain text mode 

[ChrisW67]

Where else are you creating queries against the database? Are you creating copies of the query created by this class elsewhere in the program? Are they being cleared or deleted before this point?

[beware]

Well,
All my SQL querries are create in function

void sql_functions::exec

void sql_functions::exec

To copy to clipboard, switch view to plain text mode 

Querries result are store in a structure, and others function can used this value to get back request values with

QString result;
while s_req->data.next()
     result = s_req->data.value(0).toString();

QString result;
while s_req->data.next()
     result = s_req->data.value(0).toString();

To copy to clipboard, switch view to plain text mode 

I only clear the querry in the begining of the function.