CODEWITHSUNDEEP
sqlitesqlitestudio
tdemay
tdemay

Reputation: 738

sqlite3.dll can't select records visible in sqlite studio

I'm using version 3.21.0 of sqlite3 library on Windows 7. And version 1.0.0.0 of SQLite Studio

I have several processes queuing data to be process in an sqlite3 table. When there is data to be queued, a database connection is opened, a record is INSERTed into the table and then the database connection is closed. Multiple threads can be inserting records in each of these processes, but the connection is not shared. It's opened and closed for each insert. Each time a record is inserted into the table a Windows Event (::OpenEvent) is signaled so other processes can process the data.

Then I have other processes that pulls the records from the table one at a time to process them. They open a connection, SELECTs one record and DELETEs it in a transaction (BEGIN/COMMIT) for processing. Only one thread in each of these processes are used to process the data. Once all records have been read and processed the connection is closed until it is signaled through the Windows event that there are more records to process.

If SQLITE_BUSY is returned by any process the process with continue to retry until it is successful.

After processing about 1,300 records (2-3 records inserted a second) with 4 processes inserting records and two processes selecting and deleting them, I noticed in SQLite Studio that three records were never processed. It appears that sqlite3.dll cannot see these records for some reason, but SQLite Studio can.

When I run the SELECT statement in the code sample below in SQLite Studio I can see three records. But when I use the same SELECT statement against the same database with sqlite3.dll I get no records.

sqlite3_step below returns SQLITE_DONE when it should return SQLITE_ROW.

I put together the following test to demonstrate the problem. 

#include "stdafx.h"
#include <Windows.h>
#include <sqlite3.h>
#include <crtdbg.h>

int _tmain(int argc, _TCHAR* argv[])
{
    HMODULE hModule = ::LoadLibraryW(L"sqlite3.dll") ;
    sqlite3* db ;
    int rc = ::sqlite3_open16(L"mydatabase.db", &db) ;
    _ASSERTE(SQLITE_OK == rc) ;
    _ASSERTE(db != NULL) ;

    sqlite3_stmt* stmt = NULL ;
    rc = ::sqlite3_prepare_v2(db, "SELECT * FROM MYTABLE;", -1, &stmt, NULL) ;
    _ASSERTE(SQLITE_OK == rc) ;
    _ASSERTE(stmt != NULL) ;

    rc = ::sqlite3_step(stmt) ;
    _ASSERTE(SQLITE_ROW == rc) ;

    rc = ::sqlite3_finalize(stmt) ;
    _ASSERTE(SQLITE_OK == rc) ;

    rc = ::sqlite3_close_v2(db) ;
    _ASSERTE(SQLITE_OK == rc) ;

    return 0;
}

I'm just integrating sqlite into our product and I'm trying to prove the concept. Obviously I'm concerned about the data integrity. Is SQLite Studio somehow seeing records that were deleted? Or is sqlite3.dll not seeing records that are there? Is my database corrupted and what should I be doing to prevent this? I don't believe I'm doing anything overly complex. It's a simple open, INSERT, close. Retry after a brief sleep if SQLITE_BUSY is returned. Then its, open, BEGIN; SELECT; DELETE; COMMIT on the other side. ROLLBACK instead of COMMIT if SQLITE_BUSY is returned and retry after a brief sleep.

Suggestions?

Upvotes: 0

Views: 366

Answers (1)

tdemay
tdemay

Reputation: 738

Not sure if I should just delete this post. Hopefully someone more experienced than me can answer that.

But this was user error. I got bit by .NET's VirtualStore. The database was being stored in %PROGRAMDATA%\CompanyName and when I opened it in SQLite Studio, a copy of the database was made in %LOCALAPPDATA%\VirtualStore\ProgramData\CompanyName. But there were no obvious signs this was happening. File Open from SQLite Studio showed I was accessing files in %PROGRAMDATA%\CompanyName, but it was the files in the VirtualStore. So I was looking at two different databases.

Upvotes: 0

Related Questions