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:

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.

[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.

1. Create a second database connection:

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

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

3. 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