# SQLite and DBInterface prepared statement error
**URL:** https://discourse.julialang.org/t/sqlite-and-dbinterface-prepared-statement-error/128882
**Category:** Data
**Created:** [May 9, 2025, 9:04pm UTC](https://discourse.julialang.org/t/sqlite-and-dbinterface-prepared-statement-error/128882 "2025-05-09T21:04:13Z")
**Posts on this page:** 6
**Page:** 1
### Author:  [@slwu89](https://discourse.julialang.org/u/slwu89)
#### Post date: [May 9, 2025, 9:04pm UTC](https://discourse.julialang.org/t/sqlite-and-dbinterface-prepared-statement-error/128882/1 "2025-05-09T21:04:13Z")
Hi all. Does SQLite.jl support DBInterface.jl prepared statements? My MWE is below. When running the `DBInterface.prepare` line I get an error (pasted below the MWE). On `[0aa819cd] SQLite v1.6.1`. When using DuckDB.jl as the database backend instead, for example, there is no problem.
```julia
using SQLite, DuckDB, DataFrames
# con = DBInterface.connect(DuckDB.DB, ":memory:")
con = DBInterface.connect(SQLite.DB, ":memory:")
DBInterface.execute(con, """
CREATE TABLE tab (
name TEXT,
val NUMERIC
);
""")
DBInterface.execute(con, """
INSERT INTO tab VALUES
('a', 5),
('b', 6),
('c', 5),
('a' ,3);
""")
stmt = DBInterface.prepare(con, "SELECT * FROM tab WHERE name IN ?;")
DBInterface.execute(stmt, [["a", "b"]]) |> DataFrame
DBInterface.close!(con)
```
Error:
```julia
ERROR: SQLiteException("near \"?\": 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/sqlite_bug.jl:26
```
---
### Author:  [@drizk1](https://discourse.julialang.org/u/drizk1)
#### Post date: [May 9, 2025, 9:37pm UTC](https://discourse.julialang.org/t/sqlite-and-dbinterface-prepared-statement-error/128882/2 "2025-05-09T21:37:38Z")
im not super familiar with SQLite or prepared statements, but i got it work like this. But i think having to add a `?` for each value you want to pack in seems tedious, and not ideal.
```julia
julia> stmt = DBInterface.prepare(con, "SELECT * FROM tab WHERE name IN (?, ?)")
SQLite.Stmt(SQLite.DB("./test.db"), Base.RefValue{Ptr{SQLite.C.sqlite3_stmt}}(Ptr{SQLite.C.sqlite3_stmt} @0x0000000318177530), Dict{Int64, Any}())
julia> DBInterface.execute(stmt, ["a", "b"]) |> DataFrame
3×2 DataFrame
Row │ name val
│ String Int64
─────┼───────────────
1 │ a 5
2 │ b 6
3 │ a 3
```
where as this is not the right result
```julia
julia> stmt = DBInterface.prepare(con, "SELECT * FROM tab WHERE name IN (?)")
SQLite.Stmt(SQLite.DB("./test.db"), Base.RefValue{Ptr{SQLite.C.sqlite3_stmt}}(Ptr{SQLite.C.sqlite3_stmt} @0x0000000318174b00), Dict{Int64, Any}())
julia> DBInterface.execute(stmt, [["a", "b"]]) |> DataFrame
0×2 DataFrame
Row │ name val
│ String? Float64?
─────┴───────────────────
```
---
### Author:  [@slwu89](https://discourse.julialang.org/u/slwu89)
#### Post date: [May 9, 2025, 9:41pm UTC](https://discourse.julialang.org/t/sqlite-and-dbinterface-prepared-statement-error/128882/3 "2025-05-09T21:41:03Z")
Thanks. Yeah that is interesting. In my case I don’t know the length of the list I hoped to bind to `?` during the program execution so I can’t add a `?` for each value in the list. Interestingly, DuckDB.jl is able to handle this case with no problem.
(I edited my MWE to just use an in-memory DB and added the DuckDB connection).
---
### Author:  [@slwu89](https://discourse.julialang.org/u/slwu89)
#### Post date: [May 9, 2025, 9:44pm UTC](https://discourse.julialang.org/t/sqlite-and-dbinterface-prepared-statement-error/128882/4 "2025-05-09T21:44:27Z")
Hm ok, maybe there is just no list datatype at all in SQLite: [Datatypes In SQLite](https://sqlite.org/datatype3.html). I’ll have to wait for someone more knowledgable to confirm, but that seems the case. Unfortunate.
---
### Author:  [@drizk1](https://discourse.julialang.org/u/drizk1)
#### Post date: [May 9, 2025, 9:52pm UTC](https://discourse.julialang.org/t/sqlite-and-dbinterface-prepared-statement-error/128882/5 "2025-05-09T21:52:25Z")
String interpolation is another option, but also may not be ideal either. DuckDB in general is just lovely. It may work to open the sql database in duckdb, and then from there you could use duckdb to query.
```julia
vals = ["a", "b"]
list = join(["'" * v * "'" for v in vals], ", ")
DBInterface.execute(con, "SELECT * FROM tab WHERE name IN ($list)") |>DataFrame
3×2 DataFrame
Row │ name val
│ String Int64
─────┼───────────────
1 │ a 5
2 │ b 6
3 │ a 3
```
---
### Author:  [@slwu89](https://discourse.julialang.org/u/slwu89)
#### Post date: [May 9, 2025, 10:32pm UTC](https://discourse.julialang.org/t/sqlite-and-dbinterface-prepared-statement-error/128882/6 "2025-05-09T22:32:14Z")
Thanks. I would have gone with DuckDB straightaway except that SQLite’s support for concurrency is much more advanced that DuckDB [SQLite Frequently Asked Questions](https://www.sqlite.org/faq.html#q5). Maybe I’ll have to redesign my application to use DuckDB and work around some of the concurrency limitations, or look at other database libraries.