InsertAsync/SaveAsync is throwing SQLite syntax error

[simeoncode]

Hello, I've been struggling with an issue for a while and can't seem to move forward. Querying items in the database works fine, and so does ExecuteAsync for truncating a table. But when I reach InsertAsync or SaveAsync I get the error SQLite Error 1: 'near "OUTPUT": syntax error. I'm using .NET Core 2.1.0 on a Mac, with a SQLite database file. AsyncPoco version is 2.0.0 and the EF dependencies are 2.1.0.

I've created a repository which reproduces the error: https://github.com/storafrid/AsyncPoco-InsertAsync-issue (start debugging locally and visit e.g. /Home/CreateCat/Miranda). In the repo I also added an example for Entity Framework Core which successfully creates cats.

Here is the stack trace:

SqliteException: SQLite Error 1: 'near "OUTPUT": syntax error'. Microsoft.Data.Sqlite.SqliteException.ThrowExceptionForRC(int rc, sqlite3 db) Microsoft.Data.Sqlite.SqliteCommand.PrepareAndEnumerateStatements(Stopwatch timer)+MoveNext() Microsoft.Data.Sqlite.SqliteCommand.ExecuteReader(CommandBehavior behavior) Microsoft.Data.Sqlite.SqliteCommand.ExecuteScalar() System.Data.Common.DbCommand.ExecuteScalarAsync(CancellationToken cancellationToken) AsyncPoco.Database.ExecuteScalarHelperAsync(DbCommand cmd) AsyncPoco.Database.InsertAsync(string tableName, string primaryKeyName, bool autoIncrement, object poco) cats.DAL.Repository.SaveCats(List newCats) in Repository.cs var insertedCat = await db.InsertAsync("Cats", "ID", true, cat);

[ashleynexus]

I am getting the same issue with SqLite SaveAsync while I can update rows I cannot create new ones.

Using AsyncPoco 2.0.0

Microsoft.Data.Sqlite.SqliteException (0x80004005): SQLite Error 1: 'near "OUTPUT": syntax error'. at Microsoft.Data.Sqlite.SqliteException.ThrowExceptionForRC(Int32 rc, sqlite3 db) at Microsoft.Data.Sqlite.SqliteCommand.ExecuteReader(CommandBehavior behavior) at Microsoft.Data.Sqlite.SqliteCommand.ExecuteReader() at Microsoft.Data.Sqlite.SqliteCommand.ExecuteScalar() at System.Data.Common.DbCommand.ExecuteScalarAsync(CancellationToken cancellationToken) --- End of stack trace from previous location where exception was thrown --- at AsyncPoco.Database.ExecuteScalarHelperAsync(DbCommand cmd) at AsyncPoco.Database.InsertAsync(String tableName, String primaryKeyName, Boolean autoIncrement, Object poco) at [removed]cs:line 43 at System.Runtime.CompilerServices.AsyncMethodBuilderCore.<>c.b__7_1(Object state) at System.Threading.QueueUserWorkItemCallback.<>c.<.cctor>b__6_0(Object state) at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state) --- End of stack trace from previous location where exception was thrown --- at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state) at System.Threading.QueueUserWorkItemCallback.ExecuteWorkItem() at System.Threading.ThreadPoolWorkQueue.Dispatch() at System.Threading._ThreadPoolWaitCallback.PerformWaitCallback()

[d-p-y]

It is caused by wrong detection of Microsoft.Data.SQLite. I fixed it in PR https://github.com/tmenier/AsyncPoco/pull/50

[ashleynexus]

Thank you for identifying the error I was about to switch to NPoco so you have saved me so much time as I do not have the dependancies for AsyncPoco on my work Mac :( . I Proposed changing to case insensitive comparisons to prevent any future issues as well.

[tmenier]

Thanks for identifying the issue and sorry for the delay. #50 didn't go quite far enough and #51 went a little too far and broke some tests. I pushed a compromise, hopefully 3rd time's a charm. :)

This is in 2.0.1, which is now released to NuGet.

[d-p-y]

Thank you for such a quick release. My fix was conservative. Yours is much better. Thanks!