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
4

In DuckDB, named parameters in prepared statements use $:

Of course in Julia, $ is an interpolation operator, so we need to escape it. So the latter part of your code might instead be

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

giving

2×2 DataFrame
 Row │ name    data
     │ String  Int32
─────┼───────────────
   1 │ a           5
   2 │ b          50
1 Like
5

Thanks @jd-foster, I see. In that case, I am having trouble understanding the DBInterface docs (prepare a statement with named parameters GitHub - JuliaDatabases/DBInterface.jl: Database interface definitions for Julia). I thought I was using the correct interface. Or do you mean that there is no backend-agnostic way to use named parameters and it has to be changed for DuckDB, SQLite, etc?

6

I’m not fully across this, but the interpolation does look to be dependent on the backend.
However GitHub - JuliaAPlavin/SQLCollections.jl might be useful here as the README tells us

There are no special parsing or interpolation rules to memorize, resulting in less implicit behavior.

1 Like
7

Thanks. I’ll check that out.