Problem with flag BindAndGetAllAsText in connection string

Hi

While using "BindAllAsText" flag in connecting string I have problems with retrieving data:

"Mindscape.LightSpeed.LightSpeedException : Unable to materialize field [BoolProperty] on type [Mindscape.LightSpeed.Tests.LSModel.EntA]: field is type 'System.Boolean' but database returned type 'System.String'. Check your table has an Id column and that your mappings are correct. See inner exception for details."

Look at test "DoublescanstoreNanandInfValues", please.

Best Regards, Marcin Szotka, InstalSoft

Thanks for reporting this. I will have a look into a fix and let you know once we have an update available in the nightlies for this.

Jeremy

We have added in a fix for this and this will be available in tonights nightly build.

Jeremy

Hi

Thank you for help. I still have some problem with reading just saved database:

"Unable to materialize field [Date] on type [InstalSoft.DataModel.Part.DataAndRel.Records.ProjectRootPartsInitializedItem]: field is type 'System.DateTime' but database returned type 'System.String'. Check your table has an Id column and that your mappings are correct. See inner exception for details."

I attached new example, look at test "BindAndGetAllAsTextProblem", please. In this case I have problem with DateTime type. I did not check other types.

Best regards Marcin Szotka, InstalSoft

Thanks, DateTime was being handled slightly differently to everything else so Ive added in a change to bring this inline with the other data types and this will be available in tonights nightly build.

Jeremy

Hi

DateTime now works OK but there is new problem (before this build it works fine) with double.

Change last test, please:

  public void BindAndGetAllAsTextProblem()
  {
     var fn = Path.Combine(Path.GetTempPath(), FileNamePrefix + "BindAndGetAllAsTextProblem.db3");
     if (File.Exists(fn)) File.Delete(fn);
     var context = DBCreate.SetupDB(fn);
     using (var uow = context.CreateUnitOfWork())
     {            
        var entH = new LSModel.EntityH();
        entH.DateTimeProperty = DateTime.Now;
        uow.Add(entH);

        var entA = new LSModel.EntA();
        entA.DoubleProperty = 10.0;
        entA.EntityProperty1 = "";
        uow.Add(entA);

        uow.SaveChanges();
     }

     context = DBCreate.SetupDB(fn);
     using (var uow = context.CreateUnitOfWork())
     {
        var entH = uow.EntityHs.First();
        var entA = uow.EntAs.First();
     }
     File.Delete(fn);         
  }

"Mindscape.LightSpeed.LightSpeedException : Unable to materialize field [DoubleProperty] on type [Mindscape.LightSpeed.Tests.LSModel.EntA]. Check your table has an Id column and that your mappings are correct. See inner exception for details. "

Best Regards, Marcin Szotka - InstalSoft

That test is passing for me, can you post the stack trace details for that exception?

Jeremy

Hi

I checked it also on the nightly build 05 11 2013 and the stack trace is:

Mindscape.LightSpeed.LightSpeedException : Unable to materialize field [DoubleProperty] on type [Mindscape.LightSpeed.Tests.LSModel.EntA]. Check your table has an Id column and that your mappings are correct. See inner exception for details. ----> System.FormatException : Input string was not in a correct format. at Mindscape.LightSpeed.Model.FieldModel.Materialize(LightSpeedContext context, IDataProviderAdapter dataProviderAdapter, Object target, Object[] values, ref Int32 index) at Mindscape.LightSpeed.Mapping.EntityLoader.ˆ(UnitOfWorkBase , String , Entity , Object[] ,  , Boolean ) at Mindscape.LightSpeed.Mapping.EntityLoader.ˆ(AliasedTypeModel , Object , Boolean , Boolean , Boolean& ) at ..ˆ(AliasedTypeModel , QueryExpression , Order , Group , String , Boolean , Boolean ) at .
.ˆ(LightSpeedContext , List1 , QueryExpression , Order , Group , String , Boolean , Boolean ) at ..ˆ(AliasedTypeModel , IList , String , Boolean ) at ..ˆ(TypeModel , Query , IList ) at ..ˆ(Query , TypeModel , IList ) at ..ˆ(Query , IList ) at Mindscape.LightSpeed.UnitOfWork.Find(Query query, IList results) at Mindscape.LightSpeed.UnitOfWorkBase.Find(Query query) at Mindscape.LightSpeed.Linq.Plan.SingleQueryPlan.ExecuteImmediate(IUnitOfWork unitOfWork, Type returnType) at Mindscape.LightSpeed.Linq.LinqQueryProvider.Execute(Expression expression) at Mindscape.LightSpeed.Linq.LinqQueryProvider.System.Linq.IQueryProvider.Execute(Expression expression) at System.Linq.Queryable.First(IQueryable1 source) at Mindscape.LightSpeed.Tests.LightspeedTests1.BindAndGetAllAsTextProblem() in LightSpeedTests1.cs: line 470 --FormatException at System.Number.ParseDouble(String value, NumberStyles options, NumberFormatInfo numfmt) at ..GetDouble(Object[], Int32) at Mindscape.LightSpeed.Model.FieldModel.<>cDisplayClass95.b63(IDataProviderAdapter dataProviderAdapter, Object target, Object[] values, Int32 index) at Mindscape.LightSpeed.Model.FieldModel.Materialize(LightSpeedContext context, IDataProviderAdapter dataProviderAdapter, Object target, Object[] values, ref Int32 index)

What is the data which is being parsed? The exception would seem to indicate its not a valid string representation of a double.

Jeremy

Exactly, it failed on DoubleProperty (line "var entA = uow.EntAs.First();"). I am sure that it works before. Meybe it's connected with regional settings? After changing OS I use Win 8 EN with polish regional settings.

Yep, sounds likely.

Is the decimal place separator a comma under those settings (and is the value being stored with a dot separator?). We are performing the parse using the CultureInfo of executing program - what is the value of CultureInfo.CurrentCulture.DisplayName just prior to that call? Im guessing it should be Polish in which case Im assuming its being stored incorrectly.

Jeremy

Yes, polish decimal place separator is comma.

CultureInfo.CurrentCulture.DisplayName returns "Polish (Poland)"

Marcin

Can you check that the underlying stored value is using a dot for the separator? If so then presumably there is an issue in how System.Data.SQLite is converting these into a text representation (e.g. not using the active culture). If its using a fixed culture then we could look at forcing that in our parsing code on the unbinding side as an alternative.

Jeremy

Hi

I made debug of InsertNanusingADONET test (from this example) and doubles are stored with invariant culture (1.0, 3.14 ...)

Marcin

Ok thanks for confirming this. Ill switch this to Invariant at our end.

Jeremy

Hi

Is there available in current Nightly Builds?

Best Regard Marcin Szotka, InstalSoft

Hi

Is there available in current Nightly Builds?

Best Regard Marcin Szotka, InstalSoft

Yes, this has been switched to InvariantCulture in the recent builds.

Jeremy

Hi

Test DoublescanstoreNanandInfValues still failed.

Mindscape.LightSpeed.LightSpeedException : Unable to materialize field [DoubleProperty] on type [Mindscape.LightSpeed.Tests.LSModel.EntA]. Check your table has an Id column and that your mappings are correct. See inner exception for details.

I use English version OS (Win 8.1 EN) with region settings PL - you could easily emulate it.

Best Regards - Marcin Szotka, InstalSoft

Thanks, Ill have to set up a new VM to set up this environment so I will let you know once Ive been able to do this and repro the problem.

Jeremy

Ive set up a Win 8.1 VM with PL region/language set but the test is passing for me. Ive tried a few different values for the DoubleProperty but am not seeing any failures - just to recheck we are testing the same thing could you resend the test method defintion? Im using the one as per the most recent attachment above, but am wondering if perhaps something has changed.

Thanks!
Jeremy

Hi

I have checked LightSpeed.zip from this thread and I use the same code. Maybe there are differences in settings, look at screen shots please. I attached also a bit newer version of test - in this case problems are not only with +infinity but also with 1.2 value.

Best Regards Marcin Szotka, InstalSoft

Thanks, the updated code produces the same result you are seeing so Im investigating this now.

Jeremy

The problem comes from using the Invariant culture when parsing the string representation of the double being passed up from SQLite (e.g. 1,2 is being returned, so using InvariantCulture this gets parsed as 12 where it should be 1.2 which is what is returned using CurrentCulture) - however this stems from the changes above. Thoughts?

Jeremy

Hi

It seems very strange. Could you look at test InsertNanusingADONET, please? It is simulations of this situation, maybe are there some differences I don't see? In this case all works fine. Does this test "create table" statement fits SQL generated by migration "this.AddColumn("EntA", null, "DoubleProperty", ModelDataType.Double, false);"?

Best regards, Marcin Szotka, InstalSoft

The difference is that in that test you are inserting the raw value specified as "1.2" however when the value is being passed as a parameter the SQLite provider is converting it to the text value of "1,2".

e.g. Change the insert part of the test to:

// Create and execute the DbCommand.
int rows;
using (SQLiteCommand command = connection.CreateCommand())
{
  command.CommandText =
  "INSERT INTO EntA (Id, EntityProperty1, DoubleProperty) VALUES (1, 'NaN', 'NaN'), (2, 'RegularDouble', @doubleProp), (3, 'PositiveInfinity', 'Infinity'), (4, 'NegativeInfinity', '-Infinity')";
  command.Parameters.AddWithValue("@doubleProp", (double)1.2);
  rows = command.ExecuteNonQuery();
}

Based on this it would seem that we will need to switch the parsing back to CultureInfo.CurrentCulture as thats whats being used when it gets converted initially. Its unfortunate though since CurrentCulture is going to depend on the caller so certainly prone to failure when parsing it back if there is a mismatch in decimal handling as we have seen.

Jeremy

Yes! I got this result. I will write post to SQLite company and hope that they solve my problem. If they will not fix it, the only solution would be change in your code such as:

command.Parameters.AddWithValue("@doubleProp", (double)1.2);

into

command.Parameters.AddWithValue("@doubleProp", 1.2.ToString(CultureInfo.InvariantCulture));

Now I'm waiting for SQLite answer.

Best Regards - Marcin Szotka

InstalSoft

Hi

We wrote two times to SQLite company about month ago and they did not reply. The our only hope is patch I mentioned in the previous post. Could you do it for us, please?

Best Regards - Marcin Szotka

InstalSoft

Ok Ill have a look at making an update along these lines and let you know once we have an update for the nightlies :)

Jeremy

Hi When will be available version with a patch ,we mentioned in post form Dec 20 2013?

Best Regards - Jakub Majnusz InstalSoft

Sorry, this fell of our radar. Ive made a change which should accomodate this and we should have a build available for testing tomorrow. Rather than merging this into the nightly I will make a custom build available to you for testing which includes the change so you can test this against your actual codebase rather than just the test project and we can then look at getting this merged in once you are happy with the change.

I expect this to be available tomorrow for you so I will update the post with more details then.

Jeremy

An updated set of the runtime components associated with this fix can be downloaded from: http://assets.mindscape.co.nz/Fixes/Runtime-Fix-1279686.zip

Let me know if you find any issues or if we need to make any additional changes.

Thanks!
Jeremy

Hi

It works, thank you. Now we have problem with test "RelationsmustbeaccessibleafterDBopen()" which fails on line " Assert.IsNotNull(parentEntity.SubR);" I suppose it is temporary and not connected with our patch?

Best Regards - Marcin Szotka InstalSoft

If there was an issue surfacing the FK property that may lead to the result you are seeing. Are you seeing this on any other relations (I would expect it to be all if it was a general problem).

Have a look into it there and let us know what you find.

Jeremy

You are right: all relations failed. I created three separated tests for relations:

RelationsmustbeaccessibleafterDBopen11 RelationsmustbeaccessibleafterDBopennm RelationsmustbeaccessibleafterDBopenn1

Details in attachment.

Best Regards - Marcin Szotka, InstalSoft

Thanks, this helped track down the problem which was an incompatibility where compiled queries were being used. We automatically generate these for FindById which is used for FK loading to improve performance. Ive added a fix for this and Ill update this with an updated set of binaries for you to re-test with once this is available.

Jeremy

Ive uploaded a new set of binaries here for you: http://assets.mindscape.co.nz/Fixes/Runtime-Fix-1279686-2.zip

Jeremy

Hi

Thank you. It is almost OK. All tests passed. But I found new problem: boolean data was stored in DB as "0", "1", now is stored as "True", "False".

Is it expected change? Do we have to write special migrations for elder file version?

Best Regard, Marcin Szotka - InstalSoft

Yes definitely, .ToString() will produce "True" and "False" accordingly so you will need to migrate any existing data.

Jeremy

OK, I managed it - my validations impact bools and doubles. Now I found new, but known problem:

Mindscape.LightSpeed.LightSpeedException : Unable to materialize field [ByteProperty] on type [Mindscape.LightSpeed.Tests.LSModel.EntA]: field is type 'System.Byte' but database returned type 'System.String'. Check your table has an Id column and that your mappings are correct. See inner exception for details.

I prepared it in tests, one of three failed tests is BindAndGetAllAsTextProblem.

Best Regards - Marcin Szotka

PS:

Maybe you could add a callback for validating/converting data? Then we have possibility to repair simple cases at once.

Thanks, Ill look into this and update with newer bits once these are available. Unfortunately we wont be able to expose a callback for this so we will just need to work through any issues identified.

Jeremy

Ive uploaded a new set of binaries here for you: http://assets.mindscape.co.nz/Fixes/Runtime-Fix-1279686-3.zip

Jeremy

I got next problem in SQLite so we had to change our architecture in order to avoid NaN in DB. Actually we don't use flag BindAllAsText in connection string. I apologize you for wasted time and really appreciate your help.

Best Regards, Marcin Szotka - InstalSoft