How do you bind parameters in the Julia sqlite library?

Question

I'm trying to use the Julia SQLite.jl library, but I can't figure out how to bind variables.

using SQLite

# Create DB and table
db = SQLite.DB("mydb.sqlite")
SQLite.createtable!(db, "Student", Tables.Schema((:Name, :Grade), (String, Int64)); temp=false, ifnotexists=true)

# Add vals
SQLite.execute(db, "INSERT INTO Student VALUES('Harry', 1)")

# Prepared statement: Can use: ?, ?NNN, :AAA, $AAA, @AAA

insert_stmt = SQLite.Stmt(db, "INSERT INTO Student VALUES(:N, :G)")
SQLite.bind!(insert_stmt, Dict("N"=>"George", "G"=>"4"))
# This fails, with error:  SQLiteException("`:N` not found in values keyword arguments to bind to sql statement")

insert_stmt = SQLite.Stmt(db, "INSERT INTO Student VALUES(:N, :G)")
SQLite.bind!(insert_stmt, Dict(:N=>"George", :G=>"4"))
SQLite.execute(insert_stmt)
# This fails, with error:  SQLiteException("values should be provided for all query placeholders")

insert_stmt = SQLite.Stmt(db, "INSERT INTO Student VALUES(?1, ?2)")
SQLite.bind!(insert_stmt, ["George", "4"])
SQLite.execute(insert_stmt)
# This fails, with error: SQLiteException("values should be provided for all query placeholders")

insert_stmt = SQLite.Stmt(db, "INSERT INTO Student VALUES(':N', ':G')")
SQLite.bind!(insert_stmt, Dict(:N=>"George", :G=>"4"))
SQLite.execute(insert_stmt) 
# This doesn't bind, it inserts ':N' and ':G'

What's the right syntax? Thanks!

Answer 1

You could try:

stmt = SQLite.Stmt(db, "INSERT INTO Student VALUES(?, ?)")
DBInterface.execute(stmt, ["Jack",2])

Let's check if this worked:

julia> DBInterface.execute(db, "SELECT * FROM Student") |> DataFrame
2×2 DataFrame
 Row │ Name    Grade
     │ String  Int64
─────┼───────────────
   1 │ Harry       1
   2 │ Jack        2