[LINUX] Reasons to use long type in SQLite3 (C # Mono.Data.Sqlite)

The contents that can be stored are the same regardless of the integer type (integer, int, long)

SQLite3 integer types (integer, int, long) are all Integer types. An example is shown below.

Shell command

cd /tmp
sqlite3 workdb.sqlite   #Enter this command from the terminal to launch the sqlite3 interpreter.
                        #workdb.sqlite is the file name. If it does not exist, a new one will be created.

#integer type,int type,Define a long type.
> create table t001 (i1 integer, i2 int, i3 long);
> insert into t001 values(1,2,3);
> insert into t001 values(123456789012345678,223456789012345678,323456789012345678);

#You can see that all items are of type integer.
> select typeof(i1), typeof(i2), typeof(i3) from t001;
integer|integer|integer
integer|integer|integer

#int type(4 bytes)You can see that it can be stored even if the value exceeds.
> select i1, i2, i3 from t001;
1|2|3
123456789012345678|223456789012345678|323456789012345678

So, when using the create table statement, I think it's better to use'integer'or the abbreviation'int' to specify an integer.

But if you're using a SQLite database from C #, that's a different story.

Below, I wrote a program to read SQLite3 data from C #. However, this program throws a System.OverflowException exception on the line adapter.Fill (dataset, "tmp") ;.

Program.cs

using System;
using System.Data;
using System.Xml;
using Mono.Data.Sqlite;

class MainClass
{
  public static void Main(string[] args)
  {
    SqliteConnection connection = new SqliteConnection("Data Source=/tmp/workdb.sqlite;Version=3;");
    connection.Open();

    string sql = "select * from t001;";
    using (SqliteDataAdapter adapter = new SqliteDataAdapter(sql, connection))
    using (DataSet dataset = new DataSet()) {
      adapter.Fill(dataset, "tmp");
      DataTable table = dataset.Tables["tmp"];
      foreach (DataRow row in table.Rows) {
        Console.WriteLine(row["i1"].ToString() + " " + row["i2"].ToString() + " " + row["i3"].ToString());
      }
    }
    connection.Close();
  }
}

Why the exception was thrown

This is because the value 223456789012345678 did not fit in the C # int (Value was either too large or too small for an Int 32.) because it was defined as'i2 int' in the create table statement.

Precautions when using with C # (Mono.Data.Sqlite)

・ Int is not an abbreviation for integer. -C # (Mono.Data.Sqlite) recognizes integer as a long type, but C # programmers do not recognize it as integer = long type. -When using C # (Mono.Data.Sqlite), it is best to use int and long as the type name used in the create table statement.

at the end

The SQLite documentation does not specify that long can be used in the type name of the create table statement. Rather, SQLite3 doesn't recognize long types. You need'int'or'long' when Mono.Data.Sqlite determines the type.