As part of app start-up an integrity check is performed on the DB, the DB is Vacuumed and the file copied to the backup (using C# to simplify overwrite of existing file), and then an integrity check performed on the back.

How do you know nothing is modifying the DB while C# is copying it?

It's a "single user" DB; only this app knows of it's existence/has access to it and the copy is foreground with no background/async processing occurring during the <1 ms copy event.

Looking at the data in the DB manager (DBeaver) the values were correct

This would imply the data in the database is correct, and your application is getting data and mangling it somehow... conversion from a long to an integer? While the value is an integer in SQLite, what is the type in c# ?

Have you tried assuming the value is text? (so reading the database value into a text variable).

That was certainly my initial interpretation as well (and I spent over a day trying isolate the "mangle point" but when I again got expected values after reverting the DB to a backup I started looking at the DB itself.

However, I believe I've found the cause. As a test I created an entirely new table with old table's definition & loaded it from the old table. Again I got the "corrupted" values in-app. Then I noticed I had defined the three numeric columns as INT which of course SQLite doesn't have an INT data class; it has an INTEGER data class. Changed that in the table CREATE statement and issue resolved.

I don't know what definition SQLite was giving those columns. According to the DB manager they were still categorized as numeric so maybe as REAL? It does beg the question tho -- given SQLite's "flexible/dynamic typing" -- why that was actually a problem and causes me to question my understanding of the flexible typing concept.

EDIT: Gave it a go with string & still got the "corrupted"/dynamically typed values. So it must have something to do with SQLite's under the hood magic. Interesting tho. Thanx.

What API/reference/library is your c# app using?

I may be wrong (usually am) but SQLite doesn't really care what the column is defined as. Could be a banana for all it cares.

However, in your app when you say, 'I want field 10', it might check what the column is defined as to ascertain how to grab the data. I tend to just read into a table and get field X - it's up to me then what field type to put that data... rather than say, 'I want an integer from field 10'. That may not make that much sense... :/

Glad you nailed down the issue though.

I'm using System.Data.SQLite. I forget if that's the exact name it's it has in NuGet.

To be honest I've never been real comfortable with the flexible typing approach & certainly don't rely on it. I tend to have (as I've grown more experienced) more TEXT definitions & then -- as you appear to do also -- I rely on TryParse for date, time & boolean ...

First off, I'm pretty much a novice in the C#/SQLite arena. Having said that, I did have the thought cross my mind that the DB manager was a potential culprit particularly given SQLite's installed base and the fact I'm using the "free" Community version of DBeaver.

I've never used the Command Line Shell. Is it similar to Windows' dos//cmd window?

I've just taken a quick look at the CLS link you provided, which incidentally shows defining a column as an int in its little example which lends credence to the DBeaver as culprit theory ...

One thing I didn't see was how to acquired the Shell, I presume because it's assumed I already have it. I acquired SQLite via NuGet through Microsoft's Visual Studio so I'm not sure what all is bundled in the Library but I don't recall any mention of a Shell. If you'd point me in the right direction for acquiring/accessing the Shell I'd be glad to run a quick test some time in the next couple of days.

Hi, Art,

From the front page of sqlite.org you'll see a "Download" button.

From that page, look for "Precompiled Binaries for Windows" The third item will be "A bundle of command-line tools..."

The sqlite3.exe file within that bundle is self-contained. From a windows command prompt, just enter sqlite3 followed by your database filespec.

Guess I was intimidated by all those "binary" titles when I checked the downloads page yesterday ...

Ran some tests using the Command Line Shell

3 scenarios



existing DB, new table, leveraged data
existing DB, new table, new data
new DB, table & data
2 runs/scenario; one using INT & one using INTEGER for column type

I can state conclusively it is not a DBeaver related issue. I get identical results using the Shell that I reported above. I'm using a Dapper/System.Data.SQLite combination in a C# Winforms app being developed in Microsoft Visual Studio. I guess this anomaly could arise anywhere in that chain but I'm certainly not knowledgeable enough to even begin testing that. </ul>

Testing results: INT vs INTEGER  field definition  
<<< existing DB, new table, leveraged data >>> 
sqlite> create table intTest (drvName text, size int, used int);
sqlite> insert into inttest (drvName,size,used)
   ...> select drvName,size,used from drvdata;
sqlite> select * from intTest;
┌─────────┬──────────────┬──────────────┐
│ drvName │     size     │     used     │
├─────────┼──────────────┼──────────────┤
│ C:\     │ 107374178304 │ 63829413888  │
│ D:\     │ 429496725504 │ 218584641536 │
│ E:\     │ 500106784768 │ 229941153792 │
└─────────┴──────────────┴──────────────┘

IN-APP RESULTS
intTest Result = "C:\" size=-4,096 used =-595,095,552
intTest Result = "D:\" size=-4,096 used =-458,690,560
intTest Result = "E:\" size=1,890,578,432 used =-1,987,080,192
---------------------------------------------------------------

sqlite> create table intTest_2 (drvName text, size integer, used integer);
sqlite> insert into inttest_2 (drvName,size,used)
   ...> select drvName,size,used from drvdata;
sqlite> select * from intTest_2;
┌─────────┬──────────────┬──────────────┐
│ drvName │     size     │     used     │
├─────────┼──────────────┼──────────────┤
│ C:\     │ 107374178304 │ 63829413888  │
│ D:\     │ 429496725504 │ 218584641536 │
│ E:\     │ 500106784768 │ 229941153792 │
└─────────┴──────────────┴──────────────┘

IN-APP RESULTS
intTest Result = "C:\" size=107,374,178,304 used =63,829,413,888
intTest Result = "D:\" size=429,496,725,504 used =218,584,641,536
intTest Result = "E:\" size=500,106,784,768 used =229,941,153,792
==================================================================

<<< existing DB, new table, new data >>> 
sqlite> create table intTest_3 (drvName text, size int, used int);
sqlite> insert into inttest_3 (drvName,size,used) values
   ...>   ('C:\',107374178304,63829413888),
   ...> ('D:\',429496725504,218584641536),
   ...> ('E:\',500106784768,229941153792);
sqlite> select * from intTest_3;
┌─────────┬──────────────┬──────────────┐
│ drvName │     size     │     used     │
├─────────┼──────────────┼──────────────┤
│ C:\     │ 107374178304 │ 63829413888  │
│ D:\     │ 429496725504 │ 218584641536 │
│ E:\     │ 500106784768 │ 229941153792 │
└─────────┴──────────────┴──────────────┘

IN-APP RESULTS
intTest Result = "C:\" size=-4,096 used =-595,095,552
intTest Result = "D:\" size=-4,096 used =-458,690,560
intTest Result = "E:\" size=1,890,578,432 used =-1,987,080,192
---------------------------------------------------------------
 
sqlite> create table intTest_4 (drvName text, size integer, used integer);
sqlite> insert into inttest_4 (drvName,size,used) values
   ...>   ('C:\',107374178304,63829413888),
   ...> ('D:\',429496725504,218584641536),
   ...> ('E:\',500106784768,229941153792);
sqlite> select * from intTest_4;
┌─────────┬──────────────┬──────────────┐
│ drvName │     size     │     used     │
├─────────┼──────────────┼──────────────┤
│ C:\     │ 107374178304 │ 63829413888  │
│ D:\     │ 429496725504 │ 218584641536 │
│ E:\     │ 500106784768 │ 229941153792 │
└─────────┴──────────────┴──────────────┘

IN-APP RESULTS
intTest Result = "C:\" size=107,374,178,304 used =63,829,413,888
intTest Result = "D:\" size=429,496,725,504 used =218,584,641,536
intTest Result = "E:\" size=500,106,784,768 used =229,941,153,792
==================================================================

<<< virgin DB, new table, new data >>> 
C:\Users\AppData\LocalLow\Proj_Data_dev>sqlite3 tmp.db
SQLite version 3.39.3 2022-09-05 11:02:23
Enter ".help" for usage hints.
sqlite> create table intTest_5 (drvName text, size int, used int);
sqlite> insert into inttest_5 (drvName,size,used) values
   ...>  ('C:\',107374178304,63829413888),
   ...> ('D:\',429496725504,218584641536),
   ...> ('E:\',500106784768,229941153792);
sqlite> select * from intTest_5;
┌─────────┬──────────────┬──────────────┐
│ drvName │     size     │     used     │
├─────────┼──────────────┼──────────────┤
│ C:\     │ 107374178304 │ 63829413888  │
│ D:\     │ 429496725504 │ 218584641536 │
│ E:\     │ 500106784768 │ 229941153792 │
└─────────┴──────────────┴──────────────┘

IN-APP RESULTS
intTest Result = "C:\" size=-4,096 used =-595,095,552
intTest Result = "D:\" size=-4,096 used =-458,690,560
intTest Result = "E:\" size=1,890,578,432 used =-1,987,080,192
---------------------------------------------------------------
sqlite> create table intTest_6 (drvName text, size integer, used integer);
sqlite> insert into inttest_6 (drvName,size,used) values
   ...>  ('C:\',107374178304,63829413888),
   ...> ('D:\',429496725504,218584641536),
   ...> ('E:\',500106784768,229941153792);
sqlite> select * from intTest_6;
┌─────────┬──────────────┬──────────────┐
│ drvName │     size     │     used     │
├─────────┼──────────────┼──────────────┤
│ C:\     │ 107374178304 │ 63829413888  │
│ D:\     │ 429496725504 │ 218584641536 │
│ E:\     │ 500106784768 │ 229941153792 │
└─────────┴──────────────┴──────────────┘

IN-APP RESULTS
intTest Result = "C:\" size=107,374,178,304 used =63,829,413,888
intTest Result = "D:\" size=429,496,725,504 used =218,584,641,536
intTest Result = "E:\" size=500,106,784,768 used =229,941,153,792
==================================================================

Something in your application is using the "declared column type" to decide what it is going to do. It appears that if the "declared type" is integer then the value is retrieved as the native 8-byte/64-bit signed integer that it is, however, when the "declared type" is int then that 8-byte/64-bit value is being truncated to 4-byte/32-bit binary value by discarding the upper 4-bytes/32-bits.

I've not used any of the wares (other than the C version of SQLite3) that you are using, so cannot tell you where this is happening. But it is exactly what is happening.

Aha! That fits with my observations/experience. The in-app target variables were originally what C# calls a "decimal' type, which is 64-bit. After determining the source data is always returning positive whole numbers I changed the target variable type to C# 32-bit integer type and that caused a SQLite parsing error when importing the data from the DB into the app. Changing the target variable type to a 64-bit integer resolved the issue. Truncation explains why the erroneous data is consistent which means the data isn't actually corrupted after all. The computer is just doing what it was told to do.

As you say, somewhere under-the-hood/bonnet in my "ware" chain "int" & "integer" are being interpreted as different entities & I strongly suspect it is in C# itself where "int" is the keyword for the 32-bit integer type. Still seems odd to me that the interface/importation application -- knowing the source is SQLite where "int" & "integer" are equivalent -- doesn't accommodate that ...

Thanx for this.