Hello,

I have a few questions

1. What is the maximum integer value in SQLite
2. When I store a decimal with value 9999999999 it is stored correctly but when use static CloumnInt method in the sqlite3 library to get the value from an sqlite statement to a c# object it picks the type as an integer in the switch case and reduces the value to 1410065407 which is not even the int.MaxValue.

See code sample

    public static void Test(this SQLiteConnection connection)
    {
        SQLitePCL.sqlite3_stmt stQuery = SQLite3.Prepare2(connection.Handle, "select * from sometable");
        while (true)
        {
            var step = SQLite3.Step(stQuery);
            var colLength = SQLite3.ColumnCount(stQuery);
            if (step != SQLite3.Result.Row)
            {
                break;
            }

            var obj = new object[colLength];
            for (int i = 0; i < colLength; i++)
            {
                var colType = SQLite3.ColumnType(stQuery, i);
                switch (colType)
                {
                    case SQLite3.ColType.Float:
                        obj[i] = SQLite3.ColumnDouble(stQuery, i);
                        break;

                    case SQLite3.ColType.Integer:
                        obj[i] = SQLite3.ColumnInt(stQuery, i); //9999999999 
                        break;

                    case SQLite3.ColType.Null:
                        obj[i] = null;
                        break;
                }
            }
        }
    }

Is this a library issue or was it designed to be like this? And is there any work around other than making all my numeric entries to be floats.

Thank you for your help

Chibuikem.

9999999999 & 2147483648 = 1410065407

You've wrapped around 2^32 (maxint+1) several times coming back to your result.

columnInt() returns a 32-bit int (long) value, for which the maximum is 2147483648 (2.1 Billion) which is somewhat smaller than your 9.99 Billion.

What you probably want is: columnInt64() which returns, as the name suggests, a 64-bit int (long long) value, for which the maximum is several orders of magnitude more. Documentation here.

Thanks this provides clarity.

I did a work around with the columnInt64(), I added some logic to check the value type after parsing. Thanks.

Glad to hear.

As an aside - you can just always use the 64-bit API (columnInt64()) when retrieving any integer value, there is no need to sometimes use the other one.

Within the database itself, it makes sense to save space and thus, bits. On the other hand, within the application, there is hardly ever a need to use anything other than 64-bit integers on a 64-bit OS running on 64-bit hardware (99% of modern stacks).

Speaking about these 64bit systems specifically, 64-bit integers are native to the OS and registers, so passing them as parameters (or moving them to registers) and the like requires no conversion. In these cases it can actually be slower to use 32-bit ints[1]. They are also natively interchangeable with pointers and in general application terms, the size increase of the application itself and application memory footprint is, if not negligible, small enough to be irrelevant.

There's no good reason not to use Int64's throughout your application (outside of data structures).

[1]: Some older 64Bit CPUs still had 32Bit operations it would employ when only 32Bit ops were required, but I think the latest generation of CPUs all but dropped those completely. You can also test this easily to see if it holds for your platform and functions - simply change all long to longlong (or whatever 64bit Int is on your platform), including loop variables, and measure total time. Mileage may vary on higher-level / interpreted languages.