Prepared statement with named parameters not working as expected

[slwu89]

Based on the limited documentation in the README, I thought using named parameters in the following way would work. I am not sure if this is a bug or if the named parameters feature is not capable of doing this. The error, which occurs when calling DBInterface.prepare as shown below, is pasted after the MWE.

using DuckDB, DataFrames

con = DBInterface.connect(DuckDB.DB, ":memory:")
DBInterface.execute(con, """
    CREATE TABLE tab (
        name TEXT PRIMARY KEY,
        data INTEGER
    );
""")

DBInterface.execute(con, """
    INSERT INTO tab VALUES
    ('a', 5),
    ('b', 10);
""")

DBInterface.execute(con, "SELECT * FROM tab;") |> DataFrame

# fails
stmt = DBInterface.prepare(con, """
    UPDATE tab
    SET data = :dat
    WHERE name IN :names;
""")
DBInterface.execute(stmt, (dat=50, names=["b"])) 

# works
stmt = DBInterface.prepare(con, """
    UPDATE tab
    SET data = ?
    WHERE name IN ?;
""")
DBInterface.execute(stmt, [50, ["b"]]) 
DBInterface.execute(con, "SELECT * FROM tab;") |> DataFrame

Error from DBInterface.prepare:

ERROR: Parser Error: syntax error at or near ":"

LINE 2:     SET data = :dat
                       ^
Stacktrace:
 [1] DuckDB.Stmt(con::DuckDB.Connection, sql::String, result_type::Type)
   @ DuckDB ~/.julia/packages/DuckDB/SPkZM/src/statement.jl:18
 [2] prepare
   @ ~/.julia/packages/DuckDB/SPkZM/src/result.jl:853 [inlined]
 [3] prepare
   @ ~/.julia/packages/DuckDB/SPkZM/src/result.jl:854 [inlined]
 [4] prepare(db::DuckDB.DB, sql::String)
   @ DuckDB ~/.julia/packages/DuckDB/SPkZM/src/result.jl:855
 [5] top-level scope
   @ ~/Desktop/misc/dbupdate.jl:20

[slwu89]

I note that in the stacktrace all of the errors are coming from DuckDB. Therefore I tried again with SQLite but got more or less the same problem (stacktrace below). Therefore I believe it seems like I misunderstood the README and the documentation should be updated to clearly tell what named parameters are able to do, as I am unsure now.

julia> stmt = DBInterface.prepare(con, """
           UPDATE tab
           SET data = :dat
           WHERE name IN :names;
       """)
ERROR: SQLiteException("near \":names\": syntax error")
Stacktrace:
 [1] sqliteerror(args::SQLite.DB)
   @ SQLite ~/.julia/packages/SQLite/UqCGE/src/SQLite.jl:34
 [2] macro expansion
   @ ~/.julia/packages/SQLite/UqCGE/src/base.jl:10 [inlined]
 [3] prepare_stmt_wrapper
   @ ~/.julia/packages/SQLite/UqCGE/src/SQLite.jl:110 [inlined]
 [4] SQLite.Stmt(db::SQLite.DB, sql::String; register::Bool)
   @ SQLite ~/.julia/packages/SQLite/UqCGE/src/SQLite.jl:147
 [5] Stmt
   @ ~/.julia/packages/SQLite/UqCGE/src/SQLite.jl:146 [inlined]
 [6] prepare(db::SQLite.DB, sql::String)
   @ SQLite ~/.julia/packages/SQLite/UqCGE/src/SQLite.jl:181
 [7] top-level scope
   @ ~/Desktop/misc/dbupdate.jl:22

[schlichtanders]

I am running into the same confusion. Positional interpolation using ? works seamlessly, but the colon makes problems everywhere.