CODEWITHSUNDEEP
sqlitejulia
Micoloth
Micoloth

Reputation: 89

How do you bind parameters in the Julia sqlite library?

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!

Upvotes: 4

Views: 441

Answers (2)

Sundar R
Sundar R

Reputation: 14705

(Similar to Przemyslaw Szufel's answer, just with named parameters like in the question.)

The documentation for DBInterface.execute (which the one for SQLite.execute recommends to be used) says:

DBInterface.execute(db::SQLite.DB, sql::String, [params]) DBInterface.execute(stmt::SQLite.Stmt, [params])

Bind any positional (params as Vector or Tuple) or named (params as NamedTuple or Dict) parameters to an SQL statement, given by db and sql or as an already prepared statement stmt, execute the query and return an iterator of result rows.

So you can pass on your Dict to execute directly as:

julia> insert_stmt = SQLite.Stmt(db, "INSERT INTO Student VALUES(:N, :G)")
julia> DBInterface.execute(insert_stmt, Dict(:N => "Palani", :G => 3))
SQLite.Query(SQLite.Stmt(SQLite.DB("mydb.sqlite"), 1), Base.RefValue{Int32}(101), Symbol[], Type[], Dict{Symbol, Int64}(), Base.RefValue{Int64}(0))

Upvotes: 3

Przemyslaw Szufel
Przemyslaw Szufel

Reputation: 42234

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

Upvotes: 2

Related Questions