Damir's Corner.

Sqlite Only Executes the First Statement in a Command

Let's take a look at the following code snippet (for those of you who don't recognize the API, it's Sqlite MvvmCross plugin, a slightly modified fork of sqlite-net):

using (var connection = new MvxWpfSqLiteConnectionFactory().Create(_filename))
{
  connection.Execute(
    "CREATE TABLE [MyTable] ([Id] INTEGER NOT NULL PRIMARY KEY);" + 
    "INSERT INTO [MyTable] ([Id]) VALUES (1);");

  var count = connection.ExecuteScalar<int>(
    "SELECT COUNT(*) FROM [MyTable];");

  Console.Write(count);
}

What do you think, the result is? 1? Well, actually it's 0. And no exception is thrown, in case you were wondering. You can try it out yourself, if you don't believe me.

This got me curious and I took a closer a look at the sqlite-net code. This is SQLiteConnection.Execute implementation:

public int Execute(string query, params object[] args)
{
  var cmd = CreateCommand(query, args);

  if (TimeExecution)
  {
    if (_sw == null)
    {
      _sw = new Stopwatch();
    }
    _sw.Reset();
    _sw.Start();
  }

  var r = cmd.ExecuteNonQuery();

  if (TimeExecution)
  {
    _sw.Stop();
    _elapsedMilliseconds += _sw.ElapsedMilliseconds;
    Debug.WriteLine(string.Format("...");
  }

  return r;
}

The interesting stuff is in SQLiteCommand.ExecuteNonQuery:

public int ExecuteNonQuery()
{
  if (_conn.Trace)
  {
    Debug.WriteLine("Executing: " + this);
  }

  var r = SQLite3.Result.OK;
  var stmt = Prepare();
  r = SQLite3.Step(stmt);
  Finalize(stmt);
  if (r == SQLite3.Result.Done)
  {
    int rowsAffected = SQLite3.Changes(_conn.Handle);
    return rowsAffected;
  }
  else if (r == SQLite3.Result.Error)
  {
    string msg = SQLite3.GetErrmsg(_conn.Handle);
    throw SQLiteException.New(r, msg);
  }
  else
  {
    throw SQLiteException.New(r, r.ToString());
  }
}

And also in SQLiteCommand.Prepare:

Sqlite3Statement Prepare()
{
  var stmt = SQLite3.Prepare2(_conn.Handle, CommandText);
  BindAll(stmt);
  return stmt;
}

All of the code correctly checks the result codes and throws exceptions accordingly.

Although I haven't posted all relevant code here, I did review it, and the real origin of this behavior is elsewhere - in the native sqlite3.dll sqlite3_prepare_v2 method. Here's the relevant part of the documentation:

These routines only compile the first statement in zSql, so *pzTail is left pointing to what remains uncompiled.

Since sqlite-net doesn't do anything with the uncompiled tail, only the first statement in the command is actually executed. The remainder is silently ignored. In most cases you won't notice that when using sqlite-net. You will either use its micro ORM layer or execute individual statements. The only common exception that comes to mind, is trying to execute DDL or migration scripts which are typically multi statement batches.

To handle such cases, you will need to split the batches into single statements. Here's a quick sample of the idea:

var statements = script.Split(new[] { ';' }, 
    StringSplitOptions.RemoveEmptyEntries);
foreach (var statement in statements)
{
  connection.Execute(statement);
}

Don't use this code in production, though. It will only work as long as you don't have any semicolons inside your statements. If the batches are completely under you control, you might be able ensure that. Otherwise keep in mind that creating a bulletproof tokenizer for splitting batches into statements is not trivial and you're probably better off fixing sqlite-net to correctly execute all statements in the command. And while you're at it, create a pull request, so that others will benefit from your fix as well.