[sqlite] .NET Linux problem with Microsoft.Data.Sqlite.dll and sqlite3_prepare_v2

[Frank Chang]

Richard Hipp or Igor Tadetnik,
        Here is the C# code for Microsoft.Data.Sqlite.dll, designed for
.NET users on Ubuntu Linux and Windows, I am using the latest version of
libsqlite3.so compiled as follows;

        gcc -shared -g -o libsqlite3.so -fPIC sqlite3.c


        I was able to get sqlite3_exec running properly calling it from an
C# DLLImport when I run mono Program.exe
        However, I am getting the following exception which says the return
value is being returned by slqite3_prepare_v2 is incorrect:

--- End of inner exception stack trace ---
  at Microsoft.Data.Sqlite.Interop.MarshalEx.ThrowExceptionForRC (Int32 rc,
Microsoft.Data.Sqlite.Interop.Sqlite3Handle db) <0x414bfb70 + 0x0008f> in
<filename unknown>:0
  at Microsoft.Data.Sqlite.SqliteCommand.ExecuteReader (CommandBehavior
behavior) <0x414c09e0 + 0x0022f> in <filename unknown>:0
  at Microsoft.Data.Sqlite.SqliteCommand.ExecuteReader () <0x414c09b0 +
0x00015> in <filename unknown>:0
  at Microsoft.Data.Sqlite.SqliteCommand.ExecuteNonQuery () <0x414c0860 +
0x0005e> in <filename unknown>:0
  at Microsoft.Data.Sqlite.Tests.Program.ManagedWrapperMainOne
(System.String Name) <0x414bbea0 + 0x0019b> in <filename unknown>:0
  at Microsoft.Data.Sqlite.Tests.Program.Main () <0x414bbd50 + 0x00013> in
<filename unknown>:0


   public static int sqlite3_prepare_v2(Sqlite3Handle db, string zSql, out
Sqlite3StmtHandle ppStmt, out string pzTail)
        {
            int nByte;
            var zSqlPtr = MarshalEx.StringToHGlobalUTF8(zSql, out nByte);

            try
            {
                // TODO: Something fancy with indexes?
                IntPtr pzTailPtr;
                var rc = sqlite3_prepare_v2(db, zSqlPtr, nByte, out ppStmt,
out pzTailPtr);
                pzTail = MarshalEx.PtrToStringUTF8(pzTailPtr);

                return rc;
            }
            finally
            {
                Marshal.FreeHGlobal(zSqlPtr);
            }
        }




        public new virtual SqliteDataReader ExecuteReader(CommandBehavior
behavior)
        {
            if ((behavior & ~(CommandBehavior.Default |
CommandBehavior.SequentialAccess | CommandBehavior.CloseConnection)) != 0)
            {
                throw new
ArgumentException(Strings.FormatInvalidCommandBehavior(behavior));
            }

            if (Connection == null
                || Connection.State != ConnectionState.Open)
            {
                throw new
InvalidOperationException(Strings.FormatCallRequiresOpenConnection("ExecuteReader"));
            }

            if (string.IsNullOrEmpty(CommandText))
            {
                throw new
InvalidOperationException(Strings.FormatCallRequiresSetCommandText("ExecuteReader"));
            }

            if (Transaction != Connection.Transaction)
            {
                throw new InvalidOperationException(
                    Transaction == null
                        ? Strings.TransactionRequired
                        : Strings.TransactionConnectionMismatch);
            }

            //TODO not necessary to call every time a command is executed.
Only on first command or when timeout changes
            NativeMethods.sqlite3_busy_timeout(Connection.DbHandle,
CommandTimeout * 1000);

            var hasChanges = false;
            var changes = 0;
            var stmts = new Queue<Tuple<Sqlite3StmtHandle, bool>>();
            var tail = CommandText;

            do
            {
                Sqlite3StmtHandle stmt;
                var rc = NativeMethods.sqlite3_prepare_v2(
                        Connection.DbHandle,
                        tail,
                        out stmt,
                        out tail);
                MarshalEx.ThrowExceptionForRC(rc, Connection.DbHandle);

                // Statement was empty, white space, or a comment
                if (stmt.IsInvalid)
                {
                    if (!string.IsNullOrEmpty(tail))
                    {
                        continue;
                    }

                    break;
                }

                var boundParams = 0;

                if (_parameters.IsValueCreated)
                {
                    boundParams = _parameters.Value.Bind(stmt);
                }

                var expectedParams =
NativeMethods.sqlite3_bind_parameter_count(stmt);
                if (expectedParams != boundParams)
                {
                    var unboundParams = new List<string>();
                    for (var i = 1; i <= expectedParams; i++)
                    {
                        var name =
NativeMethods.sqlite3_bind_parameter_name(stmt, i);

                        if (_parameters.IsValueCreated
                            ||

!_parameters.Value.Cast<SqliteParameter>().Any(p => p.ParameterName ==
name))
                        {
                            unboundParams.Add(name);
                        }
                    }
                    throw new
InvalidOperationException(Strings.FormatMissingParameters(string.Join(", ",
unboundParams)));
                }

                try
                {
                    var timer = Stopwatch.StartNew();
                    while (SQLITE_LOCKED == (rc =
NativeMethods.sqlite3_step(stmt)))
                    {
                        if (timer.ElapsedMilliseconds >= CommandTimeout *
1000)
                        {
                            break;
                        }

                        NativeMethods.sqlite3_reset(stmt);
                    }

                    MarshalEx.ThrowExceptionForRC(rc, Connection.DbHandle);
                }
                catch
                {
                    stmt.Dispose();
                    throw;
                }

                // NB: This is only a heuristic to separate SELECT
statements from INSERT/UPDATE/DELETE statements. It will result
                //     in unexpected corner cases, but it's the best we can
do without re-parsing SQL
                if (NativeMethods.sqlite3_stmt_readonly(stmt) != 0)
                {
                    stmts.Enqueue(Tuple.Create(stmt, rc != SQLITE_DONE));
                }
                else
                {
                    hasChanges = true;
                    changes +=
NativeMethods.sqlite3_changes(Connection.DbHandle);
                    stmt.Dispose();
                }
            }
            while (!string.IsNullOrEmpty(tail));

            var closeConnection = (behavior &
CommandBehavior.CloseConnection) != 0;

            return new SqliteDataReader(Connection, stmts, hasChanges ?
changes : -1, closeConnection);
        }



  Here is my SQL statement:
  var connMaryStr = new SqliteConnectionStringBuilder()
            { DataSource = new
Uri("file:db.sqlite",UriKind.Relative).ToString() };

            SqliteConnection connection = new SqliteConnection
(connMaryStr.ConnectionString); //"Filename=/home/venkat/Sandbox/mydb.db");

                        connection.Open();

            var insertCommand = new SqliteCommand("INSERT INTO message (
text ) VALUES (\"Hello World!\")",connection);
            insertCommand.ExecuteNonQuery();
            insertCommand.Close();


Any help is greatly appreciated.

3:28 AM (2 minutes ago)