documentation seems to have some issues

[PinkMushroom]

I recently tried to use SQLite and ran into several issues.

The example in the doc that uses the Chinook DB is broken. it doesn't contain a "using DBInterface" line, and so the first use of DBInterface causes an error, i.e.,

DBInterface.execute(db, "SELECT FirstName, LastName FROM Employee WHERE LastName REGEXP 'e(?=a)'") |> DataFrame

doesn't work.

Using

SQLite.execute(db, sql)

as a query will succeed, i.e. will not cause an error, but seems to simply return an integer.
however,

DBInterface.execute(db, sql)

does work correctly.

It appears that DBInterface is the long term interface to SQLite. However it seems like some things, like the query, should use DBInterface, while others, like tables(), should use SQLite.

What I can offer is my very simple example test code to help people get started :-)

using SQLite
using DataFrames
using DBInterface

# DROP TABLE IF EXISTS Test1;

# CREATE TABLE Test1 (
#        field1 TEXT,
#        field2 INTEGER,
#        field3 REAL
# );

# to create the DB
# cat test1.sql | sqlite3 test1.db

# quick and easy way to see what's in it
# echo "select * from Test1" | sqlite3 test1.db

function main()
    dbname = "test1.db"
    db = SQLite.DB(dbname)
    x = rand(1:100)
    y = randn()
    sql = "insert into Test1 (field1, field2, field3) values (\"foo\", $(x), $(y))"
    SQLite.execute(db, sql)

    tables = SQLite.tables(db)
    println(tables)

    cols = SQLite.columns(db, "Test1")
    println(cols)
    println(cols.name)
    println(cols.type)

    sql = "select * from Test1"
    # this isn't working. always returns 100 ...
    # s = SQLite.execute(db, sql)
    s = DBInterface.execute(db, sql)

    for r in s
        println(r)
    end

    s = DBInterface.execute(db, sql) |> DataFrame

    println(s)
    
end

main()

[felipenoris]

Regarding the using DBInterface part, as long as you execute using SQLite the DBInterface should be available since SQLite exports it.

SQLite.jl/src/SQLite.jl

Line 6 in 70ac515

export DBInterface

[PinkMushroom]

oh. i know what happened...
I did not have DBInterface installed. That's why i first got the errors.
I just tried it again, and it worked without having to explicitly say "using DBInterface". That is for an insert.

The SQLite.execute does this :

'''julia> DBInterface.execute(db, "select * from Test1")
SQLite.Query(SQLite.Stmt(SQLite.DB("test1.db"), Ptr{Nothing} @0x00000000011636c8, Dict{Int64,Any}(), 100), Base.RefValue{Int32}(100), Symbol[:field1, :field2, :field3], Type[Union{Missing, String}, Union{Missing, Int64}, Union{Missing, Float64}], Dict(:field3 => 3,:field1 => 1,:field2 => 2))
'''
Now that I look at it looks like maybe it's actually returning some sort of query statement, but just isn't executing it ?

[PinkMushroom]

doh! That's not what i meant to show, this is what I meant to do

julia> SQLite.execute(db, "select * from Test1")
100

[felipenoris]

The DBInterface.execute returns a pointer to a Query result, without collecting the data.

You can collect the data using something like:

using DataFrames
DBInterface.execute(db, "select * from Test1") |> DataFrame

You must have DataFrames previously installed for this example to work.

If you install the latest SQLite, then DBInterface should be installed along with SQLiteautomatically, since it is a dependency.