InsertOrReplaceWithChildren throws SQLiteException: near "where": syntax error

[Eino Gourdin]

SQLite.Net.SQLiteException: near "where": syntax error

Seems the objets are inserted, but I get this crash. If I replace the line by InsertOrReplaceAll, the app it doesn't crash.

I'm running a database.InsertOrReplaceAllWithChildren(AllCategories); With Category looking like this

[Table("Categories")]   
public class Category
{
    [PrimaryKey]
    public int Id { get; set;}

    [OneToMany(CascadeOperations = CascadeOperation.All)]
    public List<CategorySign> Signs { get; set;}

}

and sign like this : (I removed other info from the table, leaving only the keys, and I still get the crash)

[Table("CategorySigns")]
public class CategorySign
{
    [PrimaryKey]
    public int Id { get; set;}

    [ForeignKey(typeof(Category))]
    public int CategoryId { get; set; }

    [ManyToOne]
    public Category Category { get; set;}
}

Here's the complete crash trace :

SQLite.Net.SQLiteException: near "where": syntax error at SQLite.Net.Platform.XamarinAndroid.SQLiteApiAndroid.Prepare2 (IDbHandle db, System.String query) [0x00037] in <filename unknown>:0 at SQLite.Net.SQLiteCommand.Prepare () [0x0001c] in <filename unknown>:0 at SQLite.Net.SQLiteCommand.ExecuteNonQuery () [0x00016] in <filename unknown>:0 at SQLite.Net.SQLiteConnection.Execute (System.String query, System.Object[] args) [0x00044] in <filename unknown>:0 at SQLite.Net.SQLiteConnection.Update (System.Object obj, System.Type objType) [0x00104] in <filename unknown>:0 at --- End of stack trace from previous location where exception was thrown --- at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw () [0x0000c] in /Users/builder/data/lanes/2923/52635947/source/mono/external/referencesource/mscorlib/system/runtime/exceptionservices/exceptionservicescommon.cs:143 at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess (System.Threading.Tasks.Task task) [0x00047] in /Users/builder/data/lanes/2923/52635947/source/mono/external/referencesource/mscorlib/system/runtime/compilerservices/TaskAwaiter.cs:187 at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification (System.Threading.Tasks.Task task) [0x0002e] in /Users/builder/data/lanes/2923/52635947/source/mono/external/referencesource/mscorlib/system/runtime/compilerservices/TaskAwaiter.cs:156 at System.Runtime.CompilerServices.TaskAwaiter.ValidateEnd (System.Threading.Tasks.Task task) [0x0000b] in /Users/builder/data/lanes/2923/52635947/source/mono/external/referencesource/mscorlib/system/runtime/compilerservices/TaskAwaiter.cs:128 at System.Runtime.CompilerServices.TaskAwaiter`1[TResult].GetResult () [0x00000] in /Users/builder/data/lanes/2923/52635947/source/mono/external/referencesource/mscorlib/system/runtime/compilerservices/TaskAwaiter.cs:357 at WheezMe.Droid.MainActivity+<launchStartupProcess>c__async0.MoveNext () [0x0008b] in /Users/eino/Prog/mobile/WheezMe/Droid/Activities/MainActivity.cs:70 at --- End of stack trace from previous location where exception was thrown --- at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw () [0x0000c] in /Users/builder/data/lanes/2923/52635947/source/mono/external/referencesource/mscorlib/system/runtime/exceptionservices/exceptionservicescommon.cs:143 at System.Runtime.CompilerServices.AsyncMethodBuilderCore.<ThrowAsync>m__0 (System.Object state) [0x00000] in /Users/builder/data/lanes/2923/52635947/source/mono/external/referencesource/mscorlib/system/runtime/compilerservices/AsyncMethodBuilder.cs:1018 at Android.App.SyncContext+<Post>c__AnonStorey0.<>m__0 () [0x00000] in /Users/builder/data/lanes/3340/4e275588/source/monodroid/src/Mono.Android/src/Android.App/SyncContext.cs:18 at Java.Lang.Thread+RunnableImplementor.Run () [0x0000b] in /Users/builder/data/lanes/3340/4e275588/source/monodroid/src/Mono.Android/src/Java.Lang/Thread.cs:36 at Java.Lang.IRunnableInvoker.n_Run (IntPtr jnienv, IntPtr native__this) [0x00009] in /Users/builder/data/lanes/3340/4e275588/source/monodroid/src/Mono.Android/platforms/android-23/src/generated/Java.Lang.IRunnable.cs:81 at at (wrapper dynamic-method) System.Object:3f62eb12-2bb5-4426-b62d-dc25e0b14550 (intptr,intptr)

[Guillermo Gutiérrez]

It looks that the error is inside the sqlite-net Update method. Sadly I have no control over that method.

Can you check if this reproduces your issue:

#!

var category = new Category();
database.Insert(category);
database.Update(category);

var categorySign = new CategorySign();
database.Insert(categorySign);
database.Update(categorySign);

And if it crashes, in which line?

[Eino Gourdin]

Indeed, it crashes on the database.Update(category); line, with the same error.

[Guillermo Gutiérrez]

Try adding any other field to Category. I've seen that crash on classes that only have Id and no other property.

[Guillermo Gutiérrez]

Duplicate of #91.

[Eino Gourdin]

You're right, it was the problem. Thank you a lot !

[Eino Gourdin]

Bug in sqlite-net Update method ; solved by adding a superfluous column.

[Eino Gourdin]

Duplicate of #91.