System.Data.SQLite

I need to store numbers up to 18 digits.  I have a column defined as DECIMAL, and I can insert values with 18 digits, and they show up correctly when querying the database with SQLiteSpy.  However when querying the value it loses precision.  For example 321654987321654987 returns as 321654987321655000.  I'm using a DataReader and calling GetDecimal(). 

Is there a way I can read the value as a string?  Using DataReader.GetString() throws an invalid cast exception.  I don't want to convert the entire column to text because then it won't sort correctly.

You're absolutely right about decimal.  It's borked.

This is actually a fairly difficult problem for SQLite, who's numbers are limited to Int64 and Double -- neither of which is sufficient to store a Decimal

If you insert a number like this:

insert into numtest(id, myvalue) values(2, 165498732165498711213123.23323);

SQLite is going to truncate it.

So  ... the answer is ... I can either store decimals in binary format, or store them as strings.  And even if I store them as strings, it'll require you to insert them as strings or risk losing precision on the insert.

Strings are obviously more legible and easier to insert manually (without using a parameterized query) but binary would obviously be more compact.

David, the following will do (I had the same problem with 13 digit UPC-codes):

SELECT cast(EAN as TEXT) as EANT, * FROM

Beware that cast will not give you leading zeros. If you need that (as is the case with UPCs) in your program you must additionally code something like

EANT = Microsoft.VisualBasic.Strings.StrDup(18 - EANT.Length, "0") & EANT    'Strdup is the replacement for the VB6 string function

Thanks Mark, that looks like what I need.

I'll have a fix for decimal values in the next patch.  Basically I am going to store and fetch them as text and do the conversion in the provider.  Data already stored as numeric in the database will still be fine and work normally too.

Robert

Robert,

that sounds good.
Storage is a problem for you, because it is out of control of your wrapper. Let's take my UPC example: I declare the UPC column as TEXT and I want it treated so, because the barcodescanner gives me 13 digits regardless of leading zeros.
E.g. I want to store UPC-Code 0012345678901. With neither SQLite native nor your wrapper I get this done, because the overintelligent core converts this to an Int32 or Int64 and throws away the leading zeros. We have to live with that until SQLite obeys to column types.
On reading I would recommend to convert it to the column type declared in the datatable. This should be consistent with the .NET strict typing.

That's odd ... I just ran a quick test:

C:\Src>sqlite3 :memory:
SQLite version 3.6.0
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> create table test(myvalue text);
sqlite> insert into test values('0012345678901');
sqlite> select * from test;
0012345678901
sqlite>

The command-line left the leading zeros in place.

That's the problem with those old wisdoms: Sometimes they are no longer true :)

I had the trouble with SQLite V3.3 or earlier (can't remember), made my workaround and never cared again. It seems that the problem has been fixed, good to know.

Thx

Nope!  Not without a special collation sequence.

You can declare your own collation sequence like this:

[SQLiteFunction(Name="DECIMALSORT", FuncType = FunctionType.Collation)]
class DecimalSorter : SQLiteFunction
{
  public override int Compare(string param1, string param2)
  {
    return Decimal.Compare(Decimal.Parse(param1), Decimal.Parse(param2));
  }
}

Then you declare your table:
CREATE TABLE FOO(myvalue decimal COLLATE DECIMALSORT);

Unfortunately there's just no good way to handle decimals in SQLite.  SQLite only understands Int64, Double, String and Blob.  Everything else we attempt to pile onto it is like giving SQLite a wedgie.

Robert

If you care about leading zeros, then the "number" is not a number at all but a string of numeric digits.  It's text.  If you declare the type as TEXT in SQLite, use "string" variables in .NET and string related methods on the datareader, then everything will be fine.  That's not the same issue the original poster brought up though about valid .NET decimals that are too large to be stored as numbers in SQLite due to conversion to floating poing values and loss of precision.

Sam

So, just out of curiosity, and after no testing or experimentation whatsoever, what happens if you use a blob instead and serialize it out to the blob?  Probably doesn't help the sorting issue, though, does it?

No it sure doesn't.  I thought about using a blob, but then it wouldn't be human-readable.  As a matter of fact now that you mention it, I don't even think I support blob-based collating sequences ... Not even sure if SQLite does either.

hrm ... thanks for giving me more work to do!  :P

Readability and sorting issues aside, it would appear to be one solution to the precision problem, even if it's not really any more convenient than converting to string and back again on the trip to and from the database, though it might be slightly more efficient, depending on the efficiency of the serialization routines.  With a custom collation sequence, which is no worse than the string solution, you'd at least get proper sorting.  Display should only be an issue if some app other than the one responsible for storing data gets hold of the database.  The primary app should handle the serialization behind the scenes.

Now, am I going to object if you do some behind the scenes magic concerning decimals and blobs in the provider?  No, but it wouldn't appear to be immediately necessary if simply serializing and deserializing the decimal values works.

Brad

SQLite doesnt support collating blobs.  Blobs are always collated in a byte-by-byte comparison order. Decimal has a method GetBits which returns the binary representation of the decimal.  Unfortunatly you would have to reverse the order of the first 3 elements of the array it returns in order for sqlite to sort it properly.  Maybe a DecimalFormat=Double/Binary connection string parameter is in order?