Problem with Sqlite Query - How to convert the code from SQLite v0.9.0 to v1.0.0

Question

I am prettry new to Julia and I am just playing around, and suddenly the following code starts throwing errors, but it has worked in the past.

using SQLite
db = SQLite.DB("db")
data = SQLite.Query(db,"SELECT * FROM d")

throws:

ERROR: LoadError: MethodError: no method matching SQLite.Query(::SQLite.DB, ::String)

can someone please enlighten me wha the problem is? Thank you.

I also tried with lower case: query.

Answer 1

Here is a short MWE of differences using SQLLite (v0.9.0 vs v1.0.0) with the current Julia version (1.3.1).

You do not have the table so you need to create it first:

using SQLite
using DataFrames
db = SQLite.DB("db")
# v0.9.0
SQLite.Query(db,"CREATE TABLE d (col1 INT, col2 varchar2(100))")
# v1.0.0
DBInterface.execute(db,"CREATE TABLE d (col1 INT, col2 varchar2(100))")

Now you can check if the table exits:

julia> SQLite.tables(db) |> DataFrame
1×1 DataFrames.DataFrame
│ Row │ name    │
│     │ String⍰ │
├─────┼─────────┤
│ 1   │ d       │

Let's insert some rows (note how one should sepearate data from SQL code via precompiled statements):

stmt = SQLite.Stmt(db, "INSERT INTO d (col1, col2) VALUES (?, ?)")
#v0.9.0
SQLite.execute!(stmt; values=(1, "Hello world"))
SQLite.execute!(stmt; values=(2, "Goodbye world"))
#v1.0.0
DBInterface.execute(stmt, (1, "Hello world"))
DBInterface.execute(stmt, (2, "Goodbye world"))

Now let us get the data

v0.9.0

julia> data = SQLite.Query(db,"SELECT * FROM d") |> DataFrame
3×2 DataFrame
│ Row │ col1   │ col2          │
│     │ Int64⍰ │ String⍰       │
├─────┼────────┼───────────────┤
│ 1   │ 1      │ Hello world   │
│ 2   │ 2      │ Goodbye world │

v1.0.0

julia> data = DBInterface.execute(db, "select * from d") |> DataFrame
3×2 DataFrame
│ Row │ col1   │ col2          │
│     │ Int64⍰ │ String⍰       │
├─────┼────────┼───────────────┤
│ 1   │ 1      │ Hello world   │
│ 2   │ 2      │ Goodbye world │