SQLite ADO .NET and ExecuteScalar()

Question

Hello.

In SQLite ADO .Net (sqlite.phxsoftware.com) documentation for ExecuteScalar() query method reads: "Execute the command and return the first column of the first row of the resultset (if present), or null if no resultset was returned.". I have created a table:

create table users ( id integer primary key, name text )

And executed a queue with ExecuteScalar():

select ( id ) from users where name = @name

But, very strange - i can't cast the retur value to 'int', only to 'long'! Why is such, the 'id' field is defined as 'integer' in database, not 'bigint'?

Answer 1

I'm not familiar with SQLite so can't comment on it's behaviour, but this should work as long as the value fits in 32 bits:

Convert.ToInt32(...)

Answer 2

This is because SQLite "INTEGER PRIMARY KEYS" are not actually integers, but longs (64 bit integers). From the documentation:

Every row of every SQLite table has a 64-bit signed integer key that is unique within the same table. This integer is usually called the "rowid". The rowid is the actual key used in the B-Tree that implements an SQLite table. Rows are stored in rowid order. The rowid value can be accessed using one of the special names "ROWID", "OID", or "ROWID".

If a column is declared to be an INTEGER PRIMARY KEY, then that column is not a "real" database column but instead becomes an alias for the rowid. Unlike normal SQLite columns, the rowid must be a non-NULL integer value. The rowid is not able to hold floating point values, strings, BLOBs, or NULLs.

An INTEGER PRIMARY KEY column is an alias for the 64-bit signed integer rowid.

This is a side effect of SQLite's dynamic typing mechanism.

In practice, to answer your question, you should retrieve the value as long and work with it as such if possible, using only the casting operators or the Convert class if the value fits in 32 bits. Because it is precisely an id, you shouldn't need to convert or cast it to a 32 bits integer.