Backup an in memory database to disk (sqlite)

eortega

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:

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

Thank your for your time.

SGaist

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

eortega

Solved.

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

Create a second database connection:

QSqlDatabase copyDb = QSqlDatabase::addDatabase( "QSQLITE", "second");
copyDb.setDatabaseName( "/tmp/copy.db" );
copyDb.open();  
QSqlQuery subQuery( copyDb );

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))" );

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

HoMa

just for completness: since 2019 sqlite supports the "VACCUUM INTO" command - which solves the problem with just one piece of SQL