System.Data.SQLite

Login
This project makes use of Eagle, provided by Mistachkin Systems.
Eagle: Secure Software Automation
Ticket Hash: 4aeb674d18cadae1bc51092ddae56da8cbf69c20
Title: Can't insert negative number into table
Status: Closed Type: Incident
Severity: Important Priority: Medium
Subsystem: Data_Adapter Resolution: Not_A_Bug
Last Modified: 2014-01-02 05:53:41
Version Found In: 1.0.90.0
User Comments:
anonymous added on 2013-12-30 13:49:26: (text/x-fossil-plain)
When I updated my system.data.sqlite.dll from 3.63 to the latest one, I found I can't insert a negative number into my table as it used to be.

Error message:
System.OverflowException: Value was either too large or too small for a UInt64.
   at System.Data.Common.DbDataAdapter.UpdatedRowStatusErrors(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)
   at System.Data.Common.DbDataAdapter.UpdatedRowStatus(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)
   at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMapping tableMapping)
   at System.Data.Common.DbDataAdapter.UpdateFromDataTable(DataTable dataTable, DataTableMapping tableMapping)
   at System.Data.Common.DbDataAdapter.Update(DataTable dataTable)

My code looks like:

            SQLiteCommand cmd = myConn.CreateCommand();
            cmd.CommandTimeout = SqliteLib.defTimeout;
            cmd.CommandText = sql;
            SQLiteDataAdapter da = new SQLiteDataAdapter(cmd);

            SQLiteCommandBuilder cb = new SQLiteCommandBuilder(da);
            da.InsertCommand = cb.GetInsertCommand();
            da.DeleteCommand = cb.GetDeleteCommand();
            da.UpdateCommand = cb.GetUpdateCommand();

            DataSet ds = new DataSet();
            da.Fill(ds);
            DataTable dt = ds.Tables[0];
            dt.AcceptChanges();

            ... //insert a new record
            da.Update(dt); --> raise error here

I found it can insert positive numbers while can't insert negative ones. Please help to solve it ASAP, thanks!

mistachkin added on 2013-12-30 21:30:23: (text/x-fossil-plain)
Without seeing the schema of the database, it's very difficult to determine what
the exact problem is.  Are you able to share the database schema?

anonymous added on 2013-12-31 00:06:28:
The table schema is :

CREATE TABLE [ANA_CURSMH] (
  [UNIQUENO] INTEGER(20),	 
  [OrderNO] INTEGER,	 
  [RECMONTH] INTEGER,	 
  [RECTYPE] CHAR,	 
  [DATE] DATETIME,	 
  [TTLACCTIME] INTEGER,	 
  [DAYACCTIME] INTEGER,	 
  [AREAID] INTEGER,	 
  [LONG] INTEGER,	 
  [LAT] INTEGER,	 
  [ACCSTATUS] INTEGER,	 
  [LOCKSTATUS] INTEGER,	 
  CONSTRAINT [] PRIMARY KEY ([UNIQUENO],
	 [RECTYPE],
	 [RECMONTH]))
	 
	 I found that it can't insert data when the long & lat fields are negative, but it can work when the long and lat fields are positive

mistachkin added on 2013-12-31 01:35:28: (text/x-fossil-wiki)
Are you able to execute the following command directly (i.e. without using a data
adapter)?

<pre>
  INSERT INTO [ANA_CURSMH] (LONG, LAT) VALUES ( -1 ,-1 );
</pre>

mistachkin added on 2013-12-31 01:37:00: (text/x-fossil-plain)
I'm currently unable to reproduce this issue here locally.

Please feel free to add comments; however, please let me control the various
status field of the ticket.  Thanks.

mistachkin added on 2013-12-31 02:06:41: (text/x-fossil-plain)
Also, what is the CommandText property you are using?

I've now tried using a data adapter to perform an INSERT with negative values
here locally and it does not throw an exception.

anonymous added on 2013-12-31 12:59:48:
The number I am using is -1000000, could you please have a test, thanks a lot!

mistachkin added on 2013-12-31 21:57:03: (text/x-fossil-plain)
I've tried with much smaller (and bigger) numbers and it does not throw an
exception.  I have not seen any hint of the reported behavior.  Furthermore,
the type mapping for "INTEGER" is Int64, which is signed.  I have no idea how
the UInt64 type ended up getting used in your case.  My theory is that the .NET
Framework itself may be using UInt64 somewhere internally; however, I have not
seen that behavior either.

Are you using the standard System.Data.SQLite or a customized version?

Can you produce a self-contained test case that demonstrates the issue?

anonymous added on 2014-01-01 01:31:50:
Anyhow, thanks for your efforts & replies. The issue is really weired: it didn't raise error when the numbers were all positive but it recurred steadily when some fields were negative. Because I don't have the database on hand. I will try what you have suggested tomorrow and keep you updated.

mistachkin added on 2014-01-01 02:17:53: (text/x-fossil-plain)
If possible, the following information would make this easier to track down:

1. The complete schema of the database involved (or the database itself).

2. Minimal C# code that demonstrates the issue.

3. The SQL queries involved, along with the required sample data.

anonymous added on 2014-01-02 03:52:17:
Hi Sir,

I have attached a testBug project to this ticket, it contains not only the source, system.data.sqlite.dll and the database related. With them, you can reproduce the problem easily. Please help to fix it ASAP because my project is suspended now.

Another question here, every time, I have to use 'Anonymous' as user name when logining and subsequently I can't get your status & feedback directly(if my understanding is correct). Considering that I am an active user(already reported several bugs recently), could you assign me a formal user name? thanks in advance!

My email address is davidcdx@163.com. Looking forward to your update & reply!

anonymous added on 2014-01-02 05:25:59:
Sorry for the bothering.

This afternoon, I found the reason. It is an internal error, I should use DbType.Int64 instead of DbType.UInt64 when generating Update Command. Sorry for the inconvenience caused.

By the way, Still, Can I be a normal member of your forum instead one of "anonymous" users? Thanks~

mistachkin added on 2014-01-02 05:53:41: (text/x-fossil-plain)
Your analysis of the issue is correct, the Int64 type should be used by your code,
not the UInt64 type.  Closing this ticket.