Please nominate your Qt Champions for 2021!

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 = 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" );;  
    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( )
          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:

Log in to reply