Sqlite database remains in use by Julia REPL

New to Julia
1

I have a small program that creates sqlite database with a single table. Inserts some data into the table.
In the end of program execution database file remains in use by Julia. I have to kill REPL terminal to release it (previously created db file needs to be deleted in the beginning of execution).
How do I close that database connection? I’ll appreciate help of the experts - been google searching for days now - no luck!
function createdb()
try
if isfile(“outputs.db”)
# delete existing outputs.db
rm(“outputs.db”)
end
catch
println(“Can’t delete”)
db = SQLite.DB(“outputs.db”)
SQLite.execute(db, “DROP TABLE data”)
end
db = SQLite.DB(“outputs.db”)
SQLite.execute(db, “CREATE TABLE IF NOT EXISTS data(entity TEXT, date TEXT, varname TEXT, value TEXT)”)
return db
end

function writeToDB()
df = readfilecsv() #populate data frame from csv
db = createdb()
for row in 1:size(df, 1)
entName = df[row,1]
date = df[row,2]
varName = df[row,3]
value = df[row,4]

    stmt = SQLite.Stmt(db, "INSERT OR REPLACE INTO data (entity, date, varname, value) VALUES (?, ?, ?, ?)")
    DBInterface.execute(stmt, (entName, date, varName, value))
    #DBInterface.close!(db) - This throws an error that db is null
end

end

2

I see you are a first time poster - welcome to the Julia community! :wave:

You should close the connection, not the db variable.

Also, could you please use code fencing in your post?

Surround your code with “```” to produce code fencing.
Example:

```
println("foo")
```

which would give

println("foo")
3

Welcome .
The answer let me wonder so I checked the docs and I got:

The SQLite.DB will be automatically closed/shutdown when it goes out of scope (i.e. the end of the Julia session, end of a function call wherein it was created, etc.)

and

The SQLite.Stmt will be automatically closed/shutdown when it goes out of scope (i.e. the end of the Julia session, end of a function call wherein it was created, etc.), but you can close DBInterface.close!(stmt) to explicitly and immediately close the statement.

So you can do:

DBInterface.close!(stmt)

but it seems to be not necessary.

But you actual problem:

isn’t really addressed because it shouldn’t happen, isn’t it ?

5

Thank you! I’ve tried closing my stmt

 stmt = SQLite.Stmt(db, "INSERT OR REPLACE INTO data (entity, date, varname, value) VALUES (?, ?, ?, ?)")
        DBInterface.execute(stmt, (entName, date, varName, value))
        DBInterface.close!(stmt)

But that doesn’t help. I don’t have a variable representing connection in my code - I operate on that db object: The SQLite.DB object represents a single connection to an SQLite database. I can’t figure out why it remains in use after the execution…

6

Did some experiments with it and come to the solution, that it is some kind of a bug, because:

julia> cd("D:\\Temp")

julia> using SQLite

julia> function createdb()
       db = SQLite.DB("outputs.db")
       SQLite.execute(db, "CREATE TABLE IF NOT EXISTS data(entity TEXT, date TEXT, varname TEXT, value TEXT)")
       end
createdb (generic function with 1 method)

julia> createdb()
101

#can't delete outputs.db now

julia> GC.gc()

# CAN DELETE outputs.db NOW

So it needs a garbage collection to become really out of scope.

I don’t think this is intended.
As this is not yet in the issues, it may be important, that I am experienced this on Windows:

julia> versioninfo()
Julia Version 1.6.2
Commit 1b93d53fc4 (2021-07-14 15:36 UTC)
Platform Info:
  OS: Windows (x86_64-w64-mingw32)
  CPU: AMD Ryzen 9 3900X 12-Core Processor
  WORD_SIZE: 64
  LIBM: libopenlibm
  LLVM: libLLVM-11.0.1 (ORCJIT, znver2)

@Marie: do you want to open an issue here: https://github.com/JuliaDatabases/SQLite.jl/issues ?
You can link to this discussion.

7

Thanks so much for your help and time!!! Just submitted an issue.
Currently solved by adding GC.gc() prior to deleting existing db:

function createdb()
    GC.gc() #current julia bug: explicit garbage collection is needed to release existing outputs.db
      if isfile("outputs.db")
        rm("outputs.db")
      end
.....
end
2 Likes