30752

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 <strong>copy of</strong> an <strong>sqlite database</strong> generated and updated at production side.

Until now I've tried two approches:

<ol><li>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)</li> <li>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)</li> </ol>

Are there possibilities to <strong>import</strong> or maybe <strong>override</strong> 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

Answer1:

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 <a href="https://www.sqlite.org/backup.html" rel="nofollow">SQLite Backup</a> 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).

Recommend

  • java is not working in linux. Why?
  • Ruby on Rails: Cant download new gems (Name or service not known)
  • Implementing a function that perfect-forwards to std::thread
  • doxygen generated documentation with auto-generated links to qt project
  • Cross compiling Qt 5
  • PyQt4 application on Windows is crashing on exit
  • Google analytics measurement protocol session timeout and query time limits
  • Android Database Error - getWriteableDatabase
  • How to estimate the Kalman Filter with 'KFAS' R package, with an AR(1) transition equation
  • Azure webjobs output logs indexing taking very long
  • Find group of records that match multiple values
  • Azure table store snapshot/backup capability
  • Bigquery event streaming and table creation
  • Convert SQLite database to XML
  • SharedPreferences or SQLite Database?
  • ADO and msqli connections very slow
  • Marklogic : Query response time is very high
  • How to use remove-erase idiom for removing empty vectors in a vector?
  • Linq Objects Group By & Sum
  • Sails.js/waterline: Executing waterline queries in toJSON function of a model?
  • Optimizing database types to compact database (SQLite)
  • Sony Xperia Z Tablet not found by adb
  • How to redirect a user to a different server and include HTTP basic authentication credentials?
  • Does CUDA 5 support STL or THRUST inside the device code?
  • Can I make an Android app that runs a web view in Chrome 39?
  • Javascript convert timezone issue
  • Redux, normalised entities and lodash merge
  • Why is the timeout on a windows udp receive socket always 500ms longer than set by SO_RCVTIMEO?
  • Large data - storage and query
  • Do create extension work in single-user mode in postgres?
  • jqPlot EnhancedLegendRenderer plugin does not toggle series for Pie charts
  • How do I rollback to a specific git commit
  • Is there a mandatory requirement to switch app.yaml?
  • How do you join a server to an Active Directory (domain)?
  • Can Visual Studio XAML designer handle font family names with spaces as a resource?
  • LevelDB C iterator
  • Linking SubReports Without LinkChild/LinkMaster
  • How can I remove ASP.NET Designer.cs files?
  • Are Kotlin's Float, Int etc optimised to built-in types in the JVM? [duplicate]
  • Busy indicator not showing up in wpf window [duplicate]