SQLite/DBInterface confusion

General Usage
1

Looks like something’s changed since i last used it.

I went to the doc’s and saw this,

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

huh. i don’t remember DBInterface… ok, installed it, then tried:

   db_fname = "test1.db"
    db = SQLite.DB(db_fname)
...
DBInterface.execute(db, sql)

where sql is a string containing an insert statement which has been verifed to work. I received this error. I can’t quite figure out what’s going on. The docs say SQLite.DB should be ok for the first argument, but it looks like I need a DBInterface.something-or-other.

ERROR: LoadError: MethodError: no method matching execute(::SQLite.DB, ::String)
Closest candidates are:
  execute(!Matched::DBInterface.Statement, ::Any) at C:\Users\denheyeb\.julia\packages\DBInterface\EUwXt\src\DBInterface.jl:79
  execute(!Matched::DBInterface.Connection, ::AbstractString) at C:\Users\denheyeb\.julia\packages\DBInterface\EUwXt\src\DBInterface.jl:81
  execute(!Matched::DBInterface.Connection, ::AbstractString, !Matched::Any) at C:\Users\denheyeb\.julia\packages\DBInterface\EUwXt\src\DBInterface.jl:81

I looked at the DBInterface and it seems like maybe i simply need to create the connection using that instead ? Before I start randomly trying things, I was hoping to gain some insight into how it’s supposed to be used.

Thanks!

2

Try:

SQLite.execute(db, sql)

Looking at the source:

That should be what you are trying to call. My guess is there was a copy/paste error…the latest source appears to have the correct documentation. So my guess is that the site just hasn’t been updated yet.

3

That’s very confusing. So all of the DBInstance stuff is old or new ?

Because later on in the docs is an example that specifically refers to using DBInstance, but the example does not work, because there is no “using DBInterface” in the code.

Meanwhile here is what I used that worked…

using SQLite

function main()
    dbname = "test1.db"
    db = SQLite.DB(dbname)
    sql = "insert into Test1 (field1, field2, field3) values (\"foo\", 10, 1.0)"
    SQLite.execute(db, sql)
end

main()

the other thing that’s confusing and I see this a lot and have never quite understood the reason, is the use of

SQLite.execute

instead of just

execute

Using execute alone in my example does NOT work. I assume this simply happens because the SQLite (and many other packages) simply haven’t exported DB or execute ??

In this particular instance I went and looked at the code

export DBInterface

is what I see, which perhaps explains the problem. This seems very much like a newbie trap.

How do i access functions ?
just use “using ModuleName”.
I am and it doesn’t work.
Oh! sometimes you have to use ModuleName.functionName.
???

Thanks for your help.

BTW, only one ‘L’ in SQLite :slight_smile:

4

This is not going smoothly…

julia> using SQLite

julia> db=SQLite.DB("test1.db")
SQLite.DB("test1.db")

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

100 ? i have no idea why it’s returning that, but it’s the only answer I ever get no matter how i construct the query…
It used to be that you invoked Query directly, but the interface has definitely changed and that is no longer the case.
So, then i went back to try DBInterface, i.e.

using DBInterface

DBInterface.execute(db, "select * from Test1")

and that worked.

ugh…

5

You could try:

dump(SQLite.execute(db,"select * from Test1"))

That will show you the object. My guess is they implemented a show method for whatever that method returns…and it’s not showing much. Again looking at the code maybe try:

a = SQLite.execute(db,"select * from Test1")
getvalue(a, 1, Int)

Not sure if getvalue() should be prefixed with SQLite.

1 Like
6

Well everything is working now. I had a very strange combination of problems involving my version of julia, and the installation of DBInterface.

What’s extremely unfortunate is that i have a large amount of SQLite code which is now broken unless you use Julia 1.2 + SQLite, since the Query interface is now gone, or appears to be gone.

:frowning:

Also there is still the very confusing aspect that SQLite.execute works but does NOT work for queries. then you have to use DBInterface.execute. That’s very confusing and I can’t believe I will be the only one tripped up by it.

3 Likes