SQLite and DBInterface prepared statement error

Specific Domains Data
1

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.

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:

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
2

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> 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> 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? 
─────┴───────────────────
1 Like
3

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).

1 Like
4

Hm ok, maybe there is just no list datatype at all in SQLite: Datatypes In SQLite. I’ll have to wait for someone more knowledgable to confirm, but that seems the case. Unfortunate.

5

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.

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
1 Like
6

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. 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.