Support for System.Data.SQLite: Different API type for int/integer columns

classic Classic list List threaded Threaded
4 messages Options
JP
Reply | Threaded
Open this post in threaded view
|

Support for System.Data.SQLite: Different API type for int/integer columns

JP
Hello,

I have encountered the same problem as this: https://stackoverflow.com/q/4925084

The answers don't explain why there is a bitness difference at run-time between the types retrieved from INT and INTEGER columns, and that's my question. From reading https://sqlite.org/datatype3.html I understand there should be no difference whatsoever between defining a column INT or INTEGER (other than whether a primary key may become a rowid alias).

I don't mean the bitness how the integers are stored in the disk database, but the values returned by the System.Data.SQLite API (in particular via a DataTable loaded by a SQLiteDataReader).

I have verified that declaring a column (which isn't any kind of key) as INT causes System.Data.SQLite to return Int32/int (possibly, depending on the value? Not sure); and declaring INTEGER causes the same value to be returned as Int64/long.

Can anyone explain this, or point to where this is actually documented, if I've missed it?

It's the first time I use this mailing list, after searching for the answer on sqlite.org and around the Web; I hope I haven't missed any RTFM, otherwise please let me know.

Thanks
Xavier Porras
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Support for System.Data.SQLite: Different API type for int/integer columns

Simon Slavin-3
On 24 Jan 2019, at 3:27pm, JP <[hidden email]> wrote:

> I have encountered the same problem as this: https://stackoverflow.com/q/4925084

What is returned by typeof(reader["myColumn"]) in each case ?

I suspect that you're getting an integer when the column is declared as INTEGER, but not when the column is declared as INT.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Support for System.Data.SQLite: Different API type for int/integer columns

Joe Mistachkin-3
In reply to this post by JP

JP wrote:
>
> The answers don't explain why there is a bitness difference at run-time
> between the types retrieved from INT and INTEGER columns, and that's my
> question. From reading https://sqlite.org/datatype3.html I understand
> there should be no difference whatsoever between defining a column INT
> or INTEGER (other than whether a primary key may become a rowid alias).
>

System.Data.SQLite maps type names to their associated types.  Mappings
can be overridden on a per-connection basis.  By default, INT maps to a
32-bit integer and INTEGER maps to a 64-bit integer, mostly for reasons
of backward compatibility.

In order to customize the mappings, the flag UseConnectionTypes should
be used when opening the connection -AND- the AddTypeMapping method is
used to override a built-in mapping of the same name (e.g. "INT").

--
Joe Mistachkin

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Support for System.Data.SQLite: Different API type for int/integer columns

Barry Smith
I use Convert.ToInt32 (or whatever else is appropriate) instead of direct
casts when reading from System.Data.SQLite... I use a very similar schema
in an SQLServer and SQLite database, and Convert seems to handle many of
the differences without requiring different code for each database engine.

On Mon, 28 Jan 2019 at 03:31, Joe Mistachkin <[hidden email]> wrote:

>
> JP wrote:
> >
> > The answers don't explain why there is a bitness difference at run-time
> > between the types retrieved from INT and INTEGER columns, and that's my
> > question. From reading https://sqlite.org/datatype3.html I understand
> > there should be no difference whatsoever between defining a column INT
> > or INTEGER (other than whether a primary key may become a rowid alias).
> >
>
> System.Data.SQLite maps type names to their associated types.  Mappings
> can be overridden on a per-connection basis.  By default, INT maps to a
> 32-bit integer and INTEGER maps to a 64-bit integer, mostly for reasons
> of backward compatibility.
>
> In order to customize the mappings, the flag UseConnectionTypes should
> be used when opening the connection -AND- the AddTypeMapping method is
> used to override a built-in mapping of the same name (e.g. "INT").
>
> --
> Joe Mistachkin
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users