How to insert and retrieve UInt64 value in sqlite

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for SQLite
Post Reply
abhinav
Posts: 4
Joined: Sat 06 Feb 2010 07:17

How to insert and retrieve UInt64 value in sqlite

Post by abhinav » Sat 06 Feb 2010 08:36

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

StanislavK
Devart Team
Posts: 1710
Joined: Thu 03 Dec 2009 10:48

Post by StanislavK » Mon 08 Feb 2010 12:15

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.

abhinav
Posts: 4
Joined: Sat 06 Feb 2010 07:17

Post by abhinav » Mon 08 Feb 2010 12:28

Hi StanislavK,

can u please give me an example how to use it with floating point
it would be a great help



regards
abhinav gupta

StanislavK
Devart Team
Posts: 1710
Joined: Thu 03 Dec 2009 10:48

Post by StanislavK » Mon 08 Feb 2010 14:17

For example, create the following table:

Code: Select all

CREATE TABLE [table1]
(
	[id] INTEGER PRIMARY KEY,
	[column] INTEGER
)
To read floating-point numbers from the table, you may use the following code:

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;
}
Note that SQLite will then allow you to insert processed double into the field of the integer type:

Code: Select all

value += UInt64.MaxValue;
com.CommandText = "insert into table1 values (20, :val)";
com.Parameters.Add("val", value);
com.ExecuteNonQuery();

abhinav
Posts: 4
Joined: Sat 06 Feb 2010 07:17

Post by abhinav » Mon 08 Feb 2010 15:38

Hi StanislavK,
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();
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

Code: Select all

Create  TABLE [test](
[Id] Integer PRIMARY KEY
,[Val] integer
   
);
what should i do

StanislavK
Devart Team
Posts: 1710
Joined: Thu 03 Dec 2009 10:48

Post by StanislavK » Mon 08 Feb 2010 16:18

Please try casting the parameter to double:

Code: Select all

comm.Parameters.Add("val", (double)value);
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.

abhinav
Posts: 4
Joined: Sat 06 Feb 2010 07:17

Post by abhinav » Wed 17 Feb 2010 13:45

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 :

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

StanislavK
Devart Team
Posts: 1710
Joined: Thu 03 Dec 2009 10:48

Post by StanislavK » Thu 18 Feb 2010 10:26

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.

Post Reply