Problem about SQLite's integer and int datatype

Question

I meet a problem about: Object of type 'System.Int64' cannot be converted to type 'System.Int32' when run the SubSonic.Examples.SimpleRepo project base on SQLite provider.

I fond that the data type for table Categories's column CategoryID is 'integer', and the 'integer' in SQLite will be return as Int64, in the same time the CategoryID's data type in the Class Category is int, the above error is happened.

I checked the SubSonic's source code: \SubSonic.Core\SQLGeneration\Schema\SQLiteSchema.cs and find the following codes:

else if (column.IsPrimaryKey && column.DataType == DbType.Int32
    || column.IsPrimaryKey && column.DataType == DbType.Int16
    || column.IsPrimaryKey && column.DataType == DbType.Int64
    )
    sb.Append(" integer ");

Who can tell me the purpose for these codes? How to solve the data type convert error?

Freeholder

Answer 1

Funny I just read the sqlite3 documentation on this about an hour ago. So you're in luck :)

See the doc yourself (Scroll to the bottom, 64-bit ROWIDs section).

Here's the excerpt:

To minimize storage space, the 64-bit rowid is stored as a variable length integer. Rowids between 0 and 127 use only a single byte. Rowids between 0 and 16383 use just 2 bytes. Up to 2097152 uses three bytes. And so forth. Negative rowids are allowed but they always use nine bytes of storage and so their use is discouraged. When rowids are generated automatically by SQLite, they will always be non-negative.

3rd party edit

I was suprised that the Id-column despite being created as an int column was mapped to long as well

CREATE TABLE "Example" (
    "Id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    "Foo" TEXT NULL,
    "SomeDate" DATETIME NULL
)  

the explanation is also in the 64-bit ROWID section

Every row of a table has a unique rowid. If the table defines a column with the type "INTEGER PRIMARY KEY" then that column becomes an alias for the rowid. But with or without an INTEGER PRIMARY KEY column, every row still has a rowid.

In SQLite version 3.0, the rowid is a 64-bit signed integer.

Scalene

Many thanks for your reply, I think I need read the SQLite document in detail. – Freeholder

Answer 2

The PrimaryKey is set to int 64 (long) in SQLite - so you need to set yours to long as well.

Colorless

It seems using long is the only solution. – Freeholder

Answer 3

I just stammered through a merge procedure, taking a SQL Server DataTable and merging (upsert) it with a SQLite DataTable (c# .NET win forms app). You definitely need to set your integer PK columns in the SQL Server database to BIGINT to get that to fly. Otherwise, when you call merge on the SQLite DataTable, you get a type mismatch error.

Hypsometry