SQLITE: EXPLAIN requires adding values for all query's parameters

[lsoft]

I am not very familiar with SQLite and Microsoft.Data.SQLite in particular, but it's unclear for me why Microsoft.Data.SQLite requires a values for a query's parameters for EXPLAIN queries.

Please take a look at Visual Studio unit test screenshot

it fails with the exception 'Must add values for the following parameters: @A'.

At the same time 'DB browser for SQLite' process this query with no problem:

Looks like SQLite allows such things.

My task is to validate query syntax. My way is to add an 'explain' term in the head of the query and call ExecuteNonQuery.
How can I execute EXPLAIN-queries without parameter's values?

Thanks!

[ajcvickers]

@bricelam Backlog/enhancement?

[bricelam]

👍 We can use sqlite3_stmt_isexplain to special case these statements.

[lsoft]

any workaround exists?

[bricelam]

Provide dummy parameter values

[lsoft]

Validation class works like a service, it does not know the list of parameters for each checked query. I can split statement to term (word) list, and take those that begin with @, but it does not look good enough... anyway thanks for the answer! :)

[bricelam]

Written in notepad, so it may not compile, but this should get you started in the right direction:

static void AddDummyParameters(this SqliteCommand command)
{
    var db = command.Connection.Handle;
    
    var remainingSql = command.CommandText;
    while (!string.IsNullOrEmpty(remainingSql))
    {
        var rc = raw.sqlite3_prepare_v2(db, remainingSql, out var stmt, out remainingSql);
        SqliteException.ThrowExceptionForRC(rc, db);
        
        if (stmt.ptr == IntPtr.Zero)
        {
            // Statement was empty, white space, or a comment
            continue;
        }
        
        using (stmt)
        {        
            var count = raw.sqlite3_bind_parameter_count(stmt);
            for (var i = 1; i <= count; i++)
            {
                var name = raw.sqlite3_bind_parameter_name(stmt, i);
                
                // Add a dummy value
                command.Parameters.AddWithValue(name, "dummy");
            }
        }
    }
}

[lsoft]

@bricelam it works, thank you!

[bricelam]

@AlexanderTaeschner Are you bored? lol, here's a good one if you're interested.

[AlexanderTaeschner]

Not really bored, but interested. Unfortunately the sqlite3_stmt_isexplain is not exposed by SQLitePCL yet (opened ericsink/SQLitePCL.raw#326), so that the current PR is a bit hacky.

[bricelam]

Ah, I didn’t realize that. Maybe I’ll prepare a few PRs for SQLitePCLRaw. I think we’re blocked on a few missing APIs in there.

[AlexanderTaeschner]

I just added PR ericsink/SQLitePCL.raw#328 to add sqlite3_stmt_isexplain to SQLitePCLRaw.