DBInterface prepared statement with named parameters not working

Specific Domains Data
1

Based on reading the docs at GitHub - JuliaDatabases/DBInterface.jl: Database interface definitions for Julia, I thought that I could use named parameters in a prepared statement to update data, which did not work. However, the positional parameters worked fine. Can someone help me understand what I did wrong?

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
2

Try

(dat=50, names=[“50”])

without the leading colon?

1 Like
3

Ah, that may also be wrong but no that doesn’t fix the problem, which occurs here:

stmt = DBInterface.prepare(con, """
    UPDATE tab
    SET data = :dat
    WHERE name IN :names;
""")

The error

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/tmp.jl:20