Import SQLite database using Qt/QSqlDatabase

Question

I have two separate applications, one placed at the production another at an office, and I need to get a copy of an sqlite database generated and updated at production side.

Until now I've tried two approches:

Copy the entire sqlite file from the production-application and "redirect" my QSqlDatabase-handles to that file (was not able to get it to work, many connections are already opened and not all closable)
Access the sqlite-db over network, query all data and insert the missing data using sql (works, but since there is a lot of data it takes too much time)

Are there possibilities to import or maybe override an existing database (or even single tables, placed in different files), where there are still open connections?

Using: Qt 4.8, SQLite, Windows 7, VS2010

Copy the file to your local temp dir, then use the backup API to replace the database. — CL.
– CL., Commented Nov 19, 2014 at 11:46
File is already copied for backup reasons... Going to have a look at the backup api asap and leaving a feedback here! Thanks — Robert
– Robert, Commented Nov 19, 2014 at 12:11
@CL. works like a charm! Thanks for your tip. Add it as answer and I'll upvote and accept it :-) — Robert
– Robert, Commented Nov 19, 2014 at 16:04

Robert · Accepted Answer · 2014-11-20 10:25:27Z

So finally I was able to reach my goal by using the sqlite backup api (which is distributed as .h and .c with most Qt versions). On the documentation page SQLite Backup there are a few examples, where a database is copied either from a file to an in-memory db, or from an in-memory to a file. In my case I used the following function (1:1 from doc page, only several comments removed):

int loadOrSaveDb(sqlite3 *pInMemory, const char *zFilename, int isSave){
  int rc;                   /* Function return code */
  sqlite3 *pFile;           /* Database connection opened on zFilename */
  sqlite3_backup *pBackup;  /* Backup object used to copy data */
  sqlite3 *pTo;             /* Database to copy to (pFile or pInMemory) */
  sqlite3 *pFrom;           /* Database to copy from (pFile or pInMemory) */

  /* Open the database file identified by zFilename. Exit early if this fails
  ** for any reason. */
  rc = sqlite3_open(zFilename, &pFile);
  if( rc==SQLITE_OK ){

    pFrom = (isSave ? pInMemory : pFile);
    pTo   = (isSave ? pFile     : pInMemory);

    pBackup = sqlite3_backup_init(pTo, "main", pFrom, "main");
    if( pBackup ){
      (void)sqlite3_backup_step(pBackup, -1);
      (void)sqlite3_backup_finish(pBackup);
    }
    rc = sqlite3_errcode(pTo);
  }

  (void)sqlite3_close(pFile);
  return rc;
}

The only additional steps to get the handle needed in the function above were:

1.Get sqlite-handle from QSqlDatabase

QVariant destVar = database.driver()->handle();

2.Check handle for validity and cast to sqlite3*

if(destVar.isValid() && qstrcmp(destVar.typeName(), "sqlite3*") == 0)
{
    sqlite3* destination = *static_cast<sqlite3 **>(destVar.data());
    ...
}

Thanks to CL. (who was pointing the right direction).

Collectives™ on Stack Overflow

Import SQLite database using Qt/QSqlDatabase

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related