How to insert and retrieve UInt64 value in sqlite
How to insert and retrieve UInt64 value in sqlite
Hello,
I m using you standard component dotconnect for connecting to sqlite
but i am having probelm in storing UINT64 value in database and retreive it
sample value is
16536831622220222229
which datatype should i use to insert this value
I dont want to use varchar as i it will affect the performance
Can u please help me with this Issue
I m using you standard component dotconnect for connecting to sqlite
but i am having probelm in storing UINT64 value in database and retreive it
sample value is
16536831622220222229
which datatype should i use to insert this value
I dont want to use varchar as i it will affect the performance
Can u please help me with this Issue
-
- Devart Team
- Posts: 1710
- Joined: Thu 03 Dec 2009 10:48
According to the official documentation, SQLite does not support unsigned 64-bit integers:
http://www.sqlite.org/datatype3.html
You may use either numbers with floating point or strings.
http://www.sqlite.org/datatype3.html
You may use either numbers with floating point or strings.
-
- Devart Team
- Posts: 1710
- Joined: Thu 03 Dec 2009 10:48
For example, create the following table:
To read floating-point numbers from the table, you may use the following code:
Note that SQLite will then allow you to insert processed double into the field of the integer type:
Code: Select all
CREATE TABLE [table1]
(
[id] INTEGER PRIMARY KEY,
[column] INTEGER
)
Code: Select all
SQLiteCommand com = new SQLiteCommand("Select * from table1", sqliteConnection1);
SQLiteDataReader reader = com.ExecuteReader();
while (reader.Read())
{
object o = r["column"];
double value = (double)o;
}
Code: Select all
value += UInt64.MaxValue;
com.CommandText = "insert into table1 values (20, :val)";
com.Parameters.Add("val", value);
com.ExecuteNonQuery();
Hi StanislavK,
Thanks for your quick response
when i tried to execute the sample provided by you which is as follows
I got the following exception
System.OverflowException was unhandled
Message="Value was either too large or too small for an Int64."
table structure was simple as u recommended
what should i do
Thanks for your quick response
when i tried to execute the sample provided by you which is as follows
Code: Select all
Devart.Data.SQLite.SQLiteConnection con = new Devart.Data.SQLite.SQLiteConnection(@"Data Source=C:\testsqlite\test;FailIfMissing=False;");
Devart.Data.SQLite.SQLiteCommand comm= new Devart.Data.SQLite.SQLiteCommand();
UInt64 value = UInt64.MaxValue;
comm.Connection = con;
con.Open();
comm.CommandText = "insert into test values (20, :val)";
comm.Parameters.Add("val",value);
comm.ExecuteNonQuery();
con.Close();
System.OverflowException was unhandled
Message="Value was either too large or too small for an Int64."
table structure was simple as u recommended
Code: Select all
Create TABLE [test](
[Id] Integer PRIMARY KEY
,[Val] integer
);
-
- Devart Team
- Posts: 1710
- Joined: Thu 03 Dec 2009 10:48
Please try casting the parameter to double:
As UInt64 is an integer type, it is binded to the system type corresponding to SQLite's Int64. As the value is too large, the binding fails. Passing the parameter as double results in casting it to the SQLite Numeric type.
Code: Select all
comm.Parameters.Add("val", (double)value);
Hi StanislavK,
Sorry for such late reply as I was out of town.
The way u sugessted inserts the value in database but when i try to
read it back from database and try to convert it to Uint64
I again get the overflow exception
Please help me out
here is the sample code :
UInt64 FinalVal = Convert.ToUInt64(value1);
this line throws Arithmetic operation resulted in an overflow.
regards
Ahbinav Gupta
Sorry for such late reply as I was out of town.
The way u sugessted inserts the value in database but when i try to
read it back from database and try to convert it to Uint64
I again get the overflow exception
Please help me out
here is the sample code :
Code: Select all
Devart.Data.SQLite.SQLiteConnection con = new Devart.Data.SQLite.SQLiteConnection(@"Data Source=C:\testsqlite\ASP;FailIfMissing=False;");
Devart.Data.SQLite.SQLiteCommand comm= new Devart.Data.SQLite.SQLiteCommand();
UInt64 value = UInt64.MaxValue;
comm.Connection = con;
con.Open();
comm.CommandText = "insert into test values (20, :val)";
comm.Parameters.Add("val",(Double)value);
comm.ExecuteNonQuery();
con.Close();
con.Open();
Devart.Data.SQLite.SQLiteCommand com1 = new Devart.Data.SQLite.SQLiteCommand("Select * from test", con);
Devart.Data.SQLite.SQLiteDataReader reader1 = com1.ExecuteReader();
while (reader1.Read())
{
object o = reader1["val"];
Double value1 = (Double)o;
UInt64 FinalVal = Convert.ToUInt64(value1);
}
con.Close();
UInt64 FinalVal = Convert.ToUInt64(value1);
this line throws Arithmetic operation resulted in an overflow.
regards
Ahbinav Gupta
-
- Devart Team
- Posts: 1710
- Joined: Thu 03 Dec 2009 10:48
SQLite does not support unsigned 64-bit integers, thus you can insert large integers as numbers with floating point or strings only. Once being formatted as double, the number may fail to be converted back into integer. We can only recommend you to treat the problem field as double in your code as well as in the database. Or, as SQLite is an open-source software, you may download its sources and add the support of unsigned integers manually.