Accessing DB created with QSQLITE4.DLL, using SQLite3.exe

[ChrisW67]

If the Qt application is still running and has an open, uncommitted transaction then it sees the content of the transaction, and nothing else does. This is standard RDBMS transaction isolation and not unique to Sqlite or Qt. The transaction is tracked partly in the database (the actual data) and partly in associated files created in the same directory (See http://sqlite.org/tempfiles.html). By copying away only the main.db you lose track of the open transaction and the uncommitted data becomes visible but is possibly not consistent.

Sqlite supports an in-memory database: if it were using this there would be no disk file to see.

Edit:
Qt 4.8.1 has Sqlite 3.7.7.1 as the shipped version. This will be used by the Sqlite driver unless Qt was built to use the system version of Sqlite.

[ksandbergfl]

thanks for your reply.

i had considered the uncommitted transaction scenario, but this behavior persists even after I shutdown the 3rd party app. Perhaps the app isn't shutting down cleanly, and the transactions are still uncommitted? Or is it still possible that a version difference between my SQLite and the SQLite used to compile the QSQLITE4.DLL might cause this behavior? I see no journaling or any other temp files being created when the 3rd party app runs. This app was written, I think, sometime in 2010.... is that a clue?

[ChrisW67]

A 2010 version of Qt will contain an earlier version of Sqlite 3. All later versions the the Sqlite command line shell will be able to read it.

Cleanly shutting down the application will either commit or roll back any open transaction: it should leave a consistent database without temporary files. Abnormally terminating the application may leave the temporary files related to the transaction in-situ: they will be rolled back to consistent state when the database is next opened.

How are you determining the records are not present?

[ksandbergfl]

i think i discovered the problem. It's a Windows 7 "feature" that I was unaware of until now, called "VirtualStore".

When I use the 3d party app, the app is using a DB file in, say, the c:\myApp\data folder. DB file is C:\myApp\data\myDB.db

When I use SQLite3, from the command prompt... I navigate into the C:\myApp\data folder and execute "Sqlite3 myDB.db". Well, using Agent Ransack, I learned that SQLite3 is actually editing a different physical copy of the DB file... inside the "VirtualStore" folder in C:\users\myAccount...\VirtualStore\data.

In other words, there are some User Access Control (UAC) permissions things going on, where Windows 7 isn't allowing SQLite3 to open the real copy of the DB file in the app's Data folder. I am googling around today, it looks like it's a simple issue to resolve (just open up permissions on the app's folders, so SQLite can use the real file). I will attempt this workaround tonight, when I get back home, and post my results.

Thanks for your help.

--- Keith

[ChrisW67]

This feature exists to enable pre-Vista programs that wrote to places that are prohibited under Windows 7, such as under Program Files, the ability to keep working. Unfortunately every user account has its own virtualisation store so this broke any data that resided in those locations that was intended to be shared.

If you write an application to access this database you should add a UAC manifest to your executable, which will have the effect of disabling Windows 7's helpful virtualisation feature. With virtualisation off attempts to read and write prohibited locations will fail as you would expect.

[ksandbergfl]

I moved the 3rd party app out of C:\Program Files and into a C:\TEMP folder.... everything is working as expected now. I don't really fault Windows virtualization feature, other than -- it would've been nice to get a dialog prompt from Windows, when I started SQLite from the command line, that said something like "unable to access the Data folder, using a virtualStore copy instead" or something. Poor SQLite (and I) had no idea it wasn't reading the "real" data file.