Important: Please read the Qt Code of Conduct - https://forum.qt.io/topic/113070/qt-code-of-conduct

Backup an in memory database to disk (sqlite)



  • Hi to everyone!

    I'm quite new in QT and I'm stucked! What I'm trying to do is to save the content of an in memory database to a file. To do so I tried to use QSqlDatabase::cloneDatabase but I didn't succeed.

    Googling the same question I found that statement:
    QSqlDatabase dbcopy = QSqlDatabase::database();
    dbcopy.setDatabaseName("/tmp/dbcopy");
    dbcopy = QSqlDatabase::cloneDatabase(db,"newConnectionName");

    where "dbcopy" is the new database and "db" is the in memory one. After execute the code, the new database appears empty (yes, I have data on "db") but I can open dbcopy properly (to see how empty it is!)

    So, I have 3 questions:

    1. Am I doing something wrong? (I'm completely sure that yes...)
    2. How can I monitor the possible problems which can appear?
    3. There are other ways to backup the in memory database to a disk?

    Thank your for your time.


  • Lifetime Qt Champion

    Hi and welcome to devnet,

    cloneDatabase will clone the connection not the database itself. You'll have to copy the database content by e.g. doing a SQL dump like described here

    Hope it helps



  • Solved.

    If anyone it's interested I will post my solution.

    1. Create a second database connection:
    QSqlDatabase copyDb = QSqlDatabase::addDatabase( "QSQLITE", "second");
    copyDb.setDatabaseName( "/tmp/copy.db" );
    copyDb.open();  
    QSqlQuery subQuery( copyDb );
    
    1. Create the tables (I've hardcoded the name of the table and columns):
      bool ret = subQuery.exec( "CREATE TABLE IF NOT EXISTS names (id INTEGER UNIQUE PRIMARY KEY, firstname VARCHAR(30), lastname VARCHAR(30))" );
    
    1. Select everything from the previous DB and add it to the new DB:
      static const QString QUERYFORMAT( "INSERT INTO names (id, firstname, lastname) VALUES ( '%1' , '%2', '%3' )" );
    
      qry.exec( "select * from names" );
      QSqlRecord rec = qry.record();
      QString id, first, last;
      QString queryString;
    
    
      while( qry.next() )
      {
          id = qry.value( rec.indexOf( "id" ) ).toString();
          first = qry.value( rec.indexOf( "firstname") ).toString();
          last = qry.value( rec.indexOf( "lastname" ) ).toString();
          qDebug() << id << " " << first << " " << last ;
    
          queryString = QUERYFORMAT.arg( id, first, last );
          ret = subQuery.exec( queryString );
    
          if( !ret )
          {
              qDebug() << subQuery.lastError().text();
          }
      }
    

    Probably it is not the best way to proceed but, at least, it worked.

    Here is the reference: http://goobbe.com/questions/4341997/how-to-sqlite-dump-using-qt


Log in to reply