# DBInterface prepared statement with named parameters not working
**URL:** https://discourse.julialang.org/t/dbinterface-prepared-statement-with-named-parameters-not-working/129287
**Category:** Data
**Created:** [May 23, 2025, 10:24pm UTC](https://discourse.julialang.org/t/dbinterface-prepared-statement-with-named-parameters-not-working/129287 "2025-05-23T22:24:31Z")
**Posts on this page:** 7
**Page:** 1
### Author:  [@slwu89](https://discourse.julialang.org/u/slwu89)
#### Post date: [May 23, 2025, 10:24pm UTC](https://discourse.julialang.org/t/dbinterface-prepared-statement-with-named-parameters-not-working/129287/1 "2025-05-23T22:24:31Z")
Based on reading the docs at [GitHub - JuliaDatabases/DBInterface.jl: Database interface definitions for Julia](https://github.com/JuliaDatabases/DBInterface.jl), 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?
```julia
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
```
---
### Author:  [@jd-foster](https://discourse.julialang.org/u/jd-foster)
#### Post date: [May 24, 2025, 12:03am UTC](https://discourse.julialang.org/t/dbinterface-prepared-statement-with-named-parameters-not-working/129287/2 "2025-05-24T00:03:07Z")
> [@slwu89](#):
>
> `DBInterface.execute(stmt, (:dat=50, :names=["b"])) `
Try
```julia
(dat=50, names=[“50”])
```
without the leading colon?
---
### Author:  [@slwu89](https://discourse.julialang.org/u/slwu89)
#### Post date: [May 24, 2025, 12:31am UTC](https://discourse.julialang.org/t/dbinterface-prepared-statement-with-named-parameters-not-working/129287/3 "2025-05-24T00:31:29Z")
Ah, that may also be wrong but no that doesn’t fix the problem, which occurs here:
```julia
stmt = DBInterface.prepare(con, """
UPDATE tab
SET data = :dat
WHERE name IN :names;
""")
```
The error
```julia
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
```
---
### Author:  [@jd-foster](https://discourse.julialang.org/u/jd-foster)
#### Post date: [May 28, 2025, 4:31am UTC](https://discourse.julialang.org/t/dbinterface-prepared-statement-with-named-parameters-not-working/129287/4 "2025-05-28T04:31:33Z")
In DuckDB, named parameters in prepared statements use `$`:
> **[Prepared Statements](https://duckdb.org/docs/stable/sql/query_syntax/prepared_statements#named-parameters-parameter)**
>
> DuckDB supports prepared statements where parameters are substituted when the query is executed. This can improve readability and is useful for preventing SQL injections. Syntax There are three syntaxes for denoting parameters in prepared statements:...
Of course in Julia, `$` is an interpolation operator, so we need to escape it. So the latter part of your code might instead be
```julia
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
```julia
2×2 DataFrame
Row │ name data
│ String Int32
─────┼───────────────
1 │ a 5
2 │ b 50
```
---
### Author:  [@slwu89](https://discourse.julialang.org/u/slwu89)
#### Post date: [May 28, 2025, 2:21pm UTC](https://discourse.julialang.org/t/dbinterface-prepared-statement-with-named-parameters-not-working/129287/5 "2025-05-28T14:21:19Z")
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](https://github.com/JuliaDatabases/DBInterface.jl?tab=readme-ov-file#for-users)). 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?
---
### Author:  [@jd-foster](https://discourse.julialang.org/u/jd-foster)
#### Post date: [May 29, 2025, 1:09am UTC](https://discourse.julialang.org/t/dbinterface-prepared-statement-with-named-parameters-not-working/129287/6 "2025-05-29T01:09:18Z")
I’m not fully across this, but the interpolation does look to be dependent on the backend.
However [GitHub - JuliaAPlavin/SQLCollections.jl](https://github.com/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.
---
### Author:  [@slwu89](https://discourse.julialang.org/u/slwu89)
#### Post date: [May 29, 2025, 1:19am UTC](https://discourse.julialang.org/t/dbinterface-prepared-statement-with-named-parameters-not-working/129287/7 "2025-05-29T01:19:52Z")
Thanks. I’ll check that out.