Sqlite with c# unsigned long problem

abhinish

Hello every one, I am trying to store some Unsigned long values in sqlite database using the sqlite.data.dll in c# windows application i tried to take BigInt as datatype in database but it resulted overflow exception, the result was obvious unsigned long value was too big So I switched the data type to Numeric in database ... now data is inserting properly but when i try to retreive the value back using sqlitedatareader the value is truncated I am totally lost which data type should i use to store ulong value in sqlite database i dont want to take varchar datatype and if I use binary i'll not be able to query on table for ulong values Can any one please help me out what should i use

abhinav

Dimitri Witkowski

The problem is in SQLite sources: SQLiteStatement.BindParameter:

        case DbType.Int64:
        case DbType.UInt64:
            this.\_sql.Bind\_Int64(this, index, Convert.ToInt64(obj2, CultureInfo.CurrentCulture));
            return;

I dnt know any workaround but I'll try to find... Here's also uint64-related link: http://sqlite.phxsoftware.com/forums/t/904.aspx[^] =update= By the way I've found a workaround: INSERT query:

        using (var cmd = conn.CreateCommand()) {
            cmd.CommandText = "insert into tt (text, Value) values (@text, @tt)";
            cmd.Parameters.AddWithValue("@text", value.ToString());
            cmd.Parameters.AddWithValue("@tt", (long)value).DbType = DbType.UInt64;
            cmd.ExecuteNonQuery();
        }

SELECT query:

        using (var cmd = conn.CreateCommand()) {
            cmd.CommandText = "select Value from tt order by Value desc";
            var reader = cmd.ExecuteReader();
            while (reader.Read()) {
                long l = (long) reader\[0\];
                Console.WriteLine(\*((ulong\*)&l));
            }
            reader.Close();
            cmd.ExecuteScalar();
        }

SQLite query for table creation:

CREATE TABLE "tt" (
"ID" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
"Text" TEXT NOT NULL,
"Value" UNSIGNED BIG INT NOT NULL
)

Die Energie der Welt ist konstant. Die Entropie der Welt strebt einem Maximum zu.

abhinish

Hello Dmitry, First of all thanks a lot for your quick reply this work around works great and the value is storing and retreiving properly :) but now the problem is I cant query the the 'value' field of 'tt' table

select * from tt where value = 18446744073709551614

this does not work can you please help me. thanks a lot again for your help

abhinav

Dimitri Witkowski

Yeah, you should always pass ulong value as parameter, not as a string inside a query:

using (var cmd = conn.CreateCommand()) {
cmd.CommandText = "select * from tt where value = @val";
cmd.Parameters.AddWithValue("@val", (long)18446744073709551614ul).DbType = DbType.UInt64;
var reader = cmd.ExecuteReader();
...
}

Die Energie der Welt ist konstant. Die Entropie der Welt strebt einem Maximum zu.

abhinish

thanks Dmitry :) that did the trick

abhinav