System.Data.SQLite
View Ticket
Not logged in
Ticket UUID: a0ed28d636b73aac870cbb2027f203990f4e0e9a
Title: Type mapping on NETCF 3.5 appears to be non-functional
Status: Closed Type: Incident
Severity: Important Priority: Medium
Subsystem: Convert Resolution: Works_As_Designed
Last Modified: 2015-11-23 03:41:09
Version Found In: 1.0.98.0
User Comments:
anonymous added on 2015-11-18 14:40:36:
I have a table in a database on a WinCE6 hanehld device.
We use NETCF 3.5
I was under the impression that INT mapped to Int32 by default, but everything was coming out as Int64s.
Upgraded to 1.0.98.0 to get access to SQLiteConnection.AddTypeMapping().
Changed the NewConnection() code to be:
        public DbConnection NewConnection()
        {
            SQLiteConnection output;

            output = new SQLiteConnection(ConnectionString);
            output.Flags |= SQLiteConnectionFlags.UseConnectionTypes;
            var ret = output.AddTypeMapping("INT", DbType.Int32, false);

            return output;
        }

However, all integer values coming back from the database are still Int64s.

mistachkin added on 2015-11-19 04:22:23:
Do the same mappings work when using the desktop framework?  Looking at the code and test
cases reveals that the custom mapping for "INT" should not even be needed.  However, if you
are using something else, e.g. "INTEGER", etc, then that would need to be overridden instead of
"INT".

What is the full schema of the table that exhibits the issue?

anonymous added on 2015-11-19 09:00:12:
Here's the DDL for the table.

CREATE TABLE [Observations] (
  [ObservationID] INTEGER PRIMARY KEY, 
  [VRM] TEXT, 
  [LocationID] INT REFERENCES [Locations]([LocationID]), 
  [ObservationDateTime] DATETIME, 
  [FrontNearsideValvePos] INT, 
  [RearNearsideValvePos] INT, 
  [FrontOffsideValvePos] INT, 
  [RearOffsideValvePos] INT, 
  [BlueBadge] BOOLEAN, 
  [BlueBadgeTime] DATETIME, 
  [LocationDescription] TEXT, 
  [Removed] BOOLEAN);

CREATE INDEX [idx_Observations] ON [Observations] ([VRM], [LocationID]);

I shall build a test rig for the desktop and create a simple test database to see if there's anything wrong with the instance of the database.

anonymous added on 2015-11-19 12:24:54:
Hi,

I've tracked down the issue and I don't thing it's an issue.

For any query of the format:

SELECT * FROM Observations

The INT fields return as Int32.

But, for the one query of this type:

SELECT Observations.* FROM Observations Join/Where/etc
UNION
SELECT Observations.* FROM Observations Different/Join/Where/etc

I get Int64 instead of Int32.

I guess this is the thing where you can't guarantee the types of calculated fields? I seem to remember reading that in the documentation, something to do with DefaultType property or the TYPES [int],,[etc]; SELECT UNION SELECT

mistachkin added on 2015-11-20 17:25:19:
Ok, that makes more sense.  Perhaps you can create a temporary view using the SQL
you need and make the type INT there?