1

I am using Xamarin for Visual Studio 2013, and the SQLite-net PCL version 1.0.11 (https://www.nuget.org/packages/sqlite-net-pcl/).

Basically I am interested in storing a numeric value greater than 2,147,483,647 in a SQLite table where the field/column has a [PrimaryKey, AutoIncrement] decoration of a class via ORM.

The below class does create the table, and inserting values (rows) works fine:

public class Patient
{
    [PrimaryKey, AutoIncrement, Column("PATIENT_NBR")]
    public int PatientNumber { get; set; }
    ...
}

The two classes below do not create the table when using "long" or "Int64" in conjunction with PrimaryKey and AutoIncrement:

public class Patient
{
    [PrimaryKey, AutoIncrement, Column("PATIENT_NBR")]
    public long PatientNumber { get; set; }
    ...
}

or

public class Patient
{
    [PrimaryKey, AutoIncrement, Column("PATIENT_NBR")]
    public Int64 PatientNumber { get; set; }
    ...
}

In the last two examples above - when running the CreateTable:

public class CreateDatabase
{
    private readonly SQLiteConnection conn;
    public CreateDatabase()
    {
        try
        {
            conn = DependencyService.Get<ISQLite>().GetConnection(Constants.DatabaseName);
            conn.CreateTable<Patient>();

            if (conn.Table<Patient>().Count() == 0)
            {
                conn.Insert(new Patient { PatientCreateDate = DateTime.UtcNow, PatientFirstName = "John", PatientLastName = "Doe" });
                conn.Insert(new Patient { PatientCreateDate = DateTime.UtcNow, PatientFirstName = "Jane", PatientLastName = "Doe" });
            }
        }
        catch (Exception ex)
        {
            throw new Exception(string.Format("Create SQLite Connection. Error: {0}.", ex.Message));
        }
    }
}

I receive the exception: ex.Message: "AUTOINCREMENT is only allowed on an INTEGER PRIMARY KEY"

If I remove AutoIncrement from the decoration of the PatientNumber, it will create the table fine, but then I run into a constraint error as the value being inserted into PatientNumber always 0 (since AutoIncrement is not being used), and the field is defined as a PrimaryKey and has to be unique.

Based on the Issues opened for SQLite-net:

https://github.com/praeclarum/sqlite-net/issues/227

it makes reference to being resoved on the below:

https://github.com/praeclarum/sqlite-net/pull/345

(sorry about the two links above - it wouldn't let me post them)

In the last link above, it mentions a workaround of:

conn.ExtraTypeMappings.Add(typeof(int64), "integer")

But I am not sure of where to add this. I have tried adding this just below where "conn" is being assigned, but am getting compiler errors that: "SQLite.SQLiteConnection does not contain a definition for "ExtraTypeMapping".

My understanding is that although SQLite can store a numeric value up to 9223372036854775807, I would be restricted by the decoration of a field with a C# class of type "Int" or "Int32", as it's maximum value is 2,147,483,647.

Am I missing something, or have I misunderstood something??

Any help would be appreciated.

1 Answer 1

1

Are you mixing up which PCL you want? SQLite-net vs SQLite.net vs ? There are a ton of sqlite variants out there. ;-)

You are pulling the SQLite-net PC nuget and thus are getting SQLite-net PCL 1.0.11 that was release January 22, 2015.

The patch you reference was merged on March 4, 2015:

praeclarum merged commit 1638293 into praeclarum:master on Mar 4

If that is the library that you want, pull the master branch (head or just up to that commit) and build/use that instead the the Nuget.

Or to use the SqLiteConnection.ExtraTypeMappings, maybe you looking for oysteinkrog/SQLite.Net-PCL version?:

Line 94 of SqLiteConnection.cs

Sign up to request clarification or add additional context in comments.

1 Comment

thank you for your answer. I was using the wrong PCL.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.