[sqlite-dev] System.Data.SQLite.Linq UPDATE + returning sql error

[Den Pakizh]

Hello,

I'm getting error while modifying entity with a computed property:

SQL logic error or missing database
near "SELECT": syntax error

The problem is SQLite provider generates UPDATE command and then SELECT command without inserting semicolon in between. Here is my case:

UPDATE [transactions]
SET [Order_Status] = @p0
WHERE ([id] = @p1)     --   <-- here should be semicolon
SELECT [rec_created]
FROM [transactions]
WHERE last_rows_affected() > 0 AND [id] = @p1

It seems like a bug, doesn't it?

Looking at source code, I'd suggest to apply a fix to DmlSqlGenerator.GenerateUpdateSql method:

      ...

      // where c1 = ..., c2 = ...
      commandText.Append("WHERE ");
      tree.Predicate.Accept(translator);
      commandText.AppendLine(); //  <-- change to commandText.AppendLine(";");

      // generate returning sql
      GenerateReturningSql(commandText, tree, translator, tree.Returning);
     
       ...

Thanks,

Denis Pakizh

[Joe Mistachkin]

Den Pakizh wrote:
>
> It seems like a bug, doesn't it?
>
> Looking at source code, I'd suggest to apply a fix to
> DmlSqlGenerator.GenerateUpdateSql method:
>

Thanks for the report. Do you have example code that demonstrates
the issue, perhaps along with a minimal database schema?

--
Joe Mistachkin

_______________________________________________
sqlite-dev mailing list
sqlit...@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-dev

[Den Pakizh]

Hello Joe,

#Schema:

CREATE TABLE "transactions"
(
  "id" INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL  UNIQUE ,
  "flags" INTEGER DEFAULT 1 ,
  "rec_created"  DATETIME DEFAULT CURRENT_TIMESTAMP
)

-----

# Example code (I'm using EF6 with Code First approach)

using System;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using System.Data.Entity;

namespace SqliteBugRepro
{
    class Program
    {
        static void Main(string[] args)
        {
            Repro();
        }

        static void Repro()
        {
            using (MiniContext db = new MiniContext())
            {
                db.Database.Log = Console.Write;

                Transaction tran = db.Transactions.Add(new Transaction { Flags = 0 });
                db.SaveChanges(); // INSERT works

                tran.Flags = 3;
                db.SaveChanges(); // UPDATE fails. see console output for details
            }

        }
    }

    public class MiniContext : DbContext
    {
        public DbSet<Transaction> Transactions { get; set; }
    }

    [Table("transactions")]
    public class Transaction
    {
        [Key]
        [Column("id")]
        public long Id { get; set; }

        public int Flags { get; set; }

        [DatabaseGenerated(DatabaseGeneratedOption.Computed)]
        [Column("rec_created")]
        public DateTime Rec_Created { get; set; }
    }
}

--
Denis Pakizh

[Joe Mistachkin]

Den Pakizh wrote:
>
> CREATE TABLE "transactions"
> (
> "id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE ,
> "flags" INTEGER DEFAULT 1 ,
> "rec_created" DATETIME DEFAULT CURRENT_TIMESTAMP
> )
>

Thanks. I've been able to successfully reproduce the issue. The fix,
along with a new test case, are now on trunk.