Unable to run executemany on SQLite.DB connection

[jg-at-work]

When trying to run executemany on an DB connection made with SQLite.jl, it fails.
I am new to Julia, I do not understand exactly the issue yet.
Somehow the statement (stmt) is of the wrong type. maybe it is an issue within SQLite.js, but I do not know.

Iam using DBInterface v2.5.0 and SQLite v1.3.0
Here is a minimum working example

using DBInterface
using SQLite

conn = SQLite.DB("db.sqlite")
println("Conn type is: "*string(typeof(conn)))
DBInterface.execute(conn,"pragma journal_mode=OFF")
DBInterface.execute(conn,"pragma synchronous=OFF")
DBInterface.execute(conn,"pragma cache_size=100000")
DBInterface.execute(conn,"CREATE TABLE DATA (ID INTEGER , Name TEXT)")
DBInterface.execute(conn,"INSERT INTO DATA (ID, Name) VALUES (1,\"FOO\")")
stmt = DBInterface.prepare(conn, "INSERT INTO DATA (ID, Name) VALUES(:col1, :col2)")
println("stmt type is: "*string(typeof(stmt)))
DBInterface.execute(stmt, (col1=2, col2="FOO"))
DBInterface.executemany(stmt, (col1=[1,2,3], col2=["A","B","C"]))

The error happens in the last line.
Output:

Conn type is: SQLite.DB
stmt type is: SQLite.Stmt
ERROR: LoadError: MethodError: no method matching getconnection(::SQLite.Stmt)
@ DBInterface ~/.julia/packages/DBInterface/1Gmxx/src/DBInterface.jl:193

This is the line :

DBInterface.jl/src/DBInterface.jl

Line 193 in a2734ab

transaction(getconnection(stmt)) do

[jg-at-work]

Is this related ?
JuliaDatabases/SQLite.jl#246
Do I need to use SQLite v1.4.0 (currently not released yet ) ?

[jg-at-work]

I installed the current code (JuliaDatabases/SQLite.jl@9724a17) for SQLite.jl (v.1.4.0) and tried successfully with a slightly updated code:

conn = SQLite.DB("db.sqlite")
stmt = SQLite._Stmt(conn,"pragma journal_mode=OFF;pragma synchronous=OFF;pragma cache_size=100000")
SQLite.bind!(stmt, ())
SQLite.sqlite3_step(stmt.handle)
SQLite.sqlite3_reset(stmt.handle)
DBInterface.execute(conn,"CREATE TABLE DATA (ID INTEGER , Name TEXT)")
stmt = DBInterface.prepare(conn, "INSERT INTO DATA (ID, Name) VALUES(:col1, :col2)")
DBInterface.executemany(stmt, (col1=[1,2,3], col2=["A","B","C"]))

But according to JuliaDatabases/SQLite.jl#246 (comment) the executemany is not going to sped up transcations. My goal is to have a high transaction speed for inserting large data quickly. It would be great to be able to do this on a high-level interface like with executemany.

[elimisteve]

MethodError: no method matching getconnection(::SQLite.Stmt)

I am having this same issue and would like to use the functionality added at JuliaDatabases/SQLite.jl@9724a17 .

@quinnj Is SQLite 1.4.0 stable yet? Which version of SQLite should we use so that we can use ^^that^^ without getting the getconnection(::SQLite.Stmt) error?

Thanks!

[elimisteve]

Dear future reader,

I decided to add SQLite#master, it pulled down v1.4.0 from master on GitHub, then this worked:

db = SQLite.DB("db.sqlite")
DBInterface.execute(db, "CREATE TABLE DATA (ID INTEGER, Name TEXT)")
stmt = SQLite.Stmt(db, "INSERT INTO DATA (ID, Name) VALUES (:my_id, :name)")
DBInterface.executemany(stmt, (my_id=[1,2,3], name=["A","B","C"]))

[jg-at-work]

In a performant way ? Could you post some speed results ? rows/seconds ?

[elimisteve]

30,000 inserts/second. 10 columns of (generally quite short) text per row.

[elimisteve]

(348,000 inserts in 10.5 seconds.)

[jg-at-work]

Thank you very much for the info !

[elimisteve]

@jg-at-work Sure! Were you able to insert even faster with your lower-level performance hacks? Hopefully they're not necessary anymore!

(Those benchmarks are from my laptop, FYI, with an NVMe SSD.)