Re: [sqlite] Problems with INT columns

[Simon Slavin]

On 29 Apr 2014, at 10:15am, Kleiner Werner  wrote:

> If I understand the SQLite Docu correct there is no difference between INT or 
> INTEGER, except if you use a column as Primary Autoincrement.
> I thought an Int column in SQLite is always 64bit integer, but why does the 
> SQLiteDatareader does it recognize as integer 32?

The problem is not inside SQLite itself.  As far as SQLite is concerned INT and 
INTEGER are treated identically.  You can verify this for yourself by doing

SELECT myColumn, typeof(myColumn) FROM myTable LIMIT 1

Both versions of your database should return exactly the string 'integer' for 
the type.  If they don't please post again because I'd love to see it.

The problem must lie in your interface to SQLite, which is probably 
SQLiteDatareader itself.  I suspect that the problem is somewhere in or around

private SQLiteType GetSQLiteType(int i)

which is on line 965 of



but I don't know c# well enough to get any closer.  From what I can see, 
though, it assumes that SQLite has column types which is, of course, not true.

> What is the difference if I declare a column as bigint or int?

In SQLite, none.  As you correctly understood from section 2.2 of



both pieces of text are actually interpreted as meaning "INTEGER".  However, it 
looks like SQLiteDataReader does things differently.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Problems with INT columns

[Kleiner Werner]

Hello

In our SQLite DB there are some tables with column type as "int" which contains 
values with 13 digits, like 189336960

It seems that the values are stored correct in SQLite table. But if our Windows 
Tool with the SQLite Datareader tries to select this value
it returns wrong values.

We tracked it down to the column being created as type INT instead of 
INTEGER.  SQLite treats these as the same, but System.Data.SQLite treats INT as 
Int32 and INTEGER as Int64 using SQLiteDatareader.

Changing the Columns in the affected table from INT to INTEGER the 
error go away. 

SQLiteDataReader reader = _sqlDbCommand.ExecuteReader();
int fieldCount = reader.FieldCount;

while (reader.Read())
{
for (int i = 0; i < fieldCount; i++)
{
 string name = reader.GetName(i);
 string value = reader.GetValue(i).ToString();
 string typeName = reader.GetDataTypeName(i);
 string typeName2 =   reader.GetProviderSpecificFieldType(i).ToString();
 .
 // INT is treated as Int32
 // INTEGER is treated as Int64
 .
}
}

Does someone has any idea why is this behavior?
If I understand the SQLite Docu correct there is no difference between INT or 
INTEGER, except if you use a column as Primary Autoincrement.
I thought an Int column in SQLite is always 64bit integer, but why does the 
SQLiteDatareader does it recognize as integer 32?

What is the difference if I declare a column as bigint or int?

best regards
Hans


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users