System.Data.SQLite
View Ticket
Not logged in
Ticket UUID: 53633bbe397e27737c0d9b6175191cd918210366
Title: DetectTextAffinity/DetectStringType bug
Status: Closed Type: Incident
Severity: Important Priority: Medium
Subsystem: Convert Resolution: Fix_Already_Pending_Release
Last Modified: 2016-03-30 20:47:18
Version Found In: 1.0.99.0
User Comments:
anonymous added on 2016-03-21 04:19:58: (text/x-fossil-plain)
This issue is related to issue 3c00ec5b521b71073b7417d12daddb2c509e5691.

- Using environment vars to set the flags DetectTextAffinity/DetectStringType and DefaultDbType=String;DefaultTypeName=TEXT to generate an EF6 model for an existing database where fields are typeless.
- Model generation will succeed.
- Querying the model will fail if all data in a typeless field is of type Double.

This is due to SQLite3.ColumnAffinity returning TypeAffinity 'Double' for the query statement which SQLiteDataReader.GetSQLiteType/SQLiteDataReader.VerifyType fails to map to the .net string-type on the generated model even though the environment flags are set.


Failing test:
https://dl.dropboxusercontent.com/u/6420016/UnitTestProject1.zip

My environment:
Windows 8 x64
Visual Studio 2015
.NET 4.6
SQLite 1.0.99.0
EntityFramework 6

mistachkin added on 2016-03-21 15:29:30: (text/x-fossil-plain)
Do you have an example schema and query that demonstrates the issue?

mistachkin added on 2016-03-21 16:20:19: (text/x-fossil-plain)
So far, I'm unable to reproduce the issue here.  I've added a preliminary test
case on a branch, here [a5653a06740a80c3].

Any additional information you could provide would be very helpful.

anonymous added on 2016-03-21 16:28:50: (text/x-fossil-plain)
Yes, there is a .sqlite with a table and some sample data in the failing test solution I created for you.
https://dl.dropboxusercontent.com/u/6420016/UnitTestProject1.zip

Here is the schema and sample data if you want it separately
http://pastebin.com/mGjjrDi6

Failing test
http://pastebin.com/rz7JYybk

mistachkin added on 2016-03-21 17:17:41: (text/x-fossil-plain)
Thanks, sorry I didn't notice the DropBox URL before.

In the unit test project, where do you set the environment variables and
connection flags?

mistachkin added on 2016-03-21 17:18:53: (text/x-fossil-plain)
Also related to [dbd65441a5].

anonymous added on 2016-03-21 17:33:07: (text/x-fossil-plain)
They are set as environment vars in windows.

I set them on the connection string using the advanced settings for the data connection in VS but it did not seem to work when generating the edmx model.

Also tried setting them as appSettings in app.config but test still fails.

anonymous added on 2016-03-21 17:41:17: (text/x-fossil-plain)
Noticed in testing that the schema can be as simple as this
CREATE TABLE "a" ("x"  NOT NULL  DEFAULT (-1) )
INSERT INTO "a" VALUES(1);

Value can be any value type that is not implicitly convertible to string.

mistachkin added on 2016-03-21 18:08:58: (text/x-fossil-plain)
Since SQLiteDataReader.GetString is being called from within the EntityFramework
itself, it would appear the best solution would be to use trunk (or 1.0.100.0
when it comes out) and the new NoVerifyTextAffinity connection flag.

mistachkin added on 2016-03-21 18:13:33: (text/x-fossil-plain)
It should also be noted that your test project seemingly fails in the exact same
way whether or not the environment variables are set.

Using the new NoVerifyTextAffinity connection flag might really be the best
option.  I'm going to try it locally.

mistachkin added on 2016-03-21 19:07:19: (text/x-fossil-plain)
Confirmed, the new NoVerifyTextAffinity flag (on trunk) makes it work.  Using
the following before "using (var db = new testEntities())" in the test project
causes the test to pass:

Environment.SetEnvironmentVariable("DefaultFlags_SQLiteConnection",
    "DetectTextAffinity, DetectStringType, NoVerifyTextAffinity");

Environment.SetEnvironmentVariable("Use_SQLiteConvert_DefaultDbType",
    "String");

Environment.SetEnvironmentVariable("Use_SQLiteConvert_DefaultTypeName",
    "TEXT");

anonymous added on 2016-03-22 01:12:44: (text/x-fossil-plain)
Seems to work now, thanks!

Having trouble building 1.0.100.0 and end up getting this error every time I run the application (an assert pops up):
http://i.imgur.com/j00AaMo.png

Are there any nightly builds available? If not; any idea what the problem with my local build is?

I'm building from trunk using SQLite.NET.2015.sln in Debug/Mixed configuration.

anonymous added on 2016-03-22 01:39:59: (text/x-fossil-plain)
Same problem when building in ReleaseNativeOnly, Win32/x64 per the wiki FAQ.

mistachkin added on 2016-03-22 01:46:17: (text/x-fossil-plain)
Is it possible that it's picking up an outdated "SQLite.Interop.dll" along the PATH?

Alternatively, is it possible that it's picking up one that is not the Debug
build configuration (i.e. by default, the INTEROP_LOG feature is only enabled
for debug builds)?

anonymous added on 2016-03-22 08:04:12: (text/x-fossil-plain)
I have verified that it is picking up the correct dlls from my project with Process Explorer.

How would you normally build it using the batch scripts in setup?

mistachkin added on 2016-03-22 13:34:37: (text/x-fossil-plain)
First:

build.bat DebugManagedOnly

Then, for 32-bit Windows:

build.bat DebugNativeOnly Win32

-OR- For 64-bit Windows:

build.bat DebugNativeOnly x64

mistachkin added on 2016-03-22 14:45:21: (text/x-fossil-plain)
How are you building the project?  I'm trying to figure out how you compiled a
debug build without the INTEROP_LOG feature (and hence export) being available.

anonymous added on 2016-03-22 15:53:32: (text/x-fossil-plain)
My process was as follows:
- Download trunk
- Open SQLite.NET.2015.sln
- Switch configuration target to ReleaseNativeOnly and Win32/x64 respectively.
- Build the solution in Visual Studio
- Pick out the built assemblies using paths from .nuspec-files:
  ..\bin\2015\Release\bin\System.Data.SQLite.dll
  ..\bin\2015\Win32\ReleaseNativeOnlyStatic\SQLite.Interop.dll
  ..\bin\2015\x64\ReleaseNativeOnlyStatic\SQLite.Interop.dll
  ..\bin\2015\Release\bin\System.Data.SQLite.EF6.dll
  ..\bin\2015\Release\bin\System.Data.SQLite.Linq.dll

Will try the procedure you posted for building using the bat-files.

anonymous added on 2016-03-22 16:17:25: (text/x-fossil-plain)
Get the same error when building using the bat files. Verified that the correct dlls are loaded.

You don't happen to have a built version from trunk that I can try? .NET 4.6 32-bit windows