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

[ksandbergfl]

Hi,

I will try to keep my question simple and specific. I am a software developer by trade, but this is my first exposure to SQLite and QT. I am seeing behavior that I do not understand.

I am on Windows 7.

I have an application that was created using QT, by a 3rd party. It has several SQLite DB files that it reads/writes.

I am attempting to write some scripts to automate loading/updating the DB files with my own data.

When I run the 3rd party app, I can add/edit entries as expected. Using Windows Explorer, I can see that the DB file modification date changes. Using a hex editor, I can open up the DB file and see that the new records are there, inside the DB file.

However -- when I open up the DB file using SQLite3 (ala "sqlite3 myDB.db .dump"), the records do not appear! The output of the DUMP command always appears to be an earlier version of the DB, not the current one.

Moreover -- if I use Windows to copy the DB file to another location, and use SQLite to open it -- I can see the new records! I can edit and manipulate the records as I wish, but when I copy the DB file back to where the 3rd party app uses it... the records I added/changed do not show up using SQLite, or when I run the 3rd party app.

I have read several posts around here about "in memory" DB -- is the 3rd party app using the in-memory option? How do I get the 3rd party app to unload the in-memory copy, so I can edit it? More specifically, how/why does QT's SQLite implementaion (QSQLITE4.DLL) differ from the standard SQLite3 operation? Would I have better success downloaded QT, and using the QSQLITE4.DLL to operate on these DB files? Why can't I do it using only SQLite3? I'm wondering if the version of SQLite3 has to *exactly* match the version that was compiled into QSQLITE4.DLL -- is there a way I can check QSQLITE4.dll for which SQLite it uses?

Thanks for any input you can provide... this is not a "mission critical" endeavor, it's just something that seems like it should be "easy" to understand, and I have become OCD about figuring it out.

--- KSandbergFL

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